The following SQL statement can retrieve an Oracle user ("dev" in this example) and his asigned rols and privileges information
select
lpad(' ', 4*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('dev')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
The result:
User, his roles and privileges
DEV
ALTER ANY PROCEDURE
DEBUG ANY PROCEDURE
DEBUG CONNECT SESSION
DEVELOPER_ROLE
ALTER ANY PROCEDURE
ALTER ANY TRIGGER
CREATE ANY PROCEDURE
CREATE ANY TRIGGER
CREATE SESSION
CREATE TABLE
DROP ANY PROCEDURE
DROP ANY TRIGGER
EXECUTE ANY PROCEDURE
SELECT ANY SEQUENCE
SELECT ANY SEQUENCE
TEST_ROLE
CREATE PROCEDURE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
EXECUTE ANY PROCEDURE
SELECT ANY DICTIONARY
SELECT ANY SEQUENCE