Definer vs. invoker rights – check out SYS_CONTEXT

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.