Modify Column Security clause

The DBSECADM can use this clause to add or drop column-level protection for a table. This clause can add row-level protection by associating the column with an LBAC security label, or it can use the DROP COLUMN SECURITY keywords to drop existing column-level security protection that a label was currently providing for the column.

• To establish column-level protection to a column, specify SECURED WITH label (or equivalently, COLUMN SECURED WITH label) to associate a security label with the column.
• To drop the column-level protection that an existing label is providing for a column, specify the DROP COLUMN SECURITY keywords.

The security label can be the same label that protects other rows or columns of the table, or it can be a different label of the same security policy. The following restrictions apply to the SECURED WITH label option:

• The table must be protected by an LBAC security policy.
• The data type of the protected column cannot be IDSSECURITYLABEL.
• The label must be a label of the same security policy that secures the table.
• Any number of columns can be protected by a security label, but no more than one security label can protect a given column.
• No more than one column name can precede each SECURED WITH or DROP COLUMN SECURITY specification of the MODIFY Clause,
• Specify the label without the policy qualifier, rather than as policy.label.

For example, if the table Argoknot is protected by a security policy called Medea that has a security label called fleece, the following ALTER TABLE statement defines column-level protection with the Medea.fleece label for the column Argoknot.ColJ:

ALTER TABLE Argoknot 
   MODIFY (ColJ JSON SECURED WITH fleece);

When DBSECADM successfully runs this DDL statement, the new column-level security of column Argoknot.ColJ is registered in the syscolumns system catalog table. To do this, the database server takes this action:

• It updates the row in syscolumns that describes column Argoknot.ColJ
• by replacing the NULL value in column syscolumns.seclabelid
• with the numeric value in column sysseclabels.seclabelid
• for the row describing the Medea.fleece security label.

Similarly, DBSECADM can later detach the Medea.fleece security label from the Argoknot.ColJ column by running the following ALTER TABLE statement:

ALTER TABLE Argoknot 
   MODIFY (ColJ JSON DROP COLUMN SECURITY);

CREATE TABLE Alphanumeric 
   (ColB CHAR(18),
    Col2 INT,
    Col3 CHAR (20));

The following ALTER TABLE statement

• attaches the watchdog security policy to the Alphanumeric table,
• and changes the column-level security status of column Col1 from unprotected to protected, by using the SECURED WITH keywords to associate that column with the LBAC watchdog.Label23 security label:

ALTER TABLE Alphanumeric 
   MODIFY (Col1 CHAR(18) SECURED WITH Label23)
   ADD SECURITY POLICY watchdog;

Because the Alphanumeric table was created with no security policy, the ADD SECURITY POLICY clause is required for the Modify Column Security clause to be valid in the example above.

The result of the ALTER TABLE MODIFY operation would be the same if the COLUMN keyword had preceded the SECURED WITH keywords:

ALTER TABLE Alphanumeric 
   MODIFY (Col1 CHAR(18) COLUMN SECURED WITH Label23)
   ADD SECURITY POLICY watchdog;

In either case, the Alphanumeric table now has column-level protection for column Col1, which is secured by the Label23 security label of the watchdog security policy.

If the data in other columns of the Alphanumeric table besides Col1 have the same security requirements as Col1, the following statements secure the other columns with the same Label23 security label:

ALTER TABLE Alphanumeric 
    MODIFY (Col2 INT COLUMN SECURED WITH Label23);

ALTER TABLE Alphanumeric 
    MODIFY (Col3 CHAR(20) COLUMN SECURED WITH Label23);

DBSECADM can also grant that security label to the specific users who require access to the data that Label23 is designed to protect:

 GRANT SECURITY LABEL watchdog.Label23
   TO sam FOR READ ACCESS;

 GRANT SECURITY LABEL watchdog.Label23
   TO peter, elaine, olan FOR WRITE ACCESS;

 GRANT SECURITY LABEL watchdog.Label23
   TO lynette FOR ALL ACCESS;

Suppose table Betanumeric is a protected table that was created with watchdog as its security policy,

• with both column Col1 and Col3 secured by the column-level protection of label labelK9,
• and with column Col4 providing row-level security for the table with a security label whose default value is labelK2:,

CREATE TABLE Betanumeric 
   (Col1 CHAR (18)SECURED WITH labelK9,
    Col2 INT,
    Col3 CHAR (20)SECURED WITH labelK9
    Col4 IDSSECURITYLABEL DEFAULT 'labelK2')
   SECURITY POLICY watchdog;

Here labelK9 and 1abelK2 must both be labels of the watchdog security policy.

Suppose that DBSECADAM subsequently determines that the sensitivity of the data stored in column Col1 does not require the protection of watchdog.labelK9, but is sufficiently protected by the row-level protection of label watchdog.labelK2. In this case, DBSECADAM might issue the following ALTER TABLE MODIFY statement, thereby dropping the association between security label watchdog.labelK9 and Col1 of table Betanumeric:

ALTER TABLE Betanumeric 
   MODIFY (Col1 CHAR (18) DROP COLUMN SECURITY);

This ALTER TABLE MODIFY statement drops the security restriction that users cannot access column Col1 unless their LBAC credentials include all the components of label watchdog.labelK9, or unless they hold equivalent LBAC security exemptions. Detaching the specified column from the specified security label is the entire scope of the DROP COLUMN SECURITY option to the Modify Column Security clause.

That is, disable disabling column-level security of a single column has no effect on the column-level protection of any other columns of the same table that are secured by the same LBAC label, such as column Betanumeric.Col3 in this example. This DROP COLUMN SECURITY example also has no effect on any the following:

• On the row-level protection of table Betanumeric by the watchdog.labelK2 label.
• On any other tables protected by the watchdog security policy,
• On any other tables protected by the labelK9 security label.

The following example, based on the same Betanumeric table, illustrates the ALTER TABLE statement syntax to drop the association between a specific table and its LBAC security policy:

ALTER TABLE Betanumeric DROP SECURITY POLICY;

When this statement executes successfully, it removes all column-level and row-level protection from the table. The database server updates the system catalog to show these changes to database objects within the scope of this example:

• Table Betanumeric is no longer protected by any security policy. This change is registered in two columns of the row that describes Betanumeric in the systables system catalog table:
  • In column secpolicyid, a NULL value replaces the numeric value of the watchdog security policy ID.
  • In column protgranularity of the same row, a blank character (ASCII 32) to indicate no protection replaces the B that had encoded both row-level and column-level protection as the former LBAC granularity of the Betanumeric table.
• Columns Betanumeric.Col1 and Betanumeric.Col3 are no longer protected by LBAC label watchdog.labelK9. This change is registered in two rows that describe those columns in the syscolumns system catalog table:
  • In column seclabelid of the row describing the Betanumeric.Col1 column, a NULL value replaces the numeric value of the labelK9 security label ID.
  • In column seclabelid of the row describing the Betanumeric.Col3 column, a NULL value replaces the numeric value of the labelK9 security label ID.
• Column Col4 is automatically dropped from the schema of table Betanumeric, because a table with no security policy cannot include a column of type IDSSECURITYLABEL. This change is registered in the system catalog by dropping the row that had described the Betanumeric.Col4 column from the syscolumns system catalog table.