REVOKE Definition Privileges

The REVOKE SQL Definition Privileges authorization statement removes from one or more users or groups the privilege of performing selected actions on a specified access module, schema, table, view, function, procedure or table procedure. It is also a CA IDMS extension of the SQL standard.
idmscu19
The REVOKE SQL Definition Privileges authorization statement removes from one or more users or groups the privilege of performing selected actions on a specified access module, schema, table, view, function, procedure or table procedure. It is also a CA IDMS extension of the SQL standard.
This article describes the following information:
2
2
Authorization
To issue a REVOKE statement for a definition privilege, you must own the resource, hold the corresponding grantable privilege on the resource, or hold DBADMIN privilege on the dictionary containing the definition.
Syntax
  ►►─── REVOKE  ─┬─ DEFINE ─────────────┬───────────────────────────────────────►                │ ┌─────── , ────────┐ │                └─▼─┬─ ALTER ──────┬─┴─┘                    ├─ CREATE ─────┤                    ├─ DISPLAY ────┤                    ├─ DROP ───────┤                    └─ REFERENCES ─┘  ►─── ON ─┬─ ACCESS MODULE ─┬─────────────────┬─ 
access-module-name
 ─┬────────►           │                 └─ 
schema-name
.  ─┘                      │           ├─ SCHEMA 
schema-name
 ─────────────────────────────────────┤           ├─ table 
table-name
 ───────────────────────────────────────┤           └──────┬────────────────┬────
function-identifier
───────────┘                  └─ 
schema-name
. ─┘             ┌─────────────── , ──────────────┐  ►─── FROM ─▼─┬─ PUBLIC ───────────────────┬─┴────────────────────────────────►◄               └─ 
authorization-identifier
 ─┘  
Parameters
  • DEFINE
    Removes the ALTER, CREATE, DISPLAY, DROP, and REFERENCES privileges, as applicable, on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • ALTER
    Removes the ALTER privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • CREATE
    Removes the CREATE privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • DISPLAY
    Removes the DISPLAY privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • DROP
    Removes the DROP privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • REFERENCES
    Removes the REFERENCES privilege on the resource identified in the ON parameter from the users or groups identified in the FROM parameter.
  • ON
    Identifies the resource to which the named 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.
    • schema-name
      Identifies the schema associated with the named access module.
      If you do not specify
      schema-name
      , it defaults to the current schema in effect for your SQL session.
  • SCHEMA
    schema-name
    Identifies a schema.
  • table table-name
    Identifies a table, view, procedure or table procedure.
    If
    table-name
    does not include a schema name qualifier, the schema name qualifier defaults to the current schema in effect for your SQL session.
  • schema-name
    Specifies the schema with which the function identified by function-identifier is associated. If schema-name is not specified, the schema defaults to the current schema in effect for your SQL session.
  • function-identifier
    Identifies the function.
  • FROM
    Identifies the users from whom you are removing the specified 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. For expanded
    authorization-identifier
    syntax, see Expansion of Authorization-identifier.
Usage
Revoking Privileges
A user can hold a privilege on a resource through multiple resource names (for example, through the use of wildcards) or through multiple authorization identifiers (for example, through two different group identifiers). A REVOKE statement revokes the privileges specified in the statement only on the specified resource name and only from the specified authorization identifier.
For example, suppose:
  • User PKB is in the group SALES_ADMIN
  • PKB has been granted the CREATE privilege on the access module name SALES_SCH.SALESFCT
  • SALES_ADMIN has been granted the CREATE privilege on all access modules named SALES_SCH.SALES* where * is a wildcard character
You can revoke the CREATE privilege on SALESFCT from the user identifier PKB. However, PKB can still create an access module by that name in the SALES_SCH schema because PKB is a member of SALES_ADMIN.
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 any 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 table, you can revoke the privilege with a REVOKE SELECT statement or a REVOKE DEFINE statement. Using REVOKE DEFINE is an efficient technique when you intend to revoke all definition privileges on a table from a user or group, whether the privileges were granted singly or as a set.
Similarly, you can GRANT DEFINE on a table to a user and then REVOKE DELETE on the table from the same user as a way to grant all but one definition privilege.
Example
Revoking Privileges on a Schema
The following REVOKE statement removes 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