Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## REVOKE

> Revokes various privileges from the specified users of the database. There are two forms of the `REVOKE` statement:

> - Revoke database-wide privileges, either system administration (`DBA`), general creation (`RESOURCE`), audit administration (`AUDIT_ADMIN`), audit archive (`AUDIT_ARCHIVE`), or audit insert (`AUDIT_INSERT`).

> - Revoke various privileges on specific tables and views.

> This is the syntax to `REVOKE` database-wide privileges:

> #### Syntax

>     REVOKE  { RESOURCE , DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT}
>        FROM { username [ , username ] , ... }
>        [ RESTRICT | CASCADE ];

> `RESOURCE`

> Revokes from the specified users the privilege to issue `CREATE` statements.

> `DBA`

> Revokes from the specified users the privilege to create, access, modify, or delete any database object, and revokes the privilege to grant other users any privileges.

> `AUDIT_ADMIN`

> Revokes from the specified users the privilege to administrate and maintain a database auditing system.

> `AUDIT_ARCHIVE`

> Revokes from the specified users the privilege to read and delete audit records.

> `AUDIT_INSERT`

> Revokes from the specified users the privilege to insert application audit records.

> `FROM username [ , username ] , ...`

> Revokes the specified privileges on the table or view from the specified list of users.

> `RESTRICT | CASCADE`

> Prompts SQL to check to see if the privilege being revoked was passed on to other users. This is possible only if the original privilege included the `WITH GRANT OPTION` clause. If so, the `REVOKE` statement fails and generates an error. If the privilege was not passed on, the `REVOKE` statement succeeds.

> If the `REVOKE` statement specifies `CASCADE`, revoking the access privileges from a user also revokes the privileges from all users who received the privilege from that user.

> If the `REVOKE` statement specifies neither `RESTRICT` nor `CASCADE`, the behavior is the same as for `CASCADE`.

> Note: `CASCADE` is not supported for `AUDIT_ADMIN`,`AUDIT_ARCHIVE`,`` and`AUDIT_INSERT` privileges. The only user who can revoke an audit privilege is the user who granted it.

> Example

> In this example, the audit administration privilege is revoked from bsmith:

>     REVOKE AUDIT_ADMIN FROM bsmith RESTRICT;

> This is the syntax to `REVOKE` privileges on specific tables and views:

> #### Syntax

>     REVOKE [ GRANT OPTION FOR ]
>        { privilege [, privilege ] , ...  | ALL [ PRIVILEGES ] }
>        ON table_name
>        FROM { username [ , username ] , ... | PUBLIC }
>         [ RESTRICT | CASCADE ] ;

> `GRANT OPTION FOR`

> Revokes the `GRANT` option for the privilege from the specified users. The actual privilege itself is not revoked. If specified with `RESTRICT`, and the privilege is passed on to other users, the `REVOKE` statement fails and generates an error. Otherwise, `GRANT` `OPTION` `FOR` implicitly revokes any privilege the user might have given to other users.

> `privilege`

> This is the syntax for the `privilege` item:

> Syntax
>     { SELECT | INSERT | DELETE | INDEX
>       | UPDATE [ ( column , column , ... ) ]
>       | REFERENCES [ ( column , column , ... ) ] } ;

> `privilege [ , privilege ] , ... | ALL [ PRIVILEGES ]`

> List of privileges to be revoked. See the description in the `GRANT` statement. Revoking `RESOURCE` and `DBA` privileges can only be done by the administrator or a user with `DBA` privileges.

> If more than one user grants access to the same table to a user, then all the grantors must perform a revoke for the user to lose access to the table.

> Using the keyword `ALL` revokes all the privileges granted on the table or view.

> `FROM PUBLIC`

> Revokes the specified privileges on the table or view from any user with access to the system.

> `RESTRICT | CASCADE`

> Prompts SQL to check to see if the privilege being revoked was passed on to other users. This is possible only if the original privilege included the `WITH GRANT OPTION` clause. If so, the `REVOKE` statement fails and generates an error. If the privilege was not passed on, the `REVOKE` statement succeeds.

> If the `REVOKE` statement specifies `CASCADE`, revoking the access privileges from a user also revokes the privileges from all users who received the privilege from that user.

> If the `REVOKE` statement specifies neither `RESTRICT` nor `CASCADE`, the behavior is the same as for `CASCADE`.

> Example

>     REVOKE INSERT ON customer FROM dbuser1 ;
>     REVOKE DELETE ON cust_view FROM dbuser2 ;

> Note: If the username specified in a `GRANT DBA` or `GRANT RESOURCE` operation does not already exist, the `GRANT` statement creates a row in the `SYSDBAUTH` system table for the new username. This row is not deleted by a subsequent `REVOKE` operation.

> ##### Authorization

> Must have the `DBA` privilege or ownership of the table (to revoke privileges on a table). To revoke audit privileges, the user must have the `DBA` privilege or `AUDIT ADMINISTRATION WITH GRANT` privilege and be the user who granted the audit privilege.

> ##### Related statements

> GRANT

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095