REVOKE

Purpose

Use the REVOKE statement to withdraw system privileges, object privileges, roles or the access to connections.

Prerequisites

  • For system privileges, the revoker requires the GRANT ANY PRIVILEGE system privilege, or the user must have received this system privilege with the WITH ADMIN OPTION.
  • For object privileges, the revoker requires the GRANT ANY OBJECT PRIVILEGE system privilege, or the user must be the owner of the object.
  • For roles, the revoker requires the GRANT ANY ROLE system privilege, or the use must have received the role with the WITH ADMIN OPTION.
  • Revoking impersonation privileges are restricted to users with role DBA.
  • For connections, the revoker requires the GRANT ANY CONNECTION system privilege, or the user must have received the connection with the WITH ADMIN OPTION.

Syntax

revoke_system_privileges::=

Revoke System Privileges

revoke_object_privileges::=

Revoke Object Privileges

Revoke Object Privileges

revoke_roles::=

Revoke Roles

revoke_impersonation::=

Revoke Impersonation

revoke_connection_restricted::=

Revoke Connection Restricted

Revoke Connection Restricted

revoke_connection::=

Revoke Connections

Usage Notes

  • If a user has received the same privilege or the same role from several users, then a corresponding REVOKE will delete all of these.
  • If an object privilege was granted to a single schema object and its schema (implicitly to all contained objects), and the privilege of the schema was revoked, then the object privilege for the single schema object is still retained.
  • The object privilege REFERENCES can only be revoked if the corresponding user has not yet created foreign keys on that table. In this case, you can automatically drop those foreign keys by specifying the option CASCADE CONSTRAINTS.
  • Unlike in Oracle, REVOKE ALL [PRIVILEGES] will delete all the system or object privileges, even if the user was not granted these privileges beforehand by using GRANT ALL.
  • For information on impersonation, refer to the description of IMPERSONATE statement.

Examples

-- System privilege
REVOKE CREATE SCHEMA FROM role1;
-- Object privileges
REVOKE SELECT, INSERT ON my_schema.my_table FROM user1, role2;
REVOKE ALL PRIVILEGES ON VIEW my_schema.my_view FROM PUBLIC;
-- Role
REVOKE role1 FROM user1, user2;
-- Impersonation
REVOKE IMPERSONATION ON user2 FROM user1;
-- Connections
REVOKE CONNECTION my_connection FROM user1;