DB2 10.5 for Linux, UNIX, and Windows

REVOKE (database authorities) statement

This form of the REVOKE statement revokes authorities that apply to the entire database.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

To revoke ACCESSCTRL, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, or SECADM authority, SECADM authority is required. To revoke other authorities, ACCESSCTRL or SECADM authority is required.

Syntax

Read syntax diagramSkip visual syntax diagram
           .-,-----------------------------.                
           V                               |                
>>-REVOKE----+-ACCESSCTRL----------------+-+--ON DATABASE------->
             +-BINDADD-------------------+                  
             +-CONNECT-------------------+                  
             +-CREATETAB-----------------+                  
             +-CREATE_EXTERNAL_ROUTINE---+                  
             +-CREATE_NOT_FENCED_ROUTINE-+                  
             +-CREATE_SECURE_OBJECT------+                  
             +-DBADM---------------------+                  
             +-DATAACCESS----------------+                  
             +-EXPLAIN-------------------+                  
             +-IMPLICIT_SCHEMA-----------+                  
             +-LOAD----------------------+                  
             +-QUIESCE_CONNECT-----------+                  
             +-SECADM--------------------+                  
             +-SQLADM--------------------+                  
             '-WLMADM--------------------'                  

         .-,---------------------------------.               
         V                                   |  .-BY ALL-.   
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
           | +-USER--+                     |                 
           | +-GROUP-+                     |                 
           | '-ROLE--'                     |                 
           '-PUBLIC------------------------'                 

Description

ACCESSCTRL
Revokes the authority to grant and revoke most database authorities and object privileges.
BINDADD
Revokes the authority to create packages. The creator of a package automatically has the CONTROL privilege on that package and retains this privilege even if his BINDADD authority is subsequently revoked.

The BINDADD authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority.

CONNECT
Revokes the authority to access the database.

Revoking the CONNECT authority from a user does not affect any privileges that were granted to that user on objects in the database. If the user is subsequently granted the CONNECT authority again, all previously held privileges are still valid (assuming they were not explicitly revoked).

The CONNECT authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority (SQLSTATE 42504).

CREATETAB
Revokes the authority to create tables. The creator of a table automatically has the CONTROL privilege on that table, and retains this privilege even if his CREATETAB authority is subsequently revoked.

The CREATETAB authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority (SQLSTATE 42504).

CREATE_EXTERNAL_ROUTINE
Revokes the authority to register external routines. Once an external routine has been registered, it continues to exist, even if CREATE_EXTERNAL_ROUTINE is subsequently revoked from the authorization ID that registered the routine.

CREATE_EXTERNAL_ROUTINE authority cannot be revoked from an authorization-name holding DBADM or CREATE_NOT_FENCED_ROUTINE authority without also revoking DBADM or CREATE_NOT_FENCED_ROUTINE authority (SQLSTATE 42504).

CREATE_NOT_FENCED_ROUTINE
Revokes the authority to register routines that execute in the database manager's process. Once a routine has been registered as not fenced, it continues to run in this manner, even if CREATE_NOT_FENCED_ROUTINE is subsequently revoked from the authorization ID that registered the routine.

CREATE_NOT_FENCED_ROUTINE authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority (SQLSTATE 42504).

CREATE_SECURE_OBJECT
Revokes the authority to create secure triggers and secure functions. Revokes the authority to alter the secure attribute of such objects as well.
DATAACCESS
Revokes the authority to access data.
DBADM
Revokes the DBADM authority.

DBADM authority cannot be revoked from PUBLIC (because it cannot be granted to PUBLIC).

CAUTION:
Revoking DBADM authority does not automatically revoke any privileges that were held by the authorization-name on objects in the database.
EXPLAIN
Revokes the authority to explain, prepare, and describe static and dynamic statements without requiring access to data.
IMPLICIT_SCHEMA
Revokes the authority to implicitly create a schema. It does not affect the ability to create objects in existing schemas or to process a CREATE SCHEMA statement.

IMPLICIT_SCHEMA authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority (SQLSTATE 42504).

LOAD
Revokes the authority to LOAD in this database.
QUIESCE_CONNECT
Revokes the authority to access the database while it is quiesced.
SECADM
Revokes the authority to administer database security.
SQLADM
Revokes the authority to monitor and tune SQL statements.
WLMADM
Revokes the authority to manage workload manager objects.
FROM
Indicates from whom the authorities are revoked.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group name.
ROLE
Specifies that the authorization-name identifies a role name.
authorization-name,...
Lists the authorization IDs of one or more users, groups, or roles.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

PUBLIC
Revokes the authorities from PUBLIC.
BY ALL
Revokes each named privilege from all named users who were explicitly granted those privileges, regardless of who granted them. This is the default behavior.

Rules

Security administrator mandatory: The database must have at least one authorization ID of type USER with the SECADM authority. The SECADM authority cannot be revoked from every user authorization ID (SQLSTATE 42523).

Notes

Examples