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 revoke all privileges from the user as soon as possible.
In this post, we'll talk about how to remove several kinds of privileges from users.
- Revoke All System Privilege from User
- Revoke All Object Privilege from User
- 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.
Since we may not know how may system privileges involved, we can provide complete system privileges to the user, then take all back.
Grant All Privileges to User
SQL> grant all privileges to hr;
Grant succeeded.
Revoke All Privileges from User
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.