LBAC-protected data load considerations

For a successful load operation into a table with protected rows, you must have LBAC (label-based access control) credentials. You must also provide a valid security label, or a security label that can be converted to a valid label, for the security policy currently associated with the target table.

If you do not have valid LBAC credentials, the load fails and an error (SQLSTATE 42512) is returned. In cases where the input data does not contain a security label or that security label is not in its internal binary format, you can use several file type modifiers to allow your load to proceed.

When you load data into a table with protected rows, the target table has one column with a data type of DB2SECURITYLABEL. If the input row of data does not contain a value for that column, that row is rejected unless the usedefaults file type modifier is specified in the load command, in which case the security label you hold for write access from the security policy protecting the table is used. If you do not hold a security label for write access, the row is rejected and processing continues on to the next row.

When you load data into a table that has protected rows and the input data does include a value for the column with a data type of DB2SECURITYLABEL, the same rules are followed as when you insert data into that table. If the security label protecting the row being loaded (the one in that row of the data file) is one that you are able to write to, then that security label is used to protect the row. (In other words, it is written to the column that has a data type of DB2SECURITYLABEL.) If you are not able to write to a row protected by that security label, what happens depends on how the security policy protecting the source table was created:

If the CREATE SECURITY POLICY statement that created the policy included the option RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL, the row is rejected.
If the CREATE SECURITY POLICY statement did not include the option or if it instead included the OVERRIDE NOT AUTHORIZED WRITE SECURITY LABEL option, the security label in the data file for that row is ignored and the security label you hold for write access is used to protect that row. No error or warning is issued in this case. If you do not hold a security label for write access, the row is rejected and processing continues on to the next row.

Delimiter considerations

When loading data into a column with a data type of DB2SECURITYLABEL, the value in the data file is assumed by default to be the actual bytes that make up the internal representation of that security label. However, some raw data might contain newline characters which could be misinterpreted by the LOAD command as delimiting the row. If you have this problem, use the delprioritychar file type modifier to ensure that the character delimiter takes precedence over the row delimiter. When you use delprioritychar, any record or column delimiters that are contained within character delimiters are not recognized as being delimiters. Using the delprioritychar file type modifier is safe to do even if none of the values contain a newline character, but it does slow the load down slightly.

If the data being loaded is in ASC format, you might have to take an extra step in order to prevent any trailing white space from being included in the loaded security labels and security label names. ASCII format uses column positions as delimiters, so this might occur when loading into variable-length fields. Use the striptblanks file type modifier to truncate any trailing blank spaces.

Nonstandard security label values

You can also load data files in which the values for the security labels are strings containing the values of the components in the security label, for example, S:(ALPHA,BETA). To do so you must use the file type modifier seclabelchar. When you use seclabelchar, a value for a column with a data type of DB2SECURITYLABEL is assumed to be a string constant containing the security label in the string format for security labels. If a string is not in the proper format, the row is not inserted and a warning (SQLSTATE 01H53) is returned. If the string does not represent a valid security label that is part of the security policy protecting the table, the row is not inserted and a warning (SQLSTATE 01H53) is returned.

You can also load a data file in which the values of the security label column are security label names. To load this sort of file you must use the file type modifier seclabelname. When you use seclabelname, all values for columns with a data type of DB2SECURITYLABEL are assumed to be string constants containing the names of existing security labels. If no security label exists with the indicated name for the security policy protecting the table, the row is not loaded and a warning (SQLSTATE 01H53) is returned.

Rejected rows

Rows that are rejected during the load are sent to either a dumpfile or an exception table (if they are specified in the LOAD command), depending on the reason why the rows were rejected. Rows that are rejected due to parsing errors are sent to the dumpfile. Rows that violate security policies are sent to the exception table.

Note: You cannot specify an exception table if the target table contains an XML column.

Examples

For all examples, the input data file myfile.del is in DEL format. All are loading data into a table named REPS, which was created with this statement:

create table reps (row_label db2securitylabel,
id integer,
name char(30)) 
security policy data_access_policy

For this example, the input file is assumed to contain security labels in the default format:

db2 load from myfile.del of del modified by delprioritychar insert into reps

For this example, the input file is assumed to contain security labels in the security label string format:

db2 load from myfile.del of del modified by seclabelchar insert into reps

For this example, the input file is assumed to contain security labels names for the security label column:

db2 load from myfile.del of del modified by seclabelname insert into reps