本文用以回答留言板上的2111号问题.
在Oracle8i以前，所有已编译存储对象(包括packages, procedures, functions, triggers, and views)只能以定义者(Definer)身份解析运行；从Oracle8i开始，Oracle引入调用者(invoker)权限，使得对象可以以调用者身份和权限执行。
定义者(Definer)指编译存储对象的所有者.
调用者(Invoker)指拥有当前会话权限的模式，这可能和当前登录用户相同或不同(alter session set current_schema 可以改变调用者Schema).
TOM在他的《Expert One on One》的第23章曾经详细介绍这一特性，本文引用Tom的一个例子用于说明Definer and Invoker权限。
1.以Eygle用户(definer)创建2个过程
$ sqlplus eygle/eygle
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Dec 11 11:39:27 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> create or replace procedure definer_proc
2 as
3 begin
4 for x in
5 ( select sys_context( 'userenv', 'current_user' ) current_user,
6 sys_context( 'userenv', 'session_user' ) session_user,
7 sys_context( 'userenv', 'current_schema' ) current_schema
8 from dual )
9 loop
10 dbms_output.put_line( 'Current User: ' || x.current_user );
11 dbms_output.put_line( 'Session User: ' || x.session_user );
12 dbms_output.put_line( 'Current Schema: ' || x.current_schema );
13 end loop;
14 end;
15 /
Procedure created.
SQL>
SQL> grant execute on definer_proc to test;
Grant succeeded.
SQL>
SQL> create or replace procedure invoker_proc
2 AUTHID CURRENT_USER
3 as
4 begin
5 for x in
6 ( select sys_context( 'userenv', 'current_user' ) current_user,
7 sys_context( 'userenv', 'session_user' ) session_user,
8 sys_context( 'userenv', 'current_schema' ) current_schema
9 from dual )
10 loop
11 dbms_output.put_line( 'Current User: ' || x.current_user );
12 dbms_output.put_line( 'Session User: ' || x.session_user );
13 dbms_output.put_line( 'Current Schema: ' || x.current_schema );
14 end loop;
15 end;
16 /
Procedure created.
SQL>
SQL> grant execute on invoker_proc to test;
Grant succeeded.
|
注意invoker权限的本质是引入了AUTHID CURRENT_USER子句，通过此句Oracle得以使用invoker身份编译执行对象。
2.以test用户(invoker)身份执行
SQL> connect test/test
Connected.
SQL>
SQL> set serveroutput on
SQL> exec eygle.definer_proc
Current User: EYGLE
Session User: TEST
Current Schema: EYGLE
PL/SQL procedure successfully completed.
SQL> exec eygle.invoker_proc
Current User: TEST
Session User: TEST
Current Schema: TEST
PL/SQL procedure successfully completed.
|
注意只有使用invoker者权限执行时，Schema才转换为TEST.
