Revokes access privileges.

Syntax

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

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

You can use the REVOKE command to revoke privileges that have been granted to one or more roles. The PUBLIC keyword refers to the implicitly defined group of all roles.

For more information about the types of privileges, see the description of GRANT command.

Note that a role has the privileges that are granted directly to the role, the privileges that are granted to another role of which the role is a member, and the privileges that are granted to PUBLIC. For example, if you revoke the SELECT privilege from PUBLIC, it does not indicate that all roles have lost the SELECT privilege on the object. The roles that have the SELECT privilege granted directly and their member roles still have the SELECT privilege.

If the privilege is granted with the grant option, both privilege and the grant option for the privilege are revoked.

If a user has a privilege with the grant option and grants the privilege to other users, the privilege held by other users is called dependent privileges. If you want to revoke the privilege or grant option from the first user and dependent privileges exist, the dependent privileges are also revoked when CASCADE is specified. Otherwise, the revoke action failed. This recursive revocation only affects privileges that are granted by a chain of users that starts from the user who runs this REVOKE command. The affected users may keep the privilege if it is also granted by other users.

Notice The CASCADE option is not compatible with Oracle databases. By default, Oracle cascades dependent privileges. However, PolarDB databases compatible with Oracle requires the explicit CASCADE keyword. Otherwise, the REVOKE command will fail.

When revoking membership in a role, use GRANT OPTION instead of ADMIN OPTION, but the behavior is similar.

Notes

A user can only revoke privileges that are granted by the user. For example, if User A grants a privilege with the grant option to User B and User B grants the privilege to User C, User A cannot revoke the privilege directly from User C. Instead, User A can revoke the grant option from User B and use the CASCADE option so that the privilege is revoked from User C. For another example, if both User A and User B grant the same privilege to User C, User A can revoke the privilege granted by User A but not by User B. Therefore, after User A revokes the privilege, User C still has the privilege that is granted by User B.

If a user has no privileges on an object that belongs to another user and the non-owner user attempts to revoke privileges on the object by running the REVOKE command, the command fails. If a privilege can be revoked, the command proceeds but revokes only the privileges for which the user has grant options. If no grant options are held, the REVOKE ALL PRIVILEGES forms issue a warning message. Other forms also issue a warning message if the grant option for a privilege specified in the command is not held. This mechanism applies to the object owner. However, no warning messages are issued for the object owner because the owner holds all grant options.

In addition to the object owner, REVOKE can also be done by a member of the role that owns the object or a member of a role that holds the WITH GRANT OPTION privilege on the object. In this case, the command result is same as the result of the command that is issued by the containing role that owns the object or holds the WITH GRANT OPTION privilege. For example, if the t1 table is owned by the g1 role of which the u1 role is a member, u1 can revoke privileges on t1 that are granted by g1. Both the grants made by the u1 role and other members of the g1 role are revoked.

If the role that runs the REVOKE command holds privileges that are granted through multiple role chains, you cannot specify the role chain from which the privilege is revoked. In such cases, use SET ROLE to assume the role as which you want to run the REVOKE command. Otherwise, the privileges that are revoked are not the ones you intended, or are not revoked at all.

Note The ALTER ROLE command of PolarDB databases compatible with Oracle also supports syntax that revokes the system privileges required to create a public or private database link, or the exemptions from fine-grained access control policies (DBMS_RLS). The ALTER ROLE command is functionally equivalent to the respective REVOKE command, and is compatible with Oracle databases.

Examples

Revoke the INSERT privilege on the emp table from the PUBLIC group:

REVOKE INSERT ON emp FROM PUBLIC;

Revoke all privileges on the salesemp view from the user named mary:

REVOKE ALL PRIVILEGES ON salesemp FROM mary;

Note that all privileges granted by the user that runs the command are revoked.

Revoke membership in the admins role from the user named joe:

REVOKE admins FROM joe;

Revoke the CONNECT privilege from the user named joe:

REVOKE CONNECT FROM joe;

Revoke the CREATE DATABASE LINK privilege from the user named joe:

REVOKE CREATE DATABASE LINK FROM joe;

Revoke the EXEMPT ACCESS POLICY privilege from the user named joe:

REVOKE EXEMPT ACCESS POLICY FROM joe;