Object Privileges  «Prev  Next»

Lesson 7Revoking Privileges
ObjectiveRevoke Users Object Privileges

Revoke Users Object Privileges

The REVOKE command in Oracle can be used to revoke object privileges on both tables and databases, as well as other objects such as views, procedures, and functions. It can also be used to revoke roles from users.
To revoke object privileges on a table, you would use the following syntax: 
REVOKE privilege ON table FROM user;

To revoke object privileges on a database, you would use the following syntax: 
REVOKE privilege ON database FROM user;

To revoke object privileges on all objects of a particular type, you can use the `ALL` keyword: 
REVOKE ALL ON object_type FROM user;

For example, to revoke all object privileges on tables from the user `scott`, you would use the following SQL statement: 
REVOKE ALL ON TABLE FROM scott;

You can also use the REVOKE command to revoke object privileges granted through a role. To do this, you would first revoke the role from the user using the REVOKE ROLE command, and then revoke the object privileges from the role using the REVOKE command.
For example, to revoke the SELECT privilege on the `employees` table from the user `scott` who has been granted the `sales_manager` role, you would use the following SQL statements: 
REVOKE sales_manager FROM scott;
REVOKE SELECT ON employees FROM sales_manager;

It is important to note that you can only revoke object privileges that you have previously granted. You cannot revoke object privileges that have been granted to a user through a role that was created by another user, unless you have the `ADMINISTER ANY ROLE` privilege.
You should also be careful when revoking object privileges, as this can prevent users from performing tasks that they need to do. It is a good practice to make a backup of your database before revoking any object privileges.

Take away Privilege which was granted

Things change, and people make mistakes. There may be times when you want to take away a privilege that has been granted to a user.
The REVOKE command allows you to accomplish this goal.

REVOKE command

You can use the REVOKE command to take away any previously granted privilege.
The syntax for the REVOKE command is very similar to the syntax for the GRANT command, as shown in the following diagram below.

Syntax for the REVOKE command in Oracle

REVOKE privilege/ALL FROM object TO user/PUBLIC

  1. REVOKE keyword: A required keyword
  2. privilege/ALL: privilege is the name of a specific privilege. The ALL keyword means that all privileges will be revoked on the target object from the specified user.
  3. FROM: A required keyword.
  4. object: The name of the table or view to which the privileges apply.
  5. TO: A required keyword.
  6. user/PUBLIC: user is the name of an existing Oracle user. The keyword PUBLIC refers to a special user group that applies the privilege to all users of the database.

Privileges and Roles

In an Oracle database, privileges control access to both the actions a user can perform and the objects in the database. Privileges that control access to actions in the database are called system privileges, whereas privileges that control access to data and other objects are called object privileges.
To make assignment and management of privileges easier for the DBA, a database role groups privileges together. To put it another way, a role is a named group of privileges. In addition, a role can itself have roles assigned to it.
Privileges and roles are granted and revoked with the GRANT and REVOKE commands. The user group PUBLIC is neither a user nor a role, nor can it be dropped; however, when privileges are granted to PUBLIC, they are granted to every user of the database, both present and future.

System Privileges

System privileges grant the right to perform a specific type of action in the database, such as creating users, altering tablespaces, or dropping any view. Here is an example of granting a system privilege: 
grant drop any table to scott 
with admin option;

The user SCOTT can drop anyone’s table in any schema. The WITH GRANT OPTION clause allows SCOTT to grant his newly granted privilege to other users.

Object Privileges

Object privileges are granted on a specific object in the database. The most common object privileges are SELECT, UPDATE, DELETE, and INSERT for tables, EXECUTE for a PL/SQL stored object, and INDEX for granting index-creation privileges on a table. In the following example, the user RJB can perform any DML on the JOBS table owned by the HR schema: 
grant select, update, insert, delete 
on hr.jobs to rjb;

Conditions for REVOKE

You can only revoke a privilege that you have granted. Even if another user has granted a privilege on an object that you own, you cannot revoke it.
You can revoke privileges selectively on a column, but the privilege must have been explicitly granted to the column. You cannot grant a privilege to an entire table and then revoke the privilege for a column in the table. If you want to revoke the GRANT option from a user or role, you have to take two separate actions. First you have to REVOKE the privilege itself, and then regrant the privilege without using the GRANT option.

Example

To revoke the INSERT privilege on the COIN table that was granted in the previous 
REVOKE INSERT ON COIN 
FROM SCOTT;

Notice that you can grant ALL privileges for a table to a user and then selectively revoke individual privileges. The ALL keyword is simply a way of specifying all privileges with a single word, so the individual privileges can still be taken away. The next lesson is the module wrap-up, which includes a quiz that will indicate what you have learned and discover topics that you might want to review in a more detailed manner.