LBAC-protected data import considerations

For a successful import 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 import 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 import to proceed.

When you import 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 import 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 import 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 imported (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 insert fails and an error is returned.
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 importing 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 IMPORT 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 import down slightly.

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

Nonstandard security label values

You can also import 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 import a data file in which the values of the security label column are security label names. To import 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 inserted and a warning (SQLSTATE 01H53) is returned.

Examples

For all examples, the input data file myfile.del is in DEL format. All are importing 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 import 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 import 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 import from myfile.del of del modified by seclabelname insert into reps