• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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