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.