The introduction
This is showing the difference between definer and invoker rights in terms of current user and current schema – what context is the code running in.
The setup
In schema JOHN create two stored procedures one with definer rights the other with invoker rights both printing the same SYS_CONTEXT parameters.
SQL> create or replace procedure print_user_info_def
is
begin
dbms_output.put_line('USER: '||user);
dbms_output.put_line('SESSION_USER: '||sys_context('userenv', 'session_user'));
dbms_output.put_line('CURRENT_USER: '||sys_context('userenv', 'current_user'));
dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', 'current_schema'));
end print_user_info_def;
/
SQL> create or replace procedure print_user_info_inv
authid current_user
is
begin
dbms_output.put_line('USER: '||user);
dbms_output.put_line('SESSION_USER: '||sys_context('userenv', 'session_user'));
dbms_output.put_line('CURRENT_USER: '||sys_context('userenv', 'current_user'));
dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', 'current_schema'));
end print_user_info_inv;
/
The result
Log-in with user JANE and run the two stored procedures owned by JOHN
SQL> exec john.print_user_info_def USER: JANE SESSION_USER: JANE CURRENT_USER: JOHN CURRENT_SCHEMA: JOHN
SQL> exec john.print_user_info_inv USER: JANE SESSION_USER: JANE CURRENT_USER: JANE CURRENT_SCHEMA: JANE
Foot note: All tests have been run on a 10g 10.2.0.4 database.