REVOKE (schema privileges and authorities) statement

This form of the REVOKE statement revokes the privileges and authorities on a schema.

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

  • The privileges held by the authorization ID of the statement must include ACCESSCTRL or SECADM authority or ACCESSCTRL authority on the schema-name.
  • To revoke schema ACCESSCTRL, the authorization must have SECADM or database ACCESSCTRL authority.

Syntax

Read syntax diagramSkip visual syntax diagram REVOKE ALLPRIVILEGES,ACCESSCTRLALTERINCREATEINDATAACCESSDELETEINDROPINEXECUTEININSERTINLOADSCHEMAADMSELECTINUPDATEIN ON SCHEMAschema-nameCURRENT SCHEMA FROM ,USERGROUPROLEauthorization-namePUBLIC BY ALL

Description

ALL or ALL PRIVILEGES
Revoke all of the following schema privileges on the schema that is named in the ON clause:
  • ALTERIN
  • CREATEIN
  • DELETEIN
  • DROPIN
  • EXECUTEIN
  • INSERTIN
  • SELECTIN
  • UPDATEIN

If ALL is not specified, one or more of the keywords in the list of privileges must be specified.

ACCESSCTRL
Revokes the authority to grant and revoke schema-level privileges. For more information, see Schema access control authority (ACCESSCTRL).
ALTERIN
Revokes the privilege to alter or comment on objects in the schema.
CREATEIN
Revokes the privilege to create objects in the schema.
DATAACCESS
Revokes the authority to access data in the schema. For more information, see Schema data access authority (DATAACCESS).
DELETEIN
Revokes the privilege to delete all objects in the schema.
DROPIN
Revokes the privilege to drop objects in the schema.
EXECUTEIN
Revokes the privilege to execute user-defined functions, methods, procedures, packages, and modules defined in the schema.
INSERTIN
Revokes the privilege to insert data in to all objects in the schema.
LOAD
Revokes the authority to load in this schema. For more information, see Schema load authority (LOAD).
SCHEMAADM
Revokes the schema administrator authority. For more information, see Schema administration authority (SCHEMAADM).
SELECTIN
Revokes the privilege to select from all objects in the schema.
UPDATEIN
Revokes the privilege to update all objects in the schema.
ON
SCHEMA schema-name
Specifies the name of the schema on which privileges are to be revoked.
CURRENT SCHEMA
Specifies that the privileges will be revoked from the schema described by the DB2® special register CURRENT SCHEMA.
FROM
Indicates from whom the privileges 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 privileges 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

  • For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
    • If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined as ROLE in the database, and as either GROUP or USER according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
    • If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
    • If the authorization-name is defined in the database as ROLE only, ROLE is assumed.

Notes

  • Revoking a specific privilege does not necessarily revoke the ability to perform the 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.

Examples

  • Example 1:  Given that USER4 is only a user and not a group, revoke the privilege to create objects in schema DEPTIDX from the user USER4. 
       REVOKE CREATEIN ON SCHEMA DEPTIDX FROM USER4
  • Example 2:  Revoke the privilege to drop objects in schema LUNCH from the user CHEF and the group WAITERS. 
       REVOKE DROPIN ON SCHEMA LUNCH
     FROM USER CHEF, GROUP WAITERS