bisal的个人空间

厚积薄发,欢迎关注个人微信公众号:bisal的个人杂货铺

  • 博客访问: 2385776
  • 博文数量: 324
  • 用 户 组: 普通用户
  • 注册时间: 2013-07-26 09:29
个人简介

10g/11g OCP,11g OCM,YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),我不是DBA,但我的爱好是Oracle,微信公众号:bisal的个人杂货铺

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(324)

文章存档

2017年(66)

2016年(44)

2015年(41)

2014年(61)

2013年(112)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

前两天有位朋友,微信公众号提了一个问题,原文描述如下,


1. 我的需求是在tag库中执行一个处理,使得tag中所有用户seq的nextval与src库中一致。

2. 我在tag库的user1中创建了一个存储过程,代码逻辑为通过dblink(指向src库的user1,user1有读取dba视图的权限)查询源库的 dba_sequence与tag库的对比,找出两库间nextval相差1000以上的,并在tag中获取create seq的语句,然后用src库中的nextval值替换,并在src库中按src库的nextval重建seq。

3. 问题出在,我没有sys用户或者dba权限,使用的是一个user1用户,过程建在user1中,但程序需要处理所有用户的seq,我写的过程是给dba 用的,他能用sys执行。在用sys执行过程时,执行到 dbms_metadata.get_ddl('SEQUENCE','SEQ1','USER2')时,会报错用户USER2中没有这个序列号。 但如果不通过user1的这个存储过程,而是直接在sys用户中执行语句dbms_metadata.get_ddl。。。就可以正常获取create语 句,我不明白,执行者是sys,执行的是user1的过程,权限要按照user1的吗?但我尝试给user1授权其他用户序列号的使用权限也不行。


刚又进行了个实验,
在user1中
create or replace procedure user1.p_seq_test as
  LV_SQL VARCHAR2(1024);
begin                               
  execute immediate 'create table user2.t_dataread_test1(col1 number)';
END;
/
在sys中
begin
  user1.p_seq_test;
end;
执行报错没有权限。但是我用sys进行grant create any table to user1后就可以了。

我之前以为,虽然procedure在user1下,但是我用sys执行,权限应该是按照sys的权限走,但实际实验看即使sys执行存储过程,权限也是按照存储过程的属主用户走的。

只不过是我前面说过的问题中,我始终没有找到能让USER1成功执行dbms_metadata.get_ddl('SEQUENCE','SEQ_TEST','USER2')所需要的权限,也就是user1能操作user2的sequence的权限。


按照理解,总结一下问题,

1. 用户user1定义的存储过程,即使用sys用户执行,需要参考user1权限?

2. 用户user1中创建一个序列,sys用户可以执行dbms_metadata.get_ddl('SEQUENCE','SEQ','USER1')得到序列创建语句,但user1用户看不了属于user2的序列定义?


问题1:用户user1定义的存储过程,即使用sys用户执行,需要参考user1权限?


我们先看问题1,创建测试用户user1和user2,

SQL> create user user1 identified by 123;
User created.

SQL> create user user2 identified by 123;
User created.


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


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


sys用户创建属于user1的存储过程,

SQL> create or replace procedure user1.p_seq_test as
       LV_SQL VARCHAR2(1024);
     begin                               
       execute immediate 'create table user2.t_dataread_test1(col1 number)';
     END;
     /
Procedure created.


sys用户执行这个存储过程, 提示权限错误,

SQL> begin
       user1.p_seq_test;
     end;
     /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "USER1.P_SEQ_TEST", line 4
ORA-06512: at line 2


授予user1用户create any table权限,

SQL> grant create any table to user1;
Grant succeeded.

SQL> begin
       user1.p_seq_test;
     end;
     /
PL/SQL procedure successfully completed.


从 现象来看,即使使用sys执行user1的存储过程,权限参考的是user1,不是sys,因此由于user1没有create any table的权限,报错ORA-01031: insufficient privileges,注意编译过程未报错,而是执行过程中报错了。


杨长老有篇文章,其实提及了类似的问题,http://m.blog.itpub.net/4227/viewspace-69047,

SQL> CREATE OR REPLACE PROCEDURE P_TEST AS
  BEGIN

 FOR I IN (SELECT DBMS_METADATA.GET_DDL('TABLE', 'DUAL', 'SYS') DEFINE FROM DUAL) LOOP
 DBMS_OUTPUT.PUT_LINE(SUBSTR(I.DEFINE, 1, 255));
END LOOP;
 END;
 /

       一直就认为是角色导致的问题,而没有继续深究。而这次仔细看了Tom对定义者权限和调用者权限存储过程的描述才真正彻底清楚了导致上述现象的原因。

       一个调用者权限的存储过程,如果在定义者权限存储过程中被调用,则它的行为表现将像一个定义者权限的过程。这时由于定义者权限过程中,CURRENT_SCHEMA和所拥有的权限都是固定的,调用者权限过程中所有可能发生变化的东西都被固定了下来。
       而如果直接调用或者通过调用者权限过程来调用,那么这个调用者权限过程的全部特性得以保留。而这就是上面碰到的那个问题的真正答案。


Tom的书《Expert one-on-one Oracle》中单独有一章节,介绍的就是,调用者和定义者,