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

How to show all privileges from a user in oracle?

提问by destiny

Can someone please tell me how to show all privileges/rules from a specific user in the sql-console?

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

回答by Teja

You can try these below views.

您可以尝试以下视图。

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

DBAs and other power users can find the privileges granted to other users with the DBA_versions of these same views. They are covered in the documentation.

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

Those views only show the privileges granted directlyto the user. Finding allthe privileges, including those granted indirectly through roles, requires more complicated recursive SQL statements:

这些视图仅显示直接授予用户的权限。查找所有权限，包括通过角色间接授予的权限，需要更复杂的递归 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;

回答by Justin Cave

There are various scripts floating around that will do that depending on how crazy you want to get. I would personally use Pete Finnigan's find_all_privs script.

有各种各样的脚本可以做到这一点，这取决于你想变得多么疯狂。我个人会使用 Pete Finnigan 的find_all_privs 脚本。

If you want to write it yourself, the query gets rather challenging. Users can be granted system privileges which are visible in DBA_SYS_PRIVS. They can be granted object privileges which are visible in DBA_TAB_PRIVS. And they can be granted roles which are visible in DBA_ROLE_PRIVS(roles can be default or non-default and can require a password as well, so just because a user has been granted a role doesn't mean that the user can necessarily use the privileges he acquired through the role by default). But those roles can, in turn, be granted system privileges, object privileges, and additional roles which can be viewed by looking at ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, and ROLE_ROLE_PRIVS. Pete's script walks through those relationships to show all the privileges that end up flowing to a user.

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

回答by Ageu

Another useful resource:

另一个有用的资源：

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

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

• DBA_SYS_PRIVS
• DBA_TAB_PRIVS
• DBA_ROLE_PRIVS

• DBA_SYS_PRIVS
• DBA_TAB_PRIVS
• DBA_ROLE_PRIVS

回答by jpmc26

While Raviteja Vutukuri's answerworks and is quick to put together, it's not particularly flexible for varying the filters and doesn't help too much if you're looking to do something programmatically. So I put together my own query:

虽然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, GRANTEE 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
;

Advantages:

好处：

• I easily can filter by a lot of different pieces of information, like the object, the privilege, whether it's through a particular role, etc. just by changing that one WHEREclause.
• It's a single query, meaning I don't have to mentally compose the results together.
• It resolves the issue of whether they can grant the privilege or not and whether it includes the privileges for subobjects (the "hierarchical" part) across differences sources of the privilege.
• It's easy to see everything I need to do to revoke the privilege, since it lists all the sources of the privilege.
• It combines table and system privileges into a single coherent view, allowing us to list allthe privileges of a user in one fell swoop.
• It's a query, not a function that spews all this out to DBMS_OUTPUTor something (compared to Pete Finnigan's linked script). This makes it useful for programmatic use and for exporting.
• The filter is not repeated; it only appears once. This makes it easier to change.
• The subquery can easily be pulled out if you need to examine it by each individual GRANT.

• 我可以轻松地通过许多不同的信息进行过滤，例如对象、特权、是否通过特定角色等，只需更改一个WHERE子句即可。
• 这是一个单一的查询，这意味着我不必在精神上将结果组合在一起。
• 它解决了他们是否可以授予特权的问题，以及是否包括跨特权不同来源的子对象（“分层”部分）的特权。
• 很容易看到我需要做的一切来撤销特权，因为它列出了特权的所有来源。
• 它将表和系统权限组合到一个连贯的视图中，使我们能够一举列出用户的所有权限。
• 这是一个query，而不是一个将所有这些都吐出来的函数DBMS_OUTPUT或其他东西（与 Pete Finnigan 的链接脚本相比）。这使其可用于编程使用和导出。
• 过滤器不重复；它只出现一次。这样更容易改变。
• 如果您需要由每个人检查子查询，可以很容易地将其拉出GRANT。

回答by Ravi Bhushan

You can use below code to get all the privileges list from all users.

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

select * from dba_sys_privs 

回答by Yerbol Baigarayev

To show all privileges:

显示所有权限：

select name from system_privilege_map;

从 system_privilege_map 中选择名称；