REVOKE v17

Name

REVOKE — Remove access privileges.

Synopsis

REVOKE { { SELECT | INSERT | UPDATE | DELETE | REFERENCES }
  [,...] | ALL [ PRIVILEGES ] }
  ON tablename
  FROM { username | groupname | PUBLIC } [, ...]
  [ CASCADE | RESTRICT ]

REVOKE { { CREATE | DROP | READ | WRITE } }
  ON DIRECTORY dirname 
  FROM { username } 

REVOKE { SELECT | ALL [ PRIVILEGES ] }
  ON sequencename
  FROM { username | groupname | PUBLIC } [, ...]
  [ CASCADE | RESTRICT ]

REVOKE { EXECUTE | ALL [ PRIVILEGES ] }
  ON FUNCTION progname
    ( [ [ argmode ] [ argname ] argtype ] [, ...] )
  FROM { username | groupname | PUBLIC } [, ...]
  [ CASCADE | RESTRICT ]

REVOKE { EXECUTE | ALL [ PRIVILEGES ] }
  ON PROCEDURE progname
    [ ( [ [ argmode ] [ argname ] argtype ] [, ...] ) ]
  FROM { username | groupname | PUBLIC } [, ...]
  [ CASCADE | RESTRICT ]

REVOKE { EXECUTE | ALL [ PRIVILEGES ] }
  ON PACKAGE packagename
  FROM { username | groupname | PUBLIC } [, ...]
  [ CASCADE | RESTRICT ]

REVOKE role [, ...] FROM { username | groupname | PUBLIC }
  [, ...]
  [ CASCADE | RESTRICT ]

REVOKE { CONNECT | RESOURCE | DBA } [, ...]
  FROM { username | groupname } [, ...]

REVOKE CREATE [ PUBLIC ] DATABASE LINK
  FROM { username | groupname }

REVOKE DROP PUBLIC DATABASE LINK
  FROM { username | groupname }

REVOKE EXEMPT ACCESS POLICY
  FROM { username | groupname }

Description

The REVOKE command revokes privileges that were granted to one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles.

See GRANT for the meaning of the privilege types.

A role has the sum of:

• Privileges granted directly to it
• Privileges granted to any role it is presently a member of
• Privileges granted to PUBLIC

Thus, for example, revoking the SELECT privilege from PUBLIC doesn't necessarily mean that all roles lose SELECT privilege on the object. Roles that were granted the privilege directly or from another role still have it.

If the privilege was granted with the grant option, the grant option for the privilege is revoked along with the privilege.

If a user holds a privilege with grant option and granted it to other users, then the privileges held by those other users are called dependent privileges. If the privilege or the grant option held by the first user is revoked, any dependent privileges are also revoked if CASCADE is specified. Without the CASCADE option, the revoke action fails. This recursive revocation affects only privileges that were granted through a chain of users that's traceable to the subject of this REVOKE command. The affected users can keep the privilege if it was also granted through other users.

When revoking membership in a role, GRANT OPTION is called ADMIN OPTION. The behavior is similar.

Notes

A user can revoke only the privileges that were granted directly by that user. If, for example, user A granted a privilege with grant option to user B, and user B granted it to user C, then user A can't revoke the privilege directly from C. Instead, user A can revoke the grant option from user B and use the CASCADE option to revoke the privilege from user C. For another example, if both A and B granted the same privilege to C, A can revoke their own grant but not B’s grant. C still has the privilege.

When a non-owner of an object attempts to revoke privileges on the object, the command fails if the user doesn't have privileges on the object. As long as some privilege is available, the command proceeds, but it revokes only those privileges for which the user has grant options. The REVOKE ALL PRIVILEGES forms issue a warning message if no grant options are held. The other forms issue a warning if grant options for any of the privileges named in the command aren't held. In principle, these statements apply to the object owner as well. However, since the owner is always treated as holding all grant options, the cases can never occur.

If a superuser issues a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. Since all privileges ultimately come from the object owner (possibly indirectly by way of chains of grant options), a superuser can revoke all privileges. This might require use of CASCADE.

A role that is not the owner of the affected object can also use REVOKE. That role must be a member of the role that owns the object or a member of a role that holds privileges WITH GRANT OPTION on the object. In this case, the command is performed as if issued by the containing role that owns the object or holds the privileges WITH GRANT OPTION. For example, if table t1 is owned by role g1, of which role u1 is a member, then u1 can revoke privileges on t1 that are recorded as being granted by g1. This includes grants made by u1 as well as by other members of role g1.

If the role executing REVOKE holds privileges indirectly by more than one role membership path, the containing role that performs the command is unspecified. In such cases, best practice is to use SET ROLE to become the specific role you want to do the REVOKE as. Otherwise, you might revoke privileges other than the ones you intended or not revoke anything at all.

Examples

Revoke insert privilege for the public on table emp:

REVOKE INSERT ON emp FROM PUBLIC;

Revoke all privileges from user mary on view salesemp. This actually means “revoke all privileges that I granted.”

REVOKE ALL PRIVILEGES ON salesemp FROM mary;

Revoke write privileges from user betty on directory test1.

REVOKE WRITE ON test1 FROM betty;

Revoke membership in role admins from user joe:

REVOKE admins FROM joe;

Revoke CONNECT privilege from user joe:

REVOKE CONNECT FROM joe;

Revoke CREATE DATABASE LINK privilege from user joe:

REVOKE CREATE DATABASE LINK FROM joe;

Revoke the EXEMPT ACCESS POLICY privilege from user joe:

REVOKE EXEMPT ACCESS POLICY FROM joe;

See also

GRANT, SET ROLE

• On this page
• Name
• Synopsis
• Description
• Notes
• Examples
• See also