如何在oracle中显示用户的所有权限？

Question

有人可以告诉我如何在 sql-console 中显示特定用户的所有权限/规则吗？

原文由 destiny 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以尝试以下这些视图。

 SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

DBA 和其他高级用户可以使用这些相同视图的 DBA_ 版本找到授予其他用户的权限。它们包含在 文档 中。

这些视图仅显示 直接 授予用户的权限。查找 所有 权限，包括通过角色间接授予的权限，需要更复杂的递归 SQL 语句：

 select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;
select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;
select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;

原文由 Teja 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

更简单的单查询 oracle 版本。

 WITH data
     AS (SELECT granted_role
         FROM   dba_role_privs
         CONNECT BY PRIOR granted_role = grantee
         START WITH grantee = '&USER')
SELECT 'SYSTEM'     typ,
       grantee      grantee,
       privilege    priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_sys_privs
WHERE  grantee = '&USER'
        OR grantee IN (SELECT granted_role
                       FROM   data)
UNION
SELECT 'TABLE'    typ,
       grantee    grantee,
       privilege  priv,
       grantable  ad,
       table_name tabnm,
       '--'       colnm,
       owner      owner
FROM   dba_tab_privs
WHERE  grantee = '&USER'
        OR grantee IN (SELECT granted_role
                       FROM   data)
ORDER  BY 1;

原文由 Arpit Bhardwaj 发布，翻译遵循 CC BY-SA 4.0 许可协议