欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

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

程序员文章站 2022-04-24 08:32:28
...

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

范例截图如下：

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

三、触发器

通过触发器调用，新的客户端连接开启时自动触发，将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

客户端登陆

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

主机端：

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

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

相关标签： 查找 sessionIP 展开 --- 函数 触发器 查找

上一篇： 如何使用CSS3的渐变来实现文字颜色的渐变？

下一篇： Python常见格式化字符串方法小结（百分号与format方法）

推荐阅读