Creating tables with multilevel security

You can use multilevel security with row-level checking to control table access. You can do so by creating or altering a table that has a column with the AS SECURITY LABEL attribute.

About this task

Begin general-use programming interface information. Tables with multilevel security in effect can be dropped by using the DROP TABLE statement. Users must have a valid security label to execute CREATE TABLE, ALTER TABLE, and DROP TABLE statements on tables with multilevel security enabled.

The performance of tables that you create and alter can suffer if the security label is not included in indexes. The security label column is used whenever a table with multilevel security enabled is accessed. Therefore, the security label column should be included in indexes on the table. If you do not index the security label column, you cannot maintain index-only access.

When a user with a valid security label creates a table, the user can implement row-level security by including a security label column. The security label column can have any name, but it must be defined as CHAR(8) and NOT NULL WITH DEFAULT. It also must be defined with the AS SECURITY LABEL clause.

After the user specifies the AS SECURITY LABEL clause on a column, users can indicate the security label for each row by entering values in that column. When a user creates a table and includes a security label column, SYSIBM.SYSTABLES indicates that the table has row-level security enabled. Once a user creates a table with a security label column, the security on the table cannot be disabled. The table must be dropped and re-created to remove this protection. End general-use programming interface information.

Example

To create a table that is named TABLEMLS1 and that has row-level security enabled, issue the following statement:

CREATE TABLE TABLEMLS1
  (EMPNO      CHAR(6)       NOT NULL,
   EMPNAME           VARCHAR(20)   NOT NULL,
   DEPTNO     VARCHAR(5)
   SECURITY   CHAR(8)       NOT NULL WITH DEFAULT AS SECURITY LABEL,
   PRIMARY KEY (EMPNO)             )
IN DSN8D71A.DSN8S71D;