Definer’s and Invoker’s Right on Programs in Oracle

Definer’s and Invoker’s Right in Oracle to control the access to run procedures or programs.

Simple Way to understand
In Definer’s Right:
A user who calls to run this procedure only needs EXECUTE privilege, it is not required that such user has DELETE privilege on table SALES_ORDER.

IN Invoker’s Right:
A user who calls to run this procedure successfully must have Both EXECUTE privilege on procedure and DELETE privilege on table SALES_ORDER.

Definer’s Right
In Definer’s Right, procedure executes with the privileges of the owner.(means privileges are bound to access schema in which it created).
By Default all Procedure created with Definer’s Right.
Example:
User SALES create a procedure to modify the table SALES_ORDER present in SALES Schema. Then SALES user grant execute privileges to User HR.
If procedure created with Definer’s Right then USER HR execute the procedure which access table SALES_ORDER present in SALES Schema.

Example with Procedure:

Example of Definer Procedure
--create a procedure with SALES Schema with Definer Right
CREATE PROCEDURE DELETE_ORDER_HISTORY (cust_id IN NUMBER)
AS
BEGIN
DELETE FROM SALES_ORDER where CUSTOMER_ID = 100;
END;


--Grant execute right to HR
Grant execute on DELETE_ORDER_HISTORY to HR;


Invoker’s Right
In Invoker’s Right, Procedure executes with the privileges of the Current User. (means privileges are bound to current user only)
Example
User SALES create a procedure to modify the table SALES_ORDER present in SALES Schema. Then SALES user grant execute privileges to User HR.
If procedure created with Invoker’s Right then USER HR execute the procedure which access table SALES_ORDER present in HR Schema(means Current Schema/user).

Example of Invoker Procedure

-- Create a procedure with Sales Schema with invoker Rights
CREATE OR REPLACE PROCEDURE DELETE_ORDER_HISTORY(cust_id IN NUMBER)
AUTHID CURRENT_USER
AS
BEGIN
DELETE FROM SALES_ORDER where CUSTOMER_ID = cust_id;
END;
/


--Grant execute right to HR
Grant execute on DELETE_ORDER_HISTORY to HR;
GRANT DELETE on SALES.SALES_ORDER to HR;


When to use Invoker’s Right
1. When you are using no sql statement in Procedure or package, which is used by all users by using AUTHID CURRENT_USER.
With no SQL statement, at runtime oracle donot need to verify the permissions. DBMS_OUTPUT is an example of it.
2. Creating Invoker right procedure in high privileged schema. when lower privileged user call procedure then they are not able to do more without pervileges on objects.

Note:
A invoker’s rights procedure is useful in situations where a lower-privileged user must execute a procedure owned by a higher-privileged user.
When a user runs an invoker’s rights procedure , the procedure temporarily inherits all of the privileges of the invoking user while the procedure runs.

Problem:
Invoker’s right has problems if some one user has DBA role and he can misuse with inherit priviliges. To avoid this we need to REVOKE INHERIT PRIVILEGES ON USER HR FROM SALES;

By default, all users are granted INHERIT PRIVILEGES ON USER newuser TO PUBLIC.

GRANT or REVOKE the INHERIT PRIVILEGES on an Invoking User to a Procedure Owner
Invoking USER: User who runs the invoker’s rights procedure.
Procedure owner: User who create the invoker’s rights procedure.

--Syntax
GRANT INHERIT PRIVILEGES ON USER invoking_user TO procedure_owner;
--Example
GRANT INHERIT PRIVILEGES ON USER HR TO SALES;
REVOKE INHERIT PRIVILEGES ON USER HR FROM SALES;
OR
GRANT INHERIT ANY PRIVILEGES TO SALES;


--If you want to revoke or grant from PUBLIC
REVOKE INHERIT PRIVILEGES ON invoking_user FROM PUBLIC;
GRANT INHERIT PRIVILEGES ON USER newuser TO PUBLIC; (By default it present)


For more Detail follow Oracle link:
https://docs.oracle.com/database/121/DBSEG/dr_ir.htm#DBSEG660