今天領導讓我給一個使用者授權幾個檢視的查詢許可權
遇到了一點小意外.還是基本功不紮實啊

模擬如下
create user a identified by a;
create user b identified by b;
create user c identified by c;

grant connect,resource to a;
grant connect,resource to b;
grant connect,resource to c;
grant create view to b;

conn a/a
create table t1 as select rownum rn from dual connect by level<10;
grant select on t1 to b;

conn b/b
create table t2 as select rownum rn from dual connect by level<10;
create view v1 as select * from a.t1 union all select * from t2;
grant select on v1 to c;

conn c/c
select * from b.v1;

使用者a有一個基表,
使用者b有使用者a基表的查詢許可權,並建立了一個檢視,
現在需要把使用者b建立的檢視,授權給使用者c.

實際上grant select on v1 to c;命令執行會報錯(ORA-01720).
那是因為a.t1的查詢授權不能傳遞.

解決這個問題也很容易,在給b使用者a.t1的授權中增加with grant option
create user a identified by a;
create user b identified by b;
create user c identified by c;

grant connect,resource to a;
grant connect,resource to b;
grant connect,resource to c;
grant create view to b;

conn a/a
create table t1 as select rownum rn from dual connect by level<10;
grant select on t1 to b with grant option;

conn b/b
create table t2 as select rownum rn from dual connect by level<10;
create view v1 as select * from a.t1 union all select * from t2;
grant select on v1 to c;

conn c/c
select * from b.v1;

Oracle檢視授權(with grant option)

相關文章