jueves, 5 de agosto de 2021

The function user in oracle database

The other day I was reviewing an scenario where the customer was asking why the user "USER1" is not captured in a "after logon" trigger  in the database. When I took a look, I saw the following code:

 

declare
  v_login_user VARCHAR2(30) := NULL;
begin

  SELECT UPPER(sys.login_user) INTO v_login_user FROM dual;
  if (user in ('USER1','USER2','USER3') ) then
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = AP1';
  end if;
exception
 when others then null;
END LOGIN_TRIGGER;
 

The customer created a session with "USER1" but the session didn't have the schema "AP1" set as default schema. 

The first impression was that they were not using the variable "v_login_user" where seems they are capturing the username of the session. So I started to investigate. 

It was so interesting for me when I saw that the PL/SQL works well, the "user" variable (I thought it was a variable) has the value "USER1". After some investigation I discovered that "user" is actually a public function in oracle that returns the name of the current user, that's why the PL/SQL was working. 

I confirmed this with the following piece of code:

BEGIN
     DBMS_OUTPUT.PUT_LINE('var user: '||user);
END;
/
var user: USER1

PL/SQL procedure successfully completed.

 

You can find more information about this function in the following links:


https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj42476.html

https://www.techonthenet.com/oracle/functions/user.php

No hay comentarios:

Publicar un comentario

Oracle ACE Director Award - Deiby Gómez

Thanks #OracleACE Program for this awesome certificate recognizing the work I have done in the community for the last year. Looking forwa...