Using the INSERT statement with multilevel security

When a user with a valid security label inserts data into a table with row-level security, the security label of the row is determined according to a specific set of rules.

About this task

If the user has write-down privilege or write-down control is not enabled, the user can set the security label for the row to any valid security label. If the user does not specify a value for the security label, the security label of the row becomes the same as the security label of the user.
If the user does not have write-down privilege and write-down control is enabled, the security label of the row becomes the same as the security label of the user.

Considerations for INSERT from a fullselect: For statements that insert the result of a fullselect, Db2 does not return an error code if the fullselect contains a table with a security label column. Db2 allows it if the target table does not contain a security label column while the source table contains one.
Considerations for SELECT...FROM...INSERT statements: If the user has write-down privilege or write-down control is not in effect, the security label of the user might not dominate the security label of the row. For statements that insert rows and select the inserted rows, the INSERT statement succeeds. However, the inserted row is not returned.
Considerations for INSERT with subselect: If you insert data into a table that does not have a security label column, but a subselect in the INSERT statement does include a table with a security label column, row-level checking is performed for the subselect. However, the inserted rows will not be stored with a security label column.

Example

Suppose that Alan has a security label of HIGH, that Beth has a security label of MEDIUM and write-down privilege defined in RACF®, and that Carlos has a security label of LOW. Write-down control is enabled.

Now, suppose that Alan, Beth, and Carlos each submit the following INSERT statement:

INSERT INTO DSN8910.EMP(EMPNO, LASTNAME, WORKDEPT, SECURITY)
    VALUES('099990', 'SMITH', 'C01', 'MEDIUM');

Because Alan does not have write-down privilege, Alan cannot choose the security label of the row that he inserts. Therefore Db2 ignores the security label of MEDIUM that is specified in the statement. The security label of the row becomes HIGH because Alan's security label is HIGH.

Because Beth has write-down privilege on the table, she can specify the security label of the new row. In this case, the security label of the new row is MEDIUM. If Beth submits a similar INSERT statement that specifies a value of LOW for the security column, the security label for the row becomes LOW.

Because Carlos does not have write-down privilege, Carlos cannot choose the security label of the row that he inserts. Therefore Db2 ignores the security label of MEDIUM that is specified in the statement. The security label of the row becomes LOW because Carlos' security label is LOW.