oracle过程中的authid definer/current_user选项使用

Oracle 10g 中创建函数,存储过程,程序包都存在这个invoker_rights_clause选项

authid definer/current_user

以下为文档中的解释

create procedure

invoker_rights_clause

The
invoker_rights_clause
lets you specify whether the procedure executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of
CURRENT_USER
.

This clause also determines how the database resolves external names in queries, DML operations, and dynamic SQL statements in the procedure.

AUTHID CURRENT_USER

Specify
CURRENT_USER
to indicate that the procedure executes with the privileges of
CURRENT_USER
. This clause creates an invoker-rights procedure.

This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of
CURRENT_USER
. External names in all other statements resolve in the schema in which the procedure resides.

AUTHID DEFINER

Specify
DEFINER
to indicate that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. This is the default and creates a definer-rights procedure.

create function

invoker_rights_clause

The
invoker_rights_clause
lets you specify whether the function executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of
CURRENT_USER
.

This clause also determines how Oracle Database resolves external names in queries, DML operations, and dynamic SQL statements in the function.

AUTHID Clause

Specify
CURRENT_USER
if you want the function to execute with the privileges of
CURRENT_USER
. This clause creates an invoker-rights function.

This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of
CURRENT_USER
. External names in all other statements resolve in the schema in which the function resides.

Specify
DEFINER
if you want the function to execute with the privileges of the owner of the schema in which the function resides, and that external names resolve in the schema where the function resides. This is the default and creates a definer-rights function.

create package

invoker_rights_clause

The
invoker_rights_clause
lets you specify whether the functions and procedures in the package execute with the privileges and in the schema of the user who owns the package or with the privileges and in the schema of
CURRENT_USER
. This specification applies to the corresponding package body as well.

This clause also determines how Oracle Database resolves external names in queries, DML operations, and dynamic SQL statements in the package.

AUTHID CURRENT_USER

Specify
CURRENT_USER
to indicate that the package executes with the privileges of
CURRENT_USER
. This clause creates an invoker-rights package.

This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of
CURRENT_USER
. External names in all other statements resolve in the schema in which the package resides.

实验如下

一： 以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
二：
指定authid definer只要定义子程序的用户权限能够满足子程序调用所需要的权限.那么调用这个子程序的用户
只需要授予execute any procedure/execute on <schema.>object,就可以调用该子程序.
connect system/sky123@sky;---------以system用户登陆
create user usera identified by usera default tablespace test quota unlimited on test;
create user userb identified by userb default tablespace test quota unlimited on test; ----创建两个测试用户
grant create session to usera,userb ;----------授予创建会话的权限
grant create table , create procedure to userb;------授予userb用户创建表盒创建存储过程的权限
connect userb/userb@sky;--------以userb用户连接数据库
create table test
( no number constriant test_pk primary key ,
name varchar2(10),
grade number(4,2)
);---------------------创建测试表
insert into test
values(200802129001,'liukai',87.22);
insert into test
values(200802129002,'guoyue',99.22);
commit; ---------------插入测试数据
create or replace procedure example
authid definer
as
begin
update test
set grade=99.99
where no=20080219001;
end;
/---------------------创建测试存储过程
connect system/sky123@sky;
grant execute on userb.example to usera; --------对usera授予execute权限
connect usera/usera@sky;------usera连接数据库
begin
userb.example;
end;
/
存储过程执行完毕;-----------------usera并没有userb.test表中的update权限.因为指定authid definer 使用定义者的权限去执行存储过程.
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
create or replace procedure example1
authid current_user
as
begin
update userb.test -------------------此时这里需要指定修改test表的模式名称.应为修改的是userb的对象.
set grade=97.22
where no=200802129002;
end;
/
conenct system/sky123@sky;
grant execute on userb.example1 to usera;
grant update on userb.test to usera; -----此时usera就有execute 权限和修改test表的update权限,才能够执行该存储过程,因为此时使用调用者的权限去执行存储过程
connect usera/usera@sky
begin
userb.example1;
end;
/
过程执行成功
三:指定authid current_user 可以在子程序中使用role.
在子程序中role中的权限是不能被使用的.
create user usera identified by usera default tablespace test quota unlimited on test;
create role test_role to usera;
grant create session to usera;
grant create procedure to usera;
grant create table to temp_role; ---------将create table 权限授予在role中
grant temp_role to usera;----------将role 授予usera用户.
connect usra/usera@sky;
create or replace example
authid definer
as
begin
execute immediate 'create table ttt(col_1 number)tablespace test';
end;
/
execute example;--------------权限不足,因为create table 在role中
create or replace procedure example
authid current_user
as
begin
execute immediate 'create table ttt(col_1 number) tablespace test';
end;
/
execute example；---成功执行.