CREATE VIEW ORA-01031

朋友說遇到了一個奇怪的事，問能通路表，也有CREATE ANY VIEW的權限，為什麼不能基于被通路的表建立視圖？

而且是同一使用者中有的表可以建視圖，有的表不能建視圖。

建立視圖有兩個條件要滿足：

1.有建立視圖的權限

2.目前使用者有被表的所有者顯示授予通路表的權限(即權限不能是通過角色繼承過來的)

下面模拟一下解決過程：

-- 1.有通路表scott.emp的權限（此權限不是被顯示授予的，是通過角色繼承過來的。是以建立視圖失敗）

SQL> SELECT COUNT(1)  FROM scott.emp;

  COUNT(1)

----------

        13

-- 2. 建立視圖失敗

SQL> CREATE  VIEW emp  AS  SELECT * FROM scott.emp;

CREATE  VIEW emp  AS  SELECT * FROM scott.emp

ORA-01031: 權限不足

-- 3.檢視目前session是否有建立視圖的權限，結果表明确實有建立視圖的權限（CREATE ANY VIEW）

SQL> SELECT *    FROM SESSION_PRIVS

  2   WHERE PRIVILEGE IN ('CREATE ANY VIEW', 'SELECT ANY TABLE');

PRIVILEGE

----------------------------------------

SELECT ANY TABLE

CREATE ANY VIEW

-- 4. 基于其他表可以建立視圖，為什麼呢？

SQL> CREATE OR REPLACE  VIEW s_dept  AS  SELECT * FROM scott.dept;

View created

SQL> CREATE OR REPLACE VIEW s_emp  AS  SELECT * FROM scott.emp;

CREATE OR REPLACE VIEW s_emp  AS  SELECT * FROM scott.emp

ORA-01031: 權限不足

--5 檢視目前使用者擁有的對象權限，發現DEPT表被顯示的授予了select的權限，而emp表沒有。

    為什麼沒有授予select權限，還能通路scott.emp表呢？

    因為目前使用者擁有DBA權限。dba角色中包含了select any table的權限。

SQL> SELECT GRANTOR, TABLE_NAME, PRIVILEGE

  2    FROM USER_TAB_PRIVS

  3   WHERE GRANTOR = 'SCOTT';

GRANTOR         TABLE_NAME      PRIVILEGE     

---------------     ---------------       ---------------

SCOTT               DEPT                SELECT

--6. 知道了問題的原因，讓scott使用者顯示授權給report使用者selelct 表EMP的權限即可。

SQL> conn scott/tiger

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> grant select on emp to report;

Grant succeeded

SQL> conn  report/report

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as report

SQL> SELECT GRANTOR, TABLE_NAME, PRIVILEGE

  2    FROM USER_TAB_PRIVS

  3   WHERE GRANTOR = 'SCOTT';

GRANTOR      TABLE_NAME      PRIVILEGE     

---------------  ---------------        ---------------

SCOTT           DEPT                  SELECT

EMP               DEPT                  SELECT

--7  顯示授予report使用者selelct權限後，視圖可以成功建立。

SQL> CREATE OR REPLACE VIEW sc_emp  AS  SELECT * FROM scott.emp;

View created

SQL>