如何在Oracle中显示一个用户的所有权限？

Question

请问如何在sql控制台中显示特定用户的所有权限/规则？

- destiny

Answer 1

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

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

DBAs及其他高级用户可以使用与这些相同视图的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

Answer 2

有各种各样的脚本可以实现这个目标，具体要看你想要多复杂。 我个人会使用Pete Finnigan的find_all_privs脚本。

如果你想自己编写脚本，查询可能会相当具有挑战性。 用户可以被授予系统特权，这些特权在DBA_SYS_PRIVS中可见。他们可以被授予对象特权，这些特权在DBA_TAB_PRIVS中可见。并且他们可以被授予角色，在DBA_ROLE_PRIVS中可见（角色可以是默认或非默认的，也可以需要密码，因此仅仅因为用户已被授予角色并不意味着用户具备通过角色获取的权限）。但是这些角色可以反过来被授予系统特权、对象特权和其他角色，可以通过查看ROLE_SYS_PRIVS、ROLE_TAB_PRIVS和ROLE_ROLE_PRIVS来查看这些信息。 Pete的脚本遍历这些关系以显示所有最终流向用户的权限。

- Justin Cave

---

脚本很棒，我刚刚检查过了。 - I.Tyger

当运行Pete Finnigan的脚本时，如果您没有UTL_FILE包的权限，则会出现错误：“标识符'UTL_FILE'必须声明”。您可以通过SQL Developer连接为sys并使用roll sysdba，然后它将正常工作，或者授予自己对此包的执行权限：grant execute on UTL_FILE to <user>; - Jan

对于那些没有SYS特权，只想查看自己账户权限的人来说，这个脚本毫无用处。我没有访问UTL_FILE，也没有访问脚本所查看的其他DBA和SYS区域的权限。 - vapcguy

我认为不需要检查 ROLE_SYS_PRIVS、ROLE_TAB_PRIVS 和 ROLE_ROLE_PRIVS。文档表明它们是针对当前用户的。 - jpmc26

如果有人有这些脚本的拷贝，能否在这里或类似于gist这样更经久不衰的地方发布一下？该网站已经崩了。 - Michael Thompson

@MichaelThompson - 网站已经上线。在网站的顶部，有一个限制是“脚本不能在其他任何地方发布/发布/托管...”我的答案第二段概述了脚本的要点。脚本本身有几百行代码，重新创建非常麻烦。 - Justin Cave

Answer 3

虽然Raviteja Vutukuri的回答可行且很快，但对于不同的过滤器来说不是特别灵活，并且如果您想要以编程方式执行某些操作，则没有太大帮助。所以我自己编写了查询：

SELECT
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    USERNAME,
    LISTAGG(GRANT_TARGET, ',') WITHIN GROUP (ORDER BY GRANT_TARGET) AS GRANT_SOURCES, -- Lists the sources of the permission
    MAX(ADMIN_OR_GRANT_OPT) AS ADMIN_OR_GRANT_OPT, -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
    MAX(HIERARCHY_OPT) AS HIERARCHY_OPT -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
FROM (
    -- Gets all roles a user has, even inherited ones
    WITH ALL_ROLES_FOR_USER AS (
        SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
        FROM DBA_ROLE_PRIVS
        CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
    )
    SELECT
        PRIVILEGE,
        OBJ_OWNER,
        OBJ_NAME,
        USERNAME,
        REPLACE(GRANT_TARGET, USERNAME, 'Direct to user') AS GRANT_TARGET,
        ADMIN_OR_GRANT_OPT,
        HIERARCHY_OPT
    FROM (
        -- System privileges granted directly to users
        SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
        FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- System privileges granted users through roles
        SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
        FROM DBA_SYS_PRIVS
        JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_SYS_PRIVS.GRANTEE
        UNION ALL
        -- Object privileges granted directly to users
        SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, GRANTABLE, HIERARCHY
        FROM DBA_TAB_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- Object privileges granted users through roles
        SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, ALL_ROLES_FOR_USER.GRANTED_ROLE AS GRANT_TARGET, GRANTABLE, HIERARCHY
        FROM DBA_TAB_PRIVS
        JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_TAB_PRIVS.GRANTEE
    ) ALL_USER_PRIVS
    -- Adjust your filter here
    WHERE USERNAME = 'USER_NAME'
) DISTINCT_USER_PRIVS
GROUP BY
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    USERNAME
;

优点：

• 我可以轻松地通过更改一个WHERE子句来过滤许多不同的信息片段，例如对象、权限、是否通过特定角色等。
• 这是一个单一查询，意味着我不必在脑中组合结果。
• 它解决了他们是否可以授予权限以及是否包括来自不同权限源的子对象权限（"分层"部分）的问题。
• 由于列出了所有权限源，因此很容易看到我需要撤销权限所需做的一切。
• 它将表和系统权限组合成一个统一的视图，使我们能够一次性列出用户的所有权限。
• 它是一个查询，而不是像Pete Finnigan链接的脚本那样将所有内容都输出到DBMS_OUTPUT或其他地方。这使得它对编程使用和导出非常有用。
• 筛选器不会重复出现；它只出现一次。这使得更改更容易。
• 如果您需要逐个检查GRANT，则可以轻松地将子查询提取出来。

- jpmc26

---

一些自己的待办事项：

1. 添加指示器，如果用户可以通过将角色授予另一个用户来授予权限。
2. 找出如何在没有DBA权限的情况下为当前用户执行此操作。可能涉及到USER_SYS_PRIVS（直接授予的系统权限）、USER_TAB_PRIVS（直接授予的对象权限）、USER_ROLE_PRIVS（用户直接授予的角色）、ROLE_ROLE_PRIVS（获取继承角色）、ROLE_SYS_PRIVS（通过角色获得系统权限）和ROLE_TAB_PRIVS（通过角色获得对象权限）。哎呀，Oracle太复杂了。

- jpmc26

Answer 4

另一个有用的资源：

http://psoug.org/reference/roles.html

• DBA_SYS_PRIVS：系统权限
• DBA_TAB_PRIVS：表级权限
• DBA_ROLE_PRIVS：角色权限

- Jayron Soares

---

除非你没有DBA或SYS角色，且只想找到自己账户的权限。 - vapcguy

Answer 5

更简单的单查询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

---

比之前的代码清晰得多。 - Vic VKh

Answer 6

您可以使用以下代码从所有用户获取所有权限列表。

select * from dba_sys_privs 

- Ravi Bhushan

---

这并非列出所有特权。正如其他答案所示，这些答案比您的回答提前了数年，它省略了表格权限以及通过角色授予的所有权限。 - jpmc26

Answer 7

显示所有权限：

选择 system_privilege_map 表中的 name 列；

- Yerbol Baigarayev