Skip to content

How to Revoke All Grants from User

  • by

The Principle of Least Privilege

At times, you see plenty of unnecessary privileges on user, this is because some privileged user accidentally granted nearly all privileges to a normal user who should not own those privileges. To enforce the principle of least privilege, we should remove them as soon as possible.

In this post, we'll talk about how to remove several kinds of privileges from users.

  1. Revoke All System Privilege from User
  2. Revoke All Object Privilege from User
  3. Revoke All Role from User

Revoke All System Privilege from User

We found that an user has been granted too many unnecessary system privileges. Let's see what it has now.

SQL> select count(*) cnt from dba_sys_privs where grantee = 'HR';

       CNT
----------
       234

As we can see, almost all system privileges has been granted to the user. We should take them back.

Here are the steps to remove all system privileges from user:

Compose Revoke Statements

We can retrieve some information from DBA_SYS_PRIVS to compose revoke statements.

SQL> column stmt format a100;
SQL> set heading off;
SQL> select 'revoke ' || privilege || ' from ' || grantee || ';' stmt from dba_sys_privs where grantee = 'HR' order by 1;

revoke ADMINISTER ANY SQL TUNING SET from HR;
revoke ADMINISTER DATABASE TRIGGER from HR;
revoke ADMINISTER RESOURCE MANAGER from HR;
...
234 rows selected.

Revoke All System Privileges from User

We have 2 options to remove system privileges from the user.

Execute One by One

We execute the statements generated above one by one. A better way is to copy all statements and paste them to SQL prompt, which executes all of them at once.

SQL> revoke ADMINISTER ANY SQL TUNING SET from HR;

Revoke succeeded.

SQL> revoke ADMINISTER DATABASE TRIGGER from HR;

Revoke succeeded.

SQL> revoke ADMINISTER RESOURCE MANAGER from HR;

Revoke succeeded.
...

ALL PRIVILEGES

Since we may not know how may system privileges involved, we can provide complete system privileges to the user, then take all back.

SQL> grant all privileges to hr;

Grant succeeded.

SQL> revoke all privileges from hr;

Revoke succeeded.

We cleared all system privileges in two commands.

Grant Necessary Privileges Back to User

We added necessary privileges back to it. If you know the user's privileges before the accident, it would be much easier to recover its necessary privileges.

SQL> grant create session, create table, create view, create procedure, create type to hr;

Grant succeeded.

Please note that, we used commas to separate multiple system privileges in GRANT statement.

Revoke All Object Privilege from User

Since we have already know the principle of least privilege, we should additionally focus on object privileges.

Here are the steps to remove all system privileges from user:

Compose Revoke Statements

We can retrieve some information from DBA_TAB_PRIVS to compose revoke statements.

SQL> select 'revoke all on ' || owner || '.' || table_name || ' from ' || grantee || ' cascade constraints;' stmt from dba_tab_privs where grantee = 'OE' order by 1;

revoke all on HR.EMPLOYEES from OE cascade constraints;
revoke all on HR.EMPLOYEES from OE cascade constraints;
revoke all on HR.LOCATIONS from OE cascade constraints;
revoke all on HR.LOCATIONS from OE cascade constraints;
revoke all on SYS.DBMS_STATS from OE cascade constraints;
revoke all on SYS.SS_OE_XMLDIR from OE cascade constraints;
revoke all on SYS.SS_OE_XMLDIR from OE cascade constraints;

7 rows selected.

Please note that, the keyword ALL means all possible privileges specific to the object. In object privilege, ALL and ALL PRIVILEGES are synonyms, which means using ALL or ALL PRIVILEGES on object has no difference.

Secondly, not all statements you want to use. You may choose some of them to execute.

Revoke All on Object from User

Then we execute statements selectively. Here we choose 4 of them to execute.

SQL> revoke all on HR.EMPLOYEES from OE cascade constraints;

Revoke succeeded.

SQL> revoke all on HR.EMPLOYEES from OE cascade constraints;

Revoke succeeded.

SQL> revoke all on HR.LOCATIONS from OE cascade constraints;

Revoke succeeded.

SQL> revoke all on HR.LOCATIONS from OE cascade constraints;

Revoke succeeded.

C. Revoke All Role from User

A role is a set of system and object privileges, which can easily grant to or revoke from the user.

Here are the steps to remove all roles from user:

Compose Revoke Statements

We can retrieve some information from DBA_ROLE_PRIVS to compose revoke statements.

SQL> select 'revoke ' || granted_role || ' from ' || grantee || ';' stmt from dba_role_privs where grantee = 'IX' order by 1;

revoke AQ_ADMINISTRATOR_ROLE from IX;
revoke AQ_USER_ROLE from IX;
revoke CONNECT from IX;
revoke RESOURCE from IX;
revoke SELECT_CATALOG_ROLE from IX;

Please note that, not all statements you want to use. You may choose some of them to execute.

Revoke All Role from User

Next, we execute statements selectively. Here we choose 3 of them to execute.

SQL> revoke AQ_ADMINISTRATOR_ROLE from IX;

Revoke succeeded.

SQL> revoke AQ_USER_ROLE from IX;

Revoke succeeded.

SQL> revoke SELECT_CATALOG_ROLE from IX;

Revoke succeeded.

Using privileges properly begins with granting privileges correctly.

Leave a Reply

Your email address will not be published. Required fields are marked *