Ameer Khan

SQL> COL “USER,HIS ROLES AND PRIVILEGES” FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(‘ ‘, 5*level) || granted_role “USER,HIS ROLES AND PRIVILEGES”
FROM
(
  SELECT NULL grantee, username granted_role
  FROM dba_users
  WHERE username LIKE UPPER(‘SOUMYA’)
  UNION
  SELECT grantee,granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee,privilege
  FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;

USER,HIS ROLES AND PRIVILEGES
—————————————————————————————————-
     SOUMYA
          CONNECT
               CREATE SESSION
          RESOURCE
               CREATE CLUSTER
               CREATE INDEXTYPE
               CREATE OPERATOR
               CREATE PROCEDURE
               CREATE SEQUENCE
               CREATE TABLE
               CREATE TRIGGER
               CREATE TYPE

12 rows selected.