Previous Topic: REVOKE All Table PrivilegesNext Topic: REVOKE Execution Privilege

Statements › REVOKE SQL Definition Privileges

REVOKE SQL 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.

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:

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