Revoking user privileges and roles - Oracle DBA

REVOKE

Use to revoke privileges already granted to other users.

For example to revoke select, update, insert privilege you have granted to Sami then give the following statement.

revoke select, update, insert on emp from sami;

To revoke select statement on emp granted to public give the following command.

revoke select on emp from public;

To revoke update privilege on ename column and insert privilege on empno and ename columns give the following revoke statement.

revoke update, insert on emp from sami;

Note :You cannot take back column level privileges. Suppose you just want to take back  insert privilege on ename column, then you have to first take back the whole insert privilege and then grant privilege on empno column.

This section describes aspects of revoking user privileges and roles, and includes the following topics:

Revoking system privileges and roles:

You can revoke system privileges and/or roles using either the revoke system privileges/roles dialog box of enterprise manager or the sql command revoke.

Any user with the admin option for a system privilege or role can revoke the privilege or role from any other DATABASE user or role the grantor does not have to be the user that originally granted the privilege or role. Also, users with the grant any role can revoke any role.

The following statement revokes the create table SYSTEM PRIVILEGE and the accts_rec role from tsmith:

Sql>revoke create table, accts_rec from tsmith;

Revoking object privileges and roles:

You can revoke object privileges using enterprise manager or the sql command revoke.

To revoke an object privilege, the revoker must be the original grantor of the object privilege being revoked.

For example, assuming you are the original grantor, to revoke the select and insert privileges on the emp table from the users jfee and tsmith, you would issue the following statement:

Sql>revoke select, insert on emp  from jfee, tsmith;

The following statement revokes all privileges (which were originally granted to the role human_resource) from the table dept:

Sql>revoke all on dept from human_resources;

Checkout Oracle DBA Interview Questions

Revoking column selective object privileges:

Although users can grant column selective insert, update, and references privileges for tables and views, they cannot selectively revoke column specific privileges with a similar revoke statement. Instead, the grantor must first revoke the object privilege for all columns of a table or view, and then selectively re-grant the column specific privileges that should remain.

For example, assume that role human_resources has been granted the update privilege on the deptno and dname columns of the table dept. To revoke the update privilege on just the deptno column, you would issue the following two statements:

Sql>revoke update on dept from human_resources;
Sql>grant update (dname) on dept to human_resources;

The revoke statement revokes update privilege on all columns of the dept table from the role human_resources. The grant statement re-grants update privilege on the dname column to the role human_resources.