♣
题目部分
在Oracle中,为何SYSTEM用户可以将V$SESSION的查询权限赋权给其他用户而SYS用户却不可以?
♣
答案部分
答案:现象如下,难道SYSTEM比SYS用户的权限更大吗?
SYS@ora11g > grant
select
on
v$session to
lhr;
grant
select
on
v$session to
lhr
*
ERROR at
line 1:
ORA-02030: can only
select
from
fixed tables/views
SYS@ora11g > conn system/lhr
Connected.
SYSTEM@ora11g > grant
select
on
v$session to
lhr;
Grant
succeeded.
如果SYSTEM不能对V$SESSION赋权,那么请执行以下命令:
GRANT
SELECT
ON
SYS.V_$SESSION TO
SYSTEM WITH
GRANT
OPTION;
这样SYSTEM就可以对V$SESSION赋权了。
对于该问题可以从以下视图中获得答案:
SYS@ora11g > col OWNER format a10
SYS@ora11g > col object_name format a15
SYS@ora11g > SELECT
d.owner,d.object_name,d.object_type FROM
Dba_Objects d WHERE
d.object_name IN
( 'V$SESSION','V_$SESSION');
OWNER OBJECT_NAME OBJECT_TYPE
---------- --------------- -------------------
SYS V_$SESSION VIEW
PUBLIC
V$SESSION SYNONYM
SYS@ora11g > SELECT
d.owner,d.synonym_name,d.table_owner,d.table_name FROM
DBA_SYNONYMS D WHERE
D.synonym_name IN
( 'V$SESSION','V_$SESSION');
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------- ------------------------------ ------------------------------ ------------------------------
PUBLIC
V$SESSION SYS V_$SESSION
SYS@ora11g > SELECT
D.OWNER,
2 D.NAME,
3 D.TYPE,
4 D.REFERENCED_OWNER,
5 D.REFERENCED_NAME,
6 D.REFERENCED_TYPE
7 FROM
DBA_DEPENDENCIES D
8 WHERE
D.NAME
IN
('V$SESSION', 'V_$SESSION');
OWNER NAME
TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
---------- ------------- ------------------ ------------------- ----------------- ------------------
PUBLIC
V$SESSION SYNONYM SYS V_$SESSION VIEW
SYS V_$SESSION VIEW
SYS V$SESSION VIEW
SYS@ora11g > SYS@ora11g > SELECT
* FROM
V$FIXED_TABLE d WHERE
d.NAME
IN
( 'V$SESSION','V_$SESSION','GV$SESSION');
NAME
OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
GV$SESSION 4294951258 VIEW
65537
V$SESSION 4294950919 VIEW
65537
SYS@ora11g >
通过以上查询可以看到,V$SESSION属于公共同义词,它来源于SYS.V_$SESSION私有视图,而该视图又来源于系统底层表SYS.V$SESSION,而系统底层表SYS.V$SESSION又来源于系统底层表SYS.GV$SESSION。
Oracle选择访问对象的顺序原则是先SCHEMA后PUBLIC。所以,对于SYS用户而言,他查询V$SESSION视图其实是查询的系统底层表SYS.V$SESSION。对于系统底层表,是不能直接做赋权操作的。所以,SYS用户在将该视图赋权给其他用户的时候就会报错。而对于SYSTEM用户而言,他查询V$SESSION视图其实是查询的PUBLIC这个特殊用户下的公共同义词,而公共同义词是可以做赋权操作的。
& 说明:
有关Oracle同义词的更多内容介绍可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2154285/
有关Oracle数据字典的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2153324/
本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。