Introduction to the Author

• Author: Lucifer Liu, member of the Chinese DBA Consortium (ACDU).
• Currently mainly engaged in Oracle DBA work, has been engaged in Oracle database development work, mainly serving the production and manufacturing, Automotive Finance and other industries.
• Now has Oracle OCP, OceanBase OBCA certification, good at Oracle database maintenance development, backup recovery, installation migration, Linux automated operation and maintenance scripting, etc.

Preface

• For daily production, we usually connect to Oracle databases by listening. If you want to record the IP addresses of users who have visited the database, the general approach is not possible, but it can be done in some unconventional ways.

Realization

There are several ways to do this:

• Through triggers
• View the listen log
• DBMS_via PLSQL package SESSION

1 Trigger Implementation

• Create a separate tablespace to store records

sqlplus / as sysdba
create tablespace test datafile;

• Create session history table from v$session using ctas Specify tablespace test

sqlplus / as sysdba
create table session_history tablespace test as (select sid,username,program,machine,'000.000.000.000'ipadd,sysdate moditime from v$session where 0=1);

• Create a trigger to insert records into the session history table when a user is logged in

sqlplus / as sysdba
CREATE or replace trigger on_logon_trigger after logon
ON database begin
INSERT INTO session_history
SELECT  sid
       ,username
       ,program
       ,machine
       ,sys_context('userenv','ip_address')
       ,sysdate
FROM v$session
WHERE audsid = userenv('sessionid'); end;
/

• Local machine logs on through lucifer user

sqlplus lucifer/lucifer@10.211.55.110/orcl

• Query logon records of non-SYS users

sqlplus / as sysdba
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select * from session_history q where q.username not in ('SYS');

At this point, the first method has been described. You can see that the user's IP address to log in to the database is already available.

2 View the listen log

• View listening log location

su - oracle
lsnrctl status

• View the listen log

tail -100 log.xml

This is also a way to view login IP, but querying is too cumbersome to recommend.

3 PLSQL package DBMS_SESSION

• Delete triggers and tablespaces created in the first way for later testing

sqlplus / as sysdba
drop trigger on_logon_trigger;
drop tablespace test;

• Test if IP is still visible

sqlplus lucifer/lucifer@10.211.55.110/orcl
sqlplus / as sysdba
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select username,machine,terminal,program,client_info,logon_time from v$session;

From client_in the above diagram The info field is empty, so you can see that the v$session view does not record IP.
​

• Using DBMS_SESSION Package Settings Query IP Addresses

sqlplus / as sysdba
exec DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
/

• Host tests whether user logon can view IP address

sqlplus lucifer/lucifer@10.211.55.110/orcl
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sys_context('userenv','ip_address') from dual; 

• Change host client login to see if IP address can be queried
  

From the above experiment, it can be seen that the client can query the IP address, indicating that the plsql package is valid, but it will not be recorded in v$session, so a trigger needs to be created to implement it.

• Create triggers to log client login IP

sqlplus / as sysdba
create or replace trigger on_logon_trigger
  after logon on database
begin
  dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
/

• Query v$session to see if there is a recorded IP address

sqlplus / as sysdba
select username,machine,terminal,program,client_info,logon_time from v$session where username is not null;

You can see that the IP address has been recorded.

In these ways, we can track the IP addresses of logged-in users. As for what it does, make up your own mind.

That's the end of this sharing ~

If you find articles helpful to you, like compliments, collections, concerns, comments and one-click, four-line support, your support is my greatest motivation to create.