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
	Doc ID:	168168.1	Type:	BULLETIN
	Modified Date :	06-FEB-2009	Status:	PUBLISHED

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9252210/viewspace-591741/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9252210/viewspace-591741/