Re: need to list all user permissions

This will probably get you started.

You would probably need to incorporate it into some PL/SQL code.

I don't know if you can run straight SQL to obtain every role granted to a user for every user in one SQL statement. For example, if for some stupid reason DBA was granted to CONNECT and CONNECT was granted to RESOURCE and RESOURCE was granted to two different people, could you find out if both users had the DBA privilege in one SQL query?

• Find all roles granted to a user,
• including roles granted to roles granted to roles select su.name granted_role from ( select grantee#, privilege# from sys.sysauth$ start with grantee# in (select user_id from dba_users where username = 'THE_USERNAME') connect by prior privilege# = grantee# ) sa, sys.user$ su where su.user#=sa.privilege#;
• The others can be generated easily from the following SQL select * from dba_tab_privs where grantee = 'THE_USERNAME' ; select * from dba_SYS_privs where grantee = 'THE_USERNAME' ;

"Beldar Conehead" <beldarconehead_at_earthlink.net> wrote in message news:zm_id.5123$Gm6.1031_at_newsread3.news.atl.earthlink.net...
> I've been asked for a cross-matrix of users and their permissions.
>
> That is, for each user in the database I need to find the permissions
> he/she has against every table in the database. It makes no difference if
> permission has been granted explicitly, or thru a role, or thru a system
> privilege. Any body know of an existing script? Is there any combination
> of sys-owned tables (e.g. the $ tables) that would help me?
>
> Thanx.
>
Received on Sun Nov 07 2004 - 22:00:43 CST