group two tables result without duplicate results

Question

I am trying to get roles and privileges from two different tables for a specific user. My query is this:

Select r.grantee, r.granted_role , s.privilege 
From dba_role_privs r, dba_sys_privs s 
Where r.grantee=s.grantee and r.grantee=(select username from dba_users where username='HR'); 

I am ending up with this:

GRANTEE    GRANTED_ROLE         PRIVILEGE
---------- -------------------- --------------------
HR         DBA                  CREATE VIEW
HR         RESOURCE             CREATE VIEW
HR         DBA                  UNLIMITED TABLESPACE
HR         RESOURCE             UNLIMITED TABLESPACE
HR         DBA                  CREATE DATABASE LINK
HR         RESOURCE             CREATE DATABASE LINK
HR         DBA                  CREATE SEQUENCE
HR         RESOURCE             CREATE SEQUENCE
HR         DBA                  CREATE SESSION
HR         RESOURCE             CREATE SESSION
HR         DBA                  ALTER SESSION

GRANTEE    GRANTED_ROLE         PRIVILEGE
---------- -------------------- --------------------
HR         RESOURCE             ALTER SESSION
HR         DBA                  CREATE SYNONYM
HR         RESOURCE             CREATE SYNONYM

14 rows selected.

Which is fine when the user doesn't have many roles/privileges, but for user like SYS, for example, i am ending up with 10600 rows selected.

Is there a way to make the query outcome better? did i miss a join?

PS: I am working on oracle 11g.

If you can help making it better please do so

Thanks in advanced!

Answer 1

Perhaps you want union all rather than a join:

Select r.grantee, r.granted_role as role_or_privilege
From dba_role_privs r, 
Where r.grantee = (select username from dba_users where username = 'HR'); 
union all
select s.grantee, s.privilege 
from dba_sys_privs s 
Where s.grantee = (select username from dba_users where username = 'HR'); 

I left the logic in the where the same as in your query. However, this makes more sense to me:

Select r.grantee, r.granted_role as role_or_privilege
From dba_role_privs r, 
Where r.grantee = 'HR'; 
union all
select s.grantee, s.privilege 
from dba_sys_privs s 
Where s.grantee = 'HR';