How to Create a trigger to track Client IP addresses in the production database

Example:

$ Ifconfig-
Lo0: Flags = 1000849 <up, loopback, running, multicast, IPv4> MTU 8232 Index 1
Inet 127.0.0.1 netmask ff000000
Eri0: Flags = 1000843 <up, broadcast, running, multicast, IPv4> MTU 1500 index 2
Inet 172.28.65.15 netmask ffffff00 broadcast 172.28.65.255

$ Tnsping orcl

TNS Ping utility for Solaris: Version 9.2.0.4.0-production on 02-aug-2005 14:03:20

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
Used tnsnames adapter to resolve the alias
Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 172.28.65.16) (Port = 1521) (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = orcl )))
OK (10 msec)
$ Sqlplus wacos/OSS @ orcl

SQL> select sys_context ('userenv', 'IP _ address') from dual;

Sys_context ('userenv', 'IP _ address ')
--------------------------------------------------------------------------------
172.28.65.15

Create a trigger to track the IP address of the client:

SQL> Create or replace trigger on_logon_trigger

After logon Database

Begin

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

End;

/

SQL> exit

$ Sqlplus wacos/OSS @ orcl

SQL> select client_info from V $ session;

Client_info
----------------------------------------------------------------
172.28.65.15

1 rows selected.

In this way, we can see the IP address of the newly logged-on client in the client_info column of the V $ session view.