REVOKE SQL Definition Privileges

Revokes from one or more users or groups the privilege of performing selected actions on a specified schema, access module, or table-like object.
idms19
Revokes from one or more users or groups the privilege of performing selected actions on a specified schema, access module, or table-like object.
Authorization
To revoke a definition privilege for an SQL-defined database resource, one of the following must be true:
  • You hold the grantable definition privilege on the resource
  • You own the resource
  • You hold DBADMIN privilege on the dictionary containing the definitions
  • You hold SYSADMIN privilege
Syntax
►►─── REVOKE ──┬─ DEFINE ─────────────┬───────────────────────────────────────►                │ ┌─────── , ────────┐ │                └─▼─┬─ ALTER ──────┬─┴─┘                    ├─ CREATE ─────┤                    ├─ DISPLAY ────┤                    ├─ DROP ───────┤                    └─ REFERENCES ─┘    ►─── ON ─┬─ ACCESS MODULE ─┬─────────────────┬─ 
access-module-name
 ─┬────────►           │                 └─ 
schema-name
. ──┘                      │           ├─ SCHEMA 
schema-name
 ─────────────────────────────────────┤           └─ table 
table-name
 ───────────────────────────────────────┘               ┌─────────────── , ──────────────┐  ►─── FROM ─▼─┬─ PUBLIC ───────────────────┬─┴────────────────────────────────►◄               └─ 
authorization-identifier
 ─┘
Parameters
 
  • DEFINE
    Revokes the ALTER, CREATE, DISPLAY, and DROP privileges on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • ALTER
    Revokes the ALTER privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • CREATE
    Revokes the CREATE privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • DISPLAY
    Revokes the DISPLAY privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • DROP
    Revokes the DROP privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • REFERENCES
    Revokes the REFERENCES privilege on the table identified in the ON parameter from the users or groups identified in the FROM parameter.
  • ON
    Specifies the resource to which the definition privileges apply.
  • ACCESS MODULE
    access-module-name
    Identifies an access module.
    Privileges on any version of
    access-module-name
    in the associated schema are revoked.
    You can wildcard
    access-module-name
    . If you specify
    schema-name
    , the wildcard character is valid after the period following
    schema-name
    .
    For more information on wildcarding, see Using a Wildcard.
  • SCHEMA
    schema-name
    Identifies an SQL schema.
    You can wildcard
    schema-name
    .
    For more information on wildcarding, see Using a Wildcard.
  • table table-name
    Identifies a table-like object.
    You can wildcard any identifier in
    table-name
    when you grant definition privileges on
    table-name
    . If you specify
    schema-name
    in
    table-name
    , the wildcard character is valid after the period following
    schema-name
    .
    For more information on wildcarding, see Using a Wildcard.
    If you do not specify
    schema-name
    , it defaults to the current schema in effect for your session.
  • FROM
    Specifies the users or groups from whom you are revoking definition privileges.
  • PUBLIC
    Specifies all users.
    The privileges must have been previously given to PUBLIC by means of the GRANT statement.
  • authorization-identifier
    Identifies a user or group.
    The privileges must have been previously given to
    authorization-identifier
    by means of the GRANT statement.
    Expanded syntax for
    authorization-identifier
    is presented in Notes on Security Statement Syntax.
Usage
The DEFINE Keyword
When you use the DEFINE keyword with a GRANT statement, you grant a set of definition privileges on a resource to one or more users or groups.
When you use the DEFINE keyword with a REVOKE statement, you revoke all definition privileges that have been previously granted on the resource from the specified users or groups.
This means that if you GRANT CREATE privilege on a resource, you can revoke the privilege with either a REVOKE CREATE statement or a REVOKE DEFINE statement. Using REVOKE DEFINE is an efficient technique when you intend to revoke all definition privileges on the resource from a user or group, whether the privileges were granted singly or as a set.
Similarly, you can GRANT DEFINE on a resource to a user and then REVOKE DROP on the resource from the same user as a way to grant all but one definition privilege.
Revoking Privileges on a Schema
The following statement revokes the ALTER, CREATE, DISPLAY, and DROP privileges on all schemas that begin with 'DSF' from user DSF:
revoke define    on schema dsf*    from dsf;
More Information
For more information
about granting SQL definition privileges
, see GRANT SQL Definition Privileges.