开发者

In Oracle 10g, how can I list schema objects and the privileges I can grant them?

开发者 https://www.devze.com 2023-02-28 11:21 出处:网络
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 logg

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消