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

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

Esse post será divido em duas partes, a primeira irá exemplificar o uso da clausula CONNECT BY PRIOR e a segunda, como ela será usada para montar a visualização hierárquica de todos os privilégios de um usuário no Banco de dados Oracle (ROLES + SYS PRIVS + OBJ PRIVS).

Um recurso muito interessante, porém pouco conhecido entre os desenvolvedores Oracle é o Connect by Pior.

Ele é capaz de organizar as linhas de uma mesma tabela de modo hierárquico.

Esse modelo de consulta em que as linhas pai e filho estão presentes na mesma tabela é conhecido como SELF JOIN.

Um exemplo bem prático do funcionamento dessa ferramenta pode ser dado usando a tabela employees:

create table employees ( emp_id number primary key , emp_name varchar2(30), manager_id number);

insert into employees values (1, 'Bill', null); -- the highest hierarchy level
insert into employees values (2, 'Peter', 1);
insert into employees values (3, 'John', 1);
insert into employees values (4, 'Maria', 2);
insert into employees values (5, 'Albert', 2);
insert into employees values (6, 'Charles', 3);
insert into employees values (7, 'Martha', 3);
insert into employees values (8, 'Joseph', 4);
insert into employees values (9, 'Michael', 4);

A linha 1 representa o cargo que não possui gerente por se tratar do nível mais alto da hierarquia, por isso seu manager_id é null.

Repare que efetuando um SELECT nessa tabela é perfeitamente possível visualizar cada funcionário e seu respectivo gerente:

select * from employees;

O objetivo agora é organizar essas linhas de modo que o funcionário com mais alto grau hierárquico seja exibido no início da lista, seguido por seus subordinados.

O comando CONNECT BY PRIOR juntamente com a cláusula auxiliar START WITH, é capaz de organizar todos os itens seguidos dos seus subitens hierarquicamente:

SELECT emp_id, emp_name, manager_id
FROM employees
START WITH manager_id is null
CONNECT BY PRIOR emp_id = manager_id;

Mesmo que de forma confusa, já possível perceber que há uma organização hierárquica, diferente da consulta anterior. Note que “Peter” é gerente de “Maria” que é gerente de “Joseph” e “Michael”.

Emitindo uma instrução usando a cláusula CONNECT BY PRIOR, a coluna LEVEL é disponibilizada, ela é responsável por exibir o nível hierárquico de cada linha na tabela.

SELECT emp_id, emp_name, manager_id, level
FROM employees
start with manager_id is null
CONNECT BY PRIOR emp_id = manager_id;

Perceba agora “Peter” e “John” fazem parte do mesmo nível hierárquico.

Usando o comando LPAD, que insere caracteres a esquerda da string é possível ter uma visualização clara de toda a hierarquia:

SELECT emp_id, lpad(' ',level*2) || emp_name, manager_id, level
FROM employees
start with manager_id is null
CONNECT BY PRIOR emp_id = manager_id;

Com esse conhecimento sobre formatação hierárquica, faremos no próximo post uma listagem de todos os privilégios do usuário, incluindo ROLES e privilégios de sistema.

O segundo post está disponível no link: