Database/ORACLE

Oracle - SYS_CONTEXT 함수를 이용하여 접속 세션 정보 추출

하이주니 2017. 11. 19. 13:45
반응형

# INSTANCE NAME/ DB NAME을 확인하고 싶은데 권한이 없는 일반 유저가 확인하고 싶을때 가능
 - SYS_CONTEXT 함수는 세션정보를 얻어오는 함수 입니다.
 - USERENV : 현재 세션의 환경정보를 반환는 네임스페이스 입니다.
   SYS_CONTEXT ('namespace', 'parameter')

Oracle Docs : http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions079.htm


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- DB 이름
SELECT SYS_CONTEXT('userenv', 'db_name') db_name FROM DUAL;
DB_NAME
------------
oracle3
 
-- Instance 이름
SELECT SYS_CONTEXT('userenv', 'instance_name') instance_name FROM DUAL;
INSTANCE_NAME
------------------
oraSub3
 
-- 접속자 IP 주소
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') "My IP Address"  FROM DUAL;
 
-- DBA 여부
SELECT SYS_CONTEXT('USERENV','ISDBA') isdba FROM DUAL;
ISDBA
-------
TRUE
 
 
SELECT SYS_CONTEXT('USERENV','TERMINAL') terminal FROM DUAL;
SELECT SYS_CONTEXT('USERENV','LANGUAGE') language FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SESSIONID') sessionid FROM DUAL;
SELECT SYS_CONTEXT('USERENV','INSTANCE') instance FROM DUAL;
SELECT SYS_CONTEXT('USERENV','ENTRYID') entryid FROM DUAL;
SELECT SYS_CONTEXT('USERENV','ISDBA') isdba FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_SORT') nls_sort FROM DUAL;
SELECT SYS_CONTEXT('USERENV','CURRENT_USER') current_user FROM DUAL;
SELECT SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SESSION_USER') session_user FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SESSION_USERID') session_userid FROM DUAL;
SELECT SYS_CONTEXT('USERENV','PROXY_USER') proxy_user FROM DUAL;
SELECT SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid FROM DUAL;
SELECT SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain FROM DUAL;
SELECT SYS_CONTEXT('USERENV','DB_NAME') db_name FROM DUAL;
SELECT SYS_CONTEXT('USERENV','HOST') host FROM DUAL;
SELECT SYS_CONTEXT('USERENV','OS_USER') os_user FROM DUAL;
SELECT SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name FROM DUAL;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol FROM DUAL;
SELECT SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id FROM DUAL;
SELECT SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id FROM DUAL;
SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') FROM DUAL;


출처 : http://develop.sunshiny.co.kr/681


반응형
저작자표시 (새창열림)