oracle USER() 和 SYS_CONTEXT('USERENV','CURRENT_USER') 有什么区别？

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

提问by Andrew Martinez

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

在 Oracle 数据库中，以下各项之间有什么区别：

• user()
• sys_context('USERENV', 'CURRENT_USER')
• sys_context('USERENV', 'SESSION_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')

• 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':

在我的大多数测试中，这些值始终相同。唯一的例外是在运行以下命令来更改“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.

因此，所有这些都有某种安全/规则。但是，拥有 SESSION_USER 和 CURRENT_USER 背后一定有某种原因。我还认为 user() 可能是 sys_context('USERENV', 'CURRENT_USER') 的快捷方式，但我找不到有关此事的文档。

采纳答案by a_horse_with_no_name

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

来自手册：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.

权限当前处于活动状态的数据库用户的名称。这可能会在会话期间发生变化，以反映任何活动定义者的权限对象的所有者。当没有定义者的权限对象处于活动状态时，CURRENT_USER 返回与 SESSION_USER 相同的值。当直接在视图定义的主体中使用时，这将返回正在执行使用视图的光标的用户；它不尊重光标中使用的视图作为定义者的权利。

SESSION_USER

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 isa difference between SESSION_USER and CURRENT_USER especially when CURRENT_USER is used in a stored procedure or function.

所以SESSION_USER 和 CURRENT_USER 之间是有区别的，尤其是当 CURRENT_USER 用于存储过程或函数时。

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

我不得不承认，我不知道“企业用户”这个词是什么意思。

Btw: there is a third one:

顺便说一句：还有第三个：

SESSION_USERID

SESSION_USERID

The identifier of the database user at logon.

登录时数据库用户的标识符。

回答by A.B.Cade

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', 'CURRENT_SCHEMA')- 当前正在使用的架构和您已经发现的可以更改 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', 'SESSION_USER')- 在创建会话期间用于身份验证且无法更改的用户

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', 'CURRENT_USER')-很像不推荐使用的“session_user”（至少根据10g 文档）
（根据@a_horse_with_no_name 的回答和他对11g 文档的引用进行编辑）

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

sys_context('USERENV', 'AUTHENTICATED_IDENTITY')- 用于身份验证的身份取决于“AUTHENTICATION_METHOD”。
从文档：

• 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

• Kerberos 认证的企业用户：kerberos 主体名称
• Kerberos 认证的外部用户：kerberos 主体名称；与架构名称相同
• SSL认证的企业用户：用户PKI证书中的DN
• SSL认证的外部用户：用户PKI证书中的DN
• 密码认证企业用户：昵称；与登录名相同
• 密码认证的数据库用户：数据库用户名；与架构名称相同
• OS-authenticated external user：外部操作系统用户名
• Radius/DCE 认证的外部用户：模式名称
• 带有 DN 的代理：客户端的 Oracle Internet Directory DN
• 带证书的代理：客户端的证书DN
• 带有用户名的代理：如果客户端是本地数据库用户，则为数据库用户名；如果客户是企业用户，则昵称。
• SYSDBA/SYSOPER 使用密码文件：登录名
• SYSDBA/SYSOPER 使用 OS 认证：操作系统用户名

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

user pseudo column- 我不确定，根据文档，我认为它像CURRENT_SCHEMA但显然它像CURRENT_USER

回答by John Flack

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

CURRENT_SCHEMA是在您命名对象而不指定其所有者时将采用的模式。例如，如果 myCURRENT_SCHEMA是SCOTT，则SELECT * FROM EMP与 相同SELECT * FROM SCOTT.EMP。默认情况下，当我第一次连接到 Oracle 时，CURRENT_SCHEMA与 CURRENT_USER 相同。

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

但是，如果我连接为SCOTT，我可以发出ALTER SESSION SET CURRENT_SCHEMA=JOE，然后当我这样做时SELECT * FROM EMP，它被解释为JOE.EMP而不是SCOTT.EMP。当然，如果我没有SELECT权限JOE.EMP，或者JOE没有名为 的对象EMP，SELECT则将失败。

回答by Lukas Eder

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

在使用USER来自 PL/SQL的函数时，有一个重要的注意事项需要考虑。正如我在这篇博文中记录的那样，STANDARD.USER()实现如下：

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

So, it delegates to evaluating userin 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.

因此，它委托user在 SQL 引擎中进行评估，这会导致隐藏的 PL/SQL 到 SQL 上下文切换。如果您经常这样做，例如在触发器中，那么这在生产系统中可能会非常有害。尽量避免USER()从 PL/SQL调用，sys_context('USERENV', 'SESSION_USER')而是使用。

回答by Scott

There is also a performance difference between USER and using sys_context

USER 和使用 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

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