在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个过程
[oracle@sztech ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 1 15:22:52 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>conn hr/hr Connected.
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 sztech1; 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 sztech1; Grant succeeded. 注意invoker权限的本质是引入了AUTHID CURRENT_USER子句,通过此句Oracle得以使用invoker身份编译执行对象。
2.以sztech1用户(invoker)身份执行
SQL> connect sztech1/sztech1 Connected. SQL> SQL> set serveroutput on SQL> exec hr.definer_proc Current User: HR Session User: SZTECH1 Current Schema: HR PL/SQL procedure successfully completed. SQL> exec hr.invoker_proc Current User: SZTECH1 Session User: SZTECH1 Current Schema: SZTECH1 PL/SQL procedure successfully completed. 注意只有使用invoker者权限执行时,Schema才转换为TEST.
SQL> alter session set current_schema = system; Session altered. SQL> exec hr.definer_proc Current User: HR Session User: SZTECH1 Current Schema: HR PL/SQL procedure successfully completed. SQL> exec hr.invoker_proc Current User: SZTECH1 Session User: SZTECH1 Current Schema: SYSTEM PL/SQL procedure successfully completed. SQL> 通过alter session set current_schema方式修改当前模式之后,我们看到仍然是仅当使用invoker权限执行时,Schmea方切换为SYSTEM. |