DB2 Version 10.1 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


           .-,-----------------------------.                
           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).

For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
- For all rows for the specified object in the SYSCAT.DBAUTH catalog view where the grantee is authorization-name:
  - If all rows have a GRANTEETYPE of 'U', USER is assumed.
  - If all rows have a GRANTEETYPE of 'G', GROUP is assumed.
  - If all rows have a GRANTEETYPE of 'R', ROLE is assumed.
  - If all rows do not have the same value for GRANTEETYPE, an error is returned (SQLSTATE 56092).

Notes

Revoking a specific privilege does not necessarily revoke the ability to perform an action. A user can proceed with a task if other privileges are held by PUBLIC, a group, or a role, or if the user holds a higher level authority, such as DBADM.
Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of DB2® and with other database products.
- CREATE_NOT_FENCED can be specified in place of CREATE_NOT_FENCED_ROUTINE
- SYSTEM can be specified in place of DATABASE
- NOT INCLUDING DEPENDENT PRIVILEGES may be specified as a syntax alternative

Examples

Example 1: Given that USER6 is only a user and not a group, revoke the privilege to create tables from the user USER6.
```
   REVOKE CREATETAB ON DATABASE FROM USER6
```
Example 2: Revoke BINDADD authority on the database from a group named D024. There are two rows in the SYSCAT.DBAUTH catalog view for this grantee; one with a GRANTEETYPE of U and one with a GRANTEETYPE of G.
```
   REVOKE BINDADD ON DATABASE FROM GROUP D024
```
In this case, the GROUP keyword must be specified; otherwise an error will occur (SQLSTATE 56092).
Example 3: Revoke security administrator authority from user Walid.
```
   REVOKE SECADM ON DATABASE FROM USER Walid
```
Example 4: A user with SECADM authority revokes the CREATE_SECURE_OBJECT authority from user Haytham.
```
   REVOKE CREATE_SECURE_OBJECT ON DATABASE FROM USER HAYTHAM
```