Get Client IP Address, Host name and Current User in Oracle and SQL Server

Today I show how to get Client IP Address, Host name and Current Data base user in Sql Server and Oracle.

It is very useful for security purposes and in case of any illegal or unauthorized activity is performed on your database so you can Track and Trace how, when  and who performed that  illegal or unauthorized activity.


Get Client IP Address , Host name and Current User in Oracle
Follow the below Query

select 
SYS_CONTEXT ('userenv', 'ip_address')   as IP_Address,
SYS_CONTEXT ('userenv', 'os_user')  as Os_USER,
SYS_CONTEXT ('userenv', 'host')  as HOST, 
SYS_CONTEXT ('userenv', 'current_user') as DB_USER
from dual;

After you have got client or User Information you can save these information in table at the time when a user is Inserting ,Updating and Deleting Records by using Triggers and any other way.
For more Values in SYS_CONTEXT function.
Get Client IP Address , Host name and Current User in SQL SERVER
Follow the below Query

--Create a function which return User/Client IP address.
CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);
 
    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;
 
    Return @IP_Address;
END;
--How to Access this Function.
select GetCurrentIP() from dual;
-You can also use this
select 
session_id,
net_transport,
protocol_type,
client_net_address,
client_tcp_port,
local_net_address,
connection_id
from 
sys.dm_exec_connections;

select 
session_id,
login_time,
host_name,
client_version
from 
sys.dm_exec_sessions;

For More Information about sys.dm_exec_connections and sys.dm_exec_sessions.