Oracle – How to Get All Roles of Specific Users

oracle

I need to see all of a user's roles, including the roles inherited from a role.

Consider for example, this arrangement:

CREATE USER TEMPUSER1 IDENTIFIED BY "TEMPUSER1" ACCOUNT LOCK;
CREATE USER TEMPUSER2 IDENTIFIED BY "TEMPUSER2" ACCOUNT LOCK;

CREATE ROLE TEMP_ROLE1;
CREATE ROLE TEMP_ROLE2;
CREATE ROLE TEMP_ROLE3;
CREATE ROLE TEMP_ROLE4;
CREATE ROLE TEMP_ROLE5;

GRANT TEMP_ROLE1 TO TEMP_ROLE3;
GRANT TEMP_ROLE2 TO TEMP_ROLE3;
GRANT TEMP_ROLE3 TO TEMP_ROLE4;
GRANT TEMP_ROLE3 TO TEMP_ROLE5;
GRANT TEMP_ROLE4 TO TEMP_ROLE6;
GRANT TEMP_ROLE5 TO TEMP_ROLE6;

GRANT TEMP_ROLE2 TO TEMPUSER1;
GRANT TEMP_ROLE4 TO TEMPUSER1;
GRANT TEMP_ROLE6 TO TEMPUSER1;
GRANT TEMP_ROLE2 TO TEMPUSER2;

Then I want the result

GRANTED_USER   | GRANTED_ROLE
---------------+---------------
TEMPUSER1      | TEMP_ROLE1
TEMPUSER1      | TEMP_ROLE2
TEMPUSER1      | TEMP_ROLE3
TEMPUSER1      | TEMP_ROLE4
TEMPUSER1      | TEMP_ROLE5
TEMPUSER2      | TEMP_ROLE1
TEMPUSER2      | TEMP_ROLE2

I'm using Oracle 11.2g, but I also want answers for future versions of Oracle. I'd like for this question to remain relevant if Oracle introduces new features that make this easier (like a view that just gives this to us straight up without writing our own queries).

Best Answer

Explanation

The view that tells us about GRANTed roles is DBA_ROLE_PRIVS. (There's also USER_ROLE_PRIVS, but it only shows roles granted to the current user.) However, this view does not give us all the inherited roles. Following through the user's roles recursively requires a "hierarchical" query, or more simply known as a recursive query.

  • Logically, we want to start with roles granted to the user directly. This means that our START WITH clause should ensure that GRANTEE = 'TEMPUSER1'.
  • Then we want to look at rows where the GRANTEE is one of the roles granted to the user of interest so far. This means our CONNECT BY clause should ensure that PRIOR GRANTED_ROLE = GRANTEE.
  • To carry through the actual user through to the rows in our result set, we need to include CONNECT_BY_ROOT GRANTEE, which will give us the GRANTEE from the starting rows (and we started with our user of interest).
  • Last, it's possible for a user to inherit a role through two different roles, as exemplified with how TEMP_ROLE6 inherits TEMP_ROLE3. This means we'll need some kind of DISTINCT or GROUP BY.

The query

Assembling all that into a query gives us:

SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

This gives the exact result desired.

Bonus info: Source of role

Here's an alternate query that includes some extra info about how the user got the role:

SELECT GRANTED_USER, GRANTED_ROLE, LISTAGG(DIRECT_GRANTEE, ',') WITHIN GROUP (ORDER BY DIRECT_GRANTEE) AS ROLE_SOURCE
FROM (
    SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE, REPLACE(GRANTEE, CONNECT_BY_ROOT GRANTEE, 'Direct GRANT') AS DIRECT_GRANTEE
    FROM DBA_ROLE_PRIVS
    START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
    CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
) USER_ROLES
GROUP BY GRANTED_USER, GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

Notice that the query shows 'Direct GRANT' when the role is granted to the user directly. This stands out a bit better than just showing the user name in the list.

Notice that we still need the DISTINCT in the subquery. This is necessary because TEMPUSER1 gets TEMP_ROLE4 twice, once from a direct GRANT and once from TEMP_ROLE6. Without the DISTINCT, TEMP_ROLE4 would show up twice as the source of TEMP_ROLE3.

The result looks like this:

GRANTED_USER  | GRANTED_ROLE  | ROLE_SOURCE
--------------+---------------+-----------------------------------
TEMPUSER1     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER1     | TEMP_ROLE2    | Direct GRANT
TEMPUSER1     | TEMP_ROLE3    | TEMP_ROLE4,TEMP_ROLE5
TEMPUSER1     | TEMP_ROLE4    | Direct GRANT,TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE5    | TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE6    | Direct GRANT
TEMPUSER2     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER2     | TEMP_ROLE2    | Direct GRANT

For all users

These queries can be easily adapter to show roles for all the users in the database. All that's needed is to change the START WITH clause to:

START WITH GRANTEE IN (SELECT USERNAME FROM DBA_USERS)

For roles

The queries work equally well if you want to view the roles granted to a particular role. You need only specify the role name as the starting GRANTEE. For example,

START WITH GRANTEE = 'TEMP_ROLE6'

Or for all roles:

START WITH GRANTEE IN (SELECT ROLE FROM DBA_ROLES)
Related Question