主页 / dba / 问题 / 212084
Accepted
jpmc26
Asked: 2018-07-13 20:09:51 +0800 CST

我怎样才能获得所有几个用户的角色？

  • 772

我需要查看用户的所有角色，包括从某个角色继承的角色。

例如，考虑这种安排：

CREATE USER TEMPUSER1 IDENTIFIED BY "TEMPUSER1" ACCOUNT LOCK;
CREATE USER TEMPUSER2 IDENTIFIED BY "TEMPUSER2" ACCOUNT LOCK;

CREATE ROLE TEMP_ROLE1;
CREATE ROLE TEMP_ROLE2;
CREATE ROLE TEMP_ROLE3;
CREATE ROLE TEMP_ROLE4;
CREATE ROLE TEMP_ROLE5;

GRANT TEMP_ROLE1 TO TEMP_ROLE3;
GRANT TEMP_ROLE2 TO TEMP_ROLE3;
GRANT TEMP_ROLE3 TO TEMP_ROLE4;
GRANT TEMP_ROLE3 TO TEMP_ROLE5;
GRANT TEMP_ROLE4 TO TEMP_ROLE6;
GRANT TEMP_ROLE5 TO TEMP_ROLE6;

GRANT TEMP_ROLE2 TO TEMPUSER1;
GRANT TEMP_ROLE4 TO TEMPUSER1;
GRANT TEMP_ROLE6 TO TEMPUSER1;
GRANT TEMP_ROLE2 TO TEMPUSER2;

那我要结果

GRANTED_USER   | GRANTED_ROLE
---------------+---------------
TEMPUSER1      | TEMP_ROLE1
TEMPUSER1      | TEMP_ROLE2
TEMPUSER1      | TEMP_ROLE3
TEMPUSER1      | TEMP_ROLE4
TEMPUSER1      | TEMP_ROLE5
TEMPUSER2      | TEMP_ROLE1
TEMPUSER2      | TEMP_ROLE2

我使用的是 Oracle 11.2g，但我也想获得未来版本的 Oracle 的答案。如果 Oracle 引入了使这更容易的新特性（比如直接向我们提供此视图而无需编写我们自己的查询），我希望这个问题保持相关性。

oracle

1 个回答

  1. Best Answer

    解释

    告诉我们有关GRANTed 角色的视图是DBA_ROLE_PRIVS。（也有USER_ROLE_PRIVS，但它只显示授予当前用户的角色。）但是，这个视图并没有给我们所有继承的角色。递归地跟踪用户角色需要“分层”查询，或更简单地称为递归查询。

    • 从逻辑上讲，我们希望从直接授予用户的角色开始。这意味着我们的START WITH子句应确保GRANTEE = 'TEMPUSER1'.
    • 然后，我们要查看GRANTEE到目前为止是授予感兴趣用户的角色之一的行。这意味着我们的CONNECT BY子句应确保PRIOR GRANTED_ROLE = GRANTEE.
    • 为了将实际用户传递到结果集中的行，我们需要包含CONNECT_BY_ROOT GRANTEE，这将从GRANTEE开始的行开始（我们从感兴趣的用户开始）。
    • 最后，用户可以通过两个不同的角色继承一个角色，如 how TEMP_ROLE6inherits所示TEMP_ROLE3。这意味着我们需要某种DISTINCTor GROUP BY。

    查询

    将所有这些组合成一个查询给我们：

    SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

    这给出了所需的确切结果。

    奖励信息：角色来源

    这是一个备用查询，其中包含一些有关用户如何获得角色的额外信息：

    SELECT GRANTED_USER, GRANTED_ROLE, LISTAGG(DIRECT_GRANTEE, ',') WITHIN GROUP (ORDER BY DIRECT_GRANTEE) AS ROLE_SOURCE
FROM (
    SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE, REPLACE(GRANTEE, CONNECT_BY_ROOT GRANTEE, 'Direct GRANT') AS DIRECT_GRANTEE
    FROM DBA_ROLE_PRIVS
    START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
    CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
) USER_ROLES
GROUP BY GRANTED_USER, GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

    请注意，查询显示了'Direct GRANT'何时将角色直接授予用户。这比仅在列表中显示用户名要突出一点。

    请注意，我们仍然需要DISTINCT在子查询中。这是必要的，因为TEMPUSER1获取TEMP_ROLE4两次，一次来自直接GRANT，一次来自TEMP_ROLE6. 没有DISTINCT,TEMP_ROLE4将作为 的来源出现两次TEMP_ROLE3。

    结果如下所示：

    GRANTED_USER  | GRANTED_ROLE  | ROLE_SOURCE
--------------+---------------+-----------------------------------
TEMPUSER1     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER1     | TEMP_ROLE2    | Direct GRANT
TEMPUSER1     | TEMP_ROLE3    | TEMP_ROLE4,TEMP_ROLE5
TEMPUSER1     | TEMP_ROLE4    | Direct GRANT,TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE5    | TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE6    | Direct GRANT
TEMPUSER2     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER2     | TEMP_ROLE2    | Direct GRANT

    对于所有用户

    这些查询可以很容易地适配以显示数据库中所有用户的角色。所需要做的就是将START WITH子句更改为：

    START WITH GRANTEE IN (SELECT USERNAME FROM DBA_USERS)

    对于角色

    如果您想查看授予特定角色的角色，这些查询同样有效。您只需将角色名称指定为起始GRANTEE. 例如，

    START WITH GRANTEE = 'TEMP_ROLE6'

    或者对于所有角色：

    START WITH GRANTEE IN (SELECT ROLE FROM DBA_ROLES)
    • 2

相关问题