I had been looking for a really quick way to diagnose the roles and privileges of users that I created in a test database.
I snagged this code snippet from the web sometime ago (Sorry, I know longer have the reference if any credit is due at all). I am posting it here mainly for my reference but perhaps it will help someone else as well.
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* 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;
