一个关于Definer和Invoker的权限问题

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

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》中单独有一章节，介绍的就是，调用者和定义者，

定义者(Definer)-指的是编译存储对象的拥有者，包括包、存储过程、函数、触发器和视图。

调用者(Invoker)-指当前会话中生效的schema，不一定就是当前登录的用户。

Oracle 8i之前，所有编译存储对象的执行，都是以定义者权限为准，因此编译阶段就会发现错误，不会像上面，等待运行阶段才报错。

从Oracle 8i开始，引入了invoker rights-调用者，允许包、存储过程、函数、触发器和视图这些对象的权限，以运行时的调用者为准。

引用Tom的实验，首先user1用户，创建如下两个存储过程，分为定义者权限，和调用者权限，并将这两个存储过程，执行权限授予user2，

create or replace procedure definer_proc
as
begin
for x in
( select sys_context('userenv', 'current_user') current_user,

sys_context('userenv', 'session_user') session_user,
sys_context('userenv', 'current_schema') current_schema
from dual )
loop
  dbms_output.put_line('Current User: ' || x.current_user );
  dbms_output.put_line('Session User: ' || x.session_user );
  dbms_output.put_line('Current Schema: ' || x.current_schema );
end loop;
end;
/

Procedure created.

create or replace procedure invoker_proc

AUTHID CURRENT_USER
as
begin
for x in
( select sys_context('userenv', 'current_user') current_user,

sys_context('userenv', 'session_user') session_user,
sys_context('userenv', 'current_schema') current_schema
from dual )
loop
  dbms_output.put_line('Current User: ' || x.current_user );
  dbms_output.put_line('Session User: ' || x.session_user );
  dbms_output.put_line('Current Schema: ' || x.current_schema );
end loop;
end;
/

SQL> grant execute on definer_proc to user2;
Grant succeeded.

SQL> grant execute on invoker_proc to user2;
Grant succeeded.

接着使用user2，分别执行，

SQL> exec user1.definer_proc;
Current User: USER1
Session User: USER2
Current Schema: USER1
PL/SQL procedure successfully completed.

SQL> exec user1.invoker_proc
Current User: USER2
Session User: USER2
Current Schema: USER2
PL/SQL procedure successfully completed.

可以看出，使用定义者权限，Current User和Current Schema均为user1，因为存储过程属于user1，但调用者权限，由于调用者是user2，因此Current User和Current Schema均为user2。

尝试设置current_schema，动态改变用户所用的schema，可以看出，第一个存储过程为定义者，没有任何变化，第二个存储过程为调用者，Current User是user2，Current Schema则变为了之前设置的system，说明定义者权限，是相对静态的，而调用者权限，则相对动态，

SQL> alter session set current_schema=system;
Session altered.

SQL> exec user1.definer_proc;
Current User: USER1
Session User: USER2
Current Schema: USER1
PL/SQL procedure successfully completed.

SQL> exec user1.invoker_proc;
Current User: USER2
Session User: USER2
Current Schema: SYSTEM
PL/SQL procedure successfully completed.

对于存储过程，dba_procedures中AUTHID字段，表示当前的存储过程/函数，定义为“定义者”还是“调用者”，

11.2.0.4下，记录分布如下，

本文开始的问题，CREATE TABLE语句的存储过程，从现象来看，是定义者的权限，即使使用sys创建和执行，参考的是user1是否有相应权限，未参考sys用户自己的权限。

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

其实第一个问题解决了，第二个问题，就容易理解了。

我们先模拟下实验过程，用sys为用户user1和user2创建序列，

SQL> create sequence user1.SEQ_TEST;
Sequence created.

SQL> create sequence user2.SEQ_TEST;
Sequence created.

sys用户执行dbms_metadata.get_ddl，一切ok，

SQL> set serveroutput on
SQL> select dbms_metadata.get_ddl('SEQUENCE','SEQ_TEST','USER2') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','SEQ_TEST','USER2')
--------------------------------------------------------------------------------
CREATE SEQUENCE  "USER2"."SEQ_TEST"  MINVALUE 1 MAXVALUE 9999999999999999999

SQL> select dbms_metadata.get_ddl('SEQUENCE','SEQ_TEST','USER1') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','SEQ_TEST','USER1')
--------------------------------------------------------------------------------
CREATE SEQUENCE  "USER1"."SEQ_TEST"  MINVALUE 1 MAXVALUE 9999999999999999999

用户user1检索user1自己的SEQ_TEST序列，可以正常操作，

SQL> select dbms_metadata.get_ddl('SQUENCE','SEQ_TEST','USER1') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','SEQ_TEST','USER1')
--------------------------------------------------------------------------------
CREATE SEQUENCE  "USER1"."SEQ_TEST"  MINVALUE 1 MAXVALUE 9999999999999999999

用户user1检索user2所属的SEQ_TEST序列， 报错USER2中找不着这个SEQ_TEST序列对象，

SQL> select dbms_metadata.get_ddl('SQUENCE','SEQ_TEST','USER2') from dual;
ERROR:
ORA-31603: object "SEQ_TEST" of type SEQUENCE not found in schema "USER2"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1
no rows selected

对于某一个具体的对象，可以检索AUTHID字段，判断其为定义者，还是调用者权限，可以看出DBMS_METADATA.GET_DDL()就是调用者权限，

因此执行的时候，参考的是执行用户的权限，sys用户有检索user1和user2对象定义的权限，user1有检索自己对象的权限，但没有检索其他用户对象的权限。

这篇文章DBMS_METADATA.GET_DDL Returns Error When Select Types Ora-31603 (文档 ID 312883.1)，针对这种问题，指出了原因所在，

The appropriate privileges have not been granted to the schema executing the procedure as the same procedure works fine from the schema that owns the object and from SYS.

给出了两种解决方案，

Step 1: Execute "GRANT SELECT_CATALOG_ROLE TO <schema>;" as SYS or another user with the privilege.
Step 2. Modify the procedure to include "AUTHID CURRENT_USER"

方法1：授予GRANT SELECT_CATALOG_ROLE角色

sys用户执行，

SQL> GRANT SELECT_CATALOG_ROLE TO user1;
Grant succeeded.

user1此时可以检索user2对象定义，

SQL> select dbms_metadata.get_ddl('SEQUENCE','SEQ_TEST','USER2') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','SEQ_TEST','USER2')
--------------------------------------------------------------------------------
CREATE SEQUENCE  "USER2"."SEQ_TEST"  MINVALUE 1 MAXVALUE 9999999999999999999

但这种方法不很推荐，因为SELECT_CATALOG_ROLE角色，允许用户访问所有数据字典，一般用户不应该有角色，权限级别较高。

方法2：使用“AUTHID CURRENT_USER”定义存储过程

如果将“dbms_metadata.get_ddl”封装于存储过程，可以参考杨长老这篇http://m.blog.itpub.net/4227/viewspace-69047，文章中就用了这种定义，

CREATE OR REPLACE PROCEDURE P_TEST AUTHID CURRENT_USER 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;
/

提问的兄弟很认真，回复我如下，这种追求问题答案的态度，值得我们学习，

我又折腾了两三个小时，写了个程序把SELECT_CATALOG_ROLE角色对应的2238个表或视图、过程的授权以及被包含在这个角色中的另一个角色HS_ADMIN_SELECT_ROLE都试过了，程序内用sys每授权一个对象后都会用user1重新登录并执行dbms_metadata.get_ddl处理，但实验结果没有像预期那样能知道到底是哪个对象授权有影响，所有对象都授权了仍然无法正常获取seq的create ddl。
最后还是授权角色SELECT_CATALOG_ROLE才管用。
我不打算再试了，感觉oracle可能还会有其他很隐蔽的内部逻辑。

我们使用fyunwrap(之前这篇文章《dbms_space.create_table_cost的unwrap解密和原理解析》介绍过)，来看看这个dbms_metadata.get_ddl。

unwrap这个dbms_metadata包，可以看出，get_ddl是一个函数，调用了do_get函数，

FUNCTION GET_DDL (

OBJECT_TYPE IN  VARCHAR2,

NAME IN  VARCHAR2,

SCHEMA IN  VARCHAR2 DEFAULT NULL,

VERSION IN  VARCHAR2 DEFAULT 'COMPATIBLE',

MODEL IN  VARCHAR2 DEFAULT 'ORACLE',

TRANSFORM IN  VARCHAR2 DEFAULT 'DDL')

RETURN CLOB IS

BEGIN

DO_GET(OBJECT_TYPE,NAME,SCHEMA,VERSION,MODEL,TRANSFORM,1,'GET_DDL');

RETURN DBMS_ASSERT.NOOP(GET$_DOC);

END;

从do_get函数定义，可以看出，这是通用函数，其中参数PUBLIC_FUNC，接收的是'GET_DDL'参数，

PROCEDURE DO_GET (

OBJECT_TYPE IN  VARCHAR2,

NAME IN  VARCHAR2,

SCHEMA IN  VARCHAR2,

VERSION IN  VARCHAR2,

MODEL IN  VARCHAR2,

TRANSFORM IN  VARCHAR2,

OBJECT_COUNT IN  NUMBER,

PUBLIC_FUNC IN  VARCHAR2,

NETWORK_LINK    IN  VARCHAR2 DEFAULT NULL)

IS

HANDLE NUMBER;

SAVE_HANDLE NUMBER := CUR_HANDLE;

IND NUMBER;

TR_HANDLE NUMBER;

SCHEMA_NAME VARCHAR2(30);

OBJECT_NAME VARCHAR2(2000) := ' ';

DUMMY_PARSE_ITEMS   SYS.KU$_PARSED_ITEMS := SYS.KU$_PARSED_ITEMS();

DUMMY BOOLEAN;

DUMMY_PROCOBJ_ERRORS SYS.KU$_VCNT;

BEGIN

IF GET$_DOC IS NOT NULL THEN

DBMS_LOB.FREETEMPORARY(GET$_DOC);

END IF;

DBMS_LOB.CREATETEMPORARY(GET$_DOC,TRUE);

HANDLE := DO_OPEN(OBJECT_TYPE, VERSION, MODEL, PUBLIC_FUNC, NETWORK_LINK);

CUR_HANDLE := HANDLE;

IF PUBLIC_FUNC = 'GET_XML'

OR PUBLIC_FUNC = 'GET_SXML'

OR PUBLIC_FUNC = 'GET_SXML_DDL'

OR PUBLIC_FUNC = 'GET_DDL'

THEN

OBJECT_NAME := NAME;

IF LENGTH(NAME) > 30

THEN

SET_FILTER(HANDLE,'LONGNAME',DBMS_ASSERT.NOOP(NAME));

ELSE

SET_FILTER(HANDLE,'NAME',DBMS_ASSERT.NOOP(NAME));

END IF;

IF SCHEMA IS NOT NULL

THEN

SET_FILTER(HANDLE,'SCHEMA',DBMS_ASSERT.NOOP(SCHEMA));

END IF;

ELSIF PUBLIC_FUNC = 'GET_DEPENDENT_XML'

OR    PUBLIC_FUNC = 'GET_DEPENDENT_SXML'

OR    PUBLIC_FUNC = 'GET_DEPENDENT_DDL'

THEN

SET_FILTER(HANDLE,'BASE_OBJECT_NAME',DBMS_ASSERT.NOOP(NAME));

IF SCHEMA IS NOT NULL

THEN

SET_FILTER(HANDLE,'BASE_OBJECT_SCHEMA',DBMS_ASSERT.NOOP(SCHEMA));

END IF;

ELSIF PUBLIC_FUNC = 'GET_GRANTED_XML'

OR    PUBLIC_FUNC = 'GET_GRANTED_DDL'

THEN

IF NAME IS NOT NULL

THEN

SET_FILTER(HANDLE,'GRANTEE',DBMS_ASSERT.NOOP(NAME));

ELSE

SCHEMA_NAME := GET_CURRENT_USER;

SET_FILTER(HANDLE,'GRANTEE',DBMS_ASSERT.NOOP(SCHEMA_NAME));

END IF;

END IF;

SET_COUNT(HANDLE,OBJECT_COUNT,OBJECT_TYPE);

IF TRANSFORM IS NULL THEN

SET_XMLFORMAT(HANDLE,'PRETTY',TRUE);

ELSIF PUBLIC_FUNC = 'GET_SXML_DDL'

AND   TRANSFORM = 'SXMLDDL'

THEN

TR_HANDLE := DBMS_METADATA.ADD_TRANSFORM(HANDLE,'SXML');

TR_HANDLE := DBMS_METADATA.ADD_TRANSFORM(HANDLE,'SXMLDDL');

ELSE

TR_HANDLE := DBMS_METADATA.ADD_TRANSFORM(HANDLE,TRANSFORM);

IF MODEL = 'ORACLE' AND TRANSFORM = 'DDL'

THEN

DBMS_METADATA.SET_TRANSFORM_PARAM(TR_HANDLE,'INHERIT',TRUE);

END IF;

END IF;

IND := GET_CONTEXT_ENTRY(HANDLE,PUBLIC_FUNC,TRUE);

DUMMY := DO_FETCH(IND,GET$_DOC,DUMMY_PARSE_ITEMS,0,DUMMY_PROCOBJ_ERRORS);

DBMS_METADATA.CLOSE(HANDLE);

IF GET$_DOC IS NULL

THEN

IF PUBLIC_FUNC = 'GET_XML'

OR PUBLIC_FUNC = 'GET_SXML'

OR PUBLIC_FUNC = 'GET_SXML_DDL'

OR PUBLIC_FUNC = 'GET_DDL'

THEN

IF SCHEMA IS NOT NULL

THEN

SCHEMA_NAME := SCHEMA;

ELSE

SCHEMA_NAME := GET_CURRENT_USER;

END IF;

CUR_HANDLE := SAVE_HANDLE;

DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(OBJECT_NOT_FOUND_NUM,

OBJECT_NAME, OBJECT_TYPE, SCHEMA_NAME);

ELSE

CUR_HANDLE := SAVE_HANDLE;

DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(OBJECT_NOT_FOUND2_NUM, OBJECT_TYPE);

END IF;

END IF;

CUR_HANDLE := SAVE_HANDLE;

EXCEPTION WHEN OTHERS THEN

BEGIN

CUR_HANDLE := SAVE_HANDLE;

RAISE;

END;

END;

其中调用了do_fetch、do_fetch_local、do_fetch_direct等一系列函数，看的有些晕了，但可以说明一点，绝不是仅授权SELECT_CATALOG_ROLE角色中包含的某一个视图就可以执行dbms_metadata包，视图之间是有关联关系的。

总结：

1. 对于Definer和Invoker的含义要理解，Definer权限比较静态，Invoker权限则相对动态，通过procedures视图的AUTHID字段，可以了解对象，属于定义者还是调用者权限。

2. 两种执行DBMS_METADATA.GET_DDL权限相关的workaround，一种是授予SELECT_CATALOG_ROLE角色，一种是使用"AUTHID CURRENT_USER"定义存储过程，针对不同场景，可以选择不同的方案解决。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)