A m Z o n e

Definer and Invoker Rights for stored routines in the database (AUTHID)

Definer rights
A routine stored in the database by default is executed with the definer's rights (owner of the routine), which ever user calls it. This is a good way of having the required code performing a process logic in one place, thus giving better control, and preventing direct access to objects belonging to a user resulting in security issues.

For example, table APPPARMST belongs to schema A. A procedure UPDATE_PAR is created by user A that allows update of the table. User B is granted execute privilege on the procedure. Now user B cannot access the table as now privileges are 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 way, the same piece of code is shared by multiple schemas, and will access elements belonging to the invoker only.

For example, lets take the above case. Now the table APPPARMST is created in schema B also. The reason being each of the schema will now own same set of objects but different data as they are being used for different purposes. Now since the called procedure UPDATE_PAR is owned by User A, the ideal solution in Oracle 8 or earlier releases would be to compile it in schema B also, so that it will use the objects thereof.

With Oracle 8i, this duplication of code can be removed. 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. So this way, we have the option of creating 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 a code to run with Invoker rights, AUTHID clause needs to be used before the IS or AS keyword in the routine header. 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; 

Restriction in using Invoker rights

1. When compiling a new routine, direct privileges are only considered to resolve any external references. Grants through roles are ignored. The same applies when executing a routine created with invoker rights.
2. AUTHID is specified in the header of a program unit. The same cannot be specified for individual programs or methods within a package or object type.
3. Definer rights will always be used to resolve any external references when compiling a new routine.
4. Maintain extra caution on privileges being assigned to different user. With wrong privileges being assigned, a routine with invoker rights may have a mind of its own! Such issues will be difficult to debug. So ensure that the grants are perfectly in place.
5. For invoker rights routine referred in a view or a database trigger, the owner of these objects is always considered as the invoker, and not the user triggering it.

E.g.: 

SQL> DOC The above Procedure is created in user A and user B refers it. 

SQL> conn a/a@oradata1 
Connected. 
SQL> grant execute on update_par to B; 

Grant succeeded. 

SQL>  declare 
  2    l_status varchar2(200); 
  3   begin 
  4    a.update_par('updated by', 'User: ' || user, l_status); 
  5    commit; 
  6   end; 
  7  / 

PL/SQL procedure successfully completed. 

SQL> select * from a.appparmst; 

PARCOD               PARVAL 
-------------------- ---------------------------------------------------- 
updated by           User: A 



SQL> conn b/b@oradata1 
Connected. 
SQL> declare 
  2   l_status varchar2(200); 
  3  begin 
  4   a.update_par('updated by', 'User: ' || user, l_status); 
  5   commit; 
  6  end; 
  7  / 
declare 
* 
ERROR at line 1: 
ORA-00942: table or view does not exist 
ORA-06512: at "A.UPDATE_PAR", line 6 
ORA-06512: at line 4 

SQL> DOC the error occured because table APPPARMST does not exist for user B. 
DOC> I create it for user B and then call update_par again 

SQL> CREATE TABLE APPPARMST 
  2  (PARCOD   VARCHAR2(20) NOT NULL, 
  3   PARVAL   VARCHAR2(200)); 

Table created. 

SQL> insert into appparmst values('updated by', null); 

1 row created. 

SQL> commit; 

Commit complete. 

SQL> declare 
  2   l_status varchar2(200); 
  3  begin 
  4   a.update_par('updated by', 'User: ' || user, l_status); 
  5   commit; 
  6  end; 
  7  / 

PL/SQL procedure successfully completed. 

SQL> select * from b.appparmst; 

PARCOD               PARVAL 
-------------------- --------------------------------------- 
updated by           User: B 

SQL> DOC example over. 

Courtesy
Oracle 8.1.7 Manuals
Guide to Oracle8i features by Steven Feuerstein.
RDB Journal by Ian smith (otn.oracle.com/products/rdb/pdf/ stored_procedure_security.pdf)
Advanced techniques by Steven, Richard bolz, Bill Pribyl (http://www.plsolutions.com)