authid current_user vs pragma authid Definer

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

http://www.adp-gmbh.ch/ora/plsql/authid.html (1 of 5)4/28/2010 2:28:25 PM

authid current_user vs pragma authid Definer

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.
http://www.adp-gmbh.ch/ora/plsql/authid.html (2 of 5)4/28/2010 2:28:25 PM
authid current_user vs pragma authid Definer

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
http://www.adp-gmbh.ch/ora/plsql/authid.html (3 of 5)4/28/2010 2:28:25 PM
authid current_user vs pragma authid Definer

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
http://www.adp-gmbh.ch/ora/plsql/authid.html (4 of 5)4/28/2010 2:28:25 PM
authid current_user vs pragma authid Definer

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.

http://www.adp-gmbh.ch/ora/plsql/authid.html (5 of 5)4/28/2010 2:28:25 PM