怎么查找出直接操作数据库的那个人

2012-12-19

如何查找出直接操作数据库的那个人？数据有点不可思议，感觉有人直接操作数据库，对记录进行修改或删除，能查

如何查找出直接操作数据库的那个人？
数据有点不可思议，感觉有人直接操作数据库，对记录进行修改或删除，能查询出来是谁操作的吗，比如电脑IP等？
很多时候即便是有人操作了，但是对方不承认，所以现在要找证据。
[最优解释]
下面的语句是一个触发器，创建一个数据库用于存放账号对数据库各个表的DDL操作的记录，语句要额外新建一个数据库的，然后创建就可以了，每个账号对整个库的DDL操作都会做记录。

CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修改、删除动作
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW,
        ALTER_VIEW, DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
    BEGIN
        SET NOCOUNT ON ;
        DECLARE @EventData XML = EVENTDATA() ;--返回有关服务器或数据库事件的信息，以XML格式保存。
        DECLARE @ip VARCHAR(32) = ( SELECT  client_net_address
                                    FROM    sys.dm_exec_connections
                                    WHERE   session_id = @@SPID
                                  ) ;

        INSERT  AuditDB.dbo.DDLEvents
                ( EventType ,
                  EventDDL ,
                  EventXML ,
                  DatabaseName ,
                  SchemaName ,
                  ObjectName ,
                  HostName ,
                  IPAddress ,
                  ProgramName ,
                  LoginName
                )
                SELECT  @EventData.value('(/EVENT_INSTANCE/EventType)[1]',
                                         'NVARCHAR(100)') ,
                        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
                                         'NVARCHAR(MAX)') ,

                        @EventData ,
                        DB_NAME() ,
                        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',
                                         'NVARCHAR(255)') ,
                        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
                                         'NVARCHAR(255)') ,
                        HOST_NAME() ,
                        @ip ,
                        PROGRAM_NAME() ,
                        SUSER_SNAME() ;
    END

GO

DISABLE TRIGGER [DDLTriggertTrace] ON DATABASE
GO

[其他解释]
2008以上可以使用CDC功能跟踪
[其他解释]
SQL Server 2008 可以查看到登陆IP
打开“SQL Server Management Studio” ——选择“管理”——选择“SQL Server日志”
里面可以看到有很多日志的，你就选“当前”，弹出“日志查看器”对话框，里面会有详细记录。
例如：另外一台电脑登陆我的数据库，结果失败了。日志里就会显示登陆电脑的IP地址的：
日期2012/5/4 15:22:23
日志SQL Server (当前 - 2012/5/4 8:36:00)

源登录

消息
Login failed for user 'sa'. 原因: 密码与所提供的登录名不匹配。 [客户端: 192.168.1.125]
这个是登陆失败的例子。你也可以通过访问系统表来查看登陆IP：
SELECT session_id, client_net_address, client_tcp_port
FROM sys.dm_exec_connections;
-- 结果
session_idclient_net_addressclient_tcp_port
51<local machine>NULL
52<local machine>NULL
53<local machine>NULL
54<local machine>NULL
57<local machine>NULL
56<local machine>NULL
55<local machine>NULL
58192.168.1.12554174
60<local machine>NULL
61<local machine>NULL
[其他解释]
顶楼上两位大神！
[其他解释]
不会是DBA兄，这方面我就真的一片空白了
[其他解释]
工作需要，不懂也得学
[其他解释]
感谢，大力支持
[其他解释]

引用:

工作需要，不懂也得学

貌似哪多有你.
[其他解释]
08以上。。。。。。
[其他解释]
想问下楼主，这个只是针对ddl 事件，如果有人经常更改某个表里面的数据，我们怎么捕获他的ip地址或者是一些信息呢！
[其他解释]

引用:

下面的语句是一个触发器，创建一个数据库用于存放账号对数据库各个表的DDL操作的记录，语句要额外新建一个数据库的，然后创建就可以了，每个账号对整个库的DDL操作都会做记录。
SQL code

CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修改、删除动作
FOR CREATE_PROCEDURE, ALT……

请问，在同服务器，新建一个新数据库，然后将CREATE TRIGGER [DDLTriggertTrace] ON DATABASE中的DATABASE替换成目标数据库就可以么？
[其他解释]

引用:

引用:

下面的语句是一个触发器，创建一个数据库用于存放账号对数据库各个表的DDL操作的记录，语句要额外新建一个数据库的，然后创建就可以了，每个账号对整个库的DDL操作都会做记录。
SQL code

CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修改、删除动作
FOR CREAT……

不用，ON DATABASE是固定格式，你只要把这个触发器在所有需要的库上执行，那么这些苦一定有更改，就会插入到指定的一个库中，所以这方面很省事。
[其他解释]

引用:

引用:

引用:

下面的语句是一个触发器，创建一个数据库用于存放账号对数据库各个表的DDL操作的记录，语句要额外新建一个数据库的，然后创建就可以了，每个账号对整个库的DDL操作都会做记录。
SQL code

CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修……

追问一下，是不是我新建一个数据库A后，然后把你写的存储过程在所需要的库B上执行一下，然后在我库A中就会自动记录对库B中的任何操作记录，是存在库A中的AuditDB.dbo.DDLEvents表吗？
[其他解释]
所有的数据都存在AuditDB库的dbo.DDLEvents表中，无论你创建多少个用户数据库，只要执行了语句就可以了怎么还没结贴啊。我都忘了有这个存在了
[其他解释]

引用:

所有的数据都存在AuditDB库的dbo.DDLEvents表中，无论你创建多少个用户数据库，只要执行了语句就可以了怎么还没结贴啊。我都忘了有这个存在了

新建的数据库名一定得是AuditDB是吧。。明天我再试试。。下班了。。
[其他解释]
其实不是，AuditDB是我用来收集监控信息的数据库而已。如果你不改我的代码，那么你就要有这个库在服务器上。然后我的脚本放到你需要监控的库上。
[其他解释]
另外补充：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
[其他解释]

引用:

其实不是，AuditDB是我用来收集监控信息的数据库而已。如果你不改我的代码，那么你就要有这个库在服务器上。然后我的脚本放到你需要监控的库上。

我已经新建一个库：CREATE DATABASE AuditDB
然后把你说的触发器在我需要的库里执行，会报错：
消息 139，级别 15，状态 1，过程 DDLTriggertTrace，第 0 行
不能向局部变量赋予默认值。
消息 102，级别 15，状态 1，过程 DDLTriggertTrace，第 9 行
'(' 附近有语法错误。
消息 137，级别 15，状态 2，过程 DDLTriggertTrace，第 26 行
必须声明标量变量 "@EventData"。
消息 1088，级别 16，状态 119，第 2 行
找不到对象 "DDLTriggertTrace"，因为它不存在或者您没有所需的权限。

[其他解释]
上面这个是建立表，可以执行，没任何问题。
但是你说的那个触发器在任何一个库中运行都会报错。
我用的是SQL SERVER 2005的。
[其他解释]
这里里面用到的表，你的是多少版本？我这个脚本在开发服务器、生产服务器上执行都没出现过任何问题，刚刚又试了一下，也还是没有问题

USE [AuditDB]
 GO 
 
 
 /****** Object:  Table [dbo].[DDLEvents]    Script Date: 10/23/2012 13:03:40 ******/
 SET ANSI_NULLS ON
 GO
 
 SET QUOTED_IDENTIFIER ON
 GO
 
 SET ANSI_PADDING ON
 GO
 
 CREATE TABLE [dbo].[DDLEvents](
 [EventDate] [datetime] NOT NULL,
 [EventType] [nvarchar](64) NULL,
 [EventDDL] [nvarchar](max) NULL,
 [EventXML] [xml] NULL,
 [DatabaseName] [nvarchar](255) NULL,
 [SchemaName] [nvarchar](255) NULL,
 [ObjectName] [nvarchar](255) NULL,
 [HostName] [varchar](64) NULL,
 [IPAddress] [varchar](32) NULL,
 [ProgramName] [nvarchar](255) NULL,
 [LoginName] [nvarchar](255) NULL
 ) ON [PRIMARY]
 
 GO
 
 SET ANSI_PADDING OFF
 GO
 
 ALTER TABLE [dbo].[DDLEvents] ADD  DEFAULT (getdate()) FOR [EventDate]
 GO

[其他解释]

引用:

引用:

引用:

下面的语句是一个触发器，创建一个数据库用于存放账号对数据库各个表的DDL操作的记录，语句要额外新建一个数据库的，然后创建就可以了，每个账号对整个库的DDL操作都会做记录。
SQL code

CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修……

请教一下，这个数据库和被更改的数据库是在同一个服务器上，那么如果有人操作被跟踪数据库后，然后删除这里面的操作记录呢？还是说这里面的记录无法删除？
[其他解释]

引用:

引用:

工作需要，不懂也得学
貌似哪多有你.

我空上脑子啊！
[其他解释]
22楼考虑的是往后的，现在是要先把那个追踪触发器给搞出来呀。。那我的2005岂不是不能搞了。。不能转换一下版本格式么？
[其他解释]
我的好像是只能2008以上的才能用。另外，如果你不控制权限，那么所有跟踪都会存在漏洞

引用:

引用:

引用:

引用:

下面的语句是一个触发器，创建一个数据库用于存放账号对数据库各个表的DDL操作的记录，语句要额外新建一个数据库的，然后创建就可以了，每个账号对整个库的DDL操作都会做记录。
SQL code

CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
-……

[其他解释]
那要研究2005内部机制了，这里用的是2008新特性——DDL触发器。我也没有05，不知道有啥功能可用。
[其他解释]
哈哈。我还以为这个不可以查询呢
[其他解释]
你把这句去掉试试：
DISABLE TRIGGER [DDLTriggertTrace] ON DATABASE

我这个在最少30个库上部署的，都没发现问题。
[其他解释]

引用:

那要研究2005内部机制了，这里用的是2008新特性——DDL触发器。我也没有05，不知道有啥功能可用。

在20008的服务器端测试了一下
按照你的要求，我先创建了一个AuditDB数据库，然后在此数据库中创建了一张DDLEvents，之后把触发器[DDLTriggertTrace]在需要监控的库A中执行了一次。
我是这么试验的：在库A中create了一张表，然后drop掉。怎么表DDLEvents中一条记录都没，是怎么回事呢？
不是说会记录直接操作数据库的记录吗？
[其他解释]
终于可以了，只可惜只能跟踪DDL操作记录，如果能增加一个DML操作记录的，那就完美了。
[其他解释]
感谢，大力支持!!
[其他解释]

DML的话用2008的cdc功能

热点排行