Is there a way to get the sys_context('USERENV', 'CURRENT

Using this function below if I access the table emp from admin1, ideally it should return 0 which would signify the connection user as "ADMIN", but it return 1( the connection of the end user as its using ADMINT_TEST1.emp)

I want to get the connection identified of ADMIN1 users even when selecting audit_test1.emp (The function check_user is available in both schemas ) eg : from admin1 user

create or replace function check_user
return number
is
  n number;
begin
  SELECT sys_context('USERENV', 'CURRENT_USER') into usrname FROM dual;
  if (usrname ='AUDIT_TEST') then
    dbms_output.put_line('show user'||usrname);
    return 1;
  else 
    return 0;
  end if;
end ;

Test the function (running from user : ADMIN1)

declare
  n number(1);
begin
  select audit_test.check_user into n from dual;
  dbms_output.put_line(n);
  select check_user into n from dual;
  dbms_output.put_line(n);
end;
/


Output
===============
show user   AUDIT_TEST
1
show user   ADMIN1
0

Is this the expected functionality ? Even though the initiating user is ADMIN1, when we issue admin_test.emp (the connection_identifier) changes to AUDIT_TEST;

(I have tried best to convey what I wanted , not sure if the message is clear, please comment if the question is not clear )

CodePudding user response：

You can simple use dbms_output.put_line(sys_context('USERENV', 'CURRENT_USER')); - no need for SELECT ... INTO ... FROM dual

Anyway, check documentation:

CURRENT_USER

The name of the database user whose privileges are currently active. This may change during the duration of a database session as Real Application Security sessions are attached or detached, or to reflect the owner of any active definer's rights object. When no definer's rights object is active, CURRENT_USER returns the same value as SESSION_USER.

CURRENT_USER is not the current user, it shows the currently active privileges. By default (i.e. definer's rights) inside a Procedure/Function/Package the privileges of the procedure owner applies to current user.

In order to get the current user use for this use sys_context('USERENV', 'SESSION_USER') or simply USER.