ORA-01720: grant option does not exist for 'reference schema.table_name ora view_name'

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 BUG:18024486 -ORA-1720 WHEN CREATING VIEW AFTER TO HAVE UPGRADE FROM 11.2.0.3.0 TO 11.2.0.4.0