重庆思庄Oracle、Redhat认证学习论坛
标题: Definer and Invoker Rights [打印本页]
作者: 郑全 时间: 2014-4-1 16:32
标题: Definer and Invoker Rights
在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.
欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |