Progress
SQL-92
Guide and Reference


REVOKE Statement

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

This is syntax to REVOKE system administration privileges (DBA) or general creation privileges (RESOURCE), or both:

SYNTAX

REVOKE  { RESOURCE , DBA }  FROM { username [ , username ] , ... } ; 

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.

FROM username [ , username ] , ...

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

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

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 (to revoke DBA or RESOURCE privileges), ownership of the table (to revoke privileges on a table),

SQL COMPLIANCE

SQL-92, ODBC Core SQL grammar. Progress Extensions: INDEX, RESOURCE, DBA privileges

ENVIRONMENT

Embedded SQL, interactive SQL, ODBC applications, JDBC applications

RELATED STATEMENTS

GRANT Statement


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