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


		Post Upgrade To 11.2.0.4, "create or replace view" execution fails with "ORA-01720: Grant Option Does Not Exist" (文档 ID 1628033.1)	转到底部

In this Document

APPLIES TO:

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.

SYMPTOMS

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.

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
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'U2.TEST

CHANGES

Upgrade to 11.2.0.4 from a previous version.

CAUSE

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:

- you have already granted select or other privileges on the VIEW to some other user
- 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:

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.

SOLUTION

To avoid this issue, you can do either of the following:

1. Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
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 <> 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 <> 'PUBLIC'
and pd.grantee <> '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 <> 'PUBLIC'