首页 > 代码库 > 由查找session IP 展开---函数、触发器、包

由查找session IP 展开---函数、触发器、包

由查找session IP 展开---函数、触发器、包


一、userenv函数、sys_context函数

 --查看当前客户端会话的session IP信息

SQL>select sys_context(‘userenv‘,‘ip_address‘) from dual;

SYS_CONTEXT(‘USERENV‘,‘IP_ADDRESS‘)

--------------------------------------------------------------------------------

192.168.56.117


展开:可以通过userenv函数或者sys_context函数可以获得当前会话的信息


SQL>SELECT USERENV(‘LANGUAGE‘) FROM DUAL;

USERENV(‘LANGUAGE‘)

----------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK


--使用参数列举

--ISDBA:返回当前用户是否是dba,如果是则返回true

--SESSION:返回当前会话的标志

--EXTRYID:返回会话入口标志

--INSTANCE:返回当前instance的标志

--LANGUAGE:返回当前环境语言变量

--LANG:返回当前环境的语言缩写

--TERMINAL:返回用户的终端或机器的标志


SQL>SELECT SYS_CONTEXT(‘USERENV‘,‘LANGUAGE‘) FROM DUAL;

SYS_CONTEXT(‘USERENV‘,‘LANGUAGE‘)

------------------------------------------------------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK


SQL>select sys_context(‘userenv‘,‘host‘) from dual;

SYS_CONTEXT(‘USERENV‘,‘HOST‘)

--------------------------------------------------------------------------------

WORKGROUP\FPA4GFVZXULBFCR

 

二、v$session视图中

通过v$session视图,将客户端IP信息存入client_identifier字段或client_info字段


使用client_info字段

SQL>execdbms_application_info.set_client_info(sys_context(‘userenv‘,‘ip_address‘));

SQL>select username,sid,serial#,client_info,client_identifier from v$session wheresid=(select sys_context(‘userenv‘,‘sid‘) from dual);


使用client_identifier字段

SQL>exec dbms_session.set_identifier(sys_context(‘userenv‘,‘ip_address‘));

SQL>select username,sid,serial#,client_info,client_identifier from v$session wheresid=(select sys_context(‘userenv‘,‘sid‘) from dual);

 

范例截图如下:



三、触发器

通过触发器调用,新的客户端连接开启时自动触发,将IP信息写入client_info字段

create or replace triggeron_login_trigger

after logon on database

begin

dbms_application_info.set_client_info(sys_context(‘USERENV‘,‘IP_ADDRESS‘));

end;

/

通过触发器调用,新的客户端连接开启时自动触发,将IP信息写入client_identifier字段

create or replace triggeron_login_trigger

after logon on database

begin

dbms_session.set_identifier(sys_context(‘userenv‘,‘ip_address‘));

end;

/

四、使用UTL_INADDR Package

在没有触发器记录的前提下,通过UTL_INADDR Package来实现获取SESSION IP,而且可以获取其它SESSION IP。

通过说明UTL_INADDR Package的工作原理,体现出UTL_INADDR Package是如何实现的。

实验分析开始:

[root@org54~]# ps -ef |grep sql

oracle    2740 2707  0 21:17 pts/1    00:00:00 sqlplus          

oracle    3479 3451  0 21:49 pts/2    00:00:00 sqlplus          

root      3482 3375  0 21:49 pts/3    00:00:00 grep sql

[root@org54~]# su - oracle

[oracle@org54~]$ ps -ef |grep LO

oracle    2770 2740  0 21:18 ?        00:00:00 oraclemetro (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    3480 3479  0 21:49 ?        00:00:00 oraclemetro(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    3520 3488  0 21:50 pts/3    00:00:00 grep LO

 

补充:安装strace工具,跟踪进程执行时的系统调用和所接收的信号。 

 ——引用自网络,strace注解:在Linux世界,进程不能直接访问硬件设备,当进程需要访问硬件设备(比如读取磁盘文件,接收网络数据等等)时,必须由用户态模式切换至内核态模式,通过系统调用访问硬件设备。strace可以跟踪到一个进程产生的系统调用,包括参数,返回值,执行消耗的时间。

——引用结束。

[root@org54RHEL_5.5 x86_64 DVD]# cd Server/  --使用linux光盘安装工具

[root@org54Server]# ls -ll |grep strace

-r--r--r-- 326root root   175066 Jan 18  2010 strace-4.5.18-5.el5_4.1.i386.rpm

[root@org54Server]# rpm -ivh strace-4.5.18-5.el5_4.1.i386.rpm

warning:strace-4.5.18-5.el5_4.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID37017186

Preparing...               ########################################### [100%]

   1:strace                 ###########################################[100%]

[root@org54Server]# rpm -qa |grep strace

strace-4.5.18-5.el5_4.1

补充完毕

 

[oracle@org54~]$ strace -p 2770    --打开跟踪

SQL>SELECT UTL_INADDR.get_host_address(‘org54‘) from dual;  --执行查询

UTL_INADDR.GET_HOST_ADDRESS(‘ORG54‘)

--------------------------------------------------------------------------------------------------------

192.168.56.5


到strace跟踪信息界面下,查看信息如下:

Process 2770attached - interrupt to quit

read(8,"\0\323\0\0\6\0\0\0\0\0\3^!a\200\0\0\0\0\0\0TL\351\tj\0\0\0|\360\345"...,2064) = 211

gettimeofday({1404438819,340059}, NULL) = 0

gettimeofday({1404438819,340388}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

times(NULL)                             = 429805698

gettimeofday({1404438819,343035}, NULL) = 0

gettimeofday({1404438819,343625}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

gettimeofday({1404438819,344254}, NULL) = 0

times(NULL)                             = 429805698

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

times(NULL)                             = 429805698

gettimeofday({1404438819,345897}, NULL) = 0

gettimeofday({1404438819,346375}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

gettimeofday({1404438819,348358}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

gettimeofday({1404438819,349038}, NULL) = 0

gettimeofday({1404438819,349493}, NULL) = 0

gettimeofday({1404438819,349742}, NULL) = 0

gettimeofday({1404438819,350092}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

gettimeofday({1404438819,350832}, NULL) = 0

gettimeofday({1404438819,351126}, NULL) = 0

gettimeofday({1404438819,351435}, NULL) = 0

gettimeofday({1404438819,351851}, NULL) = 0

gettimeofday({1404438819,352268}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0

gettimeofday({1404438819,353079}, NULL) = 0

gettimeofday({1404438819,353494}, NULL) = 0

open("/etc/hosts",O_RDONLY)                         = 24   

--注意此条语句,表示当我们进行UTL_INADDR.get_host_address查询时,后台进程会去读取hosts文件,如果存在解析关系,便会返回信息显示,以下会验证如果没有解析的现象

fcntl64(24,F_GETFD)                    = 0

fcntl64(24,F_SETFD, FD_CLOEXEC)        = 0

fstat64(24,{st_mode=S_IFREG|0644, st_size=416, ...}) = 0

mmap2(NULL,4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x11a000

read(24,"# Do not remove the following li"..., 4096) = 416

close(24)                               = 0

munmap(0x11a000,4096)                  = 0

gettimeofday({1404438819,359617}, NULL) = 0

gettimeofday({1404438819,359908}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 441932}, ru_stime={0, 424935}, ...}) = 0

gettimeofday({1404438819,360851}, NULL) = 0

gettimeofday({1404438819,363074}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

times(NULL)                             = 429805700

gettimeofday({1404438819,366127}, NULL) = 0

write(11,"\1Q\0\0\6\0\0\0\0\0\20\27\30\252g\312N\23\337\326\212\21+%R>\367|xr\7\3"...,337) = 337

read(8,"\0\25\0\0\6\0\0\0\0\0\3\5\"\21\0\0\0\17\0\0\0", 2064) = 21

gettimeofday({1404438819,369259}, NULL) = 0

gettimeofday({1404438819,369906}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

times(NULL)                             = 429805701

gettimeofday({1404438819,370962}, NULL) = 0

gettimeofday({1404438819,371394}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

gettimeofday({1404438819,372304}, NULL) = 0

gettimeofday({1404438819,372741}, NULL) = 0

gettimeofday({1404438819,373117}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

times(NULL)                             = 429805701

gettimeofday({1404438819,375003}, NULL) = 0

gettimeofday({1404438819,375561}, NULL) = 0

gettimeofday({1404438819,376567}, NULL) = 0

write(11,"\0\204\0\0\6\0\0\0\0\0\4\1\0\0\0\37\0\1\1\0\0\0{\5\0\0\0\0\21\0\0\0"...,132) = 132

read(8,"\0\34\0\0\6\0\0\0\0\0\21i#L,\351\t\1\0\0\0\21\0\0\0\3\223$", 2064) =28

gettimeofday({1404438819,378886}, NULL) = 0

gettimeofday({1404438819,379120}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

times(NULL)                             = 429805702

gettimeofday({1404438819,380076}, NULL) = 0

gettimeofday({1404438819,380433}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

times(NULL)                             = 429805702

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

times(NULL)                             = 429805702

gettimeofday({1404438819,384734}, NULL) = 0

gettimeofday({1404438819,385145}, NULL) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

getrusage(RUSAGE_SELF,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0

times(NULL)                             = 429805703

gettimeofday({1404438819,386442}, NULL) = 0

gettimeofday({1404438819,386802}, NULL) = 0

gettimeofday({1404438819,387042}, NULL) = 0

write(11,"\0\21\0\0\6\0\0\0\0\0\t\1\0\0\0!\0", 17) = 17

read(8,

 

*缺少hosts解析时,UTL_INADDR.get_host_address查询现象

[root@org54~]# vi /etc/hosts    --将客户端FPA4GFVZXULBFCR解析地址注释掉

# Do notremove the following line, or various programs

# that requirenetwork functionality will fail.

127.0.0.1               localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6

192.168.56.11rac11

192.168.56.22rac22

192.168.56.31rac11-vip

192.168.56.32rac22-vip

192.168.2.11rac11-priv

192.168.2.22rac22-priv

192.168.56.7node1

192.168.56.8gc1

192.168.56.5  org54

#192.168.56.117FPA4GFVZXULBFCR

~

~

"/etc/hosts"19L, 417C written

 

客户端登陆



主机端:

SQL>col MACHINE for a50

SQL>select username,machine,program,sql_id from v$session where username is notnull

--查询到登陆主机

USERNAME       MACHINE                         PROGRAM                    SQL_ID

-------------------------------------------------------------             -------------

SYS            org54                          sqlplus@org54 (TNS V1-V3)

SCOTT          WORKGROUP\FPA4GFVZXULBFCR      sqlplus.exe

SYS            org54                          sqlplus@org54 (TNS V1-V3)   8w8k8ss45hm25

 

SQL>select UTL_INADDR.get_host_address(‘FPA4GFVZXULBFCR‘) from dual; --在没有hosts解析的情况下,报错了

selectUTL_INADDR.get_host_address(‘FPA4GFVZXULBFCR‘) from dual

       *

ERROR at line1:

ORA-29257:host FPA4GFVZXULBFCR unknown

ORA-06512: at"SYS.UTL_INADDR", line 19

ORA-06512: at"SYS.UTL_INADDR", line 40

ORA-06512: atline 1

 

[root@org54~]# vi /etc/hosts    --将客户端FPA4GFVZXULBFCR解析地址重新添加进去

# Do notremove the following line, or various programs

# that requirenetwork functionality will fail.

127.0.0.1               localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6

192.168.56.11rac11

192.168.56.22rac22

192.168.56.31rac11-vip

192.168.56.32rac22-vip

192.168.2.11rac11-priv

192.168.2.22rac22-priv

192.168.56.7node1

192.168.56.8gc1

192.168.56.5  org54

192.168.56.117FPA4GFVZXULBFCR

~

~

"/etc/hosts"19L, 417C written

 

SQL>select UTL_INADDR.get_host_address(‘FPA4GFVZXULBFCR‘) from dual;  --再次发起查询

UTL_INADDR.GET_HOST_ADDRESS(‘FPA4GFVZXULBFCR‘)

--------------------------------------------------------------------------------------------------------

192.168.56.117


小结:

    综上可知,使用UTL_INADDR Package在捕获SESSION IP时不再依赖数据库的信息。在触发器没有记录的情况下,可以实现捕捉其它SESSION的地址信息。


***********************************************声明************************************************

 原创作品,出自 “深蓝的blog” 博客,欢迎转载,转载时请务必注明出处(http://blog.csdn.net/huangyanlong)。

*****************************************************************************************************