GRANT/SELECT View时的遭遇ORA-01720和ORA-01031错误
2014-11-14 12:30 潇湘隐者 阅读(15331) 评论(0) 编辑 收藏 举报
关于创建视图遇到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.

· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库