userenv
Note：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. See SYS_CONTEXT for more information.
USERENV returns information about the current session. This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session. You cannot use USERENV in the condition of a CHECK constraint. Table 7-12 describes the values for the parameter argument.
Table 7-12 Parameters of the USERENV Function
| Parameter | Return Value |
|---|---|
CLIENT_INFO | CLIENT_INFO returns up to 64 bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.Caution: Some commercial applications may be using this context value. Refer to the applicable documentation for those applications to determine what restrictions they may impose on use of this context area. See Also: Oracle Database Security Guide for more information on application context, CREATE CONTEXT, and SYS_CONTEXT |
ENTRYID | The current audit entry number. The audit entryid sequence is shared between fine-grained audit records and regular audit records. You cannot use this attribute in distributed SQL statements. |
ISDBA | ISDBA returns ‘TRUE’ if the user has been authenticated as having DBA privileges either through the operating system or through a password file. |
LANG | LANG returns the ISO abbreviation for the language name, a shorter form than the existing ‘LANGUAGE’ parameter. |
LANGUAGE | LANGUAGE returns the language and territory used by the current session along with the database character set in this form:language_territory.characterset |
SESSIONID | SESSIONID returns the auditing session identifier. You cannot specify this parameter in distributed SQL statements. |
SID | SID returns the session ID. |
TERMINAL | TERMINAL returns the operating system identifier for the terminal of the current session. In distributed SQL statements, this parameter returns the identifier for your local session. In a distributed environment, this parameter is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations. |
| 参数 | 功能 |
|---|---|
| CLINET_INFO | 返回最高可达64个字节存储的用户会话信息,可由应用程序使用DBMS_APPLICATION_INFO包。 |
| ENTRYID | 返回当前审计条目编号。审计的EntryID序列细粒度的审计记录和定期审计记录之间共享。在分布式SQL语句不能使用这个属性。 |
| ISDBA | 如果用户已经被认证为dba;或者是通过操作系统或口令文件具有DBA特权的，返回“TRUE"，否则返回"FALSE" |
| LANG | 返回ISO缩写语言名称，一个比现有的“语言”参数较短的形式 |
| LNAGUAGE | 返回数据库当前会话的语言、地域和字符集 |
| SESSIONID | 返回审计会话标识符。在分布式SQL语句不能指定此参数 |
| SID | 返回数据库会话ID |
| TERMINAL | 返回当前会话的终端操作系统的标识符。在分布式SQL语句，此参数返回了标识符为本地会话。在分布式环境中，此参数只支持远程SELECT语句，不用于远程INSERT，UPDATE或DELETE操作。 |
案例
-- ISO缩写语音名称
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
US
-- 查看Oracle服务端字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8
-- 查看连接终端类型
SQL> select userenv('TERMINAL') from dual;
USERENV('TERMINAL')
------------------------------------------------------------
pts/2
-- 审计会话标识符
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
4294967295
-- 查询会话ID
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
1520
-- 想查看自己是不是dba权限用户
SQL> show user
USER is "SYS"
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
sys_context
sys_context函数是Oracle提供的一个获取环境上下文信息的预定义函数。该函数用来返回一个指定namespace下的parameter值。
该函数可以在sql和PL/SQL语言中使用。
sys_context实际上就是一个Oracle存储和传递参数的容器访问函数。我们登入Oracle服务器，是带有会话信息和其他一些属性信息的。其中，有一些是Oracle预定义的，登录系统的时候自动填入到指定的变量中。另一个函数userenv()保留与之兼容。
-- 以下是可以通过sys_context函数获取到的一些信息：
select sys_context('USERENV', 'TERMINAL') terminal,
sys_context('USERENV', 'LANGUAGE') language,
sys_context('USERENV', 'SESSIONID') sessionid,
sys_context('USERENV', 'INSTANCE') instance,
sys_context('USERENV', 'ENTRYID') entryid,
sys_context('USERENV', 'ISDBA') isdba,
sys_context('USERENV', 'NLS_TERRITORY') nls_territory,
sys_context('USERENV', 'NLS_CURRENCY') nls_currency,
sys_context('USERENV', 'NLS_CALENDAR') nls_calendar,
sys_context('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
sys_context('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
sys_context('USERENV', 'NLS_SORT') nls_sort,
sys_context('USERENV', 'CURRENT_USER') current_user,
sys_context('USERENV', 'CURRENT_USERID') current_userid,
sys_context('USERENV', 'SESSION_USER') session_user,
sys_context('USERENV', 'SESSION_USERID') session_userid,
sys_context('USERENV', 'PROXY_USER') proxy_user,
sys_context('USERENV', 'PROXY_USERID') proxy_userid,
sys_context('USERENV', 'DB_DOMAIN') db_domain,
sys_context('USERENV', 'DB_NAME') db_name,
sys_context('USERENV', 'HOST') host,
sys_context('USERENV', 'OS_USER') os_user,
sys_context('USERENV', 'EXTERNAL_NAME') external_name,
sys_context('USERENV', 'IP_ADDRESS') ip_address,
sys_context('USERENV', 'MODULE') module,
sys_context('USERENV', 'NETWORK_PROTOCOL') network_protocol,
sys_context('USERENV', 'BG_JOB_ID') bg_job_id,
sys_context('USERENV', 'FG_JOB_ID') fg_job_id,
sys_context('USERENV', 'AUTHENTICATION_TYPE') authentication_type,
sys_context('USERENV', 'AUTHENTICATION_DATA') authentication_data,
sys_context('userenv', 'SID')
from dual;
我们还可以自己定义上下文参数来传递值，下面是sys_context函数的使用格式：
sys_context('namespace', 'parameter'{, length});
其中，namespace是存储信息的一个组单位，namespace是按照类别进行分类的。一个namespace下可以有多个参数值，通过不同的parameter进行区分。namespace是预先定义好的SQL标识符，而parameter是可以任意大小写非敏感的字符串，不超过30位长度。函数返回值为varchar2类型，长度默认为256位。如果需要限制这个默认值，可以length参数作为新的返回长度值。
-- 1、定义namespace
create context Test using set_test_context;
-- 2、定义方法
create or replace procedure set_test_context(vc_value in varchar2) is
begin
dbms_session.set_context('Test', 'a1', vc_value);
end;
/
-- 3、设置值
exec set_test_context('m');
-- 4、获取值
SQL> select sys_context('Test', 'a1') from dual;
SYS_CONTEXT('TEST','A1')
----------------------------------------------------------------------------------------------------
m
SQL>
Read all parameters from SYS_CONTEXT/USERENV
set line 100
col name for a25
col val for a30
select res.*
from (
select *
from (
select
sys_context ('userenv','ACTION') ACTION,
sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,
sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA,
sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
sys_context ('userenv','BG_JOB_ID') BG_JOB_ID,
sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
sys_context ('userenv','CLIENT_INFO') CLIENT_INFO,
sys_context ('userenv','CURRENT_BIND') CURRENT_BIND,
sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID,
sys_context ('userenv','CURRENT_EDITION_NAME') CURRENT_EDITION_NAME,
sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA,
sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID,
sys_context ('userenv','CURRENT_SQL') CURRENT_SQL,
sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn,
sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH,
sys_context ('userenv','CURRENT_USER') CURRENT_USER,
sys_context ('userenv','CURRENT_USERID') CURRENT_USERID,
sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE,
sys_context ('userenv','DB_DOMAIN') DB_DOMAIN,
sys_context ('userenv','DB_NAME') DB_NAME,
sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
sys_context ('userenv','DBLINK_INFO') DBLINK_INFO,
sys_context ('userenv','ENTRYID') ENTRYID,
sys_context ('userenv','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,
sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY,
sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
sys_context ('userenv','HOST') HOST,
sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,
sys_context ('userenv','INSTANCE') INSTANCE,
sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME,
sys_context ('userenv','IP_ADDRESS') IP_ADDRESS,
sys_context ('userenv','ISDBA') ISDBA,
sys_context ('userenv','LANG') LANG,
sys_context ('userenv','LANGUAGE') LANGUAGE,
sys_context ('userenv','MODULE') MODULE,
sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL,
sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR,
sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY,
sys_context ('userenv','NLS_DATE_FORMAT') NLS_DATE_FORMAT,
sys_context ('userenv','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
sys_context ('userenv','NLS_SORT') NLS_SORT,
sys_context ('userenv','NLS_TERRITORY') NLS_TERRITORY,
sys_context ('userenv','OS_USER') OS_USER,
sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER,
sys_context ('userenv','PROXY_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY,
sys_context ('userenv','PROXY_USER') PROXY_USER,
sys_context ('userenv','PROXY_USERID') PROXY_USERID,
sys_context ('userenv','SERVER_HOST') SERVER_HOST,
sys_context ('userenv','SERVICE_NAME') SERVICE_NAME,
sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID,
sys_context ('userenv','SESSION_EDITION_NAME') SESSION_EDITION_NAME,
sys_context ('userenv','SESSION_USER') SESSION_USER,
sys_context ('userenv','SESSION_USERID') SESSION_USERID,
sys_context ('userenv','SESSIONID') SESSIONID,
sys_context ('userenv','SID') SID,
sys_context ('userenv','STATEMENTID') STATEMENTID,
sys_context ('userenv','TERMINAL') TERMINAL
from dual
-- where sys_context ('userenv','SESSIONID') NOT in ('SYS', 'XDB') -- <<<<< filter by user
)
unpivot include nulls (
val for name in (action, audited_cursorid, authenticated_identity, authentication_data, authentication_method, bg_job_id, client_identifier, client_info, current_bind, current_edition_id, current_edition_name, current_schema, current_schemaid, current_sql, current_sqln, current_sql_length, current_user, current_userid, database_role, db_domain, db_name, db_unique_name, dblink_info, entryid, enterprise_identity, fg_job_id, global_context_memory, global_uid, host, identification_type, instance, instance_name, ip_address, isdba, lang, language, module, network_protocol, nls_calendar, nls_currency, nls_date_format, nls_date_language, nls_sort, nls_territory, os_user, policy_invoker, proxy_enterprise_identity, proxy_user, proxy_userid, server_host, service_name, session_edition_id, session_edition_name, session_user, session_userid, sessionid, sid, statementid, terminal)
)
) res;
查询结果
NAME VAL
------------------------- ------------------------------
ACTION
AUDITED_CURSORID
AUTHENTICATED_IDENTITY oracle
AUTHENTICATION_DATA
AUTHENTICATION_METHOD OS
BG_JOB_ID
CLIENT_IDENTIFIER
CLIENT_INFO
CURRENT_BIND
CURRENT_EDITION_ID 100
CURRENT_EDITION_NAME ORA$BASE
CURRENT_SCHEMA SYS
CURRENT_SCHEMAID 0
CURRENT_SQL
CURRENT_SQLN
CURRENT_SQL_LENGTH
CURRENT_USER SYS
CURRENT_USERID 0
DATABASE_ROLE PRIMARY
DB_DOMAIN
DB_NAME testdba
DB_UNIQUE_NAME testdba
DBLINK_INFO
ENTRYID
ENTERPRISE_IDENTITY
FG_JOB_ID 0
GLOBAL_CONTEXT_MEMORY 0
GLOBAL_UID
HOST testosa
IDENTIFICATION_TYPE LOCAL
INSTANCE 1
INSTANCE_NAME testdba
IP_ADDRESS
ISDBA TRUE
LANG US
LANGUAGE AMERICAN_AMERICA.AL32UTF8
MODULE sqlplus@testosa (TNS V1-V3)
NETWORK_PROTOCOL
NLS_CALENDAR GREGORIAN
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TERRITORY AMERICA
OS_USER oracle
POLICY_INVOKER
PROXY_ENTERPRISE_IDENTITY
PROXY_USER
PROXY_USERID
SERVER_HOST testosa
SERVICE_NAME SYS$USERS
SESSION_EDITION_ID 100
SESSION_EDITION_NAME ORA$BASE
SESSION_USER SYS
SESSION_USERID 0
SESSIONID 4294967295
SID 1520
STATEMENTID
TERMINAL pts/3
59 rows selected.
参考资料
https://docs.oracle.com/cd/B13789_01/server.101/b10759/functions193.htm
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/USERENV.html#GUID-AC3C8AEF-A988-41C4-9242-69B54E5941D2
https://www.oreilly.com/library/view/oracle-sql-the/1565926978/1565926978_ch05-246-fm2xml.html
https://stackoverflow.com/questions/8114453/read-all-parameters-from-sys-context-userenv
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086
https://github.com/ReneNyffenegger/oracle-patterns/blob/master/SQL/functions/sys_context/userenv.sql
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086
258
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
