現(xiàn)象
這個(gè)問(wèn)題來(lái)自于朋友工作中遇到的一個(gè)真實(shí)場(chǎng)景：
Oracle 11.2.0.4數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)用戶A：create any view颇蜡，select any table
數(shù)據(jù)庫(kù)用戶B：select any table
數(shù)據(jù)庫(kù)用戶C: 只讀用戶价说，有且僅有create session權(quán)限
執(zhí)行步驟：
- 數(shù)據(jù)庫(kù)用戶A在數(shù)據(jù)庫(kù)用戶B中創(chuàng)建了一張VIEW辆亏，VIEW的基表來(lái)自于多個(gè)數(shù)據(jù)庫(kù)用戶。
- 使用SYS用戶將VIEW的select權(quán)限授權(quán)給數(shù)據(jù)庫(kù)用戶C鳖目。
- 數(shù)據(jù)庫(kù)用戶B和數(shù)據(jù)庫(kù)用戶C均可正常查詢?cè)揤IEW扮叨。
- 數(shù)據(jù)庫(kù)用戶A再次執(zhí)行：create or replace view語(yǔ)句，結(jié)果報(bào)出：
ORA-01720: grant option does not exist for 'TEST.T_OBJ'领迈。 - 數(shù)據(jù)庫(kù)用戶A新建一個(gè)VIEW(和老VIEW定義完全一致)彻磁，可以成功執(zhí)行。
重演過(guò)程
創(chuàng)建測(cè)試用戶
SQL> create user test0 identified by test;
grant create session to test0;
grant select any table to test0;
grant create any view to test0;
User created.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>create user test2 identified by test;
grant create session to test2;
grant select any table to test2;
SQL>
User created.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> create user test3 identified by test;
grant create session to test3;
SQL>
Grant succeeded.
SQL>
User created.
系統(tǒng)權(quán)限查詢：
SQL> select * from dba_sys_privs where grantee like 'TEST%' order by grantee,privilege;
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
TEST0 CREATE ANY VIEW NO
TEST0 CREATE SESSION NO
TEST0 SELECT ANY TABLE NO
TEST2 CREATE SESSION NO
TEST2 SELECT ANY TABLE NO
TEST3 CREATE SESSION NO
為簡(jiǎn)化測(cè)試狸捅，測(cè)試視圖僅僅引用了test.t_obj表衷蜓，記錄有800多萬(wàn)條。
SQL> connect test0/test;
Connected.
SQL> create or replace view test2.v_t_obj as select * from test.t_obj;
View created.
SQL> connect test2/test;
Connected.
SQL> select count(*) from v_t_obj;
COUNT(*)
----------
8053248
SQL> connect /as sysdba;
Connected.
SQL> grant select on test2.v_t_obj to test3;
Grant succeeded.
SQL> connect test3/test
Connected.
SQL> select count(*) from test2.v_t_obj;
COUNT(*)
----------
8053248
至此尘喝，一切看起來(lái)均很正常磁浇。
表的權(quán)限如下：
SQL> select * from dba_tab_privs where grantee like 'TEST%';
GRANT OWNER TABLE_NAME GRANT PRIVILEGE GRANTABLE HIERARCHY
----- ----- ------------------------------ ----- ---------- --------- ---------
TEST3 TEST2 V_T_OBJ TEST2 SELECT NO NO
SQL> select * from dba_sys_privs where grantee like 'TEST%' order by grantee,privilege;
GRANT PRIVILEGE ADMIN_OPTION
----- ------------------------------ ------------
TEST0 CREATE ANY VIEW NO
TEST0 CREATE SESSION NO
TEST0 SELECT ANY TABLE NO
TEST2 CREATE SESSION NO
TEST2 SELECT ANY TABLE NO
TEST3 CREATE SESSION NO
重建視圖，問(wèn)題發(fā)生了：
SQL> connect test0/test;
Connected.
SQL> create or replace view test2.v_t_obj as select * from test.t_obj;
create or replace view test2.v_t_obj as select * from test.t_obj
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'TEST.T_OBJ'
SQL> create view test2.v_t_obj_new as select * from test.t_obj;
View created.
測(cè)試到這里朽褪，結(jié)合常規(guī)場(chǎng)景中的視圖授權(quán)問(wèn)題置吓，初步可以斷定和表的select grant option策略有關(guān)。
進(jìn)一步繼續(xù)測(cè)試：
SQL> connect /as sysdba
Connected.
SQL> revoke select on test2.v_t_obj from test3;
Revoke succeeded.
SQL> connect test0/test
Connected.
SQL> create or replace view test2.v_t_obj as select * from test.t_obj;
View created.
到這里時(shí)鞍匾，可以思考一下，以上的操作里骑科，有一步操作值得關(guān)注：
SQL> connect /as sysdba;
Connected.
SQL> grant select on test2.v_t_obj to test3;
Grant succeeded.
按照正常操作橡淑，一般view的授權(quán)會(huì)以VIEW的owner登錄數(shù)據(jù)庫(kù)，然后直接grant咆爽。
SQL> connect test2/test
Connected.
SQL> grant select on test2.v_t_obj to test3;
grant select on test2.v_t_obj to test3
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'TEST.T_OBJ'
問(wèn)題還是出在多用戶的級(jí)聯(lián)授權(quán)上梁棠，繼續(xù)往下探：
SQL> connect system
Enter password:
Connected.
SQL> grant select on test2.v_t_obj to test3;
grant select on test2.v_t_obj to test3
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'TEST.T_OBJ'
SQL> select * from dba_sys_privs where grantee like 'SYS%' and privilege = 'SELECT ANY TABLE' order by grantee,privilege;
GRANTEE PRIVILEGE ADMIN_OPTION
---------- ------------------------------ ------------
SYS SELECT ANY TABLE YES
SYSTEM SELECT ANY TABLE NO
到了這里，問(wèn)題原因浮出水面：
with admin option
根據(jù)查詢官方文檔斗埂，發(fā)現(xiàn)有這么一段話：
You will get this ORA-1720 error when REPLACING a view that selects from some other user's tables and both of the following conditions are true:
- you have already granted select or other privileges on the VIEW to some other user
- the view owner does not have the GRANT option on the tables being selected from (or view owner may have some privilege with grant option but not others)
-
The code was changed in 11.2.0.4 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option, then ORA-1720 is the expected result (even in 11.2.0.3). In 11.2.0.4, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. The behavior in release 11.2.0.3 (and earlier) was incorrect; the new behavior in 11.2.0.4 is intentional and correct.
為了解決這一問(wèn)題符糊，建議對(duì)多用戶表或者視圖進(jìn)行級(jí)聯(lián)授權(quán)時(shí)：
1. 不要使用SYS用戶執(zhí)行類(lèi)似操作
2. 使用常規(guī)方式進(jìn)行相關(guān)授權(quán)
