Oracle找出一个表的间接授权信息的方法

在oracle数据库中, 如果需要找出一张表授权给了哪一个用户,这个比较简单的,如果有一些视图引用了这张表,然后这张视图授权给了其它用户的话, 那么这也属于这张表的授权信息,如果也要找出这类信息,那么如何找出来这些信息呢?

下面简单看一个例子, 在数据库中存在三个用户T1, T2, T3, 假设T1用户将表T1.TEST的查询权限授予了用户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 vhttp://www.devze.comiew to t3;

具体授权操作如下所示:

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

那么此时查看关于表TEST的授权信息如下所示:

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 FOpythonR 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的查询权限授予了用户T2,并且使用了选项GRANT OPTION的话

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

那么此时,如果在T2用户下面创建一个视图,引用表TEST, 然后将视图T2.V_TEST的查询权限授权给了用户T3.

SQL> show user;
USER is "T2"
SQL> create or replace www.devze.comview 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就相当间接拥有了http://www.devze.com表TEST的查询权限. 如下所示:

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

但是,我们用上面的SQL来查询一下表TEST授予了哪些用户.如下所示, 这个查询结果不能体现表TEST间接授权给了用户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> 

那么问题来了,如何查询这种情况下的授权呢? 其实我们可以用下面SQL实现这个需求.如下所示:

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'
);

到此这篇关于Oracle找出一个表的间接授权信息的方法的文章就介绍到这了,更多相关oracle表授权信息内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！