ORA-01720: grant option does not existfor 'reference user.table_name or view_name' 这种错误发生常发生在11.2.0.4 版本中,
官方给出的解释:
#01720, 00000,"grant option does not exist for '%s.%s'" // *Cause: A grant was being performed on a view or aview was being replaced // and the grant option was not presentfor an underlying object. // *Action: Obtainthe grant option on all underlying objects of the view or // revoke existing grants on the view. |
遇到过出现该问题的几种场景:
1. 问题往往出现在3个或3个以上schema之间互相引用或者授权不兼容时发生
2. IMPDP recompile view error with ORA-01720:grant option does not exist for
3.当使用 create or replace view 重建已存在的视图的过程中发生(主要出现在数据库迁移或者升级至11.2.0.4后)
原因及处理方法为:
1. You have already granted select or other privileges on the VIEW to some other user
2. The view owner does not have the GRANT option on the tables being selected from (or view owner may have some privilege
with grant option but not others)
方法1.该被创建或者替换的视图应该拥有所有基表上的all 权限;
方法2.回收之前授予的那些不兼容的权限(主要出现在数据库迁移或者升级至11.2.0.4后)
这种错误在10G或更早没有遇到过(oracle称之为早期版本不规范),查阅MOS,有如下一篇Note描述了类似的情况:
Oracle Note:1628033.1 – Post Upgrade to 11.2.04, “create or replace view” execution failswith “ORA-01720: Grant Option Does Not Exist” "The code was changed in 11.2.0.4 so that create view behavior issimilar to grant. If you try to make a GRANT on an existing view and the viewowner does not have the grant option, then ORA-1720 is the expected result(even in 11.2.0.3). In 11.2.0.4, we have simply made CREATE VIEW consistentwith the GRANT behavior, i.e. if an incompatible grant exists, then the new viewdefinition must not be allowed (even with FORCE). In other words, we do notallow incompatible grants to coexist with the view definition and so an errormust be thrown. The behavior in release 11.2.0.3 (and earlier) was incorrect;the new behavior in 11.2.0.4 is intentional and correct." |
此处提及在11.2.0.4或者11.2.0.3版本中,创建视图的代码和grant级联授权代码类似。如果你想向已存在的视图授权,但该视图
持有用户没有grant权限时就会出现ORA-1720错误。类似create or replace view (前提是该视图已存在).在11.2.0.3之前不会
出现错误和提示,这种方式存在使用者和视图拥有者不兼容者权限的授权选项,oracle为了,但到11.2.0.4(11.2.0.3)因为代码作
为更正,会直接提示该错误,这是正常现象。
我们做几个简单模拟场景测试:
场景1:新装Oracle database 11.2.0.4 on Linux 6.1
SQ;>Select * fromv$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 -Production CORE 11.2.0.4.0 Production TNS for Linux: Version11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 –Production
SQL> create user scott1identified by tigger; User created. * SQL> grant connect toscott1; Grant succeeded. SQL> grant create sessionto scott1; Grant succeeded. SQL> conn scott1/tigger Connected. SQL>create or replace viewv_dept as (select e.empno, e.ename, e.job, d.loc from scott.emp e left outer join scott.dept d on e.deptno=d.deptno); from scott.emp e * ERROR at line 6: ORA-01720: grant option does not exist for'SCOTT.DEPT' SQL> conn scott/tigger Connected. SQL> grant select on scott.dept to scott1 with grant option; Grant succeeded. SQL> conn scott1/tigger Connected. SQL> create or replaceview v_dept as 2 (select e.empno, 3 e.ename, 4 e.job, 5 d.loc 6 fromscott.emp e 7 leftouter join scott.dept d 8 on e.deptno=d.deptno); from scott.emp e * ERROR at line 6: ORA-01720: grantoption does not exist for 'SCOTT.EMP' SQL> conn scott/tigger Connected. SQL> grant select on scott.emp toscott1 with grant option; Grant succeeded. SQL> connscott1/tigger Connected. SQL> create orreplace view v_dept as 2 (select e.empno, 3 e.ename, 4 e.job, 5 d.loc 6 fromscott.emp e 7 leftouter join scott.dept d 8 on e.deptno=d.deptno); from scott.emp e * ERROR at line 6: ORA-01720: grantoption does not exist for 'SCOTT.EMP' SQL> conn scott/tigger Connected. SQL>grant all on scott.emp toscott1 with grant option; Grant succeeded. SQL> conn scott1/tigger Connected. SQL> create or replaceview v_dept as 2 (select e.empno, 3 e.ename, 4 e.job, 5 d.loc 6 fromscott.emp e 7 leftouter join scott.dept d 8 on e.deptno=d.deptno); View created. 这种情况符合,该被创建或者替换的视图应该拥有所有基表上的all 权限这异类。 |
场景二:视图授权给第三个用户 dbsnmp 时,同样出现该错误
SQL> select * from scott.v_dept; EMPNO ENAME JOB LOC ---------- ---------- --------- ------------- 7934 MILLER CLERK NEW YORK 7839 KING PRESIDENT NEW YORK 7782 CLARK MANAGER NEW YORK 7902 FORD ANALYST DALLAS 7876 ADAMS CLERK DALLAS 7788 SCOTT ANALYST DALLAS 7566 JONES MANAGER DALLAS 7369 SMITH CLERK DALLAS 7900 JAMES CLERK CHICAGO 7844 TURNER SALESMAN CHICAGO 7698 BLAKE MANAGER CHICAGO 7654 MARTIN SALESMAN CHICAGO 7521 WARD SALESMAN CHICAGO 7499 ALLEN SALESMAN CHICAGO 14 rows selected. SQL>conn scott1/tigger sysdba SQL>create or replace view v_dept_scott1as (selecte.empno, e.ename, e.job, e.deptno from scott.emp e left outer join scott.v_dept d one.job=d.job); View created. SQL> grant select onv_dept_scott1 to dbsnmp; grant select onv_dept_scott1 to dbsnmp * ERROR at line 1: ORA-01720: grant option doesnot exist for 'SCOTT.V_DEPT' //提示在scott1用户在scott.v_dept上无grant option 权限,检查对象权限
SQL> conn scott1/tigger Connected. SQL> grant select onv_dept_scott1 to dbsnmp; grant select onv_dept_scott1 to dbsnmp * ERROR at line 1: ORA-01720: grant option doesnot exist for 'SCOTT.V_DEPT' SQL> select * fromuser_tab_privs where grantee like 'SCOTT1'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ---------------------------------------- -------------------- ---------------------------------------- --- --- SCOTT1 SCOTT DEPT SCOTT SELECT YES NO SCOTT1 SCOTT EMP SCOTT SELECT YES NO SCOTT1 SCOTT V_DEPT SCOTT SELECT NO NO SQL> conn scott/tigger Connected. SQL> grant select on v_deptto scott1 with grant option; Grant succeeded. SQL> conn scott1/tigger Connected. SQL> grant select onv_dept_scott1 to dbsnmp; Grant succeeded. SQL> select * fromuser_tab_privs where grantee like 'SCOTT1'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ---------------------------------------- -------------------- ---------------------------------------- --- --- SCOTT1 SCOTT DEPT SCOTT SELECT YES NO SCOTT1 SCOTT EMP SCOTT SELECT YESNO SCOTT1 SCOTT V_DEPT SCOTT SELECT YES NO |
结论
如果数据库版本在11.2.0.3(不含)以前该问题不存在(11.2.0.1测试无错误)
如果当前数据库是从以前版本升级到11.2.0.4而来,oracle认为这是一个bug,bug No如下:
BUG:17994036 -POST UPGRADE TO 11.2.0.4 CREATE OR REPLACE FAILS WITH ORA-01720 |