René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

authid current_user vs pragma authid Definer

drop function  f_authid_cu;
drop function  f_authid_d;
drop procedure p_authid_cu;
drop procedure p_authid_d;
drop package   c_authid_cu;
drop package   c_authid_d;
drop type      t_audhid_cu;
drop type      t_audhid_d;

create function f_authid_cu return number
  authid current_user
as begin
  return 0;
end f_authid_cu;
/

create function f_authid_d return number
  authid definer
as begin
  return 0;
end f_authid_d;
/

create procedure p_authid_cu
  authid current_user
as begin
  null;
end p_authid_cu;
/

create procedure p_authid_d
  authid definer
as begin
  null;
end p_authid_d;
/

create package c_authid_cu
  authid current_user
as
end c_authid_cu;
/

create package c_authid_d
  authid definer
as
end c_authid_d;
/

create type t_audhid_cu 
  authid current_user
as object (
  dummy number
);
/

create type t_audhid_d
  authid definer
as object (
  dummy number
);
/
A stored procedure runs either with the rights of the caller (authid current_user) or with the rights of the procedure's owner (authid definer). This behaviour is specified with the authid clause. This authid clause immediatly follows the create procedure, create function, create package or create type statement. It can be ommited, in which case the default authid definer is taken.
The authid clause has implications, for example, on how objects are resolved within a procedure. This is demonstrated in the following.
First, two users are created, one named a and one named b. Some needed privileges are granted to these users as well. 
create user a identified by a default tablespace data quota unlimited on data;
create user b identified by b default tablespace data quota unlimited on data;

grant create session, create procedure, create table to a;
grant create session, create procedure, create table to b;
Now, user a creates tow tables named t and u and fills them with this is a's table. User u is allowed to read t. 
connect a/a;

create table t (d varchar2(40));
insert into  t values ('this is a''s table');

commit;

create table u (d varchar2(40));
insert into  u values ('this is a''s table');

grant select on t to b;
-- no grant on u
User a also creates a procedure (named pt) with authid current_user that just reads what was filled into table t: 
create procedure pt
 authid current_user
as
  v_d t.d%type;
begin
  select d into v_d from t; 
  dbms_output.put_line(v_d);
end;
/
User a then creates a procedure (pu) that reads from table u: 
create procedure pu
 authid definer
as
  v_d t.d%type;
begin
  select d into v_d from u; 
  dbms_output.put_line(v_d);
end;
/
User b should be allowed to execute both procedures: 
grant execute on pt to b;
grant execute on pu to b;
Now, it's user b's turn to do something. He, as well, creates two tables t and u and fills them with this is b's table: 
connect b/b;

create table t (d varchar2(40));
insert into  t values ('this is b''s table');

create table u (d varchar2(40));
insert into  u values ('this is b''s table');

commit;
Now, he executes user a's procedure pt: 
set serveroutput on
begin a.pt; end;
/
The output is: 
this is b's table
Then, he executes a.pu: 
begin a.pu; end;
/
This time, the output is: 
this is a's table
This demonstrates that a procedure is run in the context of the caller if has authid current_user and in the context of the owner if the procedure has authid definer

Effects on roles

A procedure with authid definer disables all roles that are granted to the caller. This can lead to ora-00942 errors.
If the procedure is created with authid current_user, the roles of the caller are enabled when the procedure is executed. However, in order to compile the procedures, the privileges still need to be granted directly.