Oracle建视图提示：create or replace view" execution fails with "ORA-01720: Grant Option Does Not Exist

傷城~ 2022-04-25 08:32 352阅读 0赞

![/images/20220125/232ac6a70fe54ebdae829bd2eb2bf691.png][https_support.oracle.com_epmos_adf_images_t.gif]

<table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="4" style="width:432pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:7.5pt;"> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=454025682145015&amp;id=1628033.1&amp;_adf.ctrl-state=143imufgzf_52" rel="nofollow"></a> </td> 
 <td style="width:7.5pt;"> <img alt="/images/20220125/232ac6a70fe54ebdae829bd2eb2bf691.png" height="10" src="/images/20220125/232ac6a70fe54ebdae829bd2eb2bf691.png" width="10"> </td> 
 <td> Post Upgrade To 11.2.0.4, "create or replace view" execution fails with "ORA-01720: Grant Option Does Not Exist" (文档 ID 1628033.1) </td> 
 <td> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=454025682145015&amp;id=1628033.1&amp;_adf.ctrl-state=143imufgzf_52" rel="nofollow">转到底部</a> </td> 
 <td> <img alt="/images/20220125/232ac6a70fe54ebdae829bd2eb2bf691.png" height="10" src="/images/20220125/232ac6a70fe54ebdae829bd2eb2bf691.png" width="20"> </td> 
 </tr> 
 </tbody> 
</table>

--------------------

![/images/20220125/232ac6a70fe54ebdae829bd2eb2bf691.png][https_support.oracle.com_epmos_adf_images_t.gif]

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> In this Document 
 <table> 
 <tbody> 
 <tr> 
 <td style="width:.3pt;">&nbsp;</td> 
 <td> <a rel="nofollow">Symptoms</a> </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; 
 <table> 
 <tbody> 
 <tr> 
 <td style="width:.3pt;">&nbsp;</td> 
 <td> <a rel="nofollow">Changes</a> </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; 
 <table> 
 <tbody> 
 <tr> 
 <td style="width:.3pt;">&nbsp;</td> 
 <td> <a rel="nofollow">Cause</a> </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; 
 <table> 
 <tbody> 
 <tr> 
 <td style="width:.3pt;">&nbsp;</td> 
 <td> <a rel="nofollow">Solution</a> </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; 
 <table> 
 <tbody> 
 <tr> 
 <td style="width:.3pt;">&nbsp;</td> 
 <td> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=454025682145015&amp;id=1628033.1&amp;_adf.ctrl-state=143imufgzf_52#REF" rel="nofollow">References</a> </td> 
 </tr> 
 </tbody> 
 </table> 
 <hr>&nbsp; <h2 style="margin-left:0cm;">APPLIES TO:</h2> Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. <h2 style="margin-left:0cm;"><a rel="nofollow"></a>SYMPTOMS</h2> NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.&nbsp; &nbsp; After upgrading from 11.2.0.3 to 11.2.0.4, you encounter the following error while executing the "create or replace view" statement: ORA-01720: grant option does not exist Views were created before the upgrade and "CREATE OR REPLACE VIEW" had worked fine. For example the following testcase will run without errors on version 11.2.0.3, while the last statement 'create or replace view testv as select * from u2.test' will fail on 11.2.0.4 connect / as sysdba -- drop user u1 cascade; -- drop user u2 cascade; create user u1 identified by xxx; create user u2 identified by xxx; grant resource to u1; grant resource to u2; grant create session to u1; grant create session to u2; grant create view to u1; connect u2/xxx create table u2.test(a number); grant select on test to u1 with grant option; connect u1/xxx create view testv as select * from u2.test; grant select on testv to system; connect u2/xxx revoke select on test from u1; grant select on test to u1; connect u1/xxx create or replace view testv as select * from u2.test; The result in version 11.2.0.4 (and higher) will be: create or replace view testv as select * from u2.test &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * ERROR at line 1: ORA-01720: grant option does not exist for 'U2.TEST &nbsp;' <h2 style="margin-left:0cm;"><a rel="nofollow"></a>CHANGES</h2> Upgrade to 11.2.0.4 from a previous version. <h2 style="margin-left:0cm;"><a rel="nofollow"></a>CAUSE</h2> The observed behavior is correct. You will get this ORA-1720 error when REPLACING a view that selects from some other user's tables and both of the following conditions are true:&nbsp; &nbsp; - you have already granted select or other privileges on the VIEW to some other user&nbsp; - 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) Development has explained it as follows: &nbsp; The code was changed in 11.2.0.4 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner 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 consistent with the GRANT behavior, i.e. if an incompatible grant exists, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must 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.&nbsp; <h2 style="margin-left:0cm;"><a rel="nofollow"></a>SOLUTION</h2> To avoid this issue, you can do either of the following:&nbsp; &nbsp; 1. Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.&nbsp; 2. Drop and recreate the view. Dropping the view will automatically remove all grants. To detect views with this condition you can use the following statement, however be aware it only detects first level dependencies: select unique d.owner, d.name "view" from dba_dependencies d, dba_tab_privs pv, dba_tab_privs pd where -- there are dependencies outside the schema of the view owner d.TYPE = 'VIEW' and d.REFERENCED_OWNER &lt;&gt; d.owner -- there are grants issued on the view and pv.TABLE_NAME = d.name and d.owner = pv.owner and pv.privilege in ('SELECT','INSERT','UPDATE') -- dependent objects have not been granted with admin option and pd.TABLE_NAME = d.REFERENCED_NAME and pd.GRANTABLE = 'NO' -- exlude grants to roles and pv.grantee &lt;&gt; 'PUBLIC' and pd.grantee &lt;&gt; 'PUBLIC' and pv.grantee not in (select role from dba_roles) and pd.grantee not in (select role from dba_roles) -- exclude public owned objects and d.REFERENCED_OWNER &lt;&gt; 'PUBLIC' &nbsp; When used with the testcase at the top of this document, the offending view can be singled out: SQL&gt; @view_check OWNER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; view ------------------------------ ------------------------------ U1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TESTV </td> 
 </tr> 
 </tbody> 
</table>

[https_support.oracle.com_epmos_adf_images_t.gif]: /images/20220125/232ac6a70fe54ebdae829bd2eb2bf691.png