create view receive "ORA-01031: insufficient privileges"

    今天無意遇到一個問題，之前沒仔細研究過，看來裡面的門道還是蠻值得研究一下的：
     使用者A， 使用者B， 使用者A有select * from b.tables 的許可權，但是當使用者A執行：
     create view view_a as select * from b.tables的時候，收到一個錯誤資訊：ORA-01031: insufficient privileges
     使用者A已經有dba許可權了，而且又可以單獨執行select * from b.tables ，為什麼建立試圖的時候，會報無效許可權呢？
     原因為：在建立create view 和 create procedure的時候，是不可以利用相應的role隱式授權的，必須顯式的授予這個物件相應的許可權。metalink解釋如下：
    reason：Under SQL, if a user can select another user's table and has the privilege to create a view, then the create view  works. Yet, a create view on the other user's table generates ORA-01031 if the select privilege has been granted to a role and not directly.

試驗結果如下：
SQL> conn sys/oracle@devdb1 as sysdba
Connected as SYS
--1.建立使用者a，授權connect, resource
SQL> create user a identified by a ;
User created
SQL> grant connect, resource, create role to a;
Grant succeeded
--2.建立使用者b，授權connect, resource，dba
SQL> create user b identified by b ;
User created
SQL> grant connect , resource , dba to b;
Grant succeeded
--3.使用者a建立表table_a
SQL> conn a/a@devdb1
Connected as a
SQL> create table table_a as select * from dual;
Table created
--4.使用者b可以查詢a下的物件
SQL> conn b/b@devdb1
Connected as b
SQL> select * from a.table_a;
DUMMY
-----
X
--5.使用者b建立試圖，選擇使用者a下的表
SQL> create view view_b as select * from a.table_a;
create view view_b as select * from a.table_a
ORA-01031: insufficient privileges
--6.嘗試在使用者a下建立role，賦予select on table to b
SQL> conn a/a@devdb1
Connected as a
SQL> create role role_a_select;
Role created
SQL> grant select on table_a to role_a_select;
Grant succeeded
SQL> grant role_a_select to b;
Grant succeeded
--7.使用者b仍然無法建立訪問a使用者下物件的試圖
SQL> conn b/b@devdb1
Connected as b
SQL> create view view_b as select * from a.table_a;
create view view_b as select * from a.table_a
ORA-01031: insufficient privileges
--8.直接，顯式的授予select on table to b
SQL> conn a/a@devdb1
Connected as a
SQL> grant select on table_a to b;
Grant succeeded
SQL> conn b/b@devdb1
Connected as b
SQL> create view view_b as select * from a.table_a;
View created
--同理，procedure也會有這樣的限制，透過role隱式的授權是無效的。
SQL> conn b/b@devdb1
Connected as b
SQL> select * from a.table_a;
DUMMY
-----
X
SQL> create or replace procedure procedure_b
  2  as
  3  num number;
  4  begin
  5  select count(*) into num from a.table_a;
  6  end;
  7  /
Warning: Procedure created with compilation errors
SQL> show errors;
Errors for PROCEDURE B.PROCEDURE_B:
LINE/COL ERROR
-------- -----------------------------------------------
5/33     PL/SQL: ORA-00942: table or view does not exist
5/1      PL/SQL: SQL Statement ignored
SQL> conn sys/oracle@devdb1 as sysdba
Connected as SYS
SQL> grant select any table to b;
Grant succeeded
SQL> conn b/b@devdb1
Connected as b
SQL> create or replace procedure procedure_b
  2  as
  3  num number;
  4  begin
  5  select count(*) into num from a.table_a;
  6  end;
  7  /
Procedure created
--note:即使成功編譯了procedure，但是當沒有顯示的授予create table的許可權，那麼在exec procedure的時候也會出現問題。
SQL> create or replace procedure b2 as
  3  begin
  4     execute immediate 'Create table tb as select * from dual;';
  5  end;
  6  /
Procedure created
SQL> exec b2
begin b2; end;
ORA-01031: insufficient privileges
ORA-06512: at "B.B2", line 4
ORA-06512: at line 2
SQL> grant create table to b;
Grant succeeded
SQL> exec b2;
PL/SQL procedure successfully completed

Explanation:
Roles that are granted to one user cannot be applied to another by intermediate objects
such as views or pl/sql procedures. Roles exist in sessions and are associated with a user
in an active session only, the privileges of a role cannot be transferred to objects.
If you're accessing tables/views in PL/SQL procedure or package and getting
either ORA-1031 or ORA-942 (or PLS-201), but the same select/update/insert/delete
works ok in SQL only, then you need to check if the privileges have been granted
to the user creating the procedure via a role. Privileges granted via role do not work
inside stored procedures

Subject:	Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in PL/SQL, works in SQL*Plus
	:	168168.1	Type:	BULLETIN
	Modified Date :	06-FEB-2009	Status:	PUBLISHED