關於創建視圖遇到ORA-01031錯誤信息，請參考我以前整理的一篇文章Create view failed with ORA-01031:insufficient privileges，本來以為我那篇文章已經已經囊括了很多案例，但是今天遇到一個特殊案例，折騰了我比較久。下面通過幾個例子來演示一下遭遇ORA-01031的來龍去脈。
在測試環境准備兩個用戶dm、ods並授予一定的權限，准備好我們演示的案例。
SQL> show user;
USER is "SYS"
SQL> create user dm identified by dm;
User created.
SQL> create user ods identified by ods;
User created.
SQL> grant connect , resource to dm;
Grant succeeded.
SQL> grant create view to dm;
Grant succeeded.
SQL> grant connect , resource to ods;
Grant succeeded.
案例1： 將VIEW的SELECT權限授予其它用戶時，遭遇ORA-01720錯誤
SQL> conn ods/ods
Connected.
SQL> create table department
2 (
3 dept_id number(10) ,
4 dept_name varchar2(12)
5 );
Table created.
SQL> grant select on department to dm;
Grant succeeded.
SQL> conn dm/dm
Connected.
SQL> create table employee
2 (
3 employee_id number(10) ,
4 employee_name varchar2(32),
5 dept_id number(10)
6 );
Table created.
SQL> show user
USER is "DM"
SQL> create table employee
2 (
3 employee_id number(10) ,
4 employee_name varchar2(32),
5 dept_id number(10)
6 );
Table created.
SQL> select * from v_test;
no rows selected
SQL> grant select on dm.v_test to ods;
grant select on dm.v_test to ods
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'ODS.DEPARTMENT'
出現這個錯誤，是因為用戶ods將表DEPARTMENT的SELECT權限授予了用戶dm，而dm將試圖v_test的查詢權限授予ods時，也試圖將表DEPARTMENT授予用戶ods,但是dm並沒有這個權限（是不是很繞口），其實解決這個問題很簡單，即在授權時要使用WITH GRANT OPTION，如下所示即可解決：
SQL> conn ods/ods
Connected.
SQL> grant select on department to dm with grant option;
Grant succeeded.
SQL> conn dm/dm;
Connected.
SQL> grant select on dm.v_test to ods;
Grant succeeded.
SQL> conn ods/ods
Connected.
SQL> select * from dm.v_test;
no rows selected
案例2： 將VIEW授權給其它用戶（sys賬號下），查詢時遭遇ORA-01031: insufficient privileges
SQL> show user;
USER is "ODS"
SQL> create or replace function get_deptcode( departname varchar2) return varchar2
2 as
3 dept_code varchar2(2);
4 begin
5 select substr(departname,1,1) into dept_code from dual;
6 return dept_code;
7 end;
8 /
Function created.
SQL> grant execute on get_deptcode to dm;
Grant succeeded.
SQL> conn dm/dm
Connected.
SQL> create or replace view v_test
2 as
3 select e.employee_id
4 ,e.employee_name
5 ,(select ods.get_deptcode(d.dept_name) from dual) dept_code
6 from employee e
7 inner join ods.department d on e.dept_id =d.dept_id;
View created.
SQL> select * from v_test;
no rows selected
以sys登錄授權ods擁有查詢視圖的權限，之所以用sys執行授權，而不用對應賬號dm，是因為我發布腳本時，一般都用sys賬號發布，結果就遭遇了比較隱秘的ORA-01031錯誤。
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant select on dm.v_test to ods;
Grant succeeded.
SQL> conn ods/ods
Connected.
SQL> select * from dm.v_test;
select * from dm.v_test
*
ERROR at line 1:
ORA-01031: insufficient privileges
如果以dm賬號登錄，將視圖v_test的權限授予給ods，就能發現這個錯誤，而以sys賬號操作，反而隱藏了該錯誤。如下所示
SQL> conn dm/dm
Connected.
SQL> grant select on v_test to ods;
grant select on v_test to ods
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'ODS.GET_DEPTCODE'
SQL> conn ods/ods
Connected.
SQL> grant execute on ODS.GET_DEPTCODE to dm with grant option;
Grant succeeded.
SQL> conn dm/dm
Connected.
SQL> grant select on v_test to ods;
Grant succeeded.
