Hi All,
I like to join following two SQLs using tables dba_role_privs and dba_sys_privs.
SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs where grantee='WMSYS';
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
WMSYS RESOURCE
WMSYS CONNECT
WMSYS WM_ADMIN_ROLE
SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where grantee='WMSYS';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
WMSYS CREATE ANY VIEW
WMSYS DROP ANY INDEX
WMSYS CREATE ANY PROCEDURE
WMSYS CREATE PUBLIC SYNONYM
WMSYS ALTER ANY TABLE
WMSYS SELECT ANY DICTIONARY
Required output is as below.
GRANTEE GRANTED_ROLE PRIVILEGE
--------------------------------------------------------------------------------------------------------------------------------------
WMSYS RESOURCE CREATE ANY VIEW
WMSYS CONNECT DROP ANY INDEX
WMSYS WM_ADMIN_ROLE CREATE ANY PROCEDURE
WMSYS ALTER ANY TABLE
WMSYS SELECT ANY DICTIONARY