Connect By Prior – Consulta de todos os privilégios do usuário no BD (Parte 2)

Connect By Prior – Consulta de todos os privilégios do usuário no BD

Leia aqui a primeira parte !

Na segunda parte desse post iremos criar uma tabela que irá exibir todos os privilégios do usuário de maneira hierárquica.

Nessa tabela serão exibidos todos os ROLES concedidos aos usuários e seus respectivos privilégios, além dos privilégios de sistema e também os privilégios específicos concedidos aos objetos do Banco de Dados ( SELECT ON, INSERT ON etc.).

O primeiro passo é identificar todas as tabelas que retornam esses privilégios:

dba_users – exibe o nome do usuário

dba_role_privs – exibe todos os ROLES concedidos a todos os usuários

dba_sys_privs – privilégios de sistema

dba_tab_privs – privilégios específicos para tabelas (SELECT ON, UPDATE ON etc)

dba_col_privs – privilégios específicos para colunas (SELECT (column_name) ON)

Usaremos o operador de conjuntos UNION, para unir todos os valores de todas as tabelas

select username from dba_users
union
select grantee, granted_role from dba_role_privs;

A consulta retornará um erro:

ORA-01789: o bloco de consulta possui número incorreto de colunas de resultado
01789. 00000 -  "query block has incorrect number of result columns"
*Cause:
*Action:

O operador UNION, aliás, todos os operadores de conjuntos, necessitam do mesmo número de colunas nas duas queries que o compõe ( questão cobrada no exame 1Z0-047).

Analisando a consulta acima nota-se que se o usuário não possui nenhum privilégio acima dele, sendo o topo no nível hierárquico. Se comparado com o tutorial anterior, o usuário seria “Bill” e seu manager_id seria definido como null.

Portanto definiremos a segunda coluna da nossa primeira query como null

Select username, null from dba_users
union
select grantee, granted_role from dba_role_privs;

Identificaremos o usuário do banco de dados, para isso basta criar uma cláusula where para identificar o nome do usuário:

select * from (select username, null from dba_users where username= 'SCOTT'
union
select grantee, granted_role from dba_role_privs);

O resultado dessa query já exibe todos os ROLES pertencentes ao usuário e também todos os outros ROLES do banco de dados (esses outros ROLES serão usados em breve para obtermos sub-roles):

Agora a consulta já apresenta uma hierarquia, bastando apenas organizá-la e excluir os dados desnecessários a exibição.

Chegamos nesse momento ao ponto inicial do nosso tutorial anterior. Basta agora aplicarmos a cláusula CONNECT BY PRIOR:

select lpad(' ', 2*level) || granted_role granted_role, grantee, level  from (
select username granted_role, null grantee  from dba_users  where username = 'SCOTT'
union
select granted_role , grantee  from dba_role_privs
)
start with grantee is null
connect by prior granted_role = grantee;

Temos nossa hierarquia definida, faltando apenas adicionar os privilégios de sistema e dos objetos.

Adicionando listagem dos privilégios de sistema:

select lpad(' ', 2*level) || granted_role granted_role, grantee, level  from (
select username granted_role, null grantee  from dba_users  where username = 'SCOTT'
union
select granted_role , grantee  from dba_role_privs
union  
select privilege, grantee from dba_sys_privs    
)
start with grantee is null
connect by prior granted_role = grantee;

Adicionando a listagem de privilégios das tabelas:

select lpad(' ', 2*level) || granted_role granted_role, grantee, level  from (
select username granted_role, null grantee  from dba_users  where username = 'SCOTT'
union
select granted_role , grantee  from dba_role_privs
union
select privilege, grantee from dba_sys_privs
  union
  select privilege || ' ON ' || table_name granted_role, grantee  from dba_tab_privs where grantee='SCOTT'
)
start with grantee is null
connect by prior granted_role = grantee;

Repare que para a tabela dba_sys_privs é necessário inserir novamente a cláusula where, assim como para a tabela dba_col_privs:

select lpad(' ', 2*level) || granted_role granted_role, grantee, level  from (
select username granted_role, null grantee  from dba_users  where username = 'SCOTT'
union
select granted_role , grantee  from dba_role_privs
union
select privilege, grantee from dba_sys_privs
union
select privilege || ' ON ' || table_name granted_role, grantee  from dba_tab_privs where grantee='SCOTT'
  union
  select grantee, privilege || ' ON ' || table_name || '.' || column_name  granted_role  from dba_col_privs where grantee='SCOTT'
)
start with grantee is null
connect by prior granted_role = grantee;

Essa é a nossa consulta final.

Espero que tenham gostado.

Críticas e sugestões são bem-vindas.