Multiple JOIN of 6 tables via 5 database instances

join;oracleoracle-11g-r2

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the roles of a user which he has of all instances.

I already used UNION ALL, but the output was unstructured and you couldn't tell which role on which instance. So I tried the following for only 3 tables:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
   ON (U.USERNAME = W.GRANTEE AND U.DB_INSTANCE = 'DB1')
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND U.DB_INSTANCE = 'DB2')
WHERE U.USERNAME = 'USER'
ORDER BY U.USERNAME ASC;

It actually worked, but the output wasn't satisfying:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1
ROLE_2
ROLE_3
ROLE_4
                            ROLE_1
                            ROLE_2
                            ROLE_4
                            ROLE_5
                            ROLE_6

Is there any way to make an output like this:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1                      ROLE_1
ROLE_2                      ROLE_2
ROLE_3
ROLE_4                      ROLE_4
                            ROLE_5
                            ROLE_6

I tried ON ((U.USERNAME = V.GRANTEE OR W.GRANTED_ROLE = V.GRANTED_ROLE) AND U.DB_INSTANCE = 'DB2') but the output was even worse.

You guys have any suggestions or helpful thoughts?

Best Answer

Does the SCHEMA.USR_ALL_USERS table actually need to take part in this query? It doesn't seem to: the same column that you are filtering the results on, USERNAME, is also used to join the other two tables. So, you could omit SCHEMA.USR_ALL_USERS and apply the filtering directly to SYS.DBA_ROLE_PRIVS and to SYS.DBA_ROLE_PRIVS@DB2_LINK.

And now, having the two subsets from those two tables, you could either take @Chris Saxon's approach and use a FULL JOIN:

SELECT
  DB1.GRANTED_ROLE AS "GRANTED_ROLE_DB1",
  DB2.GRANTED_ROLE AS "GRANTED_ROLE_DB2"
FROM (
  SELECT GRANTED_ROLE
  FROM SYS.DBA_ROLE_PRIVS
  WHERE GRANTEE = 'USER'
) DB1
FULL OUTER JOIN (
  SELECT GRANTED_ROLE
  FROM SYS.DBA_ROLE_PRIVS@DB2_LINK
  WHERE GRANTEE = 'USER'
) DB2
ON DB1.GRANTED_ROLE = DB2.GRANTED_ROLE
ORDER BY
  COALESCE(DB1.GRANTED_ROLE, DB2.GRANTED_ROLE)
;

or you could UNION the two sets:

SELECT
  'GRANTED_ROLE_DB1' AS GRANTED_ROLE_DB,
  GRANTED_ROLE
FROM SYS.DBA_ROLE_PRIVS
WHERE GRANTEE = 'USER'
UNION ALL
SELECT
  'GRANTED_ROLE_DB2' AS GRANTED_ROLE_DB,
  GRANTED_ROLE
FROM SYS.DBA_ROLE_PRIVS@DB2_LINK
WHERE GRANTEE = 'USER'

getting you the results like this:

GRANTED_ROLE_DB   GRANTED_ROLE
----------------  ------------
GRANTED_ROLE_DB1  ROLE_1
GRANTED_ROLE_DB1  ROLE_2
GRANTED_ROLE_DB1  ROLE_3
GRANTED_ROLE_DB1  ROLE_4
GRANTED_ROLE_DB2  ROLE_1
GRANTED_ROLE_DB2  ROLE_2
GRANTED_ROLE_DB2  ROLE_4
GRANTED_ROLE_DB2  ROLE_5
GRANTED_ROLE_DB2  ROLE_6

which you would then PIVOT, e.g. like this:

SELECT
  CASE WHEN "'GRANTED_ROLE_DB1'" > 0 THEN GRANTED_ROLE END AS GRANTED_ROLE_DB1,
  CASE WHEN "'GRANTED_ROLE_DB2'" > 0 THEN GRANTED_ROLE END AS GRANTED_ROLE_DB2
FROM (
  SELECT
    'GRANTED_ROLE_DB1' AS GRANTED_ROLE_DB,
    GRANTED_ROLE
  FROM SYS.DBA_ROLE_PRIVS
  WHERE GRANTEE = 'USER'
  UNION ALL
  SELECT
    'GRANTED_ROLE_DB2' AS GRANTED_ROLE_DB,
    GRANTED_ROLE
  FROM SYS.DBA_ROLE_PRIVS@DB2_LINK
  WHERE GRANTEE = 'USER'
) s
PIVOT (
  COUNT(*) FOR GRANTED_ROLE_DB IN ('GRANTED_ROLE_DB1', 'GRANTED_ROLE_DB2')
) p
ORDER BY
  GRANTED_ROLE
;

You can see both approaches "in action" at SQL Fiddle.

Related Question