SECURITY POLICY Clause

Use the Security Policy clause of the ALTER TABLE statement to drop the security policy that is currently associated with the table, or to associate a security policy with a table that has none.

This syntax fragment is part of the ALTER TABLE statement.


Element	Description	Restrictions	Syntax
policy	Name of a security policy	Must be an existing security policy	Identifier

Usage

Only DBSECADM can use this clause to add a security policy to an existing table, or to remove from the table the protection of the security policy that currently protects a table.

Restrictions on adding a security policy

The following guidelines apply to tables that can be protected by executing the ADD SECURITY POLICY clause of the ALTER TABLE statement:

A table is not protected unless it has a security policy associated with it and has either rows secured, or has at least one column secured.
- Having rows secured indicates that the table is a protected table with row-level granularity.
- Having at least one column secured indicates that the table is a protected table with column-level granularity.
Securing rows by using the ALTER TABLE ... ADD column statement to add an IDSSECURITYLABEL column to an existing table fails if the table does not have a security policy associated with it.
Securing a column with the ALTER TABLE ... MODIFY column SECURED WITH label clause fails if the table does not have a security policy associated with it.
A table can have at most one security policy. The ALTER TABLE ... ADD SECURITY POLICY statement fails if the table already has a security policy.
A table can have any number of protected columns. Each protected column can have a different security label, or several protected columns can share the same security label, but all labels must have the same security policy.
This clause cannot add a security policy to protect any of the following table objects
- a temporary table,
- a table outside the current database,
- a typed table in a table hierarchy,
- an object defined by the CREATE EXTERNAL TABLE statement.
A table can have at most one column of type IDSSECURITYLABEL.
The IDSSECURITYLABEL column cannot have column-level protection (that is, a security label).
The IDSSECURITY LABEL column cannot have single column constraints.
The IDSSECURITY LABEL column cannot be part of the multiple column key of a referential constraint or of a check constraint.
The IDSSECURITYLABEL column cannot be encrypted.
The IDSSECURITYLABEL column has an implicit DEFAULT NOT NULL constraint. The default column value is the value of the security label of the user for write access.
The IDSSECURITYLABEL column cannot be modified by the ALTER TABLE MODIFY statement.

Security policies and tables with distributed storage

Detaching a fragment of a protected table creates a new table that is protected by the same security policy for the same row security label column, and the same set of protected columns.

Some restrictions apply to associating a fragmented table with a security policy. Attaching a fragment to a protected fragmented table fails if any of these conditions are true:

if the source table and the target table are not protected using the same security policy;
if the tables do not have the same protection granularity;
if the tables do not have the same set of protected columns, each protected by the same security label.

For more information on using the ALTER FRAGMENT statement to attach fragments to protected tables, see Additional Restrictions on the ATTACH Clause.

Associating a security policy with a table

In a database in which the LBAC security policy Watchdog is defined, consider an unprotected table with the following schema:

CREATE TABLE IF NOT EXISTS MyData 
   (C1 CHAR (8),
   C2 INT,
   C3 CHAR (10));

The DBSECADM can change this to a table with row-level protection of the Watchdog security policy by issuing the following ALTER TABLE statement:

ALTER TABLE MyData
   ADD C4 IDSSECURITYLABEL,
   ADD SECURITY POLICY Watchdog;

Here no security label is specified, so the default security label for the rows of the MyData table is the label of the Watchdog security policy that the current user holds for write access. This example illustrates a requirement that if the table is protected by no security policy, both the ADD COLUMN IDSSECURITYLABEL clause and the ADD SECURITY POLICY clause must be included in the same ALTER TABLE statement.

An alternative to the syntax illustrated by the previous example is that the DBSECADM can instead change the MyData table to a table protected by a specific security label of the Watchdog security policy, rather than by a default security label, as in this ALTER TABLE statement:

ALTER TABLE MyData
   ADD C4 IDSSECURITYLABEL DEFAULT 'canine',
   ADD SECURITY POLICY Watchdog;

Here the security label canine must be part of the Watchdog security policy.

Dropping a security policy from a table

Conversely, in a database in which the LBAC security policy Watchdog is defined, consider a protected table called MyOtherData with the same schema that resulted after the ALTER TABLE statement in the previous example modified for the MyData table:

CREATE TABLE IF NOT EXISTS MyOtherData 
   (C1 CHAR (8),
   C2 INT,
   C3 CHAR (10)
   C4 IDSSECURITYLABEL DEFAULT canine NOT NULL)
   SECURITY POLICY Watchdog;

The DBSECADM can change this MyOtherData table to an unprotected table with no security policy by issuing the following statement:

ALTER TABLE MyOtherData
   DROP SECURITY POLICY Watchdog;

When a security policy is dropped from a table by the ALTER TABLE DROP SECURITY POLICY statement, the IDSSECURITYLABEL column C4 that provided row-level protection is automatically dropped. If labels table had protected any columns, those columns become unprotected when the security policy was dropped.

The DROP SECURITY POLICY option requires the Connect, Resource, and Alter discretional access privileges for dropping columns, because this option drops the IDSSECURITYLABEL column that only tables associated with a security policy can include in their schema.

Replacing the security policy of an existing table

If the DROP SECURITY POLICY clause of the ALTER TABLE statement executes successfully, the table is no longer protected by any security policy. Other tables that are associated with the same security policy, however, are not affected by the alterations to this table. The IDSSECURITYLABEL column and any security label associated with the dropped security policy are no longer part of the table schema.

If no protection of a security policy is needed for the data in the table, no further action is needed.

If either row-level or column-level protection is needed for the table, however, a user who holds DBSECADM credentials should take steps to attach another security policy to the table :

Replace the security policy that was dropped with another security policy, using the ADD SECURITY POLICY clause in an ALTER TABLE statement that also establishes row-level or column-level security. The following example establishes both row-level and column-level protection for the MyOtherData table, based on the Robodog security policy:
```
ALTER TABLE MyOtherData
   ADD (C4 IDSSECURITYLABEL DEFAULT),
   MODIFY (C2 INT COLUMN SECURED WITH label7),
   ADD SECURITY POLICY Robodog;
```
If the new security design requires only row-level protection, then instead of the previous example, the C2 column need not be associated with label7:
```
ALTER TABLE MyOtherData
   ADD (C4 IDSSECURITYLABEL DEFAULT),
   ADD SECURITY POLICY Robodog;
```
If the new security design requires only column-level protection, use the MODIFY . . . COLUMN SECURED WITH clause to protect one of more columns with labels of the new security policy.
```
ALTER TABLE MyOtherData
   MODIFY (C1 CHAR (8) COLUMN SECURED WITH label9),
   ADD SECURITY POLICY Robodog;
```

Dropping a security policy from a table or from the database

Do not confuse this DROP SECURITY POLICY clause of the ALTER table statement with the DROP SECURITY POLICY statement of SQL.

When the DROP SECURITY POLICY clause of the ALTER TABLE statement executes successfully, its immediate effects include these:
- It terminates the association of that table with the security policy,
- and it drops the IDSSECURITYLABEL column from that table,
- and it removes LBAC protection from data that had been protected in that table.
It has no effect, however, on the security policy, nor on other tables protected by the policy.
When the DROP SECURITY POLICY statement executes successfully, the scope of its effects depends on whether the policy is dropped in RESTRICT mode or in CASCADE mode, but in either mode, it destroys the specified policy.
See the description of DROP SECURITY statement for more information on the DROP SECURITY POLICY statement of SQL, and about restrictions on that statement.