Use the REVOKE statement to withdraw system privileges, object privileges, roles or the access to connections.
- 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.
- 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.
-- Object privileges
REVOKE SELECT, INSERT ON my_schema.my_table FROM user1, role2;
REVOKE ALL PRIVILEGES ON VIEW my_schema.my_view FROM PUBLIC;