今天简单的对SQLServer的Audit功能测试了下,需要注意的是Audit功能仅在企业版或者开发版中有效.

实验中的Audit功能是监控dbo用户对表wh42.audittest上select和update操作.

以下是具体的操作步骤:

USE MASTER

GO

CREATE LOGIN WH42 WITH PASSWORD='WISON',CHECK_POLICY=OFF

CREATE DATABASE WISON

USE WISON

GO

CREATE USER WH42 FROM LOGIN WH42;

CREATE SCHEMA WH42 AUTHORIZATION WH42;

CREATE TABLE WH42.AUDITTEST(ID INT);

INSERT INTO WH42.AUDITTEST SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

USE MASTER

GO

CREATE SERVER AUDIT WISONAUDIT

TO FILE(FILEPATH='C:\');

ALTER SERVER AUDIT WISONAUDIT WITH (STATE=ON)

----之后可以在instance下的Security中Audit里看到WISONAUDIT

USE WISON

GO

CREATE DATABASE AUDIT SPECIFICATION WISONAUDIT_TABLE

FOR SERVER AUDIT WISONAUDIT

ADD(SELECT,UPDATE ON WH42.AUDITTEST BY DBO)

WITH (STATE=ON)

GO

------之后可以在WISON数据库下的Security中的Database Audit Specification下看到WisonAudit_Table

之后使用sa账号执行以下的语句

SELECT * FROM WH42.AUDITTEST

UPDATE WH42.AUDITTEST

SET ID=4

WHERE ID=3

DELETE FROM WH42.AUDITTEST WHERE ID=4

INSERT INTO WH42.AUDITTEST SELECT 5

CREATE TABLE A(ID INT)

INSERT INTO A SELECT ID FROM WH42.AUDITTEST

之后就可以在instance下的Security中Audit里看到WISONAUDIT上右键选择"View Audit Logs",则可以看到dbo用户对wh42.audittest的select和update操作.需要注意的是:带有where条件的delete操作也会被监控到,因为delete ....where....也会用到select操作的.

另外,其他用户对该表的select或者update操作不会被监控到.

ITPub博客

简单的Audit测试

最新文章