【Oracle】如何让一个用户能够访问另外一个用户下所有的表
根据需求的不同，也分为好几种方法，且看下文。
先构造基本的环境：创建两个用户AA，BB，基本需求为用户AA能够访问用户BB下所有的表，即用户AA有对BB下所有的表有“select on”权限。
SYS@zkm> create user aa identified by oracle account unlock;
User created.
SYS@zkm> create user bb identified by oracle account unlock;
User created.
SYS@zkm> grant connect,resource to aa,bb;
Grant succeeded.
SYS@zkm> create table bb.t1(id int);
Table created.
SYS@zkm> create table bb.t2(id int);
Table created.
SYS@zkm> conn aa/oracle
Connected.
AA@zkm> select * from bb.t1;
select * from bb.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
对用户BB创建了两张表T1和T2，用户AA并没有对这两张表访问权限。
根据需求，有方法1：
SYS@zkm> select 'grant select on "'||owner||'"."'||table_name||'" to aa;' grant_sql from dba_tables where owner='BB';
GRANT_SQL
--------------------------------------------------------------------------------
grant select on "BB"."T1" to aa;
grant select on "BB"."T2" to aa;
SYS@zkm> grant select on "BB"."T1" to aa;
Grant succeeded.
SYS@zkm> grant select on "BB"."T2" to aa;
Grant succeeded.
SYS@zkm> conn aa/oracle
Connected.
AA@zkm> select * from bb.t1;
no rows selected
AA@zkm> select * from bb.t2;
no rows selected
缺点也很明显，对于新增加的表用户AA仍然无权限访问。
方法2：
回收权限，重新进行新的实验。
AA@zkm> conn bb/oracle
Connected.
BB@zkm> revoke select on "BB"."T1" from aa;
Revoke succeeded.
BB@zkm> revoke select on "BB"."T2" from aa;
Revoke succeeded.
BB@zkm> conn aa/oracle
Connected.
AA@zkm> select * from bb.t1;
select * from bb.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
方法如下：
SYS@zkm> grant select any table to aa;
Grant succeeded.
SYS@zkm> conn aa/oracle
Connected.
AA@zkm> select * from bb.t1;
no rows selected
AA@zkm> select * from bb.t2;
no rows selected
这种方法对新增加的表也有访问权限，缺点是除了SYS用户以外所有用户的表AA均有访问权限，由于权限被放大了，也就存在安全隐患。客户能接受是最方便的办法了。
方法3：
根据需求，不放大权限，并且对新建的表也有权限访问的话，目前我只想到用触发器来实现。先使用方法1对当前现有的表授权访问权限给AA，编写触发器实现BB创建新表后，触发授权动作达到目的。
回收select any table权限，并使用方法1对已有的表授权访问权限。
SYS@zkm> revoke select any table from aa;
Revoke succeeded.
SYS@zkm> grant select on "BB"."T1" to aa;
Grant succeeded.
SYS@zkm> grant select on "BB"."T2" to aa;
Grant succeeded.
不过在研究过程还是遇到一个问题，对于触发器触发的动作只能是DML操作，不能是DDL或者DCL。grant这个动作属于DCL操作会报错。详细见下文。
BB@zkm> create or replace trigger new_table_grant_tri
2 after create
3 on schema
4 declare
5 sqlstr varchar2(100);
6 begin
7 sqlstr:='grant select on '||ora_dict_obj_name||' to aa';
8 execute immediate sqlstr;
9 end;
10 /
Trigger created.
BB@zkm> create table t3(id int);
create table t3(id int)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 5
BB@zkm> !oerr ora 30511
30511, 00000, "invalid DDL operation in system triggers"
// *Cause: An attempt was made to perform an invalid DDL operation
// in a system trigger. Most DDL operations currently are not
// supported in system triggers. The only currently supported DDL
// operations are table operations and ALTER?COMPILE operations.
// *Action: Remove invalid DDL operations in system triggers.
上网找资料，发现一种技术叫“自治事务”，以前接触过一下没在意。
自治事务是在某个会话中独立开启一个事务，在其中处理的操作不会影响到同一会话中其他事务未提交的内容。反过来也是，同一会话的事务不会影响自治事务的内容。
于是将触发器改写如下：
BB@zkm> CREATE OR REPLACE TRIGGER new_table_grant_tri
2 after create
3 ON SCHEMA
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 sqlstr varchar2(100);
7 BEGIN
8 IF ora_dict_obj_type='TABLE' THEN
9 sqlstr:='grant select on '||ora_dict_obj_name||' to aa';
10 execute immediate sqlstr;
11 COMMIT;
12 end if;
13 END;
14 /
Trigger created.
BB@zkm> create table t3(id int);
create table t3(id int)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04020: deadlock detected while trying to lock object BB.T3
ORA-06512: at line 7
目前尚不清楚ORA-4020错误产生原因。
换另外一种实现方式，先创建存储过程，该存储过程实现对新表授权动作。再创建触发器，调用该存储过程。如下：
BB@zkm> create or replace procedure new_table_grant_procedure(v_tabname IN VARCHAR2)
2 is
3 sqlstr VARCHAR2(200);
4 begin
5 sqlstr := 'grant select on ' || v_tabname ||' to aa';
6 execute immediate sqlstr;
7 end;
8 /
Procedure created.
BB@zkm> CREATE OR REPLACE TRIGGER new_table_grant_tri
2 after create
3 ON SCHEMA
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 lv_job NUMBER;
7 BEGIN
8 IF ora_dict_obj_type='TABLE' THEN
9 DBMS_JOB.SUBMIT(lv_job,'new_table_grant_procedure('''||ora_dict_obj_name||''');');
10 COMMIT;
11 end if;
12 END;
13 /
Trigger created.
BB@zkm> create table t3(id int);
Table created.
BB@zkm> conn aa/oracle
Connected.
AA@zkm> select * from bb.t3;
no rows selected
如果不使用DBMS_JOB.SUBMIT的方式同样会报错ORA-4020，待探究。
关于触发器的其他知识点：
ora_client_ip_address:用于返回客户端的IP地址
ora_database_name：用于返回当前数据库名
ora_des_encrypted_password:用于返回DES加密后的用户口令
ora_dict_obj_name:用于返回DDL操作所对应的数据库对象名
ora_dict_obj_name_list(name_list_ OUT ora_name_list_t):用于返回字事件中被修改的对象名列表
ora_dict_obj_owner:用于返回DDL操作所对应的对象的所有者名。
ora_dict_obj_ower_list(ower_list OUT ora_name_list_t):用于返回在事件中被修改对象的所有者列表
ora_dict_obj_type:用于返回DDL操作所对应的数据库对象的类型。
ora_grantee(user_list OUT ora_name_list_t):用于返回授权时事件授权者。
ora_instance_num:用于返回历程号。
ora_is_alter_column(column_name IN VARCHAR2):用于检测特定列是否被修改
ora_is_creating_nested_table:用于检测是否正在建立嵌套表
ora_is_drop_column(column_name IN VARCHAR2):用于检测特定列是否被删除
ora_is_servererror(error_number):用于检测是否返回了特定Oracle错误。
ora_login_user：用于返回登录用户名
ora_sysevent ：用于返回触发 触发器的系统时间名。
内容大致以上这些。
