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;
Sample Output:
Code: Select all
User, his roles and privileges
---------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
CONNECT
CREATE SESSION
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE