Oracle授權A使用者查詢B使用者的所有表

需求：
新建的使用者userA，要授權給他訪問使用者scott的所有表

有三種兩方法：
1）
SQL> conn / as sysdba;
SQL> grant select any table on userA

這種方法的缺點是授予的許可權過大，userA不僅可以訪問scott下的所有表，也可以訪問其他使用者包括sys,system下的所有表。

2）
SQL> conn scott/tiger;
SQL> select 'GRANT SELECT ON' || table_name || 'to userA;'  from user_tables
得到的結果如下
grant select on emp to userA;
grant select on dept to userA;
grant select on bonus to userA;
grant select on loc to userA;

再把上面得到的結果逐一執行一遍:
SQL> grant select on emp to userA;
SQL> grant select on dept to userA;
SQL> grant select on bonus to userA;
SQL> grant select on loc to userA;

這種方法的缺點是要執行比較多的語句，如果有100個表，就得執行100個grant語句；
另外scott新建的表不在被授權的範圍內，新建的表要想被userA訪問，也得執行grant語句:
grant select on 新建的表 to userA;

（3）使用遊標
先建立兩個使用者
SQL> create user test1 identified by oracle;
User created.

SQL> create user test2 identified by oracle;
User created.

授權
SQL> grant connect, resource to test1;
Grant succeeded.

SQL> grant connect, resource to test2;
Grant succeeded.

在test2下建立一個表作測試用
SQL> conn test2/oracle;
Connected.

SQL> create table t(id number);
Table created.

建立角色並用遊標給角色授權
SQL> conn /as sysdba;
Connected.
 
SQL> create role select_all_test2_tab;
Role created

SQL>
declare
  CURSOR c_tabname is select table_name from dba_tables where owner = 'TEST2';
  v_tabname dba_tables.table_name%TYPE;
  sqlstr    VARCHAR2(200);
    
begin
  open c_tabname;
  loop
    fetch c_tabname into v_tabname;
    exit when c_tabname%NOTFOUND;
    sqlstr := 'grant select on test2.' || v_tabname ||' to select_all_test2_tab';
    execute immediate sqlstr;
  end loop;
  close c_tabname;
end;
/

PL/SQL procedure successfully completed.

把角色授權給test1
SQL> grant select_all_test2_tab to test1;
Grant succeeded.

嘗試用test1訪問test2的表
SQL> conn test1/oracle;
Connected.

SQL> select * from test2.t;
no rows selected

在test2下新建表
SQL> conn test2/oracle;
Connected.
SQL> create table ta(id number);
Table created.

嘗試用test1訪問新建的表
SQL> conn test1/oracle;
Connected.
SQL> select * from test2.ta;
select * from test2.ta
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

結論：與第二種方案相比，用這種方式不需逐一把test2下的表授權給test1訪問，但test2新建的表無法被test1訪問。