SQL> create user a identified by a;
SQL> create user b identified by a;
SQL> create user c identified by a;
SQL> grant connect ,resource to a;
SQL>grant connect to b,c;
a
SQL> create table a(id int,name varchar2(20));
b
SQL> grant select on a.a to b;
SQL> grant create view to b;
SQL> create view v1 as select * from a.a;
c
SQL> grant create view to c;
SQL> grant select on b.v1 to c;
SQL> grant select on a.a to c;
SQL> create view v2 as select id from b.v1;
ORA-01031: insufficient privileges
有权限的
select * from user_tab_privs
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
C A A A SELECT NO NO
C B V1 B SELECT NO NO
select * from user_sys_privs
USERNAME PRIVILEGE ADM
C CREATE VIEW NO
评论
有用 0这个是没有级联，因为最后create view v2 as select id from b.v1;这个需要取调用底层视图，你可以通过10046去追踪一下，我今天测试了一下。
PARSING IN CURSOR #139935025613160 len=37 dep=1 uid=0 oct=3 lid=0 tim=1587694095465031 hv=1398610540 ad=‘de1f8af0’ sqlid=‘grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #139935025613160:c=33,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1587694095465029
BINDS #139935025613160:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=7f45297e4528 bln=16 avl=16 flg=05
value=000161C0.0001.0001
EXEC #139935025613160:c=612,e=10292,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1587694095475417
FETCH #139935025613160:c=31,e=31,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1587694095475487
STAT #139935025613160 id=1 cnt=1 pid=0 pos=1 obj=69 op=‘TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=23 us cost=1 size=15 card=1)’
CLOSE #139935025613160:c=71,e=71,dep=1,type=0,tim=1587694095475574
PARSING IN CURSOR #139935025614104 len=40 dep=0 uid=104 oct=21 lid=104 tim=1587694095475977 hv=2292582133 ad=‘7f452979fdf0’ sqlid=‘8t3mccy4ac1rp’
create view v2 as select * from user2.v1
END OF STMT
PARSE #139935025614104:c=1735,e=11416,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1587694095475976
PARSING IN CURSOR #139935025607992 len=45 dep=1 uid=0 oct=3 lid=0 tim=1587694095476147 hv=3393782897 ad=‘de1df018’ sqlid=‘9p6bq1v54k13j’
select value$ from sys.props$ where name = :1
END OF STMT
PARSE #139935025607992:c=28,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1587694095476146
BINDS #139935025607992:
Bind#0
oacdty=01 mxl=32(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7f4529710bc8 bln=32 avl=22 flg=05
value=“GG_XSTREAM_FOR_STREAMS”
EXEC #139935025607992:c=5506,e=13369,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1587694095489567
FETCH #139935025607992:c=32,e=32,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1587694095489617
STAT #139935025607992 id=1 cnt=0 pid=0 pos=1 obj=98 op=‘TABLE ACCESS FULL PROPS$ (cr=2 pr=0 pw=0 time=32 us cost=2 size=28 card=1)’
CLOSE #139935025607992:c=5,e=5,dep=1,type=0,tim=1587694095489670
因为调用底层视图，肯定没有权限。
所以你这个很好处理，我昨天测试用了两种方法：
1.授予c select any table的权限，就可以创建了。
2.grant select on a.a to b; 加上with grant option就可以了。
但是这种应该在生产少见，一般不会这么用，因为视图查视图这种本身就比较复杂，如果是a表创建非常复杂，根据a表创建视图v1也很复杂的话，创建V2去做测试这种视图估计可能会产生性能问题的，一般这种直接去查a表就可以，不过研究研究还是挺好玩的。
评论 有用 0
