All Roles to a user(s)

Show all the roles assigned to a user(s):

select

lpad(' ', 2*level) || granted_role "Roles, Privileges of USER(s)"

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;