DBSECADM Clause

The REVOKE DBSECADM statement prevents the user to whom the DBSECADM role was granted from issuing DDL statements that can create, alter, rename, or drop security objects, including security policies, security labels, and security components.

DBSECADM Clause

DBSECADM FROM
[ USER ] user

Element Description Restrictions Syntax
user User from whom the role is to be revoked Must be the authorization identifier of a user Owner name

The DBSECADM role is a built-in role that only the DBSA can revoke. Unlike user-defined roles, whose scope is the database in which the role is created, the scope of the DBSECADM role is all of the databases of the Informix® instance. It is not necessary for DBSA to reissue the REVOKE DBSECADM statement in other databases of the same server.

Only a user who holds the DBSECADM role can issue the following SQL statements that create or modify security objects:
  • ALTER SECURITY LABEL COMPONENT
  • CREATE SECURITY LABEL
  • CREATE SECURITY LABEL COMPONENT
  • CREATE SECURITY POLICY
  • DROP SECURITY LABEL
  • DROP SECURITY LABEL COMPONENT
  • DROP SECURITY POLICY
  • RENAME SECURITY LABEL
  • RENAME SECURITY LABEL COMPONENT
  • RENAME SECURITY POLICY
Only a user who holds the DBSECADM role can use the following SQL statements to reference tables that are protected by a security policy:
  • ALTER TABLE ... ADD SECURITY POLICY
  • ALTER TABLE ... ADD ... IDSSECURITYLABEL [DEFAULT label]
  • ALTER TABLE ... ADD ... [COLUMN] SECURED WITH
  • ALTER TABLE ... DROP SECURITY POLICY
  • ALTER TABLE ... MODIFY ... [COLUMN] SECURED WITH
  • ALTER TABLE ... MODIFY ... DROP COLUMN SECURITY
  • CREATE TABLE ... COLUMN SECURED WITH
  • CREATE TABLE ... IDSSECURITYLABEL [DEFAULT label]
  • CREATE TABLE ... SECURITY POLICY
The following GRANT and REVOKE statements also cannot be issued by a user who does not hold the DBSECADM role:
  • GRANT EXEMPTION
  • GRANT SECURITY LABEL
  • GRANT SETSESSIONAUTH
  • REVOKE EXEMPTION
  • REVOKE SECURITY LABEL
  • REVOKE SETSESSIONAUTH

The USER keyword that can follow the FROM keyword is optional, and has no effect, but any authorization identifier that the DBSA specifies in the REVOKE DBSECADM statement must be the identifier of an individual user, rather than the identifier of a role. The user cannot be the DBSA who issues this REVOKE DBSECADM statement.

In the following example, the DBSA cancels the DBSECADM role of user niccolo: 
REVOKE DBSECADM FROM niccolo;
If this statement executes successfully, user niccolo can no longer perform the operations listed above.

After the DBSECADM role is revoked, only the DBSA can grant it again to the user from whom it was revoked.