10g script to view privileges for user?

[aswolff]

Hello,

I am trying to find a script that I can run that will show me, for a particular user, their systems and object privileges, roles, etc?


I tried googling but the scripts I tried failed with invalid object and other weird errors. I am not a DBA just pretending to be one.

 

[SantaMufasa]

Aswolff,

Following is a script that should do what you want. (Since you wanted the script for a specific user, there is an "ACCEPT...PROMPT" sequence. Therefore, you must save this code to a script file, then run the script from SQL*Plus.)

accept usr prompt "Enter the USERNAME whose privileges you would like to survey: "
break on a skip 1
col a heading "Privilege|Category" format a12
col b heading "Granted Privilege" format a30
select 'Role Privs' a, granted_role b
from dba_role_privs
where grantee = upper('&usr')
union
select 'Sys Privs',privilege
from dba_sys_privs
where grantee = upper('&usr')
union
select 'Object Privs',owner||'.'||table_name||' ('||privilege||')'
from dba_tab_privs
where grantee = upper('&usr')
order by 1,2
/

Privilege
Category     Granted Privilege
------------ ---------------------------
Object Privs DHUNT.DD_SEQUENCES (INSERT)
             DHUNT.DD_SEQUENCES (SELECT)
             SYS.MYFLATFILES (READ)
             SYS.MYFLATFILES (WRITE)
             SYS.YADA (READ)
             SYS.YADA (WRITE)

Role Privs   CONNECT
             DBA
             PLUSTRACE
             RESOURCE
             WHATEVER
             YADA

Sys Privs    CREATE ANY INDEX
             CREATE ANY VIEW
             CREATE SESSION
             CREATE TABLE
             DELETE ANY TABLE
             INSERT ANY TABLE
             SELECT ANY TABLE
             UNLIMITED TABLESPACE
             UPDATE ANY TABLE

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Welshbird]

Wow Dave - I'm gonne be using that too. Fank!


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]