ITPub博客

首页 > Linux操作系统 > Linux操作系统 > create view receive "ORA-01031: insufficient privileges"

create view receive "ORA-01031: insufficient privileges"

原创 Linux操作系统 作者:zhanglei_itput 时间:2009-04-24 17:34:57 0 删除 编辑

    今天无意遇到一个问题,之前没仔细研究过,看来里面的门道还是蛮值得研究一下的:
     用户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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1113910