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 by using 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 needed.
Note: In Db2 11.5.7 and later, to revoke CREATE_EXTERNAL_ROUTINE or CREATE_NOT_FENCED_ROUTINE authority, SYSADM, SECADM or ACCESSCTRL authority is needed.

To revoke other authorities, ACCESSCTRL or SECADM authority is needed.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE,ACCESSCTRLBINDADDCONNECTCREATETABCREATE_EXTERNAL_ROUTINECREATE_NOT_FENCED_ROUTINECREATE_SECURE_OBJECTDBADMDATAACCESSEXPLAINIMPLICIT_SCHEMALOADQUIESCE_CONNECTSECADMSQLADMWLMADMON DATABASEFROM ,USERGROUPROLEauthorization-namePUBLIC BY ALL

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 the creator's BINDADD authority is later 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 again granted the CONNECT authority, 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 the creator's CREATETAB authority is later 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. When an external routine is registered, it continues to exist, even if CREATE_EXTERNAL_ROUTINE is later 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 run in the database manager's process. When a routine is registered as not fenced, it continues to run in this manner, even if CREATE_NOT_FENCED_ROUTINE is later 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 that is 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 behavior is the default.

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 USER, GROUP, or ROLE is not specified, then:
    • For all rows for the specified object in the SYSCAT.DBAUTH catalog view where the grantee is authorization-name:
      • USER is assumed if all rows have a GRANTEETYPE of 'U'.
      • GROUP is assumed if all rows have a GRANTEETYPE of 'G'.
      • ROLE is assumed if all rows have a GRANTEETYPE of 'R'.
      • An error is returned (SQLSTATE 56092) if all rows do not have the same value for GRANTEETYPE.

Notes

  • Revoking a specific privilege does not necessarily revoke the ability to complete a task. 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 can 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. Two rows exist 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 occurs (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