如何从 SQL Server 数据库获取客户端 IP 地址 [英] How to get a client IP address from an SQL Server database
问题描述
假设我有三台机器.一个是服务器，另外两个是客户端.服务器上已经安装了 SQL 数据库，另外两台机器上安装了 SQL Server 客户端.因此人们可以从他们的客户端计算机连接到 SQL Server 服务器数据库.
现在我想要，每当有人在表上执行 DML 操作时，就会触发一个触发器，并且从该触发器中，用户的客户端机器 IP 地址将存储在另一个表中.所以我搜索得很好，找到了一个脚本:
create Procedure sp_get_ip_address (@ip varchar(40) out)作为开始声明@ipLine varchar(200)声明@pos int设置无计数设置@ip = NULL创建表 #temp (ipLine varchar(200))插入 #temp exec master..xp_cmdshell 'ipconfig'选择@ipLine = ipLine来自#temp其中上 (ipLine) 像 '%IP ADDRESS%'if (isnull (@ipLine,'***') != '***')开始设置@pos = CharIndex(':',@ipLine,1);设置 @ip = rtrim(ltrim(substring (@ipLine ,@pos + 1 ,len (@ipLine) - @pos)))结尾删除表#temp取消计数结尾走声明@ip varchar(40)exec sp_get_ip_address @ip out打印@ip但是上面的脚本给出了安装数据库的服务器 IP 地址.我不是在寻找这个脚本.我得到了另一个运行良好的脚本.脚本是:
CREATE FUNCTION [dbo].[GetCurrentIP] ()返回 varchar(255)作为开始声明@IP_Address varchar(255);选择@IP_Address = client_net_address从 sys.dm_exec_connectionsWHERE Session_id = @@SPID;返回@IP_Address;结尾选择 dbo.GetCurrentIP()更多脚本
<小时>声明@host varchar(255)SET @host = 主机名()创建表 #Results (结果 varchar(255))声明@cmd varchar(260)SET @cmd = 'ping' + @host插入#ResultsEXEC master..xp_cmdshell @cmdSELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging', '') As [client], host_name() 作为 [host_name()]来自#Results像Ping%"这样的结果删除表#Results--****************************************************
-- DROP 触发器和/或表(如果存在)IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGINDROP TRIGGER myTable_InsertUpdate结尾IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN删除表 myTable结尾-- 创建表；注意审计字段创建表 myTable (id int PRIMARY KEY NOT NULL IDENTITY(1,1), field1 字符(1),changed_by_ip 字符(15),changed_by_host 字符(15), datetime_changed 日期时间)走-- 为更新和插入创建触发器创建触发器 myTable_InsertUpdate在我的表上FOR 插入，更新作为声明@ipLine varchar(255)声明@pos int声明@ip char(15)-- 临时表创建创建表#ip (ipLine varchar(255))-- 将ipconfig的返回插入到临时表中INSERT #ip EXEC master..xp_cmdshell 'ipconfig'-- 找到包含 IP 地址的行并将其分配给变量设置@ipLine = (选择 ipLine从#ipWHERE ipLine LIKE '%IP 地址%')-- 如果 IP 地址已知IF Coalesce(@ipLine, '***') <>'***' 开始--从字符串的END处查找冒号的索引SET @pos = CharIndex(':', Reverse(@ipLine), 1) - 1--修剪字符串末尾的IPSET @ip = Right(@ipLine, @pos)--删除任何尾随或前导空格SET @ip = RTrim(LTrim(@ip))结尾-- 删除临时表删除表#ip-- 根据更新的值更新审计字段更新我的表SET changed_by_ip = @ip, datetime_changed = GetDate(), changed_by_host = host_name()WHERE id IN (SELECT id FROM 插入)走-- 插入一些测试值INSERT INTO myTable (field1) VALUES ('a')INSERT INTO myTable (field1) VALUES ('a')-- 显示初始值SELECT * FROM myTable-- 更新字段之一更新我的表SET 字段 1 = 'b'哪里 id = 2-- 显示更改的值.SELECT * FROM myTable-- 注意 datetime_changed 的变化，其中 id = 2走——最后；自己收拾DROP TRIGGER myTable_InsertUpdate删除表 myTable这适用于我安装的 SQL Server 2000 和 SQL Server 2005.
我可以从触发器调用这个 GetCurrentIP() 函数吗?
GetCurrentIP() 函数是否兼容所有 SQL Server 版本?例如，SQL Server 2000
如果任何用户连接到数据库并通过第三方应用程序(如自定义应用程序)进行 DML 操作，此脚本能否获取客户端 IP 地址?
大问题 :) 但答案真的很短.
<块引用>- 我可以从触发器调用这个 GetCurrentIP() 函数吗?
是的.您可以直接在触发器中使用其中的代码来代替此函数.
<块引用>
- GetCurrentIP() 函数是否兼容所有 SQL Server 版本?例如，SQL Server 2000?
没有.仅适用于 SQL Server 2005 及更高版本.因为 sys.dm_exec_sessions 在 SQL Server 2000 中不可用.对于 SQL Server，你将不得不采用一些不同的技术
<块引用>
- 如果任何用户连接到数据库并通过第三方应用程序(如自定义应用程序)进行 DML 操作，此脚本能否获取客户端 IP 地址?
是的.客户端应用程序是客户端应用程序，无论是第三方应用程序还是自制应用程序 :) SQL Server 不会区分.
Suppose I have three machines. One is server and the other two are clients. An SQL database has been installed on the server and an SQL Server client is installed on the other two machines. So people can connect to the SQL Server server database from their client machine.
Now I want, whenever anybody do the DML operation on table, then a trigger will fire and from that trigger the user's client machine IP address will be stored in another table. So I search good and found a script which is:
create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
Declare @ipLine varchar(200)
Declare @pos int
set nocount on
set @ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@ipLine,'***') != '***')
begin
set @pos = CharIndex (':',@ipLine,1);
set @ip = rtrim(ltrim(substring (@ipLine ,
@pos + 1 ,
len (@ipLine) - @pos)))
end
drop table #temp
set nocount off
end
go
declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip
But this above script gives a server IP address where the database has been installed. I am not looking for this script. I got another script which works fine. The script is:
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
SELECT dbo.GetCurrentIP()
More scripts
DECLARE @host varchar(255)
SET @host = host_name()
CREATE TABLE #Results (
Results varchar(255)
)
DECLARE @cmd varchar(260)
SET @cmd = 'ping ' + @host
INSERT INTO #Results
EXEC master..xp_cmdshell @cmd
SELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '') As [client]
, host_name() As [host_name()]
FROM #Results
WHERE Results LIKE 'Pinging%'
DROP TABLE #Results
--*********************************************
-- DROP trigger and/or table if they exist
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGIN
DROP TRIGGER myTable_InsertUpdate
END
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN
DROP TABLE myTable
END
-- Create out table; note the audit fields
CREATE TABLE myTable (
id int PRIMARY KEY NOT NULL IDENTITY(1,1)
, field1 char(1)
, changed_by_ip char(15)
, changed_by_host char(15)
, datetime_changed datetime
)
GO
-- Create trigger for update and insert
CREATE TRIGGER myTable_InsertUpdate
ON myTable
FOR insert, update
AS
DECLARE @ipLine varchar(255)
DECLARE @pos int
DECLARE @ip char(15)
-- Temporary table creation
CREATE TABLE #ip (
ipLine varchar(255)
)
-- Insert the return of ipconfig into the temp table
INSERT #ip EXEC master..xp_cmdshell 'ipconfig'
-- Find the line which contains the IP address and assign it to a variable
SET @ipLine = (
SELECT ipLine
FROM #ip
WHERE ipLine LIKE '%IP Address%'
)
-- If the IP address is known
IF Coalesce(@ipLine, '***') <> '***' BEGIN
--Find the index of the colon from the END of the string
SET @pos = CharIndex(':', Reverse(@ipLine), 1) - 1
--Trim the IP off the end of the string
SET @ip = Right(@ipLine, @pos)
--Remove any trailing or leading white space
SET @ip = RTrim(LTrim(@ip))
END
-- Drop the temp table
DROP TABLE #ip
-- Update the audit fields based on the value being updated
UPDATE myTable
SET changed_by_ip = @ip
, datetime_changed = GetDate()
, changed_by_host = host_name()
WHERE id IN (SELECT id FROM inserted)
GO
-- Insert some test values
INSERT INTO myTable (field1) VALUES ('a')
INSERT INTO myTable (field1) VALUES ('a')
-- Display initial values
SELECT * FROM myTable
-- Update one of the fields
UPDATE myTable
SET field1 = 'b'
WHERE id = 2
-- Display changed values.
SELECT * FROM myTable
-- Notice the change in datetime_changed where id = 2
GO
-- And finally; clean up after ourselves
DROP TRIGGER myTable_InsertUpdate
DROP TABLE myTable
This works on my install of SQL Server 2000 and SQL Server 2005.
Can I call this GetCurrentIP() function from trigger?
Will the GetCurrentIP() function be compatible for all SQL Server versions? For example, SQL Server 2000
If any user connects to the database and does a DML operation through third-party applications like a custom application, can this script get the client IP address?
Big Question :) But the answers are really short.
- Can I call this GetCurrentIP() function from trigger?
Yes. Instead of this function you can directly use the code inside it in your trigger.
- Will the GetCurrentIP() function be compatible for all SQL Server versions? For example, SQL Server 2000?
No. Only for SQL Server 2005 and later. Because sys.dm_exec_sessions was not available in SQL Server 2000. For SQL Server you will have to adopt some different technique
- If any user connects to the database and does a DML operation through third-party applications like a custom application, can this script get the client IP address?
Yes. A client application is a client application whether it's a third-party one or home made :) SQL Server won't differentiate.
这篇关于如何从 SQL Server 数据库获取客户端 IP 地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
