ORA-01031: insufficient privileges while inserting through procedure

Question

I have two Oracle DB schemas SCHEMA1 and SCHEMA2 in same instance.

Objects in SCHEMA1 : 1. A procedure PROC_ABC.

Objects in SCHEMA2 : 1. A table TABLE_DEF. 2. A Trigger TRIG_DEF.

Grants : 1. Grant insert any table to SCHEMA1 by SYSTEM user. 2. Grant insert on TABLE_DEF to SCHEMA1 by SCHEMA2 user. 3. Grant execute on PROC_ABC to SCHEMA2 by SCHEMA1 user.

Problem : When I am firing the command 'insert into SCHEMA2.TABLE_DEF..' from SCHEMA1 it is working properly. But when the same insert command is being fired from inside the body of procedure PROC_ABC it is throwing an error. Kindly note that the procedure PROC_ABC of SCHEMA1 is being called by Trigger TRIG_DEF of Schema2.

Error : ORA-01031: insufficient privileges

Answer 1

In Oracle you can choose whether a procedure is runnning under definer rights or user rights. Under definer rights the procedure executes with the privileges of the owner of the schema (this is the default).

CREATE OR REPLACE PRODECURE schema.procedure_name () AUTHID CURRENT_USER AS ...

or

CREATE OR REPLACE PRODECURE schema.procedure_name () AUTHID DEFINER AS ...

If PROC_ABC has been declared with AUTHID CURRENT_USER, then it will run with the rights of whoever is calling it. Specify AUTHID DEFINER to make it run with SCHEMA1 privileges.

Note also that triggers execute as the definer of the trigger.