快捷導航

Oracle找出一個表的間接授權(quán)信息的方法

更新時間：2025年06月27日 10:00:05 投稿：mrr

在Oracle中,查詢表授權(quán)需結(jié)合直接權(quán)限和通過視圖間接傳遞的權(quán)限,直接權(quán)限可通過ALL_TAB_PRIVS獲取,而間接授權(quán)需遞歸檢查視圖的授權(quán)鏈,本文給大家介紹Oracle找出一個表的間接授權(quán)信息的方法,感興趣的朋友一起看看吧

在Oracle數(shù)據(jù)庫中, 如果需要找出一張表授權(quán)給了哪一個用戶,這個比較簡單的,如果有一些視圖引用了這張表,然后這張視圖授權(quán)給了其它用戶的話, 那么這也屬于這張表的授權(quán)信息,如果也要找出這類信息,那么如何找出來這些信息呢?

下面簡單看一個例子, 在數(shù)據(jù)庫中存在三個用戶T1, T2, T3, 假設(shè)T1用戶將表T1.TEST的查詢權(quán)限授予了用戶T2.

create user t1 identified by t123456;
create user t2 identified by t234561;
create user t3 identified by t345612;
alter user t1 quota unlimited on users;
alter user t2 quota unlimited on users;
alter user t3 quota unlimited on users;
grant connect, resource to t1;
grant connect, resource to t2;
grant connect, resource to t3;
grant create view to t2;
grant create view to t3;

具體授權(quán)操作如下所示:

SQL> show user;
USER is "T1"
SQL> create table test(id number(10), name varchar2(30));
Table created.
SQL> insert into test
  2  select 1, 'k1' from dual union all
  3  select 2, 'k2' from dual;
2 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> grant select on test to t2;
Grant succeeded

那么此時查看關(guān)于表TEST的授權(quán)信息如下所示:

SET LINESIZE 820;
COL GRANTEE FOR A12
COL OWNER FOR A12
COL TABLE_NAME FOR A12
COL GRANTOR FOR A12
COL PRIVILEGE FOR A12
SELECT OWNER, TABLE_NAME, GRANTOR , GRANTEE, PRIVILEGE, GRANTABLE, TYPE 
FROM DBA_TAB_PRIVS WHERE TABLE_NAME='TEST';

SQL> show user;
USER is "SYS"
SQL> SET LINESIZE 820;
SQL> COL GRANTEE FOR A12
SQL> COL OWNER FOR A12
SQL> COL TABLE_NAME FOR A12
SQL> COL GRANTOR FOR A12
SQL> COL PRIVILEGE FOR A12
SQL> SELECT OWNER, TABLE_NAME, GRANTOR , GRANTEE, PRIVILEGE, GRANTABLE, TYPE 
  2  FROM DBA_TAB_PRIVS WHERE TABLE_NAME='TEST';
OWNER        TABLE_NAME   GRANTOR      GRANTEE      PRIVILEGE    GRA TYPE
------------ ------------ ------------ ------------ ------------ --- ------------------------
T1           TEST         T1           T2           SELECT       NO  TABLE
SQL>

如果用戶T1將表TEST的查詢權(quán)限授予了用戶T2,并且使用了選項GRANT OPTION的話

SQL> show user;
USER is "T1"
SQL> grant select on test to t2 with grant option;
Grant succeeded.
SQL>

那么此時,如果在T2用戶下面創(chuàng)建一個視圖,引用表TEST, 然后將視圖T2.V_TEST的查詢權(quán)限授權(quán)給了用戶T3.

SQL> show user;
USER is "T2"
SQL> create or replace view v_test
  2  as
  3  select name from t1.test;
View created.
SQL> grant select on t2.v_test to t3;
Grant succeeded.
SQL>

此時用戶T3就相當間接擁有了表TEST的查詢權(quán)限. 如下所示:

SQL> show user;
USER is "T3"
SQL> select * from t2.v_test;
NAME
------------------------------
k1
k2
SQL>

但是,我們用上面的SQL來查詢一下表TEST授予了哪些用戶.如下所示, 這個查詢結(jié)果不能體現(xiàn)表TEST間接授權(quán)給了用戶T3

SQL> show user;
USER is "SYS"
SQL> SET LINESIZE 820;
SQL> COL GRANTEE FOR A12
SQL> COL OWNER FOR A12
SQL> COL TABLE_NAME FOR A12
SQL> COL GRANTOR FOR A12
SQL> COL PRIVILEGE FOR A12
SQL> SELECT OWNER, TABLE_NAME, GRANTOR , GRANTEE, PRIVILEGE, GRANTABLE, TYPE 
  2  FROM DBA_TAB_PRIVS WHERE TABLE_NAME='TEST';
OWNER        TABLE_NAME   GRANTOR      GRANTEE      PRIVILEGE    GRA TYPE
------------ ------------ ------------ ------------ ------------ --- ------------------------
T1           TEST         T1           T2           SELECT       YES TABLE
SQL>

那么問題來了,如何查詢這種情況下的授權(quán)呢? 其實我們可以用下面SQL實現(xiàn)這個需求.如下所示:

SET LINESIZE 820
COL OWNER FOR A10
COL TABLE_NAME FOR A16;
COL GRANTOR FOR A16
COL GRANTEE FOR A16
COL PRIVILEGE FOR A8;
SELECT OWNER, TABLE_NAME, GRANTOR , GRANTEE, PRIVILEGE, GRANTABLE, TYPE 
FROM DBA_TAB_PRIVS 
WHERE TABLE_NAME=UPPER(TRIM('&tb_name'))
UNION ALL
SELECT  OWNER, TABLE_NAME, GRANTOR , GRANTEE, PRIVILEGE, GRANTABLE, TYPE  
FROM DBA_TAB_PRIVS 
WHERE TABLE_NAME IN(
SELECT  NAME FROM dba_dependencies WHERE 
REFERENCED_NAME=UPPER(TRIM('&tb_name')) AND TYPE='VIEW'
);