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 user 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.

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.

• For information about system and object privileges, see the lists below. For more details about privileges and rights management, refer to Details on rights management under the Privileges section.

List of System and Object privileges

System privileges

Users

System privilege	Permissions
ALTER USER	Alter the password of any user This is a powerful privilege and should be granted restrictively.
CREATE USER	Create user
DROP USER	Delete user
IMPERSONATE ANY USER	Impersonate other users

Roles

System privilege	Permissions
CREATE ROLE	Create roles
DROP ANY ROLE	Delete roles
GRANT ANY ROLE	Grant any role This is a powerful privilege and should be granted restrictively.

Connections

System privilege	Permissions
ACCESS ANY CONNECTION	Access any connection details from scripts
ALTER ANY CONNECTION	Change connection data of a connection
CREATE CONNECTION	Create external connections
DROP ANY CONNECTION	Delete connections
GRANT ANY CONNECTION	Grant any connection to users/roles
USE ANY CONNECTION	Use any connection in statements IMPORT and EXPORT

Schema

System privilege	Permissions
ALTER ANY SCHEMA	Allocate a schema to another user or role
ALTER ANY VIRTUAL SCHEMA	Update parameters of any virtual schema
ALTER ANY VIRTUAL SCHEMA REFRESH	Update the metadata of any virtual schema
CREATE SCHEMA	Create a schema
CREATE VIRTUAL SCHEMA	Create of a virtual schema
DROP ANY SCHEMA	Delete any schema
DROP ANY VIRTUAL SCHEMA	Delete any virtual schema
USE ANY SCHEMA	See and use any schema. This privilege is granted to PUBLIC by default in a new database.

Tables

System privilege	Permissions
ALTER ANY TABLE	Alter a table in any schema
CREATE ANY TABLE	Create a table in any schema
CREATE TABLE	Create a table in the user’s own schema or the schema of an allocated role DROP is implicit because the owner of an object can always delete it.
DELETE ANY TABLE	Delete rows in a table in any schema
DROP ANY TABLE	Delete tables in any schema
INSERT ANY TABLE	Insert data into a table in any schema
SELECT ANY DICTIONARY	Access the contents of any system table
SELECT ANY TABLE	Access the contents of a table or view in any schema (does not include system tables)
UPDATE ANY TABLE	Alter rows in a table in any schema

Views

System privilege	Permissions
CREATE VIEW	Create a view in the user’s own schema or in the schema of an allocated role DROP is implicit because the owner of an object can always delete it.
CREATE ANY VIEW	Create a view in any schema
DROP ANY VIEW	Delete views in any schema

Functions

System privilege	Permissions
CREATE FUNCTION	Create a function in the user’s own schema or in the schema of an allocated role DROP is implicit because the owner of an object can always delete it.
CREATE ANY FUNCTION	Create a function in any schema
DROP ANY FUNCTION	Delete functions in any schema
EXECUTE ANY FUNCTION	Execute functions in any schema

Scripts

System privilege	Permissions
CREATE SCRIPT	Create a script in the user’s own schema or in the schema of an allocated role DROP is implicit because the owner of an object can always delete it.
CREATE ANY SCRIPT	Create a script in any schema
DROP ANY SCRIPT	Delete scripts in any schema
EXECUTE ANY SCRIPT	Execute scripts in any schema

Miscellaneous

System privilege	Permissions
ALTER SYSTEM	Alter system-wide settings (for example, NLS_DATE_FORMAT)
CREATE SESSION	Connect to the database
EXPORT	Export data from tables using the EXPORT command
GRANT ANY OBJECT PRIVILEGE	Grant or withdraw any object rights
GRANT ANY PRIVILEGE	Grant or withdraw any system rights This is a powerful privilege and should be granted restrictively.
IMPORT	Import data into tables using the IMPORT command
KILL ANY SESSION	Kill session or query
MANAGE CONSUMER GROUPS	Create, alter and drop consumer groups
SET ANY CONSUMER GROUP	Set consumer group for users or roles

Object privileges

Object privilege	Schema objects	Permissions
ACCESS	Connection	Access details of a connection from scripts
ALTER	Schema, table, virtual schema	Run the ALTER TABLE statement and ALTER VIRTUAL SCHEMA (except CHANGE OWNER)
DELETE	Schema, table	Delete rows
EXECUTE	Schema, function, script	Run functions or scripts
IMPERSONATION	User	Impersonate another user identity
INSERT	Schema, table	Insert rows in a table
REFERENCES	Table	Create foreign keys referencing this table
REFRESH	Virtual schema	Update the metadata of a virtual schema
SELECT	Schema, table, view, virtual schema, virtual table	Access the table contents
UPDATE	Schema, table	Change the contents of a row in a table
USAGE	Schema	View and use a schema

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;