Thinking Out Loud

April 3, 2015

Find Users with DBA Roles

Filed under: oracle — mdinh @ 12:24 am

This sounds like a simple request doesn’t it?

Simple if there are no roles which are granted DBA role and allow me to show what can go wrong.

Create ROLE “secret”, Grant DBA to “secret”, Grant “secret” to USER “michael”

ARROW:(SYS@hawklas):PRIMARY> create role secret;

Role created.

ARROW:(SYS@hawklas):PRIMARY> grant dba to secret;

Grant succeeded.

ARROW:(SYS@hawklas):PRIMARY> grant secret to michael identified by michael;

Grant succeeded.

ARROW:(SYS@hawklas):PRIMARY>

When a simple SQL is used, “secret” is a ROLE NOT USER.

ARROW:(SYS@hawklas):PRIMARY> r
  1  SELECT grantee
  2  FROM dba_role_privs
  3  WHERE granted_role='DBA'
  4  AND NOT regexp_like(grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  5*

GRANTEE
------------------------------
MDINH
HR
GGADMIN
TESTING
SECRET

ARROW:(SYS@hawklas):PRIMARY>

Easy enough, let’s join dba_role_privs and dba_users. Where’s “Michael” at?

ARROW:(SYS@hawklas):PRIMARY> r
  1  SELECT  r.grantee
  2  FROM  dba_role_privs r, dba_users u
  3  WHERE r.grantee=u.username
  4  AND r.granted_role='DBA'
  5  AND NOT regexp_like(r.grantee,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  6  ORDER BY 1
  7*

GRANTEE
------------------------------
GGADMIN
HR
MDINH
TESTING

ARROW:(SYS@hawklas):PRIMARY>

We found “Michael”

ARROW:(SYS@hawklas):PRIMARY> r
  1  WITH u AS (
  2  SELECT username
  3  FROM dba_users
  4  WHERE NOT regexp_like(username,'SYS|OUTLN|DBSNMP|ORACLE_OCM')
  5  )
  6  SELECT distinct username
  7  FROM   u, dba_role_privs d
  8  WHERE  username = d.grantee
  9  OR    (d.granted_role='DBA' AND d.granted_role in (SELECT role FROM role_role_privs WHERE granted_role='DBA'))
 10  ORDER BY 1
 11*

USERNAME
------------------------------
GGADMIN
HR
MDINH
MICHAEL
TESTING

ARROW:(SYS@hawklas):PRIMARY>

Updated:

Came across a blog which has another solution.

http://otipstricks.blogspot.com/2011/03/who-has-dba-privs.html

ARROW:(SYS@hawklas):PRIMARY> r
  1  select username, 1 level_deep from V$PWFILE_USERS
  2  union
  3  select grantee, max(level_deep) from (
  4  select distinct level level_deep, grantee, granted_role
  5  from dba_role_privs
  6  start with granted_role='DBA'
  7  connect by prior grantee=granted_role
  8  ) where grantee in (select username from dba_users)
  9  group by grantee
 10* order by 1

USERNAME                       LEVEL_DEEP
------------------------------ ----------
GGADMIN                                 1
HR                                      1
MDINH                                   1
MICHAEL                                 2
SYS                                     1
SYSTEM                                  1
TESTING                                 1

7 rows selected.

ARROW:(SYS@hawklas):PRIMARY>

Note: regexp_like is available from 10g onward.

Another update.

ARROW:(SYS@hawklas):PRIMARY> SELECT MAX(level_deep) level_deep, grantee users, granted_role
  2  FROM
  3    ( SELECT DISTINCT level level_deep, grantee, granted_role
  4      FROM dba_role_privs
  5      START WITH granted_role ='DBA'
  6      CONNECT BY prior grantee=granted_role
  7     )
  8  WHERE grantee IN (SELECT username FROM dba_users)
  9  GROUP BY grantee, granted_role
 10  ORDER BY 1,2
 11  ;

LEVEL_DEEP USERS                          GRANTED_ROLE
---------- ------------------------------ ------------------------------
         1 GGADMIN                        DBA
         1 HR                             DBA
         1 MDINH                          DBA
         1 ORACLE                         DBA
         1 SYS                            DBA
         1 SYSTEM                         DBA
         1 TESTING                        DBA
         2 MICHAEL                        SECRET
         3 SYS                            TOPSECRET
         3 TOPS                           TOPSECRET

10 rows selected.

ARROW:(SYS@hawklas):PRIMARY> SELECT MAX(level_deep) level_deep, grantee roles, granted_role
  2  FROM
  3    ( SELECT DISTINCT level level_deep, grantee, granted_role
  4      FROM dba_role_privs
  5      START WITH granted_role ='DBA'
  6      CONNECT BY prior grantee=granted_role
  7     )
  8  WHERE grantee NOT IN (SELECT username FROM dba_users)
  9  GROUP BY grantee, granted_role
 10  ORDER BY 1,2
 11  ;

LEVEL_DEEP ROLES                          GRANTED_ROLE
---------- ------------------------------ ------------------------------
         1 SECRET                         DBA
         2 TOPSECRET                      SECRET

ARROW:(SYS@hawklas):PRIMARY>
Advertisements

1 Comment »

  1. […] Users with DBA […]

    Pingback by Log Buffer #417: A Carnival of the Vanities for DBAs | InsideMySQL — April 10, 2015 @ 4:42 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: