ホームページ >データベース >mysql チュートリアル >由查找sessionIP展开---函数、触发器、包

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

WBOYオリジナル: 2016-06-07 15:58:211074ブラウズ

由查找session IP 展开---函数、触发器、包一、userenv函数、sys_context函数 --查看当前客户端会话的session IP信息 SQLselect sys_context(userenv,ip_address) from dual; SYS_CONTEXT(USERENV,IP_ADDRESS) -------------------------------------------

由查找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

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

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

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

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

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

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）。

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

声明：

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

前の記事：再看数据库（2）视图次の記事：MongoDB下配置用户权限

続きを見る

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

一、userenv函数、sys_context函数

二、v$session视图中

三、触发器

四、使用UTL_INADDR Package

関連記事