查詢沒有許可權但資料字典中顯示有許可權

系統中可能會碰到查詢資料沒有許可權，但是從資料字典中檢視卻顯示包含查詢許可權的情況。

先看一下問題：

SQL> CONN U3/U3@YTK已連線。
SQL> SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM USER_SYNONYMS
2 WHERE SYNONYM_NAME = 'V_T2';

SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ----------------
V_T2 U2 V_T2

SQL> SELECT COUNT(*) FROM V_T2;
SELECT COUNT(*) FROM V_T2
*第 1 行出現錯誤:
ORA-01031: 許可權不足

SQL> SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
2 FROM USER_TAB_PRIVS
3 WHERE TABLE_NAME = 'V_T2';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
-------- ------ ------------------------------ -------- ----------------------
U3 U2 V_T2 U2 SELECT

SQL> CONN U2/U2@YTK已連線。
SQL> SELECT COUNT(*) FROM V_T2;

COUNT(*)
----------
40834

現在問題已經出來了，U2可以正常訪問，而且U2也對U3進行了授權，但是U3無法進行訪問。

其實問題的產生很簡單，V_T2本身並不是一個表，而是一個檢視，這個檢視訪問其他使用者的物件。這個時候U2不僅需要SELECT許可權，為了讓U3可以訪問自己建立的檢視，U2需要SELECT WITH GRANT OPTION。

如果U2使用者丟失了這個許可權，且在重現獲取許可權時只獲取到SELECT許可權，而沒有WITH GRANT OPTION，就會造成上面的問題。

透過一個例子來描述這個問題：

SQL> CONN YANGTK/YANGTK@YTK已連線。
SQL> DROP USER U1 CASCADE;

使用者已刪除。

SQL> DROP USER U2 CASCADE;

使用者已刪除。

SQL> DROP USER U3 CASCADE;

使用者已刪除。

SQL> CREATE USER U1 IDENTIFIED BY U1 DEFAULT TABLESPACE YANGTK;

使用者已建立。

SQL> CREATE USER U2 IDENTIFIED BY U2 DEFAULT TABLESPACE YANGTK;

使用者已建立。

SQL> CREATE USER U3 IDENTIFIED BY U3 DEFAULT TABLESPACE YANGTK;

使用者已建立。

SQL> GRANT CONNECT, RESOURCE TO U1;

授權成功。

SQL> GRANT CONNECT, RESOURCE TO U2;

授權成功。

SQL> GRANT CONNECT, RESOURCE TO U3;

授權成功。

SQL> GRANT CREATE VIEW TO U1;

授權成功。

SQL> GRANT CREATE VIEW, CREATE SYNONYM TO U2;

授權成功。

SQL> GRANT CREATE VIEW, CREATE SYNONYM TO U3;

授權成功。

SQL> CONN U1/U1@YTK已連線。
SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

表已建立。

SQL> GRANT SELECT ON T TO U2 WITH GRANT OPTION;

授權成功。

SQL> CONN U2/U2@YTK已連線。
SQL> CREATE SYNONYM T FOR U1.T;

同義詞已建立。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
40834

SQL> CREATE VIEW V_T2 AS SELECT * FROM U1.T;

檢視已建立。

SQL> GRANT SELECT ON T TO U3;

授權成功。

SQL> GRANT SELECT ON V_T2 TO U3;

授權成功。

SQL> CONN U3/U3@YTK已連線。
SQL> CREATE SYNONYM T FOR U2.T;

同義詞已建立。

SQL> CREATE SYNONYM V_T2 FOR U2.V_T2;

同義詞已建立。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
40834

SQL> SELECT COUNT(*) FROM V_T2;

COUNT(*)
----------
40834

現在就將測試的環境建立好了，使用者U1建立T表並對U2授權查詢同時執行T2將T表的訪問許可權授權給其他使用者。

U2建立一個同義詞指向T表，同時建立了一個檢視查詢T表。

U2將T表的查詢許可權和檢視V_T2的查詢許可權授權給U3。

U3可以訪問T表和V_T2檢視。

下面看看資料字典中的許可權：

SQL> CONN YANGTK/YANGTK@YTK已連線。
SQL> SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE
2 FROM DBA_TAB_PRIVS
3 WHERE OWNER IN ('U1', 'U2');

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
-------- ------ ---------- -------- ---------- ---
U2 U1 T U1 SELECT YES
U3 U1 T U2 SELECT NO
U3 U2 V_T2 U2 SELECT NO

刪除T表，重建後只賦予U2使用者SELECT許可權：

SQL> CONN U1/U1@YTK已連線。
SQL> DROP TABLE T PURGE;

表已刪除。

SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

表已建立。

SQL> GRANT SELECT ON T TO U2;

授權成功。

SQL> CONN YANGTK/YANGTK@YTK已連線。
SQL> SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE
2 FROM DBA_TAB_PRIVS
3 WHERE OWNER IN ('U1', 'U2');

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
-------- ------ ---------- -------- ---------- ---
U3 U2 V_T2 U2 SELECT NO
U2 U1 T U1 SELECT NO

可以看到U3從U2出獲取的T表的查詢許可權被連帶刪除，但是U3仍然有U2授權的V_T2的查詢許可權，不過這個時候U3已經無法查詢U2的V_T2檢視了：

SQL> CONN U3/U3@YTK已連線。
SQL> SELECT COUNT(*) FROM V_T2;
SELECT COUNT(*) FROM V_T2
*第 1 行出現錯誤:
ORA-01031: 許可權不足

SQL> CONN U2/U2@YTK已連線。
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
40834

SQL> SELECT COUNT(*) FROM V_T2;

COUNT(*)
----------
40834

這就是開始看到的現象，雖然資料字典中顯示U3有訪問U2物件的許可權，且U2的物件可以正常訪問，但是U3實際上已經沒有訪問物件的許可權了。

而且，這個時候U2也無法再次授權給U3了：

SQL> GRANT SELECT ON V_T2 TO U3;
GRANT SELECT ON V_T2 TO U3
*第 1 行出現錯誤:
ORA-01720: 不存在 'U1.T' 的授權選項