[Client_ip] queries the client's IP information through v $ session

To view the client information (mainly IP addresses) connecting to the database, you can use the v $ session view, which contains several fields related to the client information:

OSUSER	VARCHAR2 (30)	Operating system client user name
PROCESS	VARCHAR2 (12)	Operating system client process ID
MACHINE	VARCHAR2 (64)	Operating system machine name
TERMINAL	VARCHAR2 (30)	Operating system terminal name
PROGRAM	VARCHAR2 (48)	Operating system program name
MODULE	VARCHAR2 (48)	Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTION	VARCHAR2 (32)	Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure
CLIENT_INFO	VARCHAR2 (64)	Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
CLIENT_IDENTIFIER	VARCHAR2 (64)	Client identifier of the session

The following shows the values of each field in a pl/SQL developer Connection SESSION (some irrelevant fields are removed from the display ):

SID 140

USERNAME SYSTEM

COMMAND 3

OWNERID 2147483644

TADDR

LOCKWAIT

STATUS ACTIVE

SERVER DEDICATED

SCHEMA #5

SCHEMANAME SYSTEM

OSUSER ballontt

PROCESS 5864: 4884

Machine workgroup \ BALLONTT-PC

TERMINAL BALLONTT-PC

PROGRAM plsqldev.exe

TYPE USER

Module pl/SQL Developer

Modulle_hash 1190136663

Action SQL window-New

ACTION_HASH 2127054360

CLIENT_INFO

CLIENT_IDENTIFIER

There is a session with SID 140 connected through pl/SQL developer. I open another window to create a session and view the client information of the 140 session in the v $ session view, the value of each field shows the machine name, OS name, and application of the host Where the client is located, but the client_info field is null and there is no IP information. It is inconvenient to find which client the session belongs. In some cases, the IP address of the client is displayed. In this way, the problem arises. When does this field have a client IP address?

 

Problem

When does client_info in the V $ session view have the IP address of the client?

 

Solution

1. dbms_application_info.set_client_info

In the above list, the CLIENT_INFO field is described as follows:

Information set byDBMS_APPLICATION_INFO.SET_CLIENT_INFOProcedure.

That is to say, the value of this field isDBMS_APPLICATION_INFO.SET_CLIENT_INFOStored procedures. When the client starts a session, it first executes the stored procedure and uses the IP address as the parameter of the stored procedure (that is, the client information ). The client_info field in the v $ session view shows the IP information defined in the stored procedure.

 

1) First, log on to a session on SQL * plus of the remote client.

C: \ Users \ ballontt> sqlplus system/oracle @ ballontt

 

SQL> select userenv ('sid ') from dual;

USERENV ('sid ')

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

139

SQL> select client_infofrom v $ session where sid = 139;

CLIENT_INFO

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

 

The client_info field of the remote session whose SID is 139 is empty.

 

2) run the command once in a session with SID 139.DBMS_APPLICATION_INFO.SET_CLIENT_INFOStored Procedure, and then queryClient_inf field o

SQL> begin

  2  dbms_application_info.set_client_info('192.68.10.10');

  3  end;

  4  /

PL/SQL The process has been completed successfully.

 

SQL> select client_info from v$session where sid=139;

CLIENT_INFO

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

192.68.10.10

Therefore, when the client application is used in the sessiondbms_application_infoThe package definesIPInformation, we can find the client_info field of the session in the v $ session attempt. Otherwise,v$sessionNo correspondingIPInformation. (dbms_application_infoThe package contains other processes with the type function:set_action/set_module/set_session_longops）

 

2. Create a trigger on the server

If the method in 1 is to use the dbms_application_info.set_client_info stored procedure on the client, we can also use this stored procedure to create a trigger triggered when a user logs on to the server.

 

1) Use the sys user to create a trigger

SQL> create or replace triggerlogon_on_database after logon database

2 begin

3 dbms_application_info.set_client_info (sys_context ('userenv', 'IP _ address '));

4 end;

5/

This trigger writes the SID and IP address of a session into the v $ session when a user logs on (that is, when a session is generated;

 

2) After the creation is successful, open a new session on the remote client and query the client_info field information of the v $ session type.

C: \ Users \ ballontt> sqlplussystem/oracle @ ballontt

SQL> select userenv ('sid ') from dual;

USERENV ('sid ')

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

138

 

SQL & gt; select sid, client_info fromv $ session where sid = 138;

SID CLIENT_INFO

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

138 192.168.10.1

The IP address of my remote client is 192.168.10.1.

 

3. utl_inaddr Stored Procedure

When viewing information on the Internet, someone mentioned that the two stored procedures in the utl_inaddr package can Query IP addresses based on the host name or the host name based on the Ip address.

 

SQL> desc utl_inaddr;

FUNCTION GET_HOST_ADDRESS RETURNS VARCHAR2

Argument Name Type In/Out Default?

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

HOST VARCHAR2 IN DEFAULT

 

FUNCTION GET_HOST_NAME RETURNS VARCHAR2

Argument Name Type In/Out Default?

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

IP VARCHAR2 IN DEFAULT

 

 

SQL> select utl_inaddr.get_host_address ('balontt01') from dual;

UTL_INADDR.GET_HOST_ADDRESS ('balontt01 ')

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

192.168.1.11

We can see that the IP address of the ballontt01 host is 192.168.1.11.

 

If the server is connected to the Internet, we can query the IP address corresponding to the host name on the Internet.

SQL> select utl_inaddr.get_host_address ('www .baidu.com ') from dual;

UTL_INADDR.GET_HOST_ADDRESS ('www. BAIDU. com ')

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

220.181.111.148

 

How it works: first obtain the domain name resolution server (resolv. conf. the conf file determines the resolution order. Because the default value is hosts file resolution, the/etc/hosts file will continue to be read at this time. If the Hosts file has a resolution relationship, the system returns information. If the Hosts file does not exist, the system continues to ask the DNS server for the resolution address. If the resolution fails, an error is returned.

SQL> selectutl_inaddr.get_host_address ('balontt001') from dual;

Selectutl_inaddr.get_host_address ('balontt001') from dual

*

ERROR at line 1:

ORA-29257: host ballontt001 unknown

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

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

ORA-06512: at line 1

 

If the IP address corresponding to ballontt001 is added to/etc/hosts, query again:

100.100.100.100 ballontt001

SQL> selectutl_inaddr.get_host_address ('balontt001') from dual;

 

UTL_INADDR.GET_HOST_ADDRESS ('balontt001 ')

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

100.100.100.100

In some production environments, when you try to use the utl_inaddr package in combination with the machine field in the v $ session view (the session host name) to query the session IP address, because the hosts file and DNS Server do not have the machine field information and cannot be parsed, an error is reported and the expected results cannot be obtained.

 

To sum up, as a DBA, if we want to obtain the IP address of the session, we can create a trigger in method 2.

Ballontt
2014/02/25 --- The End --- Weibo: weibo.com/ballontt If you need to reprint the data, please indicate the source and link. Thank you!