What is the difference between USER() and SYS_CONTEXT('USERENV','CURRENT_USER')?

sys_context 12c
sys_context('userenv', 'ip_address)
sys_context in sql server
sys_context('userenv','service_name)
oracle 19c sys_context
oracle apex sys_context
oracle get current schema
oracle user

In an Oracle Database, what are the differences between the following:

  • user()
  • sys_context('USERENV', 'CURRENT_USER')
  • sys_context('USERENV', 'SESSION_USER')

Are these also possible related values to whatever 'the current user' is?

  • sys_context('USERENV', 'CURRENT_SCHEMA')
  • sys_context('USERENV', 'AUTHENTICATED_IDENTITY')

I am specifically interested in which ones can change, what can change them, which ones can not change value, which ones have different values based on connection type, and which one(s) is(are) always the schema used to log into the database.

In most of my testing the values are always the same. The only exception would be when running the following to alter 'CURRENT_SCHEMA':

alter session set current_schema=<SCHEMA>

Doing following results in an error:

alter session set current_user=<USER> --even as sys/system, which is good I suppose

So there is some kind of security/rules around all of this. However there must be some reason behind having a SESSION_USER and a CURRENT_USER. I also suppose user() could be a shortcut to sys_context('USERENV', 'CURRENT_USER'), but I could find no documentation on the matter.

From the manual at: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm#SQLRF51825

CURRENT_USER

The name of the database user whose privileges are currently active. This may change during the duration of a session 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. When used directly in the body of a view definition, this returns the user that is executing the cursor that is using the view; it does not respect views used in the cursor as being definer's rights.

SESSION_USER

The name of the database user at logon. For enterprise users, returns the schema. For other users, returns the database user name. This value remains the same throughout the duration of the session.

So there is a difference between SESSION_USER and CURRENT_USER especially when CURRENT_USER is used in a stored procedure or function.

I have to admit that I don't know what the term "enterprise user" means though.

Btw: there is a third one:

SESSION_USERID

The identifier of the database user at logon.

SYS_CONTEXT, SYS_CONTEXT returns the value of parameter associated with the context The following statement returns the name of the user who logged onto the database: IDENTIFICATION_TYPE to distinguish between external and enterprise users  A user context means all the pernissions and access settings that are provided to a user will be followed strictly.A system context will override these permissions and would execute irrespective of permissions.

What is the difference between USER () and SYS_CONTEXT , From the manual at: http://docs.oracle.com/cd/E11882_01/server.112/e26088/​functions184.htm#SQLRF51825. CURRENT_USER. The username of the  Notice that there is a difference between “current_schema” and “session_user”: The first would change after an “alter session set current_schema=someotherschema;” but the second stays the same. Following creates a small demo schema with one table, containing the sensitive column “BUDGET”:

CURRENT_SCHEMA is the schema that will be assumed if you name an object without specifying its owner. For instance, if my CURRENT_SCHEMA is SCOTT, then SELECT * FROM EMP is the same as SELECT * FROM SCOTT.EMP. By default, when I first connect to Oracle, the CURRENT_SCHEMA is the same as CURRENT_USER.

However, if I am connected as SCOTT, I can issue ALTER SESSION SET CURRENT_SCHEMA=JOE and then when I do SELECT * FROM EMP, it is interpreted as JOE.EMP rather than SCOTT.EMP. Of course, if I don't have the SELECT privilege on JOE.EMP, or JOE doesn't have an object named EMP, the SELECT will fail.

Oracle USER vs sys_context, Connor has more detail on the difference. declare v_result varchar2(100); begin for i in 1..1000000 loop v_result := nvl(sys_context('userenv'  sys_context('USERENV', 'CURRENT_SCHEMA') - The schema that is currently being used and as you already found out can be changed with alter session. sys_context('USERENV', 'SESSION_USER') - The user that was used for authentication during the creation of the session and cannot be changed

There is an important note to take into account when using the USER function from PL/SQL. As I have documented in this blog post, STANDARD.USER() is implemented as follows:

function USER return varchar2 is
c varchar2(255);
begin
    select user into c from sys.dual;
    return c;
end;

So, it delegates to evaluating user in the SQL engine, which leads to a hidden PL/SQL to SQL context switch. If you're doing that too often, e.g. from within a trigger, then that can turn out to be quite hurtful in a production system. Try to avoid calling USER() from PL/SQL, and use sys_context('USERENV', 'SESSION_USER') instead.

Oracle / PLSQL: SYS_CONTEXT Function, Oops, something seems to be preventing the display of our advertisements. This website would not exist without the advertisements we display and your kind  See the docs: » USERENV is a legacy function that is retained for backward compatibility. Oracle recommends that you use the SYS_CONTEXT function with the built-in USERENV namespace for current functionality.

There is also a performance difference between USER and using sys_context

declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := sys_context('userenv','session_user');
  end loop;
end;
/

-- 2.5s

declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := user;
  end loop;
end;
/ 

-- 47s

Also see https://svenweller.wordpress.com/2016/02/24/sequence-and-audit-columns-with-apex-5-and-12c/ and http://www.grassroots-oracle.com/2019/01/oracle-user-vs-sys-context.html

sys_context Tips, Answer: Here are some examples of using sys_context: A) sid of current session: I) Get the database user name by which the current user is authenticated: SYS_CONTEXT . Syntax. Description of the illustration ''sys_context.gif'' Purpose. SYS_CONTEXT returns the value of parameter associated with the context namespace at the current instant. You can use this function in both SQL and PL/SQL statements. SYS_CONTEXT must be executed locally.

oracle, in oracle database, differences between following: user(); sys_context('userenv', '​current_user'); sys_context('userenv', 'session_user'). are these possible  User is the amount of CPU time spent in user-mode code (outside the kernel) within the process. This is only actual CPU time used in executing the process. Other processes and time the process spends blocked do not count towards this figure. Sys is the amount of CPU time spent in the kernel within the process.

Oracle SYS_CONTEXT Function, In the list that follows, the type of user is followed by the value returned: Kerberos-​authenticated This is the only attribute of USERENV for which Oracle implements such a change. SID, The session number (different from the session ID). Tom, Is there any difference between values returned by sys_context ( 'userenv','current_schema' ) and sys_context ( 'userenv','current_user' ) ? Boths returns the same value in definer rights and invoker rights. So what is the difference between those?

Oracle SYS_CONTEXT Function with Examples (including Oracle , The purpose of the Oracle SYS_CONTEXT function is to return information about The differences between USER and SYS_CONTEXT are:. sys_context('USERENV', 'CURRENT_SCHEMA') - Le schéma qui est actuellement utilisé et comme vous l'avez déjà découvert peut être changé avec alter session sys_context('USERENV', 'SESSION_USER') - L'utilisateur qui a été utilisé pour l'authentification lors de la création de la session et ne peut pas être modifié

Comments
  • session_user/current_user could be influenced by the usage of proxy users, but I'm not sure.
  • ALTER SESSION SET CURRENT_USER= is not valid syntax anyway: docs.oracle.com/cd/E14072_01/server.112/e10592/…
  • Isn't SESSION_USERID to SESSION_USER the same as CURRENT_USERID to CURRENT_USER and CURRENT_SCHEMAID to CURRENT_SCHEMA ? the documentation isn't so clear... is it really a third one ? are there also a 4th and 5th ?
  • @A.B.Cade: at least SESSION_USERID and SESSION_USER have a different description (see above). If they were the same I'd assume Oracle would have written the same description.
  • Of course they're not the same - one is the identifier and the other is the name. But what about CURRENT_USERID and CURRENT_SCHEMAID? according to the documentation, all 3 starts with the same description (replace "name" with "identifier") as their partners. What am I missing ?
  • What about the function 'user()'? How does that relate to any of those values? Also I was under the impression that USERs == SCHEMAs in Oracle.
  • @AndrewMartinez: Quote from the manual: "USER returns the name of the session user (the user who logged on)" So it's the same as SESSION_USER. When creating a user, a corresponding schema with that name is created as well (that's when user == schema). But when you switch the current schema (alter session set current_schema=...) then current_user <> current_schema