Removes access privileges.
REVOKE [GRANT OPTION FOR] { {SELECT | INSERT | UPDATE | DELETE
| REFERENCES | TRIGGER | TRUNCATE } [,...] | ALL [PRIVILEGES] }
ON [TABLE] <tablename> [, ...]
FROM {<rolename> | PUBLIC} [, ...]
[CASCADE | RESTRICT]
REVOKE [GRANT OPTION FOR] { {USAGE | SELECT | UPDATE} [,...]
| ALL [PRIVILEGES] }
ON SEQUENCE <sequencename> [, ...]
FROM { <rolename> | PUBLIC } [, ...]
[CASCADE | RESTRICT]
REVOKE [GRANT OPTION FOR] { {CREATE | CONNECT
| TEMPORARY | TEMP} [,...] | ALL [PRIVILEGES] }
ON DATABASE <dbname> [, ...]
FROM {rolename | PUBLIC} [, ...]
[CASCADE | RESTRICT]
REVOKE [GRANT OPTION FOR] {EXECUTE | ALL [PRIVILEGES]}
ON FUNCTION <funcname> ( [[<argmode>] [<argname>] <argtype>
[, ...]] ) [, ...]
FROM {<rolename> | PUBLIC} [, ...]
[CASCADE | RESTRICT]
REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}
ON LANGUAGE <langname> [, ...]
FROM {<rolename> | PUBLIC} [, ...]
[ CASCADE | RESTRICT ]
REVOKE [GRANT OPTION FOR] { {CREATE | USAGE} [,...]
| ALL [PRIVILEGES] }
ON SCHEMA <schemaname> [, ...]
FROM {<rolename> | PUBLIC} [, ...]
[CASCADE | RESTRICT]
REVOKE [GRANT OPTION FOR] { CREATE | ALL [PRIVILEGES] }
ON TABLESPACE <tablespacename> [, ...]
FROM { <rolename> | PUBLIC } [, ...]
[CASCADE | RESTRICT]
REVOKE [ADMIN OPTION FOR] <parent_role> [, ...]
FROM <member_role> [, ...]
[CASCADE | RESTRICT]
REVOKE
command revokes previously granted privileges from one or more roles. The key word PUBLIC
refers to the implicitly defined group of all roles.
See the description of the GRANT command for the meaning of the privilege types.
Note that any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC
. Thus, for example, revoking SELECT
privilege from PUBLIC
does not necessarily mean that all roles have lost SELECT
privilege on the object: those who have it granted directly or via another role will still have it. Similarly, revoking SELECT
from a user might not prevent that user from using SELECT
if PUBLIC
or another membership role still has SELECT
rights.
If GRANT OPTION FOR
is specified, only the grant option for the privilege is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.
If a role holds a privilege with grant option and has granted it to other roles then the privileges held by those other roles are called dependent privileges. If the privilege or the grant option held by the first role is being revoked and dependent privileges exist, those dependent privileges are also revoked if CASCADE
is specified, else the revoke action will fail. This recursive revocation only affects privileges that were granted through a chain of roles that is traceable to the role that is the subject of this REVOKE
command. Thus, the affected roles may effectively keep the privilege if it was also granted through other roles.
When revoking membership in a role, GRANT OPTION
is instead called ADMIN OPTION
, but the behavior is similar.
See GRANT.
Revoke insert privilege for the public on table films
:
REVOKE INSERT ON films FROM PUBLIC;
Revoke all privileges from role sally
on view topten
. Note that this actually means revoke all privileges that the current role granted (if not a superuser).
REVOKE ALL PRIVILEGES ON topten FROM sally;
Revoke membership in role admins
from user joe
:
REVOKE admins FROM joe;
The compatibility notes of the GRANT command also apply to REVOKE
.
Either RESTRICT
or CASCADE
is required according to the standard, but Greenplum Database assumes RESTRICT
by default.
Parent topic: SQL Command Reference