How to find hidden granted roles at Oracle Database

This is very critical issue to find hidden granted roles to any user. For example If you want to find users whose have granted DBA roles.Normally , you can use below query at your database

SELECT GRANTEE, GRANTED_ROLE   FROM DBA_ROLE_PRIVS

WHERE GRANTED_ROLE=’DBA’

AND GRANTEE NOT IN (‘SYS’,’SYSTEM’);

But this kind of search does not show DBA users all time.

Lets give an example ;

We have an user named  appuser03 and we have two roles;  admin_role and admin_role01

Now ; give DBA grant to admin_role01 and assign   admin_role01 to admin_role and at the end assign admin_role to appuser03;

Let’s show it;

SQL> create role admin_role01;

Role created.

SQL> create role admin_role;

Role created.

SQL> grant dba to admin_role01;

Grant succeeded.

SQL> grant admin_role01 to admin_role;

Grant succeeded.

SQL> grant admin_role to appuser03;

Grant succeeded.

Now check the DBA users at your database;

SQL>

SELECT GRANTEE, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE=’DBA’
AND GRANTEE NOT IN (‘SYS’,’SYSTEM’);

GRANTEE                GRANTED_ROLE
————       —————–
ADMIN_ROLE01     DBA

As you see;  you can not see that APPUSER03 have DBA grant you still you have to make investigation about Admin_role01 to find APPUSER03 have DBA grant.

Instead use hierarchical  queries ; like this

SELECT DISTINCT a.grantee , granted_role
FROM
(
SELECT DISTINCT LEVEL level_deep, grantee, granted_role
FROM dba_role_privs
START WITH granted_role = ‘DBA’
CONNECT BY PRIOR grantee = granted_role ) a, dba_users b
WHERE a.GRANTEE = b.USERNAME AND
b.USERNAME NOT IN (‘SYSTEM’,’SYS’) AND
b.ACCOUNT_STATUS = ‘OPEN’

GRANTEE            GRANTED_ROLE
——————– ——————–
APPUSER03          ADMIN_ROLE

Bingo, at this query you can find that APPUSER03 have DBA role via ADMIN_ROLE role. Simply  you should revoke ADMIN_ROLE from APPUSER03 .

By hierarchical queries ; you can see which roles are granted to other roles.

The hierarchy of granted roles are shown below.

SQL>

SELECT DISTINCT LEVEL level_deep, grantee,granted_role
FROM  dba_role_privs WHERE grantee NOT in (‘SYS’,’SYSTEM’)
START WITH granted_role = ‘DBA’
CONNECT BY PRIOR grantee = granted_role
ORDER BY level_deep desc

LEVEL_DEEP   GRANTEE        GRANTED_ROLE
----------  -----------     --------------------
 3          APPUSER03        ADMIN_ROLE
 2          ADMIN_ROLE       ADMIN_ROLE01
 1          ADMIN_ROLE01     DBA

 

You can use this query to find other granted system roles like EXP_FULL_DATABASE or IMP_FULL_DATABASE ..

See you on the next note.

Anil Akduygu.

 

Oracle 12c New Security Features – 01, Use READ Privilige for Inquiry Users.

I would like to start a new series to introduce Oracle 12c new security features.

I want to start with READ privilege first. This privilege solves many problems at Oracle security. Before Oracle 12c you can give SELECT privilege to a user to SELECT any table. But with this privilege the user not only SELECT any table but also she or he can LOCK the table with SELECT for UPDATE or direct LOCK TABLE statement . This is an unwanted situation for inquiry users; because any inquiry user with  SELECT privilege can lock your table and stop your application. For this reason administrators would create views and then would grant these views to  inquiry users. It was an long operation if you had more than hundred tables and It was difficult to manage all views and tables.

To solve this problem; Oracle introduced a new privilege – READ privilege. With this privilege you can give SELECT grant to a user. But the user can only SELECT the table and he cannot lock it anymore.This enhancement is very important for inquiry users.

I will show you with examples.

Let’s start with Oracle 11g

SQL> connect scott/oracle

Connected.
SQL> grant SELECT on EMP to USER01;

Grant succeeded.

SQL> connect USER01/oracle

Connected.

SQL> select empno,ename from scott.emp;

EMPNO ENAME
———- ———-
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.

And now the tricky point

SQL> select * from scott.emp for update of ename;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

……

And now nobody at the database can update this table till USER01 commit the transaction.

Try it. Connect with Scott user at the same time

sqlplus scott/oracle

SQL> update emp set ename=’XXX’;

This statement hangs forever till USER01 commits its transaction. Simply a SELECT statement hangs you your database and your application. This is unwanted sitiuation

You can lock the EMP table with Lock table command  with USERB01 as well.

SQL> Lock table scott.emp in exclusive mode;

Table(s) Locked.

Therefore At Oracle 11g It is very dangerous to give SELECT grant table on any tables to a inquiry user. Instead , you should create views and then you should grant these views to inquiry users.

Let’s look at Oracle 12c version now.

SQL> connect appuser/oracle;
Connected.
SQL> grant READ on test to appuser03;

Grant succeeded.

SQL> connect appuser03/oracle
Connected.
SQL> select * from appuser.test;

X
———-
1
2
3

SQL> lock table appuser.test in exclusive mode;
lock table appuser.test in exclusive mode

• ERROR at line 1:

ORA-01031: insufficient privileges

As you see; you cannot lock with READ privilege

And then check for Select for Update command;
SQL> select * from appuser.test for update of x;
select * from appuser.test for update of x

• ERROR at line 1:

ORA-01031: insufficient privileges

Simply; After upgrade your database to Oracle 12c, recheck the privileges of  inquiry users

and use READ privileges for them.

 

Anıl Akduygu