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.