Using SQL, I would like to create a list of certain schema objects (3-4 of them, tables and views) 开发者_运维知识库and the privileges I can grant them as the SYS user, or any other user I may be logged in as.
Here is a list of pre-defined Oracle Object privileges available for different type of schema objects. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2077938
In order for a user to grant object privileges (any one of the pre-defined object privileges mentioned above) to other users, the user must be the owner of the object or the user must have been granted the object privileges WITH GRANT OPTION. Otherwise, the user must have been given "GRANT ANY OBJECT PRIVILEGE" system privilege.
Suppose, when user U1 logs in,
1) To get a list of objects owned by the user which could be granted to other users
SELECT object_name FROM user_objects;
2) To get the list of object grants given to user U1, that could be granted to other users by U1.
SELECT grantor, grantee, table_name, owner
FROM user_tab_privs
WHERE grantee = 'U1' and grantable = 'YES'
3) To see whether U1 has GRANT ANY OBJECT PRIVILEGE, query
SELECT * FROM user_sys_privs where privilege = 'GRANT ANY OBJECT PRIVILEGE';
sys can grant anything.
and the object themselves will come from one or more of the oracle data dictionary objects.
精彩评论