How can I get all of a few users' roles?
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)