DROP SECURITY statement

Use the DROP SECURITY statement to remove an existing security object from the current database. The object can be a security policy, security label, or a security label component.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

DROP SECURITY { { LABEL [IF EXISTS] policy. label | LABEL COMPONENT [IF EXISTS] component } RESTRICT | POLICY [IF EXISTS] policy { RESTRICT | CASCADE } }
Element Description Restrictions Syntax
component Security label component to drop Must exist in the database Identifier
label Security label to drop Must exist in the database as a label of the specified policy Identifier
policy Security policy to drop Must exist in the database Identifier

Usage

Only DBSECADM can issue this statement. When the DROP SECURITY statement executes successfully, the database server deletes any rows that reference the name or the numeric identifier of the specified object from the tables of the system catalog, including these tables:
  • sysecpolicies for security policies
  • sysseclabels for security labels
  • sysseclabelcomponents for security label components.
The keyword or keywords that follow the SECURITY keyword identify the type of security object that is being dropped.
  • SECURITY POLICY policy specifies a security policy
  • SECURITY LABEL policy.label specifies a security label
  • SECURITY LABEL COMPONENT component specifies a security label component.

There is no SQL statement that selectively drops some elements of a security label component without destroying the entire component object. To remove only a subset of the elements of a security label component from the database, DBSECADM can use the DROP SECURITY LABEL COMPONENT statement to drop the component, and then redefine the dropped component, using the CREATE SECURITY LABEL COMPONENT statement, but without including any elements that are no longer needed. (An alternative is to drop all the security labels that include the deprecated elements, and then use the CREATE SECURITY LABEL statement to redefine new labels with the same components as the dropped labels, but without those elements. In this case, the deprecated elements persist in the database, but no security label uses them as values for their component.)

If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no security object of the specified security object type and of the specified name is registered in the current database.

Examples

The following statement instructs the database server to drop the security label witty: 
DROP SECURITY LABEL witty;
The statement fails if any column is protected by the witty label, or if any user holds this label.
The next example instructs the database server to drop the security label component adhesive from the database: 
DROP SECURITY LABEL COMPONENT adhesive;
The statement fails if any security policy depends on the adhesive security label component.
The following example instructs the database server to drop the best security policy in CASCADE mode: 
DROP SECURITY POLICY best CASCADE;
This statement fails if that policy is currently protecting any table. If this statement succeeds, however, it has the following additional effects because of the CASCADE specification:
  • All security labels associated with the best security policy are also dropped.
  • All exemptions from the best security policy are revoked.
  • All security labels that were dropped because the best security policy was dropped are revoked from all users who hold those labels.