SQL Script to list object privileges granted to a particular user

Posted on May 29, 2008. Filed under: DBA/Admin Tips, Scripts, User | Tags: , , , |

Login to database as sys / system / or any user who had dba privileges and run below command

select grantor || ‘ granted ‘ || privilege || ‘ on ‘ || table_name || ‘ owned by ‘|| owner || ‘ to ‘|| grantee
from dba_tab_privs where grantee like upper(‘&GRANTEE_NAME’) order by privilege;

where GRANTEE_NAME is the user you want to find out the privileges.

Even though the view name is dba_tab_privs, it lists privileges on all objects including tables, views, procedures, etc.,

Sample Output

APPS granted DELETE on FND_USER_RESP_GROUPS owned by APPS to XXXXXX
APPLSYS granted DELETE on FND_USER owned by APPLSYS to XXXXXX;

cheers,
OraclePitStop

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: