SQL Reference

REVOKE (Schema Privileges)

This form of the REVOKE statement revokes the privileges 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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The authorization ID of the statement must hold either SYSADM or DBADM authority (SQLSTATE 42501).

Refer to REVOKE (Database Authorities), REVOKE (Index Privileges), REVOKE (Package Privileges), REVOKE (Server Privileges) and REVOKE (Table, View, or Nickname Privileges) for other GRANT statements.

Syntax

             .-,-------------.
             V               |
>>-REVOKE------+-ALTERIN--+--+--ON SCHEMA--schema-name---------->
               +-CREATEIN-+
               '-DROPIN---'
 
           .-,------------------------------------.
           V                                      |
>----FROM----+-+-------+---authorization-name--+--+------------><
             | +-USER--+                       |
             | '-GROUP-'                       |
             '-PUBLIC--------------------------'
 

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.

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.

authorization-name,...

Lists one or more authorization IDs.

The authorization ID of the REVOKE statement itself cannot be used (SQLSTATE 42502). It is not possible to revoke the privileges from an authorization-name that is the same as the authorization ID of the REVOKE statement.

PUBLIC

Revokes the privileges from PUBLIC.

Rules

• If neither USER nor GROUP is specified, then:
  • If all rows for the grantee in the SYSCAT.SCHEMAAUTH catalog view have a GRANTEETYPE of U, then USER will be assumed.
  • If all rows have a GRANTEETYPE of G, then GROUP will be assumed.
  • If some rows have U and some rows have G, then an error (SQLSTATE 56092) is raised.
  • If DCE authentication is used, then an error is raised (SQLSTATE 56092).

Notes

• Revoking a specific privilege does not necessarily revoke the ability to perform the action. A user may proceed with their task if other privileges are held by PUBLIC or a group, or if they have 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