在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.
SQL> alter session set current_schema = system; Session altered. 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: SYSTEM PL/SQL procedure successfully completed. SQL> |
通过alter session set current_schema方式修改当前模式之后,我们看到仍然是仅当使用invoker权限执行时,Schmea方切换为SYSTEM.
阅读(721) | 评论(0) | 转发(0) |