Skip to main content

REVOKE

Revokes privileges from one or more roles.


Syntax

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

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE } ( <column_name> [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( <column_name> [, ...] ) }
ON { [ [TABLE] [[[ONLY] <table_name> [, ...]] [, ...]] }
FROM <role_spec> [, ...]
[ CASCADE | RESTRICT ]

REVOKE [GRANT OPTION FOR]
{ {CREATE | CONNECT | TEMPORARY | TEMP} [, ...] | ALL [PRIVILEGES] }
ON DATABASE <database_name> [, ...]
FROM <role_spec> [, ...]
[CASCADE | RESTRICT]


REVOKE [GRANT OPTION FOR] {EXECUTE | ALL [PRIVILEGES]}
ON { { FUNCTION | PROCEDURE | ROUTINE } <funcname> [( [[<argmode>] [<argname>] <argtype> [, ...]] )] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM <role_spec> [, ...]
[CASCADE | RESTRICT]

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

where <role_spec> can be:

[ GROUP ] <role_name>
| PUBLIC
| CURRENT_USER
| SESSION_USER

Description

To revoke privileges from all roles, you can specify keyword PUBLIC in your statement. However, to revoke a privilege from PUBLIC on an object does not indicates that all roles do not have the privilege on the object. This is because the privileges that a role consist of the the privileges that are directly granted to it, the privileges that PUBLIC has, and the privileges that are granted to any other role of which the role is a member. For example, after you revoke the INSERT privilege on view myview from PUBLIC, the roles to which the INSERT privilege are directly granted and their members still can have the INSERT privilege on myview.

To revoke the grant option for a privilege from a role, specify GRANT OPTION FOR in your statement. By doing this, the role still has the privilege. Note that if you also specify keyword CASCADE, the privilege will also be revoked from the roles to which the privilege is granted by this role.

When you REVOKE a privilege on a table from a role, the privilege on each column of the table will also be revoked. But when you REVOKE the privilege from one or more columns of the table, the role still has the table-level privilege.


Parameters

For details, see section Parameters in GRANT.


Examples

Revoke the SELECT privilege on table students from role mary:

REVOKE SELECT ON students FROM mary;

Revoke the membership in role role1 from user john:

REVOKE role1 FROM john;

SQL standard compatibility

See section "SQL standard compatibility" in GRANT.