关于创建视图遇到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.
