GRANT/SELECT View时的遭遇ORA-01720和ORA-01031错误

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

GRANT/SELECT View时的遭遇ORA-01720和ORA-01031错误

 

出 现这个错误,是因为用户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

GRANT/SELECT View时的遭遇ORA-01720和ORA-01031错误

如果以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.
上一篇:【 CDN 最佳实践】CDN 加速 OSS 常见问题及处理思路


下一篇:闫永慧的技术博客今天开启!