Rules of row and column access control

The rules of row and column access control apply to both read and write operations on a table. The conditions that are specified in row permissions and column masks apply to both data retrieval operations and data change operations.

Begin general-use programming interface information.

The following table shows an example of how row and column access control rules are applied depending on the types of data operations. Assume that tables T1 and T2 are activated for row and column access control and that both tables include columns C1 and C2.

Table 1. Rules and access types for row and column access control
SQL statement	Row permission	Column mask (defined for column C1)
SELECT SUBSTR( C1,8,4) FROM T1;	If user-defined row permissions exist for the table, only the rows that satisfy the permissions are returned. If no user-defined row permissions exist for the table, the default row permission is applied and no row is returned.	The column mask is applied to column C1 that is referenced in the select list of the outermost SELECT clause. It does not interfere with the operations of other clauses within the statement, such as the WHERE, GROUP BY, HAVING, SELECT DISTINCT, or ORDER BY clauses. Some column mask restrictions may apply to the other clauses within the statement. The masked value that is determined by the evaluation of the CASE expression in the column mask is returned in place of the column value in the output row. If column C1 is embedded in an expression, the column mask is applied to the input column before the evaluation of the expression takes place.
INSERT INTO T1(C1, C2) VALUES('A', 'B');	For each row to be inserted: If a user-defined row permission exists, the row can be inserted only when that row can be subsequently retrieved by the authorization ID of the INSERT statement. If the row cannot be inserted, the INSERT statement returns an error. If no user-defined row permissions exist for the table only the default row permission is applied and no row is inserted. The INSERT statement returns an error. The ENFORCED FOR ALL ACCESS clause ensures that users cannot insert data that they cannot read.
INSERT INTO T1(C1) SELECT SUBSTR( T2.C1, 8, 4) FROM T2 WHERE T2.C2 > 10;		When the columns are used to derive the new values for an INSERT statement, the original column values, not the masked values, are used. If the columns have column masks, those column masks are applied to ensure the evaluation of the access control rules at run time masks the column to itself, not to a constant or an expression. This ensures that the masked values are the same as the original column values. If a column mask does not mask the column to itself, the new row is not inserted and an error is returned at run time. For example, column T2.C1 is used to derive the value of a new row for INSERT. The column value of T2.C1, not the masked value, is used to derive the new value. Because column T2.C1 has a column mask, the column mask is applied to ensure the evaluation of the access control rule in the column mask masks column T2.C1 to itself, not to a constant or an expression. This ensures the masked value is the same as the original column value. If the column mask of T2.C1 does not mask column T2.C1 to itself, the new value cannot be used and an error is returned at run time. The column mask rules that apply to the new value for INSERT are the same as those for SELECT.
UPDATE T1 SET C2 = (SELECT SUBSTR(T2.C1, 8, 4) FROM T2 WHERE T2.C2 > 10);	The following rules are applied in the order as shown: Identify candidate rows for updates: If a user-defined row permission exists, only the rows of the table that satisfy the row permission can be the candidate rows for UPDATE. If no user-defined row permissions exist for the table, only the default row permission is applied and no rows are updated. If there are rows to be updated, for each row to be updated: When the columns are used to derive the new values for an UPDATE statement, the original column values, not the masked values, are used. If the columns have column masks, those column masks are applied to ensure that the evaluation of the access control rules at run time masks the column to itself, not to a constant or an expression. This ensures the masked values are the same as the original column values. If a column mask does not mask the column to itself, the new rvalue cannot be used for the update and an error is returned at run time. For example, column T2.C1 is used to derive the new value for the update. The column value of T2.C1, not the masked value, is used to derive the new value. Because column T2.C1 has a column mask, the column mask is applied to ensure that the evaluation of the access control rule in the column mask masks column T2.C1 to itself, not to a constant or an expression. This ensures that the masked value is the same as the original column value. If the column mask of T2.C1 does not mask column T2.C1 to itself, the new value cannot be used for the update and an error is returned at run time The column mask rules that apply to the new value for UPDATE are the same as those for SELECT. If there are rows to be updated, for each row to be updated: If a user-defined row permission exists, the row can be updated only when that row can be subsequently retrieved by the authorization ID of the UPDATE statement. If the row cannot be updated, the UPDATE statement returns an error. The ENFORCED FOR ALL ACCESS clause ensures that users cannot update data that they cannot read. The column mask is not applicable in this retrieval.
MERGE	The row and column access control rules for the UPDATE and INSERT operations in the MERGE statement are the same as those for the UPDATE and INSERT statements.
DELETE	If a user-defined row permission exists for the table, only the rows that satisfy the permission are the candidate rows for an DELETE statement. If no user-defined row permissions exist for the table, the default row permission is applied and no row can be deleted.

End general-use programming interface information.