Oracle找出一个表的间接授权信息的方法_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 view 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 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的查询权限授予了用户t2,并且使用了选项grant option的话

sql> show user;
user is "t1"
sql> grant 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 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的查询权限. 如下所示:

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