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

在 Oracle 数据库中，以下各项之间的区别是什么:

• 用户()
• sys_context('USERENV', 'CURRENT_USER')
• sys_context('USERENV', 'SESSION_USER')

这些是否也可能与当前用户"相关的值?

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

我特别感兴趣的是哪些可以更改，哪些可以更改它们，哪些不能更改值，哪些具有基于连接类型的不同值，以及哪些始终是用于登录数据库.

在我的大部分测试中，这些值始终相同.唯一的例外是在运行以下命令来更改CURRENT_SCHEMA"时:

alter session set current_schema=

执行以下操作会导致错误:

alter session set current_user=--即使是系统/系统，我想这很好

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

解决方案

来自手册:http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm#SQLRF51825

CURRENT_USER

<块引用>

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

SESSION_USER

<块引用>

登录时数据库用户的名称.对于企业用户，返回架构.对于其他用户，返回数据库用户名.该值在整个会话期间保持不变.

因此，SESSION_USER 和 CURRENT_USER 之间存在区别，尤其是当 CURRENT_USER 用于存储过程或函数时.

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

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

SESSION_USERID

<块引用>

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

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.