DBMSSESSION package views the IP information of client connection in Oracle

Keywords: Programming Session Database

Thank you for your reference- http://bjbsair.com/2020-03-27/tech-info/7042/ Summary

It is well known that the machine name of the client is recorded in the v$session, but there is no IP. How to record the clinet ip?

 select username,machine,terminal,program,client_info,logon_time from v$session;

It can be seen that there is no IP record..

1. Use DBMS? Session process package

BEGIN   
  DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));   
END;

2. By trigger

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

3, test

When the client logs in, the corresponding IP information will be recorded in the client info column of v$session.

 select username,machine,terminal,program,client_info,logon_time from v$session order by logon_time desc;

Thank you for your reference- http://bjbsair.com/2020-03-27/tech-info/7042/ Summary

It is well known that the machine name of the client is recorded in the v$session, but there is no IP. How to record the clinet ip?

 select username,machine,terminal,program,client_info,logon_time from v$session;

It can be seen that there is no IP record..

1. Use DBMS? Session process package

BEGIN   
  DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));   
END;

2. By trigger

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

3, test

When the client logs in, the corresponding IP information will be recorded in the client info column of v$session.

 select username,machine,terminal,program,client_info,logon_time from v$session order by logon_time desc;

Thank you for your reference- http://bjbsair.com/2020-03-27/tech-info/7042/ Summary

It is well known that the machine name of the client is recorded in the v$session, but there is no IP. How to record the clinet ip?

 select username,machine,terminal,program,client_info,logon_time from v$session;

It can be seen that there is no IP record..

1. Use DBMS? Session process package

BEGIN   
  DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));   
END;

2. By trigger

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

3, test

When the client logs in, the corresponding IP information will be recorded in the client info column of v$session.

 select username,machine,terminal,program,client_info,logon_time from v$session order by logon_time desc;

Thank you for your reference- http://bjbsair.com/2020-03-27/tech-info/7042/ Summary

It is well known that the machine name of the client is recorded in the v$session, but there is no IP. How to record the clinet ip?

 select username,machine,terminal,program,client_info,logon_time from v$session;

It can be seen that there is no IP record..

1. Use DBMS? Session process package

BEGIN   
  DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));   
END;

2. By trigger

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

3, test

When the client logs in, the corresponding IP information will be recorded in the client info column of v$session.

 select username,machine,terminal,program,client_info,logon_time from v$session order by logon_time desc;

Posted by julieb on Fri, 27 Mar 2020 03:15:36 -0700