Consulta jerárquica de Oracle con múltiples padres

Necesito ayuda para construir una consulta que devuelva los datos de la siguiente forma. Creo que una consulta jerárquica podría ayudar en esto. Pero no estoy seguro de cómo usarla en mi caso.

Por ejemplo, tengo los siguientes datos

Grantee Rol

User_1 Rol_4
Rol_4 Rol_1
Rol_4 Rol_2
Rol_4 Rol_3
User_2 Rol_5
User_2 Rol_6

Lo que necesito es devolver todos los roles que los usuarios han sido otorgados directa o indirectamente. Sé cómo consultar todos los roles de un solo usuario.

SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER(‘&USERNAME’)
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE

El resultado de la consulta para el usuario User_1 es

Rol_1
Rol_2
Rol_3
Rol_4

Pero no puedo averiguar cómo devolver todos los roles para varios usuarios. Por ejemplo, deseo obtener un conjunto de resultados de la siguiente manera

Grantee Rol

User_1 Rol_4
User_1 Rol_1
User_1 Rol_2
User_1 Rol_3
User_2 Rol_5
User_2 Rol_6

davy.ai

June 19, 2023 at 2:41 am

Para devolver todos los roles de múltiples usuarios, puedes usar una combinación de subconsultas y uniones. Aquí hay una consulta de ejemplo:

 SELECT DISTINCT t1.GRANTEE, t2.GRANTED_ROLE AS ROLE
 DE DBA_ROLE_PRIVS t1
 JOIN (
     SELECT GRANTED_ROLE, CONNECT_BY_ROOT GRANTEE AS GRANTEE
     FROM DBA_ROLE_PRIVS
     CONNECT BY PRIOR GRANTED_ROLE = GRANTEE
 ) t2 ON t1.GRANTEE = t2.GRANTED_ROLE
 WHERE t1.GRANTEE IN ('User_1', 'User_2')

Esta consulta devolverá todos los roles para los usuarios especificados (‘User_1’ y ‘User_2’) en el formato que solicitaste, con filas separadas para cada combinación de usuario y rol.

La subconsulta (t2) utiliza una consulta jerárquica para encontrar todos los roles otorgados indirectamente a cada usuario, e incluye al usuario que otorgó originalmente el rol (usando el operador CONNECT_BY_ROOT).

Luego, la consulta principal une esta subconsulta con la tabla original (t1) en la relación otorgante/otorgado_role y filtra los resultados para incluir solo los usuarios especificados.

Ten en cuenta que si tienes un gran número de usuarios para consultar, también puedes generar la lista de usuarios dinámicamente (por ejemplo, desde otra tabla o consulta) y sustituirla en la cláusula WHERE utilizando una variable.

Answer

davy.ai

June 19, 2023 at 2:41 am
Para devolver todos los roles de múltiples usuarios, puedes usar una combinación de subconsultas y uniones. Aquí hay una consulta de ejemplo:
```
 SELECT DISTINCT t1.GRANTEE, t2.GRANTED_ROLE AS ROLE
 DE DBA_ROLE_PRIVS t1
 JOIN (
     SELECT GRANTED_ROLE, CONNECT_BY_ROOT GRANTEE AS GRANTEE
     FROM DBA_ROLE_PRIVS
     CONNECT BY PRIOR GRANTED_ROLE = GRANTEE
 ) t2 ON t1.GRANTEE = t2.GRANTED_ROLE
 WHERE t1.GRANTEE IN ('User_1', 'User_2')
```
Esta consulta devolverá todos los roles para los usuarios especificados (‘User_1’ y ‘User_2’) en el formato que solicitaste, con filas separadas para cada combinación de usuario y rol.

La subconsulta (t2) utiliza una consulta jerárquica para encontrar todos los roles otorgados indirectamente a cada usuario, e incluye al usuario que otorgó originalmente el rol (usando el operador CONNECT_BY_ROOT).

Luego, la consulta principal une esta subconsulta con la tabla original (t1) en la relación otorgante/otorgado_role y filtra los resultados para incluir solo los usuarios especificados.

Ten en cuenta que si tienes un gran número de usuarios para consultar, también puedes generar la lista de usuarios dinámicamente (por ejemplo, desde otra tabla o consulta) y sustituirla en la cláusula WHERE utilizando una variable.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Consulta jerárquica de Oracle con múltiples padres

Answer

davy.ai