How to use & When to use Invoker Rights, Definer Rights in Stored Procedures & SQL methods ?

Sub-programs by default i.e., without AUTHID clause are called "Definer Rights" sub-programs.

Sub-programs with AUTHID clause are called "Invoker Rights" sub-programs.

How to use? Let see-

Assume you have two Schemas - MySchema_1, MySchema_2.

Both the Schemas are having table called EMP. Now, create a standalone procedure in MySchema_1.

CREATE PROCEDURE emp_details (

             p_emp_no NUMBER

            ,p_emp_name VARCHAR2

            ,p_emp_email VARCHAR2) AS

BEGIN

UDPATE EMP 

             SET emp_email = p_emp_email

             WHERE emp_no = p_emp_no;

END;

The above written is a "Definer Rights" sub-program. 

Assume that user MySchema_1 has granted the EXECUTE privilege on this procedure to user MySchema_2.

This will execute with the privileges of their owner (MySchema_1), not their current user (MySchema_2). So, it will update the EMP table of MySchema_1.

One way is to fully qualify references to the objects, as in

INSERT INTO MySchema_1.EMP...

CREATE PROCEDURE emp_details (

             p_emp_no NUMBER

            ,p_emp_name VARCHAR2

            ,p_emp_email VARCHAR2) AUTHID CURRENT_USER AS

BEGIN

UDPATE EMP 

             SET emp_email = p_emp_email

             WHERE emp_no = p_emp_no;

END;

The above written is an "Invoker Rights" sub-program.

Such invoker-rights subprograms are not bound to a particular schema.

When to use? Let see-

Invoker-rights subprograms let you reuse code and centralize application logic.

They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base.

e.g.,

Consider a company that uses a definer-rights (DR) procedure to analyze sales. To provide local sales statistics, procedure analyze must access sales tables that reside at each regional site. So, the procedure must also reside at each regional site. This causes a maintenance problem.

To solve the problem, the company installs an invoker-rights (IR) version of procedure analyze at headquarters. Now, all regional sites can use the same procedure to query their own sales tables.

To restrict access to sensitive data, you can have an invoker-rights subprogram call a definer-rights subprogram. Suppose headquarters would like procedure analyze to calculate sales commissions and update a central payroll table.

That presents a problem because current users of analyze should not have direct access to the payroll table, which stores employee salaries and other sensitive data. The solution is to have procedure analyze call definer-rights procedure calc_comm, which in turn updates the payroll table.