[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

how to report on all privileges and roles for a user.

how to report on all privileges and roles for a user.
0
Cha1tu
Asked:
Cha1tu
5 Solutions
 
sdstuberCommented:
easy way with no aggregation

SELECT   drp.grantee,
         drp.granted_role,
         drp.admin_option,
         drp.default_role,
         dsp.privilege system_privs
    FROM sys.dba_role_privs drp, sys.dba_sys_privs dsp
   WHERE     drp.admin_option = 'YES'
         AND drp.grantee NOT IN ('SYS', 'SYSTEM')
         AND drp.granted_role = dsp.grantee
ORDER BY drp.grantee, drp.granted_role, dsp.privilege

Open in new window


11gR2 - easy way with built in aggregation

SELECT   drp.grantee,
         drp.granted_role,
         drp.admin_option,
         drp.default_role,
         listagg(dsp.privilege, ',') WITHIN GROUP (ORDER BY privilege) privs
    FROM sys.dba_role_privs drp, sys.dba_sys_privs dsp
   WHERE     drp.admin_option = 'YES'
         AND drp.grantee NOT IN ('SYS', 'SYSTEM')
         AND drp.granted_role = dsp.grantee
GROUP BY drp.grantee, drp.granted_role, drp.admin_option, drp.default_role;

Open in new window

0
 
sdstuberCommented:
XML aggregation supported in 10g


SELECT   drp.grantee,
         drp.granted_role,
         drp.admin_option,
         drp.default_role,
         RTRIM(
             EXTRACT(XMLAGG(XMLELEMENT("s", dsp.privilege || ',') ORDER BY 1), '/s/text()').getstringval(),
             ',')
             system_privs
    FROM sys.dba_role_privs drp, sys.dba_sys_privs dsp
   WHERE     drp.admin_option = 'YES'
         AND drp.grantee NOT IN ('SYS', 'SYSTEM')
         AND drp.granted_role = dsp.grantee
GROUP BY drp.grantee, drp.granted_role, drp.admin_option, drp.default_role

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Are you wanting just system level privs or object level privs as well?

for example if someone issues: grant select on table to someuser;

someuser will not be reported in those queries.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
also these queries only show first level grants.

if you have roles granted to roles (which is not uncommon) then you can have privileges granted any depth
0
 
Cha1tuAuthor Commented:
I need object level privs also........
0
 
sdstuberCommented:
if you want to see a hierarchy of all privileges granted through roles and roles to roles

try this...
SELECT     LPAD(' ', 3 * (LEVEL - 1)) || granted_role role_or_privilege_granted, description, grantee
      FROM (SELECT x.*, ROWNUM
              FROM (SELECT grantee, granted_role, 'Role' description FROM dba_role_privs
                    UNION ALL
                    SELECT grantee, privilege, 'System Privilege' description FROM dba_sys_privs
                    UNION ALL
                    SELECT grantee,
                           privilege || ' on ' || owner || '.' || table_name,
                           'Object Privilege'
                      FROM dba_tab_privs
                    UNION ALL
                    SELECT NULL, username, 'User Name'
                      FROM dba_users
                     WHERE username NOT IN ('SYS', 'SYSTEM')) x)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;

Open in new window

0
 
martin_seaCommented:
all the information would be available using the below views:

1.) system privs -->  dba_sys_privs
2.) roles privs --> dba_role_privs

the above is always necessary while taking the export for a user as these privs has to be give manually but the table privs is automaticallly given while imorting the user dump.

3.) dba_tab_privs --> user select ,update,insert privs on any schema tables for which the particular schema has access to.

The above 3 are the only privs details
0
 
sdstuberCommented:
martin_sea,

please read previous posts to be sure you're not duplicating,  the query provided a month ago uses those 3 views
0
 
martin_seaCommented:
@sdstuber:

I had only provided the necessary info on view which the at schema import level was necessary with meaning. since already the query was providedso only explain the view use for which at purpose database level.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now