What is the difference between USER() and SYS_CONT

Question

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.

Answer 1

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

sys_context('USERENV', 'CURRENT_USER') - Pretty much like "session_user" deprecated (at least according to the 10g documentation)
(editted according to @a_horse_with_no_name's answer and the reference he gave to the 11g docs)

sys_context('USERENV', 'AUTHENTICATED_IDENTITY') - The identity used for authentication, depends on the "AUTHENTICATION_METHOD".
from documentation:

• Kerberos-authenticated enterprise user: kerberos principal name
• Kerberos-authenticated external user : kerberos principal name; same as the schema name
• SSL-authenticated enterprise user: the DN in the user's PKI certificate
• SSL-authenticated external user: the DN in the user's PKI certificate
• Password-authenticated enterprise user: nickname; same as the login name
• Password-authenticated database user: the database username; same as the schema name
• OS-authenticated external user: the external operating system user name
• Radius/DCE-authenticated external user: the schema name
• Proxy with DN : Oracle Internet Directory DN of the client
• Proxy with certificate: certificate DN of the client
• Proxy with username: database user name if client is a local database user; nickname if client is an enterprise user.
• SYSDBA/SYSOPER using Password File: login name
• SYSDBA/SYSOPER using OS authentication: operating system user name

user pseudo column - I'm not sure, according to documentation I'd think it's like CURRENT_SCHEMA but apparently it's like CURRENT_USER

Answer 2

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

Answer 3

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.

Answer 4

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.