首页 » Cloud, ORACLE 9i-23ai » Alert behavior changed from 11.2.0.4 “create or replace view” fail with ORA-01720

Alert behavior changed from 11.2.0.4 “create or replace view” fail with ORA-01720

  • 2023/03/09
  • Cloud, ORACLE 9i-23ai
  • 185 views
  • Alert behavior changed from 11.2.0.4 “create or replace view” fail with ORA-01720已关闭评论

今天有个同事咨询，发现在11.2.0.4以后的版本create or replace view 修改view 视图时，即使view owner当前用户是dba role也无法create or replace方式重建view，如当前用户u1把select on u1.t1 给u2（without grant option）, 用户u2创建 view 给了u3 select 查询.  按说u3对u1.t1是当前没有级联授权，所以u2在编辑view时会报错ORA-01720，而在11.2.0.3之前是正常编辑，但行为是不正确的, 从11.2.0.4以后已做修正。

Post Upgrade To 11.2.0.4, “create or replace view” execution fails with “ORA-01720: Grant Option Does Not Exist” (Doc ID 1628033.1)说明了这种形为的改变。

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;

不同版本测试结果
work fine. < 11.2.0.4 <= ORA-01720: grant option does not exist for ‘U2.TEST

 

  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. 

检查

col "view" for a80
col "cause" for a80
select unique d.owner||'''s view ('||d.name||') refer '||REFERENCED_OWNER||'.'||d.REFERENCED_NAME||' granted to '||pv.grantee "view" , pd.GRANTOR||' '||pd.GRANTABLE||' grant '||PD.PRIVILEGE||' on '||REFERENCED_OWNER||'.'||d.REFERENCED_NAME|| ' to '||pv.owner||' with grant option' "cause" 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';

view                                                                             cause
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
U1's view (TESTV) refer U2.TEST granted to SYSTEM                                U2 NO grant SELECT on U2.TEST to U1 with grant option

解决方法
1， 回收view上的所有权限或删除view，再replace 或重建view
2， 给view owner 授权“ with grant option”

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：