Product Documentation

Appearance

REVOKE ​

Revoke privileges from specific roles on database objects. Also revoke certain privileges at the system level and revoke membership from roles.

REVOKE [ GRANT OPTION FOR ]
   { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES }
   [, ...] | ALL [ PRIVILEGES ] }
   ON { [ TABLE ] table_name [, ...]
        | ALL TABLES IN SCHEMA schema_name [, ...] }
   FROM { role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
   [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
   ON [ TABLE ] table_name [, ...]
   FROM { role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { CREATE | CONNECT | TEMPORARY | TEMP | EXPLAIN QUERY | TRACE QUERY | VIEW QUERY TEXT | BACKUP | RESTORE } [, ...] | ALL [ PRIVILEGES ] }
   ON DATABASE database_name [, ...]
   FROM { role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { EXECUTE | ALL [ PRIVILEGES ] }
   ON { PROCEDURE procedure_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
        | ALL PROCEDURES IN SCHEMA schema_name [, ...] }
   FROM { [ GROUP ] role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
   ON SCHEMA schema_name [, ...]
   FROM { role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
   ON { SEQUENCE sequence_name [, ...]
        | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
   FROM { role_name | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ ADMIN OPTION FOR ]
   role_name [, ...] 
   FROM role_name [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { ENCRYPT | DECRYPT | HMAC | ALL [ PRIVILEGES ] }
   ON KEY key_name 
   FROM { role_specification } [, ...] 

REVOKE [ GRANT OPTION FOR ]
   { CREATE ROLE | ALTER ANY ROLE | DROP ANY ROLE | CREATE DATABASE | RESTORE ANY DATABASE } [, ...] | ALL [ PRIVILEGES ] }
   ON SYSTEM
   FROM { role_specification } [, ...]

Note: See the GRANT command for details about most of the REVOKE options.

TABLE

When you revoke privileges on a table, any corresponding column privileges are automatically revoked. However, if a role has been granted privileges on a table, revoking the same privileges from individual columns has no effect.

GRANT OPTION FOR, ADMIN OPTION FOR

This syntax means that the grant option for the privilege is revoked, not the privilege itself. If you do not specify GRANT OPTION FOR, both the privilege and the grant option are revoked. If a user holds a privilege with grant option and has granted it to other users, the privileges held by those other users are dependent privileges.

When you revoke membership in a role, GRANT OPTION FOR is called ADMIN OPTION FOR, but the behavior is similar.

CASCADE

This syntax revokes dependent privileges. If the privilege or the grant option held by a user is being revoked and dependent privileges exist, those dependent privileges are also revoked if CASCADE is specified; if it is not specified, the revoke action fails. Affected users might keep the privilege if it was also granted through users other than the subject of this REVOKE command.

role_name

When you revoke membership in a role, GRANT OPTION FOR is called ADMIN OPTION FOR, but the behavior is similar.

Examples ​

Revoke USAGE on a sequence:

premdb=# revoke usage on sequence matchid from bobr;
REVOKE

Revoke EXECUTE privilege on a stored procedure from the ybd user:

premdb=# revoke execute on procedure proc1() from ybd;
REVOKE

Revoke ENCRYPT privilege on key yb100key from user yb100:

premdb=# revoke encrypt on key yb100key from yb100;
REVOKE

Revoke ENCRYPT and DECRYPT privileges on key yb100key from user yb100:

premdb=# revoke all privileges on key yb100key from yb100;
REVOKE

Parent topic:SQL Commands