英文:
Oracle Get Current Username When Using a Proxy User
问题
可以在OracleDB中通过以下方式获取使用代理用户帐户连接的用户的真实用户名：
- 查询：
SELECT sys_context('userenv', 'session_user') FROM dual;
期望结果：
i_am_the_true_user_name
实际结果：
i_am_the_true_user_name
英文:
Is it possible to get the true user name of a user in OracleDB when the user in question is connected via a proxy user account?
True User Name: i_am_the_true_user_name
Proxy User Name: i_am_the_proxy_user_name
What I have tried:
- Query:
Select user from dual;
Desired Result:
i_am_the_true_user_name
Actual Result:
i_am_the_proxy_user_name
- Query:
SELECT username FROM user_users;
Desired Result:
i_am_the_true_user_name
Actual Result:
i_am_the_proxy_user_name
答案1
得分: 3
尝试这样做：
select sys_context('userenv','proxy_user') from dual;
代理账户名（开发者的个人账户）可在系统会话上下文中获得，并可以通过数据库触发器自动在v$session视图中显示，以便DBA随时了解谁连接到共享账户。注意：在实施之前，请确保您没有在其他地方使用 DBMS_SESSION.SET_IDENTIFIER 功能。
CREATE OR REPLACE TRIGGER db_session_trig
AFTER LOGON ON DATABASE
v_proxy_user varchar2;
BEGIN
v_proxy_user := sys_context('userenv','proxy_user');
if v_proxy_user is not null then
dbms_session.set_identifier(v_proxy_user);
end if;
END;
select username, osuser, client_identifier
from v$session where username='I_AM_THE_TRUE_USER_NAME';
用户名 操作系统用户 客户端标识
----------------------- --------- ----------------------
I_AM_THE_TRUE_USER_NAME oracle I_AM_THE_PROXY_USER_NAME
英文:
Try this:
select sys_context('userenv','proxy_user') from dual;
The proxy account name (the developer’s personal account) is available in the system session context, and can be automatically made visible in the v$session view through a database trigger so that the DBA can tell who is connected to shared accounts at all times. Note: make sure you're not using the DBMS_SESSION.SET_IDENTIFIER functionality for anything else before implementing this.
CREATE OR REPLACE TRIGGER db_session_trig
AFTER LOGON ON DATABASE
v_proxy_user varchar2;
BEGIN
v_proxy_user := sys_context('userenv','proxy_user');
if v_proxy_user is not null then
dbms_session.set_identifier(v_proxy_user);
end if;
END;
select username, osuser, client_identifier
from v$session where username='I_AM_THE_TRUE_USER_NAME';
USERNAME OSUSER CLIENT_IDENTIFIER
----------------------- --------- ----------------------
I_AM_THE_TRUE_USER_NAME oracle I_AM_THE_PROXY_USER_NAME
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论