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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s