oracle 如何列出Oracle数据库中存在的所有角色？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20725581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 05:26:04 来源:igfitidea点击:
How to list all the roles existing in Oracle database?
提问by Thomas Carlton
How to list all the roles existing in Oracle database?
如何列出Oracle数据库中存在的所有角色？
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.
但是我找不到我刚刚创建的角色。
回答by Thomas Carlton
Got the answer :
得到了答案：
SELECT * FROM DBA_ROLES;
回答by Malcolm Boekhoff
all_roles.sql
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.
