SQL REVOKE

The privileges that you have granted with the GRANT statement can be taken away with the SQL REVOKE statement. When you issue a REVOKE statement in SQL, you can take away only those privileges that you previously granted to another user. That user may also have privileges that were granted by other users; those privileges are not affected by your SQL REVOKE statement. Note specifically that if two different users grant the same privilege on the same object to a user and one of them later revokes the privilege, the second user’s grant will still allow the user to access the object.

Following privileges can be removed using Revoke:

Privileges on database:

• CONNECT allows user to connect to database.
• RESOURCE security privilege allows a user to create, alter, and drop- tables, domains, and indexes.
• DBA privilege has the same capabilities as the RESOURCE privilege, but may also create tablespaces and files. Users with the DBA privilege can also grant or revoke object privileges for schema objects owned by other users, except system schema objects.

Example 1:

The following revokess the CONNECT privilege from a user named Bryan and a user named Jenny.

REVOKE CONNECT FROM bryan, jenny;

Example 2:

The following revokes the RESOURCE privilege from users Bryan and Jenny.

REVOKE RESOURCE FROM bryan, jenny;

Example 3:

The following revokes the DBA privilege from users Bryan and Jenny.

REVOKE DBA FROM bryan, jenny

Privileges on objects:

• SELECT privilege is used to select data in a database object, applies to the entire object, and cannot be granted to specific columns.
• INSERT privilege is used to insert new data into a database object. The privilege can also be restricted to specific columns.
• DELETE privilege is used to delete data from a database object, applies to the entire object and cannot be granted on specific columns.
• UPDATE privilege is used to update data in a database object. The privilege can also be restricted to specific columns.
• INDEX privilege is used to create an index on a database object, applies to the entire object, and cannot be granted on specific columns.
• ALTER privilege is used to alter the schema of a database object, applies to the entire object and cannot be granted on specific columns.
• REFERENCE privilege is used to create referential constraints, such as foreign keys, on a database object. The privilege can also be restricted to specific columns.
• EXECUTE privileges control which executable database objects a user can use.

The keywords ALL and ALL PRIVILEGES can also be used to revoke all privileges on an object.

Example 4:

The following revokess SELECT, INSERT, and UPDATE object privileges on the
Employee table from the user named Vivian.

REVOKE SELECT, INSERT, UPDATE ON employee FROM Vivian;

Example 5:

The following revokes all privileges on the Employee table from the user named Sue.

REVOKE ALL PRIVILEGES ON employee FROM sue;