Object Privilege

[Upeshp]

Hi,

Can any one suggest me a sql query to get the report on
User Name and all objects privillages the user is having on any schema.

This query should give the result for all users created on the database.

Thanks,

UpeshP

[rotem_fo]

Hi,
Every user can query the ALL_XXX views such as ALL_TABLES, ALL_VIEWS, ALL_OBJECTS etc..
These views provide for all objects that user has privileges too.
You can query the DBA_TAB_PRIVS to view all user table privileges:
select owner,table_name,privilege,grantee
from DBA_TAB_PRIVS
where owner not in ('SYS','SYSTEM');

cheers
R.

[Upeshp]

Hi,
Thanks for the reply.

This query gives the information about tables only. If i want for other objects also then ?

Can any one suggest ??

[SANJAY_G]

Originally Posted By: Upeshp
This query gives the information about tables only. If i want for other objects also then ?

DBA_TAB_PRIVS lists all object_privilege granted to users (excluding column specific privileges) use DBA_COL_PRIVS for column specific privileges.

Sanjay

[Upeshp]

If it is so...........and when i am executing the above written query ..i am not getting procedures, pakages, functions privileges listed in it.

[ggnanaraj]

You can use DBA_OBJECTS, grouped by owner...

So, also USER_OBJECTS, ALL_OBJECTS...

HTH.

[Upeshp]

DBA_OBJECTS doesn't have any column related to Privileges.

[aarroyob]

Originally posted by Upeshp

DBA_OBJECTS doesn't have any column related to Privileges.

No, and what mean for you the column object_type in dba_objects?

Cheers

Angel

[SANJAY_G]

Originally Posted by Upeshp:
If it is so...........and when i am executing the above written query ..i am not getting procedures, pakages, functions privileges listed in it.

Have a look at this..

sanjay@ORAM> select * from user_tab_privs
  2  where owner not in ('SYS','SYSTEM');

GRANTEE         OWNER           TABLE_NAME      GRANTOR         PRIVILEGE       GRA
--------------- --------------- --------------- --------------- --------------- ---
PUBLIC          SANJAY          SANJAY          SANJAY          SELECT          NO
PUBLIC          SANJAY          V_SANJAY        SANJAY          SELECT          NO

sanjay@ORAM> create procedure my_proc is
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

sanjay@ORAM> grant execute on my_proc to public;

Grant succeeded.

sanjay@ORAM> select * from user_tab_privs
  2   where owner not in ('SYS','SYSTEM');

GRANTEE         OWNER           TABLE_NAME      GRANTOR         PRIVILEGE       GRA
--------------- --------------- --------------- --------------- --------------- ---
PUBLIC          SANJAY          SANJAY          SANJAY          SELECT          NO
PUBLIC          SANJAY          V_SANJAY        SANJAY          SELECT          NO
PUBLIC          SANJAY          MY_PROC         SANJAY          EXECUTE         NO

HTH
Sanjay