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


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


    一、userenv函数、sys_context函数

     --查看当前client会话的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')

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

    WORKGROUPFPA4GFVZXULBFCR

     

    二、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,"32363^!a200TL351 j|360345"...,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,"1Q6202730252g312N2333732621221+%R>367|xr73"...,337) = 337

    read(8,"25635"2117", 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,"2046413711{521"...,132) = 132

    read(8,"34621i#L,351 1213223$", 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,"216 1!", 17) = 17

    read(8,

     

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

    [root@org54~]# vi /etc/hosts    --将clientFPA4GFVZXULBFCR解析地址凝视掉

    # 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

     

    client登陆



    主机端:

    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          WORKGROUPFPA4GFVZXULBFCR      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    --将clientFPA4GFVZXULBFCR解析地址又一次加入进去

    # 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)。

    表述有错误之处,请您留言,不胜感激。

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




  • 相关阅读:
    hdu 1166 敌兵布阵 线段树区间修改、查询、单点修改 板子题
    POJ 1655 Balancing Act ( 树的重心板子题,链式前向星建图)
    P1268 树的重量(板子)
    P1896 [SCOI2005]互不侵犯 状压dp
    Delphi {$M +} 与{$TYPEINFO ON} 和 {$M -} 与{$TYPEINFO OFF}
    SQL 通过SQL语句检索字段类型、长度、主键
    随笔2020.11.10
    Delphi 操作Windows系统睡眠-防止系统/电脑 进入睡眠或关闭显示器
    Delphi WinAPI SetThreadExecutionState
    Delphi TOpenDialog的使用介绍以及如何动态创建和释放
  • 原文地址:https://www.cnblogs.com/mfrbuaa/p/5240779.html
Copyright © 2020-2023  润新知