Combine Reports

[j_70]

I would like to combine two DBA reports (System Priviledge and Role Privileges) into one. I tried just combinding the

 part from each and also tried combining the query with a union. Each gives me a SQL error. How to proceed??

 

[William Robertson]

Correct the SQL?

 

[Stew Stryker]

j_70,

Care to post the SQL here? Maybe someone could give a hint, as I think some people in this forum know SQL!

 

[j_70]

I am using the existing SQL from the System Priviledge and Role Privileges reports. However, I do not think I am putting it into the combined report properly. I have tried:

select p.* from dba_role_privs p
where p.grantee like &
and p.granted_role like &
order by p.grantee, p.granted_role

select p.* from dba_sys_privs p
where p.grantee like &
and p.privilege like &
order by p.grantee, p.privilege

But I get a 'SQL command not properly ended' error. TIA.

 

[rbrooker]

Hi,

i did exactly what you tried to do, take the sql from the role privs and sys privs reports and combine them.

this is what i came up with;

SELECT a.username,
       a.account_status,
       b.grant_type,
       b.grant_name,
       b.admin_option
FROM dba_users a,
     (SELECT grantee,
             'ROLE' grant_type,
             granted_role grant_name,
             admin_option
      FROM dba_role_privs
      UNION ALL
      SELECT grantee,
             'SYSPRIV' grant_type,
             privilege grant_name,
             admin_option
      FROM dba_sys_privs) b
WHERE a.username = b.grantee
ORDER BY 1,
         2

 

[j_70]

Works great. I changed your code to add a username parameter:

SELECT a.username,
a.account_status,
b.grant_type,
b.grant_name,
b.admin_option
FROM dba_users a,
(SELECT grantee,
'ROLE' grant_type,
granted_role grant_name,
admin_option
FROM dba_role_privs
UNION ALL
SELECT grantee,
'SYSPRIV' grant_type,
privilege grant_name,
admin_option
FROM dba_sys_privs) b
WHERE a.username = b.grantee
and a.username like &
ORDER BY 1,2

Can I feed in a break of any type between the role section and the system privelege section??

 

[rbrooker]

they are ordered by ROLE and then SYSPRIV inside a persons set of rows. not sure how you would add a blank line in between...