Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

REVOKE

Description

Revokes various privileges to the specified users for the database. There are three forms of the REVOKE statement:

  • The first form revokes database-wide privileges, either system administration (DBA) or general creation (RESOURCE)
  • The second form revokes various privileges on specific tables and views
  • The third form revokes the privilege to execute the specified stored procedure

Syntax

REVOKE { RESOURCE | DBA }

FROM { {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ... } ;

REVOKE [ GRANT OPTION FOR ]

{ privilege [ , privilege, ] ... | ALL [ PRIVILEGES ] }

ON table_name

FROM { {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ... | PUBLIC } [ RESTRICT | CASCADE ] ;

REVOKE [ GRANT OPTION FOR ] EXECUTE ON procedure_name

FROM { {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ... | PUBLIC } [ RESTRICT | CASCADE ] ;

privilege ::

{ SELECT | INSERT | DELETE | ALTER | INDEX

| UPDATE [ (column, column, ... ) ]

| REFERENCES [ (column, column, ... ) ] }

Arguments

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 was passed on to other users, the REVOKE statement fails and generates an error. Otherwise, GRANT OPTION FOR implicitly revokes any rights the user may have in turn given to other users.

{ privilege [ , privilege, ] ... | ALL [ PRIVILEGES ] }

List of privileges to be revoked. See the description in GRANT (GRANT, SQL GRANT) for details on specific privileges. Revoking RESOURCE and DBA rights can only be done by the administrator or a user with DBA rights.

If a user has been granted access to a table by more than one user then all the users have to perform a revoke for the user to lose his access to the table.

Using the keyword ALL revokes all the rights granted on the table/view.

ON table_name

The table or view for which FairCom DB SQL revokes the specified privileges.

EXECUTE ON procedure_name

Revokes the right to execute the specified stored procedure.

FROM {user_name | usergroup_name} [ , {user_name | usergroup_name} ] ...

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

FROM PUBLIC

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

RESTRICT | CASCADE

If the REVOKE statement specifies RESTRICT, FairCom DB SQL checks to see if the privilege being revoked was passed on to other users (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 right of a user also revokes the rights from all users who received the privilege as a result of that user giving the privilege to others.

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 ALTER ON cust_view FROM dbuser2 ;

Authorization

The user revoking DBA or RESOURCE privileges must have the DBA privilege.

The user revoking privileges on a table must have any of the following privileges:

  • DBA privilege
  • Ownership of the table
  • All the specified privileges on the table, granted with the WITH GRANT OPTION clause

    SQL Compliance

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

    Environment

    Embedded SQL, interactive SQL, ODBC applications

    Related Statements

    GRANT

TOCIndex