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 very Use full security purposes and in case of any illegal or unauthorized activity is perform on your Database so you can Track and Trace how, when and who is performed that illegal or unauthorized activity.

Get Client IP Adrres , 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 Adrres , 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 Funcation.
select GetCurrentIP() from dual;
-You can Also Uss 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.

Plese Feel Free to Socializer This Post

SOCIALIZE IT →

Aug 20, 2013