SQLServer Audit开启审核记录某表的DML操作

1.创建SQLServer审计

USE master

GO

CREATE SERVER AUDIT Audit_test_db

  TO FILE (FILEPATH = 'D:\sqlserver\sqlaudit', MAXSIZE = 1 GB)  --注意更改文件路径

  WITH (ON_FAILURE = CONTINUE)

GO

 

USE TEST_DB

GO

create table t_zyx(id int,name nvarchar(10))

go

 

CREATE DATABASE AUDIT SPECIFICATION audit_t_zyx FOR SERVER AUDIT Audit_test_db

 ADD(UPDATE,INSERT,DELETE ON object::dbo.t_zyx by public)

with (state=on)

GO

 

3.激活SQLServer审计

USE master

GO

ALTER SERVER AUDIT Audit_test_db

WITH (STATE=ON)

 

USE TEST_DB

GO

ALTER DATABASE AUDIT SPECIFICATION audit_t_zyx

WITH (STATE=ON)

 

4.查看审计数据

SELECT event_time ,

        succeeded ,

    server_principal_name ,

        [object_name] ,

        [statement]

FROM sys.fn_get_audit_file('D:\sqlserver\sqlaudit\*', NULL, NULL)

WHERE database_name = 'TEST_DB'

GO

 

 

5.DML语句测试

USE TEST_DB

GO

select * from t_zyx

go

insert t_zyx values(1,'zhong1')

go

insert t_zyx values(2,'zhong2')

go

update t_zyx set name='server' where id=1

go

select * from t_zyx

 

 

SELECT event_time ,

        succeeded ,

    server_principal_name ,

        [object_name] ,

        [statement]

FROM sys.fn_get_audit_file('D:\sqlserver\sqlaudit\*', NULL, NULL)

WHERE database_name = 'TEST_DB'

GO

 

 

SELECT *

FROM sys.fn_get_audit_file('D:\sqlserver\sqlaudit\*', default, default)

 

 

 

 

 

declare @i int

set @i=1

while @i<100

begin

set @i=@i+1

insert t_zyx values(@i,'xue')

end

 

 

SELECT event_time ,

       action_id,

        succeeded ,

        session_id,

        server_principal_id,

        session_server_principal_name,

        server_principal_name ,

        server_instance_name,

        database_name,

        [object_name] ,

        [statement]

FROM sys.fn_get_audit_file('D:\sqlserver\sqlaudit\*', default, default)

WHERE database_name = 'TEST_DB'

GO

 

 

 

6.附 查询语句

SELECT  [event_time] AS '触发审核的日期和时间' ,

        sequence_number AS '单个审核记录中的记录顺序' ,

        action_id AS '操作的ID' ,

        succeeded AS '触发事件的操作是否成功' ,

        permission_bitmask AS '权限掩码' ,

        is_column_permission AS '是否为列级别权限' ,

        session_id AS '发生该事件的会话的ID' ,

        server_principal_id AS '执行操作的登录上下文ID' ,

        database_principal_id AS '执行操作的数据库用户上下文ID' ,

        target_server_principal_id AS '执行GRANT/DENY/REVOKE 操作的服务器主体' ,

        target_database_principal_id AS '执行GRANT/DENY/REVOKE 操作的数据库主体' ,

        object_id AS '发生审核的实体的ID(服务器对象，DB,数据库对象，架构对象)' ,

        class_type AS '可审核实体的类型' ,

        session_server_principal_name AS '会话的服务器主体' ,

        server_principal_name AS '当前登录名' ,

        server_principal_sid AS '当前登录名SID' ,

        database_principal_name AS '当前用户' ,

        target_server_principal_name AS '操作的目标登录名' ,

        target_server_principal_sid AS '目标登录名的SID' ,

        target_database_principal_name AS '操作的目标用户' ,

        server_instance_name AS '审核的服务器实例的名称' ,

        database_name AS '发生此操作的数据库上下文' ,

        schema_name AS '此操作的架构上下文' ,

        object_name AS '审核的实体的名称' ,

        statement AS 'TSQL 语句（如果存在）' ,

        additional_information AS '单个事件的唯一信息，以XML 的形式返回' ,

        file_name AS '记录来源的审核日志文件的路径和名称' ,

        audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量'

      -- , user_defined_event_id AS '作为sp_audit_write 参数传递的用户定义事件ID' ,

      --  user_defined_information AS '于记录用户想要通过使用sp_audit_write 存储过程记录在审核日志中的任何附加信息'

FROM    sys.[fn_get_audit_file]('D:\sqlserver\sqlaudit\Audit_test_db_8CC59D08-35A1-4EA4-8244-343E3D20F1FE_0_131172529126400000.sqlaudit',

                                DEFAULT, DEFAULT)

WHERE   [event_time] BETWEEN '2016-09-01 11:02:00'

                     AND     '2016-09-04 11:18:00'

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2124465/，如需转载，请注明出处，否则将追究法律责任。