Início / dba / Perguntas / 212084
Accepted
jpmc26
Asked: 2018-07-13 20:09:51 +0800 CST

Como posso obter todas as funções de alguns usuários?

  • 772

Preciso ver todas as funções de um usuário, incluindo as funções herdadas de uma função.

Considere, por exemplo, este arranjo:

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;

Então eu quero o resultado

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

Estou usando o Oracle 11.2g, mas também quero respostas para futuras versões do Oracle. Eu gostaria que essa pergunta permanecesse relevante se a Oracle introduzisse novos recursos que facilitassem isso (como uma visão que apenas nos fornece isso diretamente sem escrever nossas próprias consultas).

oracle

1 respostas

  1. Best Answer

    Explicação

    A visão que nos informa sobre as GRANTfunções de ed é DBA_ROLE_PRIVS. (Há também USER_ROLE_PRIVS, mas mostra apenas as funções concedidas ao usuário atual.) No entanto, essa visualização não nos fornece todas as funções herdadas. Seguir as funções do usuário recursivamente requer uma consulta "hierárquica" ou, mais simplesmente, conhecida como consulta recursiva.

    • Logicamente, queremos começar com funções concedidas diretamente ao usuário. Isso significa que nossa START WITHcláusula deve garantir que GRANTEE = 'TEMPUSER1'.
    • Em seguida, queremos ver as linhas em que GRANTEEé uma das funções concedidas ao usuário de interesse até o momento. Isso significa que nossa CONNECT BYcláusula deve garantir que PRIOR GRANTED_ROLE = GRANTEE.
    • Para levar o usuário real até as linhas em nosso conjunto de resultados, precisamos incluir CONNECT_BY_ROOT GRANTEE, que nos fornecerá as GRANTEElinhas iniciais (e começamos com nosso usuário de interesse).
    • Por último, é possível que um usuário herde uma função por meio de duas funções diferentes, conforme exemplificado em como TEMP_ROLE6herda TEMP_ROLE3. Isso significa que precisaremos de algum tipo de DISTINCTou GROUP BY.

    A pergunta

    Montar tudo isso em uma consulta nos dá:

    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
;

    Isto dá o resultado exato desejado.

    Informações de bônus: Origem da função

    Aqui está uma consulta alternativa que inclui algumas informações extras sobre como o usuário obteve a função:

    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
;

    Observe que a consulta mostra 'Direct GRANT'quando a função é concedida diretamente ao usuário. Isso se destaca um pouco melhor do que apenas mostrar o nome do usuário na lista.

    Observe que ainda precisamos do DISTINCTna subconsulta. Isso é necessário porque TEMPUSER1obtém TEMP_ROLE4duas vezes, uma vez de um direct GRANTe uma vez de TEMP_ROLE6. Sem o DISTINCT, TEMP_ROLE4apareceria duas vezes como a fonte do TEMP_ROLE3.

    O resultado fica assim:

    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

    Para todos os usuários

    Essas consultas podem ser facilmente adaptadas para mostrar funções para todos os usuários no banco de dados. Tudo o que é necessário é alterar a START WITHcláusula para:

    START WITH GRANTEE IN (SELECT USERNAME FROM DBA_USERS)

    Para funções

    As consultas funcionam igualmente bem se você quiser visualizar as funções concedidas a uma função específica. Você só precisa especificar o nome da função como o GRANTEE. Por exemplo,

    START WITH GRANTEE = 'TEMP_ROLE6'

    Ou para todas as funções:

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

relate perguntas