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.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE,ALTERINCREATEINDROPINEXECUTEIN1INSERTIN1SELECTIN1ON SCHEMAschema-nameFROM ,USERGROUPROLEauthorization-namePUBLIC BY ALL
Notes:
  • 1 Db2® Big SQL 7.2 and later service This feature requires the Db2 Big SQL 7.2 and later service on IBM Cloud Pak® for Data 4.0 and later.

Description

ALTERIN
Revokes the privilege to alter or comment on objects in the schema.
CREATEIN
Revokes the privilege to create objects in the schema.
DROPIN
Revokes the privilege to drop objects in the schema.
EXECUTEIN

Db2 Big SQL 7.2 and later service This feature requires the Db2 Big SQL 7.2 and later service on IBM Cloud Pak for Data 4.0 and later.

Revokes the privilege to execute user-defined functions, methods, procedures, packages, and modules defined in the schema.

INSERTIN

Db2 Big SQL 7.2 and later service This feature requires the Db2 Big SQL 7.2 and later service on IBM Cloud Pak for Data 4.0 and later.

Revokes the privilege to insert data in to all objects in the schema.

SELECTIN

Db2 Big SQL 7.2 and later service This feature requires the Db2 Big SQL 7.2 and later service on IBM Cloud Pak for Data 4.0 and later.

Revokes the privilege to select from all objects in the schema.

ON SCHEMA schema-name
Specifies the name of the schema on which privileges are to be revoked.
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:
    • For all rows for the specified object in the SYSCAT.SCHEMAAUTH 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 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