-
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
-
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.
-
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 ??
-
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
-
If it is so...........and when i am executing the above written query ..i am not getting procedures, pakages, functions privileges listed in it.
-
You can use DBA_OBJECTS, grouped by owner...
So, also USER_OBJECTS, ALL_OBJECTS...
HTH.
-
DBA_OBJECTS doesn't have any column related to Privileges.
-
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
-
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..
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|