How to list all the roles existing in Oracle database?
I have been searching in the tables :
ROLE_TAB_PRIVS ROLE_SYS_PRIVS ROLE_ROLE_PRIVS SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'ROLETEST'; but I can't find a role that I have just created.
Add a comment |
2 Answers
Got the answer :
SELECT * FROM DBA_ROLES; 2 Comments
Bill Worthington
What I need next is a list of the Roles that would be contained in the Roles, i.e. the owner and child Roles.
Bill Worthington
Nobody has the answer? Then I will give it: SELECT * FROM dba_role_privs Gives Grantee and Granted_Role, that is you can see the sub-roles of roles.
all_roles.sql
SELECT SUBSTR(TRIM(rtp.role),1,12) AS ROLE , SUBSTR(rp.grantee,1,16) AS GRANTEE , SUBSTR(TRIM(rtp.privilege),1,12) AS PRIVILEGE , SUBSTR(TRIM(rtp.owner),1,12) AS OWNER , SUBSTR(TRIM(rtp.table_name),1,28) AS TABLE_NAME , SUBSTR(TRIM(rtp.column_name),1,20) AS COLUMN_NAME , SUBSTR(rtp.common,1,4) AS COMMON , SUBSTR(rtp.grantable,1,4) AS GRANTABLE , SUBSTR(rp.default_role,1,16) AS DEFAULT_ROLE , SUBSTR(rp.admin_option,1,4) AS ADMIN_OPTION FROM role_tab_privs rtp LEFT JOIN dba_role_privs rp ON (rtp.role = rp.granted_role) WHERE ('&1' IS NULL OR UPPER(rtp.role) LIKE UPPER('%&1%')) AND ('&2' IS NULL OR UPPER(rp.grantee) LIKE UPPER('%&2%')) AND ('&3' IS NULL OR UPPER(rtp.table_name) LIKE UPPER('%&3%')) AND ('&4' IS NULL OR UPPER(rtp.owner) LIKE UPPER('%&4%')) ORDER BY 1 , 2 , 3 , 4 ; Usage
SQLPLUS> @all_roles '' '' '' '' '' '' SQLPLUS> @all_roles 'somerol' '' '' '' '' '' SQLPLUS> @all_roles 'roler' 'username' '' '' '' '' SQLPLUS> @all_roles '' '' 'part-of-database-package-name' '' '' '' etc. 