Difference Between Invoker Rights and Definer Rights

Invoker Rights Vs Definer Rights

In Oracle database, the stored PL/SQL procedures executes by default with the owner's privileges. This means that these subprograms execute only for the schema where they exists or created.

Let me explain this concept clearly using a scenario:

Let's say there are two schema schema1 and schema2. Schema1 has procedure proc1 and table emp_tab and Schema2 has table emp_tab only.

The structure of the proc1 procedure in Schema1 is as follows:

Create or replace procedure proc1 (p_empid number, p_ename varchar2, p_salary number)
as
begin
insert into emp_tab (eid, emp_name,emp_sal)
values (p_empid,p_ename,p_salary);
commit;
end;
/

Now, User Schema1 granted EXECUTE permission to User Schema2 on this procedure. If user Schema2 wants to insert data into his emp_tab table using this procedure he cannot do because when user Schema2 executes the procedure, the procedure will be executed with the privileges granted to user Schema1.

Though the user Schema2 has the same table in his schema and does not have the permissions for table emp_tab in Schema1, still the procedure will insert the new values into Schema1 table instead of his schema table.

Before Oracle 8i release, there were following two ways out to resolve this problem :

First either to copy the procedure into Schema2 as well which leads to code replication and hampers the maintenance.

Secondly use the schema references to the objects used in the procedure (under schema1) like insert into schema2.emp_tab which leads to hamper the code portability. To resolve this issue you can pass the schema name as parameter to the procedure and associate in sql.

To overcome this problem, in Oracle 8i and higher releases the AUTHID CURRENT_USER clause included. This invoker rights enables the procedure to execute with the privileges of the current user.

The syntax of the procedure in Schema1 will be like this:


Create or replace procedure proc1
(p_empid number, p_ename varchar2, p_salary number) AUTHID CURRENT_USER
as
begin
insert into emp_tab (eid, emp_name,emp_sal)
values (p_empid,p_ename,p_salary);
commit;
end;

Now, if the user Schema2 executes the procedure which is residing under Schema1 user the procedure will update Schema2 emp_tab table only. If the table is not existing in Schema2 user it will throw an error.
By default, Oracle assumes AUTHID DEFINER (definer rights) if you don't use the clause.