Definer and Invoker Rights for stored routines in Oracle

by Amar Kumar Padhi

Definer rights

A
routine stored in the database by default, is executed with the definer rights
(owner of the routine), depending on the user who calls it. This is a good way
of having the required code perform process logic in one place. It gives better
control, preventing direct access to objects that belong to another user, which
might result in security issues.

For
example, table APPPARMST belongs to schema A. User A creates a procedure
UPDATE_PAR allowing for updates of a table. User B is granted execute privileges
on the procedure. Now user B cannot access the table as no privileges have been
granted, but can call the procedure to do the required process logic for
updating the table.

Invoker Rights

Invoker
rights is a new model for resolving references to database elements in a PL/SQL
program unit. From Oracle 8i onwards, we can decide if a program unit should
run with the authority of the definer or of the invoker. This means that multiple
schemas, accessing only those elements belonging to the invoker, can share the
same piece of code.

For
example, let’s take the above case. The table, APPPARMST, is created in schema
B also. Each of the schema will now own the same set of objects but different
data, as they are being used for different purposes. Since the called procedure,
UPDATE_PAR, is owned by User A, the ideal solution in Oracle 8 and earlier
releases, was to compile it in schema B also, so that it will use the objects
thereof.

With Oracle 8i, there is no need for this duplication
of code. A single compiled program unit can be made to use schema A’s objects
when invoked by User A and schema B’s objects when invoked by User B. This way,
we have the option of creating a code repository in one place and sharing it
with various production users. The owner of the routine must grant EXECUTE
privilege to other users.

To
enable code to run with Invoker rights, an AUTHID clause needs to be used
before the IS or AS keyword in the routine header. The AUTHID clause tells
Oracle whether the routine is to be run with the invoker rights (CURRENT_USER),
or with the Owner rights (DEFINER). If you do not specify this clause, Oracle
by default assumes it to be AUTHID DEFINER.

E.g.

create or replace procedure update_par(pi_parcod  in     varchar2,
pi_val in varchar2,
pio_status in out varchar2)
authid current_user is
begin
pio_status = ‘OK’;

update appparmst
set parval = pi_val
where parcod = pi_parcod
and rownum = 1;

if sql%notfound then
pio_status = ‘Error in resetting the parameter’;
end if;
end;

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles