Syntax Description of REVOKE

GRANT OPTION FOR

Specifies that the WITH GRANT OPTION or the privilege is to be revoked. The privilege itself is not revoked.

privilege [,privilege ]... | ALL_DML [PRIVILEGES] | ALL_DDL [PRIVILEGES] | ALL [PRIVILEGES]

Specifies the privileges to revoke.

You can specify these privileges for a table or a view.

SELECT

Cannot use the SELECT statement on the object.

DELETE

Cannot use the DELETE statement on the object.

INSERT

Cannot use the INSERT statement on the object.

UPDATE

Cannot use the UPDATE statement on the object.

REFERENCES

Cannot create constraints that reference the object.

You can specify this privilege for a sequence generator.

USAGE

Cannot use the pseudocolumns CURRVAL and NEXTVAL to access sequence generator values.

You can specify this privilege for a stored procedure or function.

EXECUTE

Cannot call the stored procedure or function.

You can specify this privilege for all types of objects.

ALL PRIVILEGES

Cannot have all privileges that apply to the object type.

When the object is a table or view and ALL is specified, it implies the SELECT, DELETE, INSERT, UPDATE, and REFERENCES privileges. When the object is a stored procedure and ALL is specified, only the EXECUTE privilege is implied. When the object is a sequence generator and ALL is specified, only the USAGE privilege is implied.

You can specify this privilege for a table or view.

ALL_DML PRIVILEGES

Cannot have all DML privileges.

You can specify these privileges for objects in the schema.

CREATE

Cannot authorize the grantee to create objects in the schema.

ALTER

Cannot authorize the grantee to alter the definition of the objects in the schema. This includes the ability to execute utility operations that alter the definition of objects, like MODIFY.

DROP

Cannot authorize the grantee to drop any objects in the schema, including the schema itself.

ALL_DDL

Cannot authorize the grantee to revoke all DDL privileges on the objects.

(column [,column]...)

Names the columns of the table or view on which the UPDATE or REFERENCES privileges are revoked. If you specify UPDATE or REFERENCES without column names, the privileges are revoked from all columns of the table or view.

ON [TABLE | SEQUENCE | PROCEDURE ] object

Specifies the table, view, sequence generator, or stored procedure from which to revoke privileges.

ON SCHEMA schema-name

Specifies the schema name on which to revoke privileges.

FROM grantee [,grantee ]...

Specifies one or more users from whom you revoke privileges. A grantee can be an authid, a usernumber, or one of the special authorization IDs: PUBLIC and DB_PUBLIC.

authid specifies an authorization ID from whom you revoke privileges. Authorization IDs identify users during the processing of SQL statements. The authorization ID must be a valid Guardian username, enclosed in double quotes (for example, "PAYROLL.HANS"). authid is not case-sensitive.

SQL: 1999 specifies two special authorization IDs: PUBLIC and SYSTEM.

  • PUBLIC specifies all present and future authorization IDs.

  • SYSTEM specifies the implicit grantor of privileges to the creators of objects.

You cannot specify SYSTEM as a grantee in a REVOKE statement.

usernumber specifies the user from whom you are revoking the privilege. The usernumber is any legal Guardian user number (group, user) enclosed in double quotes (for example, "255,255"). The usernumber option is provided as a means of revoking a privilege from a user that has been deleted from the USERID file.

drop-behavior

If you specify RESTRICT, the REVOKE operation fails if there are privilege descriptors or objects that would no longer be valid after the specified privileges are removed.

If you specify CASCADE, any such dependent privilege descriptors and objects are removed as part of the REVOKE operation.

The default is RESTRICT.

BY authid-grantor

Specifies the authorization ID authid-grantor on whose behalf the revoke operation is performed. authid-grantor cannot be SYSTEM. A user number for authid-grantor (for example, "151,18") is not allowed. A Security Administrator or the Super ID can use the BY clause, however the SUPER ID can only use the BY clause when one of the following applies:

  • The Security Administrators Group is empty.

  • The Super ID has been designated as a Security Administrator.

The effect of using the BY clause is the same as if the authid-grantor were to issue the REVOKE directly (without using the BY clause). If the Security Administrator's Group is empty, then authid-grantor must be a valid authorization ID and hold one or more privileges being revoked WITH GRANT OPTION. Security Administrators have Super REVOKE BY capabilities in which authid-grantor may be any valid authorization ID that previously granted privileges on the target object.

authid

Specifies an authorization ID which must be one of the following:

  • A valid Guardian username, enclosed in double quotes.

  • An External Username that is already associated with one of the Guardian users.

  • The special authid, PUBLIC, or DB_PUBLIC.

pg-name

Is the name of an existing privilege group, a SQL identifier.