This is the 25th day of my participation in the August Genwen Challenge.More challenges in August

preface

  • Daily work production, we generally connect to Oracle database through listening. If you want to log the IP addresses of users who have accessed the database, you can’t do this normally, but you can do it in some unconventional ways.

implementation

Here are a few ways:

  • Through triggers
  • Viewing listener Logs
  • Through the PLSQL package DBMS_SESSION

1 Trigger implementation

  • Create a separate tablespace to store records
sqlplus / as sysdba
create tablespace test datafile;
Copy the code

  • Create session history table from v$session by ctAS
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 where0 = 1);Copy the code

  • Create a trigger to insert records into the Session history table when a user logs 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;
/
Copy the code

  • Log in to the local host as user Lucifer
Up sqlplus lucifer/[email protected] / formerCopy the code

  • Example Query the login 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');
Copy the code

So far, the first method has been introduced. As you can see, the IP address of the user logging in to the database can already be recorded.

2 View listening logs

  • View the listener log location
su - oracle
lsnrctl status
Copy the code

  • Viewing listener Logs
tail -100 log.xml
Copy the code

This method can also be used to view the login IP address, but it is too troublesome to query, so it is not recommended.

3 DBMS_SESSION PLSQL pack

  • For later testing, delete the triggers and table Spaces created in the first way
sqlplus / as sysdba
drop trigger on_logon_trigger;
drop tablespace test;
Copy the code

  • Tests to see if the IP is still visible
Sqlplus Lucifer /[email protected]/orcl sqlplus/as sysdba ALTER sessionset nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select username,machine,terminal,program,client_info,logon_time from v$session;
Copy the code

From the empty client_info field in the figure above, you can see that the V $session view does not record IP. ​

  • The IP address can be queried using the DBMS_SESSION package setting
sqlplus / as sysdba
exec DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV'.'IP_ADDRESS'));
/
Copy the code

  • The host tests whether the user can view the IP address after logging in
Up sqlplus lucifer/[email protected] / former alter sessionset nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sys_context('userenv'.'ip_address') from dual; 
Copy the code

  • Log in to another host client and check whether the 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 effective, but will not be recorded in v$session, need to create a trigger to achieve.

  • Create a trigger and record the client login IP address
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;
/
Copy the code

  • Query v$session to check whether the IP address is recorded
sqlplus / as sysdba
select username,machine,terminal,program,client_info,logon_time from v$session where username is not null;
Copy the code

As you can see, the IP address has already been logged.

In the preceding methods, you can trace and record the IP address of the login user. As for what it does, you can imagine.

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.