Definer and Invoker Rights
Before Oracle8i, Definer Right procedures execute with the privileges of their owner, not their current user, such definer’s rights sub-programs are bound to the schema in which they reside; since Oracle 8i, introduced Invoker Right, then Invoker Right procedures execute with the privileges of the current user, that is, the user who invokes the procedure, such procedures are not bound to a particular schema. They can be run by a variety of users and allow multiple users to manage their own data by using centralized application logic.
Advantages of Invoker’s Rights
Invoker’s rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.
Specifying the Privileges for a Subprogram with the AUTHID Clause
In the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, or CREATE TYPE statement, you can include either AUTHID CURRENT_USER or AUTHID DEFINER immediately before the IS or AS keyword that begins the declaration section.
DEFINER is the default option. In a package or object type, the AUTHID clause applies to all subprograms.
Who Is the Current User During Subprogram Execution?
In a sequence of calls, whenever control is inside an invoker’s rights subprogram, the current user is the session user. When a definer’s rights subprogram is called, the owner of that subprogram becomes the current user.
To verify who the current user is at any time, you can check the USER_USERS data dictionary view.
Using Roles with Invoker’s Rights Subprograms
The use of roles in a subprogram depends on whether it executes with definer’s rights or invoker’s rights. Within a definer’s rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.
Within an invoker’s rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer’s rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.
Using Views and Database Triggers with Invoker’s Rights Subprograms
For invoker’s rights subprograms executed within a view expression, the schema that created the view, not the schema that is querying the view, is considered to be the current user. This rule also applies to database triggers.
Do a small test as follows:
SQL> conn / as sysdba
SQL> create user jenny identified by jenny default tablespace users temporary tablespace temp;
User created.
SQL> grant connect, resource to jenny;
Grant succeeded.
SQL> conn jenny/jenny
Connected.
SQL> set serveroutput on size 1000000
SQL> create or replace procedure definer_proc
2 as
3 uname varchar2(30);
4 begin
5 for x in
6 ( select sys_context( 'userenv', 'current_user' ) current_user,
7 sys_context( 'userenv', 'session_user' ) session_user,
8 sys_context( 'userenv', 'current_schema' ) current_schema
9 from dual )
10 loop
11 dbms_output.put_line( 'Current User: ' || x.current_user );
12 dbms_output.put_line( 'Session User: ' || x.session_user );
13 dbms_output.put_line( 'Current Schema: ' || x.current_schema );
14 end loop;
15 execute immediate 'select username from user_users' into uname;
16 dbms_output.put_line(' Current User: ' || uname);
17 end;
18 /
Procedure created.
SQL> exec definer_proc
Current User: JENNY
Session User: JENNY
Current Schema: JENNY
Current User: JENNY
PL/SQL procedure successfully completed.
SQL> grant execute on jenny.definer_proc to test;
Grant succeeded.
SQL> create or replace procedure invoker_proc
2 AUTHID CURRENT_USER
3 as
4 uname varchar2(30);
5 begin
6 for x in
7 ( select sys_context( 'userenv', 'current_user' ) current_user,
8 sys_context( 'userenv', 'session_user' ) session_user,
9 sys_context( 'userenv', 'current_schema' ) current_schema
10 from dual )
11 loop
12 dbms_output.put_line( 'Current User: ' || x.current_user );
13 dbms_output.put_line( 'Session User: ' || x.session_user );
14 dbms_output.put_line( 'Current Schema: ' || x.current_schema );
15 end loop;
16 execute immediate 'select username from user_users' into uname;
17 dbms_output.put_line(' Current User: ' || uname);
18 end;
19 /
Procedure created.
SQL> exec invoker_proc
Current User: JENNY
Session User: JENNY
Current Schema: JENNY
Current User: JENNY
PL/SQL procedure successfully completed.
SQL> grant execute on jenny.invoker_proc to test;
Grant succeeded.
SQL> connect test/test Connected. SQL> set serveroutput on size 1000000 SQL> exec jenny.definer_proc Current User: JENNY Session User: TEST Current Schema: JENNY Current User: JENNY PL/SQL procedure successfully completed. SQL> exec jenny.invoker_proc Current User: TEST Session User: TEST Current Schema: TEST Current User: TEST PL/SQL procedure successfully completed. SQL> alter session set current_schema = jenny; Session altered. SQL> exec jenny.definer_proc Current User: JENNY Session User: TEST Current Schema: JENNY Current User: JENNY PL/SQL procedure successfully completed. SQL> exec jenny.invoker_proc Current User: TEST Session User: TEST Current Schema: JENNY Current User: TEST PL/SQL procedure successfully completed. SQL> alter session set current_schema = system; Session altered. SQL> exec jenny.definer_proc Current User: JENNY Session User: TEST Current Schema: JENNY Current User: JENNY PL/SQL procedure successfully completed. SQL> exec jenny.invoker_proc Current User: TEST Session User: TEST Current Schema: SYSTEM Current User: TEST PL/SQL procedure successfully completed.
Reference: http://www.eygle.com/archives/2005/12/definer_and_invoker_rights.html
