[Oracle Tips] Session (Login) user vs. Current user

When you connect to an Oracle database, during your session, you may have used privileges of multiple users behind the scene. Why not we always have a single user instead of multiple? Isn't there a security hole here? This post tries to explain the reason behind multiple users in a session. There are two important users at any given time during a session: session user and current user. These two identities may refer to the same user or two different users.

Session user - the user under which you log into the database. This user remains unchanged during the life time of the session. It is sometimes called the login user. You can access it via SYS_CONTEXT('USERENV', 'SESSION_USER'). So, if you connect to the database as Scott, the session user is Scott.

Current user - the user whose privileges are currently active. This user may change during the life time of the session. You can access it via SYS_CONTEXT('USERENV', 'CURRENT_USER'). For example, if user Scott is executing a definer's rights procedure foo which is owned Tim, during the time of execution, the current user is Tim and the session user remains unchanged as Scott. (When you execute a definer's rights procedure, the privileges of the owner of the procedure become active.)

The following is a simple example showing the above two users:

SQL> CONN / as SYSDBA
Connected.

SQL> CREATE USER u1 IDENTIFIED BY p1;
User created.

SQL> CREATE USER u2 IDENTIFIED BY p2;
User created.

SQL> GRANT CREATE SESSION TO u1;
Grant succeeded.

SQL> GRANT CREATE SESSION, CREATE PROCEDURE TO u2;
Grant succeeded.

SQL> CONN u2/p2
Connected.

We create a very simple definer's rights procedure in u2's schema. It simply outputs the current user and the session user:

SQL> CREATE PROCEDURE foo AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Session user is ' || SYS_CONTEXT('USERENV', 'SESSION_USER'));
  DBMS_OUTPUT.PUT_LINE('Current user is ' || SYS_CONTEXT('USERENV', 'CURRENT_USER'));
END;
/  2    3    4    5    6  

Procedure created.

SQL> GRANT EXECUTE ON foo to u1;
Grant succeeded.

SQL> CONN u1/p1
Connected.

SQL> SET SERVEROUTPUT ON

As you can see, at the time of log on, the current user and the session user is the same u1:

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Session user is ' || SYS_CONTEXT('USERENV', 'SESSION_USER'));
Session user is U1

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Current user is ' || SYS_CONTEXT('USERENV', 'CURRENT_USER'));
Current user is U1

PL/SQL procedure successfully completed.

During the time of execution of u2. foo, we have two different users for session user and current user values. As you can see, the definer's rights procedure u2.foo is executed under the privileges of u2 even though u1 was running the procedure. If used correctly, you can use definer's right procedures to grant controlled access to database objects. (For example, a user might not have select privilege on a table, but you want to allow some computation on the table or show only a  selected set of rows of the table to the user. You can write a definer's rights procedure to restrict access.) At the same time, if used incorrectly, these definer's rights procedures may give users access to database objects that they are not supposed to access and further they may potentially misuse procedures which are executed as privileged users.

SQL> EXEC u2.foo;
Session user is U1
Current user is U2

PL/SQL procedure successfully completed.

Once the execution is over, the current user and session user become the same user u1:

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Session user is ' || SYS_CONTEXT('USERENV', 'SESSION_USER'));
Session user is U1

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Current user is ' || SYS_CONTEXT('USERENV', 'CURRENT_USER'));
Current user is U1

PL/SQL procedure successfully completed.