REVOKE

Function

REVOKE revokes permissions from one or more roles.

Precautions

If a non-owner user of an object attempts to REVOKE permission on the object, the statement is executed based on the following rules:

• If the user has no permissions whatsoever on the object, the statement will fail outright.
• If an authorized user has some permissions, only the permissions with authorization options are revoked.
• If the authorized user does not have the authorization option, the REVOKE ALL PRIVILEGES form will issue an error message. For other forms of statements, if the permission specified in the statement does not have the corresponding authorization option, the statement will issue a warning.
• Do not perform REVOKE to a table partition. Otherwise, an alarm will be generated.

Syntax

• Revoke the permission on a specified table or view.

  REVOKE [ GRANT OPTION FOR ]
      { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM }[, ...]
      | ALL [ PRIVILEGES ] }
      ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a specified field in a table.

  REVOKE [ GRANT OPTION FOR ]
      { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )}[, ...]
      | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
      ON [ TABLE ] table_name [, ...]
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a specified sequence.

  REVOKE [ GRANT OPTION FOR ]
      { { SELECT | UPDATE | ALTER | DROP | COMMENT }[, ...]
      | ALL [ PRIVILEGES ] }
      ON { [ SEQUENCE ] sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a specified database.

  REVOKE [ GRANT OPTION FOR ]
      { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...]
      | ALL [ PRIVILEGES ] }
      ON DATABASE database_name [, ...]
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a specified function.

  REVOKE [ GRANT OPTION FOR ]
      { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a specified large object.

  REVOKE [ GRANT OPTION FOR ]
      { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
      ON LARGE OBJECT loid [, ...]
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a specified schema.

  REVOKE [ GRANT OPTION FOR ]
      { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON SCHEMA schema_name [, ...]
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a specified tablespace.

  REVOKE [ GRANT OPTION FOR ]
      { { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON TABLESPACE tablespace_name [, ...]
      FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke permissions from a role.

  REVOKE [ ADMIN OPTION FOR ]
      role_name [, ...] FROM role_name [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the sysadmin permission from a role.

  REVOKE ALL { PRIVILEGES | PRIVILEGE } FROM role_name;

• Revoke the permission on a data source object.

  REVOKE {USAGE | ALL [PRIVILEGES]}
      ON DATA SOURCE src_name [, ...]
      FROM {[GROUP] role_name | PUBLIC} [, ...];

• Revoke the specified CMK permission.

  REVOKE [ GRANT OPTION FOR ]
      {USAGE | DROP | ALL [PRIVILEGES]}
      ON {  CLIENT_MASTER_KEYS  client_master_keys_name [, ...]
            FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the specified CEK permission.

  REVOKE [ GRANT OPTION FOR ]
      {USAGE | DROP | ALL [PRIVILEGES]}
      ON {  COLUMN_ENCRYPTION_KEYS column_encryption_keys_name [, ...]
            FROM { [ GROUP ] role_name | PUBLIC } [, ...]
      [ CASCADE | RESTRICT ];

• Revoke the permission on a directory object.

  REVOKE {READ|WRITE| ALL [PRIVILEGES]}
     ON DIRECTORY src_name [, ...]
     FROM {[GROUP] role_name | PUBLIC} [, ...] [WITH GRANT OPTION];

Parameter Description

The keyword PUBLIC indicates an implicitly defined group that has all roles.

For details about the permission types and parameters, see Parameter Description in GRANT.

Permissions of a role include the permissions directly granted to the role, permissions inherited from the parent role, and permissions granted to PUBLIC. Therefore, revoking the SELECT permission for an object from PUBLIC does not necessarily mean that the SELECT permission for the object has been revoked from all roles, because the SELECT permission directly granted to roles and inherited from parent roles remains. Similarly, if the SELECT permission is revoked from a user but is not revoked from PUBLIC, the user can still run the SELECT statement.

If GRANT OPTION FOR is specified, the permission cannot be granted to others, but permission itself is not revoked.

If user A holds the UPDATE permissions on a table and the WITH GRANT OPTION and has granted them to user B, the permissions that user B holds are called dependent permissions. When user A's permission or grant option is revoked, CASCADE must be specified to revoke all dependent permissions.

A user can only revoke permissions that were granted directly by that user. For example, if user A has granted permission with grant option (WITH ADMIN OPTION) to user B, and user B has in turn granted it to user C, then user A cannot revoke the permission directly from C. However, user A can revoke the grant option held by user B and use CASCADE. In this way, the permission of user C is automatically revoked. For another example, if both user A and user B have granted the same permission to C, A can revoke his own grant but not B's grant, so C will still effectively have the permission.

If the role executing REVOKE holds permissions indirectly via more than one role membership path, it is unspecified which containing role will be used to execute the statement. In such cases, it is best practice to use SET ROLE to become the specific role you want to do the REVOKE as, and then execute REVOKE. Failure to do so may lead to deleting permissions not intended to delete, or not deleting any permissions at all.

Examples

See Examples in GRANT.