今天简单的对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博客 ” ,链接:http://blog.itpub.net/16754036/viewspace-722628/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16754036/viewspace-722628/