Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
If there are users with no privileges on either database the join to usr_all_users would enable you to list the user and show they have no roles. So there may be some benefit to including it in the query. –  Chris Saxon Jun 27 '13 at 20:40
1  
@ChrisSaxon: Kind of makes sense. It's just that the OP appears to be requesting the rights for a particular user only, using a WHERE clause. I can see no problem in returning an empty set to such a request. (And by the way, your query doesn't work with the WHERE filter, does it? I have upvoted your answer but I can see now that I was a bit too hasty. Please consider fixing it.) –  Andriy M Jun 27 '13 at 20:52
    
Both of your queries are really awesome. Worked as I wanted it to do. :) I really have to learn my stuff in the future and thanks a lot for your advice. –  Chris.V Jun 28 '13 at 6:41
    
@AndriyM - well spotted, I've updated the query; should work correctly now. –  Chris Saxon Jun 28 '13 at 11:17
add comment

The issue is you're restricting on the instance field, which is resulting in the roles only appearing in one column or the other.

To get around this, remove the restrictions on db_instance and full outer join the roles from the second database to the roles in the first, like so:

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)
FULL OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND V.GRANTED_ROLE = W.GRANTED_ROLE)
WHERE nvl(U.USERNAME, v.username) = 'USER'
ORDER BY nvl(W.GRANTED_ROLE, V.GRANTED_ROLE) ASC;

This SQLFiddle has a working example. Updated fiddle following comments from Andriy.

share|improve this answer
    
Just what I was looking for. Needed to add a DISTINCT to the SELECT, because it always listed everything three times. But it really does the job. Thanks a lot. –  Chris.V Jun 28 '13 at 6:44
    
Thanks for not letting me down (if you pardon a certain amount of excessive pathos)! :) –  Andriy M Jun 28 '13 at 12:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.