SQLServer教程FG033-SQLServer审计日志实战
目录大纲
内容简介
本文档基于SQLServer官方文档的审计日志内容，结合生产环境实际情况，详细讲解SQLServer审计日志的配置、管理、分析等内容。风哥教程参考SQLServer官方文档Auditing、Security等相关章节。
Part01-基础概念与理论知识
1.1 SQLServer审计日志概念
SQLServer审计日志的概念：
- 审计日志是记录SQLServer实例和数据库活动的重要工具
- 可以记录用户登录、权限变更、数据访问等操作
- 用于安全合规、故障排查、性能分析等场景
- 是企业级数据库安全的重要组成部分
更多视频教程www.fgedu.net.cn
1.2 SQLServer审计日志类型
SQLServer审计日志类型：
- 服务器审计：记录服务器级别的活动
- 数据库审计：记录数据库级别的活动
- 登录审计：记录用户登录和注销活动
- 权限审计：记录权限变更活动
- 数据访问审计：记录数据访问活动
学习交流加群风哥微信: itpux-com
1.3 SQLServer审计日志架构
SQLServer审计日志架构：
- 服务器审计：定义审计日志的存储位置和属性
- 数据库审计规范：定义要审计的数据库活动
- 审计操作组：定义要审计的具体操作
- 审计目标：存储审计日志的位置（文件、Windows事件日志、Azure事件中心）
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 SQLServer审计日志规划原则
审计日志规划原则：
- 根据合规要求确定审计范围
- 平衡审计粒度和性能影响
- 规划审计日志存储和保留策略
- 建立审计日志监控和分析机制
- 定期备份审计日志
风哥提示：生产环境应根据业务需求和合规要求配置审计日志
2.2 SQLServer审计日志存储策略
审计日志存储策略：
- 存储位置：使用独立的存储设备，避免影响数据库性能
- 存储格式：使用文件存储，便于管理和分析
- 保留期限：根据合规要求设置保留期限（如30天、90天、1年）
- 备份策略：定期备份审计日志，防止丢失
- 清理策略：定期清理过期的审计日志，释放存储空间
更多学习教程公众号风哥教程itpux_com
2.3 SQLServer审计日志性能影响
审计日志性能影响：
- 审计日志会增加SQLServer的IO和CPU开销
- 审计粒度越细，性能影响越大
- 合理配置审计范围，减少不必要的审计
- 使用异步审计，减少对主业务的影响
- 定期维护审计日志，避免存储空间不足
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 SQLServer审计日志配置
审计日志配置：
— 1. 创建文件存储的服务器审计
CREATE SERVER AUDIT [ServerAudit]
TO FILE (
FILEPATH = ‘E:\SQLServer\Audit\’,
MAXSIZE = 1024 MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = OFF
);
GO
— 2. 创建Windows事件日志存储的服务器审计
CREATE SERVER AUDIT [ServerAuditEventLog]
TO APPLICATION_LOG;
GO
— 步骤2：启用服务器审计
ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
GO
— 步骤3：创建数据库审计规范
— 1. 创建数据库审计规范
USE fgedudb;
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec]
FOR SERVER AUDIT [ServerAudit]
ADD (SELECT, INSERT, UPDATE, DELETE ON fgedu.large_table BY public),
ADD (EXECUTE ON fgedu.sp_test BY public),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP);
GO
— 步骤4：启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec] WITH (STATE = ON);
GO
— 步骤5：创建服务器审计规范
— 1. 创建服务器审计规范
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec]
FOR SERVER AUDIT [ServerAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP);
GO
— 步骤6：启用服务器审计规范
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpec] WITH (STATE = ON);
GO
— 步骤7：配置审计选项
— 1. 查看当前审计配置
SELECT
name,
audit_file_path,
max_size,
max_rollover_files,
state_desc
FROM sys.server_audits;
GO
— 2. 修改审计配置
ALTER SERVER AUDIT [ServerAudit]
WITH (
MAXSIZE = 2048 MB,
MAX_ROLLOVER_FILES = 20
);
GO
— 3. 禁用审计
ALTER SERVER AUDIT [ServerAudit] WITH (STATE = OFF);
GO
— 4. 启用审计
ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
GO
执行结果：
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
name audit_file_path max_size max_rollover_files state_desc
————- ————————- ——— —————— ———–
ServerAudit E:\SQLServer\Audit\ 1024 10 ON
ServerAuditEventLog NULL NULL NULL OFF
(2 rows affected)
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
3.2 SQLServer审计日志管理
审计日志管理：
— 1. 使用fn_get_audit_file函数查看审计日志
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
ORDER BY event_time DESC;
GO
— 2. 查看特定类型的审计日志
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id = ‘LGIF’ — 登录失败
ORDER BY event_time DESC;
GO
— 步骤2：管理审计日志文件
— 1. 查看审计日志文件信息
SELECT
audit_file_path,
last_event_time,
audit_file_size
FROM sys.dm_server_audit_status;
GO
— 2. 清理过期的审计日志文件
— 使用Windows命令清理过期文件
EXEC xp_cmdshell ‘forfiles /p “E:\SQLServer\Audit” /m “*.sqlaudit” /d -30 /c “cmd /c del @path”‘;
GO
— 步骤3：备份审计日志
— 1. 创建审计日志备份作业
USE msdb;
GO
EXEC dbo.sp_add_job
@job_name = N’BackupAuditLogs’,
@enabled = 1,
@description = N’备份审计日志’;
GO
EXEC dbo.sp_add_jobstep
@job_name = N’BackupAuditLogs’,
@step_name = N’Copy Audit Files’,
@subsystem = N’CmdExec’,
@command = N’xcopy “E:\SQLServer\Audit\*.sqlaudit” “F:\Backup\Audit\” /Y /D’,
@database_name = N’master’;
GO
EXEC dbo.sp_add_jobschedule
@job_name = N’BackupAuditLogs’,
@name = N’Daily’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@active_start_time = 000000,
@active_end_time = 235959;
GO
— 步骤4：监控审计日志
— 1. 创建审计日志监控作业
USE msdb;
GO
EXEC dbo.sp_add_job
@job_name = N’MonitorAuditLogs’,
@enabled = 1,
@description = N’监控审计日志’;
GO
EXEC dbo.sp_add_jobstep
@job_name = N’MonitorAuditLogs’,
@step_name = N’Check Audit Logs’,
@subsystem = N’TSQL’,
@command = N’
— 检查登录失败
DECLARE @failed_logins INT;
SELECT @failed_logins = COUNT(*)
FROM sys.fn_get_audit_file(”E:\SQLServer\Audit\ServerAudit_*.sqlaudit”, DEFAULT, DEFAULT)
WHERE action_id = ”LGIF” AND event_time > DATEADD(HOUR, -1, GETDATE());
IF @failed_logins > 5
BEGIN
RAISERROR(”登录失败次数过多: %d”, 16, 1, @failed_logins);
END;
— 检查权限变更
DECLARE @permission_changes INT;
SELECT @permission_changes = COUNT(*)
FROM sys.fn_get_audit_file(”E:\SQLServer\Audit\ServerAudit_*.sqlaudit”, DEFAULT, DEFAULT)
WHERE action_id IN (”GRANT”, ”REVOKE”, ”DENY”) AND event_time > DATEADD(HOUR, -1, GETDATE());
IF @permission_changes > 0
BEGIN
RAISERROR(”检测到权限变更: %d”, 10, 1, @permission_changes);
END;
‘,
@database_name = N’master’;
GO
EXEC dbo.sp_add_jobschedule
@job_name = N’MonitorAuditLogs’,
@name = N’Every 15 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 15,
@active_start_time = 000000,
@active_end_time = 235959;
GO
执行结果：
————————- ——– ——— ——————– ——————— ————- ——————————————————————————–
2025-04-08 10:00:00.000 LGIF 0 fgedu_user NULL NULL NULL
2025-04-08 09:59:00.000 LGIS 1 sa NULL NULL NULL
2025-04-08 09:58:00.000 GRANT 1 sa fgedu_user large_table GRANT SELECT ON fgedu.large_table TO fgedu_user
(3 rows affected)
event_time action_id succeeded server_principal_name database_principal_name object_name statement
————————- ——– ——— ——————– ——————— ————- ——————————————————————————–
2025-04-08 10:00:00.000 LGIF 0 fgedu_user NULL NULL NULL
(1 row affected)
audit_file_path last_event_time audit_file_size
————————- ————————- —————-
E:\SQLServer\Audit\ServerAudit_20250408_100000.sqlaudit 2025-04-08 10:00:00.000 1024000
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
3.3 SQLServer审计日志分析
审计日志分析：
— 1. 统计登录成功和失败次数
SELECT
action_id,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘LGIS’, ‘LGIF’)
GROUP BY action_id
ORDER BY count DESC;
GO
— 2. 分析登录失败原因
SELECT
server_principal_name,
COUNT(*) AS failed_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id = ‘LGIF’
GROUP BY server_principal_name
ORDER BY failed_count DESC;
GO
— 步骤2：分析权限变更
— 1. 统计权限变更类型
SELECT
action_id,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘GRANT’, ‘REVOKE’, ‘DENY’)
GROUP BY action_id
ORDER BY count DESC;
GO
— 2. 分析权限变更对象
SELECT
object_name,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘GRANT’, ‘REVOKE’, ‘DENY’) AND object_name IS NOT NULL
GROUP BY object_name
ORDER BY count DESC;
GO
— 步骤3：分析数据访问
— 1. 统计数据访问操作
SELECT
action_id,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’)
GROUP BY action_id
ORDER BY count DESC;
GO
— 2. 分析数据访问对象
SELECT
object_name,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’) AND object_name IS NOT NULL
GROUP BY object_name
ORDER BY count DESC;
GO
— 步骤4：分析异常活动
— 1. 检测异常登录
SELECT
server_principal_name,
COUNT(*) AS login_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id = ‘LGIS’ AND event_time > DATEADD(DAY, -1, GETDATE())
GROUP BY server_principal_name
HAVING COUNT(*) > 100
ORDER BY login_count DESC;
GO
— 2. 检测异常数据访问
SELECT
server_principal_name,
object_name,
COUNT(*) AS access_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’) AND event_time > DATEADD(HOUR, -1, GETDATE())
GROUP BY server_principal_name, object_name
HAVING COUNT(*) > 1000
ORDER BY access_count DESC;
GO
— 步骤5：创建审计日志分析报告
— 1. 创建审计日志分析表
CREATE TABLE dbo.audit_analysis (
id INT IDENTITY(1,1) PRIMARY KEY,
analysis_date DATETIME,
login_success_count INT,
login_failed_count INT,
permission_change_count INT,
data_access_count INT,
异常活动_count INT
);
GO
— 2. 插入分析数据
INSERT INTO dbo.audit_analysis (
analysis_date,
login_success_count,
login_failed_count,
permission_change_count,
data_access_count,
异常活动_count
) VALUES (
GETDATE(),
(SELECT COUNT(*) FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT) WHERE action_id = ‘LGIS’ AND event_time > DATEADD(DAY, -1, GETDATE())),
(SELECT COUNT(*) FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT) WHERE action_id = ‘LGIF’ AND event_time > DATEADD(DAY, -1, GETDATE())),
(SELECT COUNT(*) FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT) WHERE action_id IN (‘GRANT’, ‘REVOKE’, ‘DENY’) AND event_time > DATEADD(DAY, -1, GETDATE())),
(SELECT COUNT(*) FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT) WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’) AND event_time > DATEADD(DAY, -1, GETDATE())),
(SELECT COUNT(*) FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\ServerAudit_*.sqlaudit’, DEFAULT, DEFAULT) WHERE action_id = ‘LGIF’ AND event_time > DATEADD(HOUR, -1, GETDATE()) GROUP BY server_principal_name HAVING COUNT(*) > 5)
);
GO
— 3. 查看分析报告
SELECT * FROM dbo.audit_analysis ORDER BY analysis_date DESC;
GO
执行结果：
———- ———–
LGIS 100
LGIF 10
(2 rows affected)
server_principal_name failed_count
——————— ————
fgedu_user 5
(1 row affected)
action_id count
———- ———–
GRANT 5
REVOKE 2
DENY 1
(3 rows affected)
object_name count
————- ———–
large_table 3
(1 row affected)
action_id count
———- ———–
SELECT 1000
UPDATE 500
INSERT 200
DELETE 100
(4 rows affected)
object_name count
————- ———–
large_table 1000
(1 row affected)
server_principal_name login_count
——————— ———–
(0 rows affected)
server_principal_name object_name access_count
——————— ————- ————
(0 rows affected)
Commands completed successfully.
(1 row affected)
id analysis_date login_success_count login_failed_count permission_change_count data_access_count 异常活动_count
———– ————————- ——————- —————— ———————- —————— ————
1 2025-04-08 10:00:00.000 100 10 8 1800 1
(1 row affected)
Part04-生产案例与实战讲解
4.1 SQLServer审计日志配置案例
审计日志配置实战：
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb
— 步骤1：创建服务器审计
— 1. 创建文件存储的服务器审计
CREATE SERVER AUDIT [fgedu_audit]
TO FILE (
FILEPATH = ‘E:\SQLServer\Audit\’,
MAXSIZE = 1024 MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = OFF
);
GO
— 2. 启用服务器审计
ALTER SERVER AUDIT [fgedu_audit] WITH (STATE = ON);
GO
— 步骤2：创建数据库审计规范
— 1. 创建数据库审计规范
USE fgedudb;
GO
CREATE DATABASE AUDIT SPECIFICATION [fgedu_db_audit]
FOR SERVER AUDIT [fgedu_audit]
ADD (SELECT, INSERT, UPDATE, DELETE ON fgedu.large_table BY public),
ADD (EXECUTE ON fgedu.sp_test BY public),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP);
GO
— 2. 启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [fgedu_db_audit] WITH (STATE = ON);
GO
— 步骤3：创建服务器审计规范
— 1. 创建服务器审计规范
CREATE SERVER AUDIT SPECIFICATION [fgedu_server_audit]
FOR SERVER AUDIT [fgedu_audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP);
GO
— 2. 启用服务器审计规范
ALTER SERVER AUDIT SPECIFICATION [fgedu_server_audit] WITH (STATE = ON);
GO
— 步骤4：验证审计配置
— 1. 查看审计配置
SELECT
a.name AS audit_name,
a.audit_file_path,
a.state_desc AS audit_state,
s.name AS spec_name,
s.type_desc AS spec_type,
s.state_desc AS spec_state
FROM sys.server_audits a
LEFT JOIN sys.server_audit_specifications s ON a.audit_guid = s.audit_guid
UNION ALL
SELECT
a.name AS audit_name,
a.audit_file_path,
a.state_desc AS audit_state,
s.name AS spec_name,
s.type_desc AS spec_type,
s.state_desc AS spec_state
FROM sys.server_audits a
JOIN sys.database_audit_specifications s ON a.audit_guid = s.audit_guid;
GO
— 2. 测试审计
— 执行一些操作，触发审计
CREATE LOGIN fgedu_test WITH PASSWORD = ‘Password123!’;
GO
USE fgedudb;
GO
CREATE USER fgedu_test FOR LOGIN fgedu_test;
GO
GRANT SELECT ON fgedu.large_table TO fgedu_test;
GO
SELECT * FROM fgedu.large_table;
GO
DROP USER fgedu_test;
GO
DROP LOGIN fgedu_test;
GO
— 3. 查看审计日志
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
ORDER BY event_time DESC;
GO
执行结果：
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
audit_name audit_file_path audit_state spec_name spec_type spec_state
————- ————————- ———– ——————- ——————- ———–
fgedu_audit E:\SQLServer\Audit\ ON fgedu_server_audit SERVER_AUDIT_SPEC ON
fgedu_audit E:\SQLServer\Audit\ ON fgedu_db_audit DATABASE_AUDIT_SPEC ON
(2 rows affected)
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
event_time action_id succeeded server_principal_name database_principal_name object_name statement
————————- ——– ——— ——————– ——————— ————- ——————————————————————————–
2025-04-08 10:00:00.000 DROP 1 sa NULL NULL DROP LOGIN fgedu_test
2025-04-08 09:59:59.000 DROP 1 sa NULL fgedu_test DROP USER fgedu_test
2025-04-08 09:59:58.000 SELECT 1 sa sa large_table SELECT * FROM fgedu.large_table
2025-04-08 09:59:57.000 GRANT 1 sa fgedu_test large_table GRANT SELECT ON fgedu.large_table TO fgedu_test
2025-04-08 09:59:56.000 ADDMEMBER 1 sa fgedu_test NULL CREATE USER fgedu_test FOR LOGIN fgedu_test
2025-04-08 09:59:55.000 GDR 1 sa NULL NULL CREATE LOGIN fgedu_test WITH PASSWORD = ‘********’, CHECK_POLICY = ON
2025-04-08 09:59:54.000 LGIS 1 sa NULL NULL NULL
(7 rows affected)
4.2 SQLServer审计日志分析案例
审计日志分析实战：
— 环境准备：
— SQLServer实例：fgedu-server
— 审计日志：E:\SQLServer\Audit\
— 步骤1：分析登录活动
— 1. 统计每日登录情况
SELECT
CAST(event_time AS DATE) AS login_date,
action_id,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘LGIS’, ‘LGIF’)
GROUP BY CAST(event_time AS DATE), action_id
ORDER BY login_date, action_id;
GO
— 2. 分析登录失败的用户
SELECT
server_principal_name,
COUNT(*) AS failed_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id = ‘LGIF’
GROUP BY server_principal_name
ORDER BY failed_count DESC;
GO
— 3. 分析登录时间分布
SELECT
DATEPART(HOUR, event_time) AS login_hour,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id = ‘LGIS’
GROUP BY DATEPART(HOUR, event_time)
ORDER BY login_hour;
GO
— 步骤2：分析权限变更
— 1. 统计权限变更类型
SELECT
action_id,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘GRANT’, ‘REVOKE’, ‘DENY’)
GROUP BY action_id
ORDER BY count DESC;
GO
— 2. 分析权限变更执行者
SELECT
server_principal_name,
COUNT(*) AS change_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘GRANT’, ‘REVOKE’, ‘DENY’)
GROUP BY server_principal_name
ORDER BY change_count DESC;
GO
— 3. 分析权限变更对象
SELECT
object_name,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘GRANT’, ‘REVOKE’, ‘DENY’) AND object_name IS NOT NULL
GROUP BY object_name
ORDER BY count DESC;
GO
— 步骤3：分析数据访问
— 1. 统计数据访问操作
SELECT
action_id,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’)
GROUP BY action_id
ORDER BY count DESC;
GO
— 2. 分析数据访问用户
SELECT
server_principal_name,
COUNT(*) AS access_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’)
GROUP BY server_principal_name
ORDER BY access_count DESC;
GO
— 3. 分析数据访问对象
SELECT
object_name,
COUNT(*) AS count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’) AND object_name IS NOT NULL
GROUP BY object_name
ORDER BY count DESC;
GO
— 步骤4：分析异常活动
— 1. 检测异常登录（短时间内多次登录）
SELECT
server_principal_name,
COUNT(*) AS login_count,
MIN(event_time) AS first_login,
MAX(event_time) AS last_login
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id = ‘LGIS’ AND event_time > DATEADD(HOUR, -1, GETDATE())
GROUP BY server_principal_name
HAVING COUNT(*) > 50
ORDER BY login_count DESC;
GO
— 2. 检测异常数据访问（短时间内大量访问）
SELECT
server_principal_name,
object_name,
COUNT(*) AS access_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’) AND event_time > DATEADD(HOUR, -1, GETDATE())
GROUP BY server_principal_name, object_name
HAVING COUNT(*) > 1000
ORDER BY access_count DESC;
GO
— 3. 检测权限滥用（未授权的权限变更）
SELECT
event_time,
server_principal_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘GRANT’, ‘REVOKE’, ‘DENY’) AND server_principal_name NOT IN (‘sa’, ‘fgedu_admin’)
ORDER BY event_time DESC;
GO
执行结果：
———– ———- ———–
2025-04-07 LGIS 100
2025-04-07 LGIF 5
2025-04-08 LGIS 80
2025-04-08 LGIF 3
(4 rows affected)
server_principal_name failed_count
——————— ————
fgedu_user 5
(1 row affected)
login_hour count
———– ———–
8 20
9 40
10 30
11 20
12 10
13 10
14 20
15 30
16 40
17 20
(11 rows affected)
action_id count
———- ———–
GRANT 10
REVOKE 3
DENY 1
(3 rows affected)
server_principal_name change_count
——————— ————
sa 10
fgedu_admin 4
(2 rows affected)
object_name count
————- ———–
large_table 8
(1 row affected)
action_id count
———- ———–
SELECT 10000
UPDATE 5000
INSERT 2000
DELETE 1000
(4 rows affected)
server_principal_name access_count
——————— ————
fgedu_app 10000
fgedu_dev 5000
fgedu_read 2000
(3 rows affected)
object_name count
————- ———–
large_table 15000
(1 row affected)
— 无异常登录
— 无异常数据访问
— 无权限滥用
4.3 SQLServer审计日志故障处理案例
审计日志故障处理实战：
— 环境准备：
— SQLServer实例：fgedu-server
— 审计日志：E:\SQLServer\Audit\
— 场景1：审计日志存储空间不足
— 问题：审计日志文件占用过多空间
— 解决方案：
— 1. 查看审计日志文件大小
SELECT
audit_file_path,
audit_file_size
FROM sys.dm_server_audit_status;
GO
— 2. 清理过期的审计日志文件
EXEC xp_cmdshell ‘forfiles /p “E:\SQLServer\Audit” /m “*.sqlaudit” /d -30 /c “cmd /c del @path”‘;
GO
— 3. 调整审计日志配置
ALTER SERVER AUDIT [fgedu_audit]
WITH (
MAXSIZE = 512 MB,
MAX_ROLLOVER_FILES = 5
);
GO
— 场景2：审计日志无法写入
— 问题：审计日志写入失败
— 解决方案：
— 1. 查看审计状态
SELECT
name,
state_desc,
status_desc
FROM sys.dm_server_audit_status;
GO
— 2. 检查存储权限
— 确保SQLServer服务账户有审计日志目录的写入权限
— 3. 重启审计
ALTER SERVER AUDIT [fgedu_audit] WITH (STATE = OFF);
GO
ALTER SERVER AUDIT [fgedu_audit] WITH (STATE = ON);
GO
— 场景3：审计日志分析性能问题
— 问题：查询审计日志速度慢
— 解决方案：
— 1. 限制查询范围
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_20250408_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE event_time BETWEEN ‘2025-04-08 00:00:00’ AND ‘2025-04-08 23:59:59’
ORDER BY event_time DESC;
GO
— 2. 创建审计日志分析表
CREATE TABLE dbo.audit_log_analysis (
id INT IDENTITY(1,1) PRIMARY KEY,
event_time DATETIME,
action_id VARCHAR(10),
succeeded BIT,
server_principal_name VARCHAR(128),
database_principal_name VARCHAR(128),
object_name VARCHAR(128),
statement NVARCHAR(MAX)
);
GO
— 3. 定期导入审计日志到分析表
INSERT INTO dbo.audit_log_analysis (
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
) SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\fgedu_audit_20250408_*.sqlaudit’, DEFAULT, DEFAULT);
GO
— 4. 在分析表上创建索引
CREATE INDEX IX_audit_log_analysis_event_time ON dbo.audit_log_analysis(event_time);
CREATE INDEX IX_audit_log_analysis_action_id ON dbo.audit_log_analysis(action_id);
CREATE INDEX IX_audit_log_analysis_server_principal_name ON dbo.audit_log_analysis(server_principal_name);
GO
— 场景4：审计日志配置错误
— 问题：审计配置不正确，导致审计不完整
— 解决方案：
— 1. 检查审计配置
SELECT
a.name AS audit_name,
a.state_desc AS audit_state,
s.name AS spec_name,
s.type_desc AS spec_type,
s.state_desc AS spec_state
FROM sys.server_audits a
LEFT JOIN sys.server_audit_specifications s ON a.audit_guid = s.audit_guid
UNION ALL
SELECT
a.name AS audit_name,
a.state_desc AS audit_state,
s.name AS spec_name,
s.type_desc AS spec_type,
s.state_desc AS spec_state
FROM sys.server_audits a
JOIN sys.database_audit_specifications s ON a.audit_guid = s.audit_guid;
GO
— 2. 修复审计配置
— 确保所有审计规范都已启用
ALTER SERVER AUDIT SPECIFICATION [fgedu_server_audit] WITH (STATE = ON);
GO
ALTER DATABASE AUDIT SPECIFICATION [fgedu_db_audit] WITH (STATE = ON);
GO
— 3. 重新配置审计操作组
ALTER DATABASE AUDIT SPECIFICATION [fgedu_db_audit]
WITH (STATE = OFF);
GO
ALTER DATABASE AUDIT SPECIFICATION [fgedu_db_audit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP);
GO
ALTER DATABASE AUDIT SPECIFICATION [fgedu_db_audit]
WITH (STATE = ON);
GO
执行结果：
audit_file_path audit_file_size
————————- —————-
E:\SQLServer\Audit\fgedu_audit_20250408_100000.sqlaudit 1048576000
(1 row affected)
(1 row affected)
Commands completed successfully.
— 场景2结果：
name state_desc status_desc
————- ———– ——————–
fgedu_audit ON STARTED
(1 row affected)
Commands completed successfully.
Commands completed successfully.
— 场景3结果：
event_time action_id succeeded server_principal_name statement
————————- ——– ——— ——————– ——————————————————————————–
2025-04-08 10:00:00.000 LGIS 1 sa NULL
(1 row affected)
Commands completed successfully.
(1000 rows affected)
Commands completed successfully.
— 场景4结果：
audit_name audit_state spec_name spec_type spec_state
————- ———– ——————- ——————- ———–
fgedu_audit ON fgedu_server_audit SERVER_AUDIT_SPEC ON
fgedu_audit ON fgedu_db_audit DATABASE_AUDIT_SPEC ON
(2 rows affected)
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Part05-风哥经验总结与分享
5.1 SQLServer审计日志最佳实践
- 根据业务需求和合规要求配置审计日志
- 使用文件存储审计日志，便于管理和分析
- 设置合理的审计日志大小和保留策略
- 定期备份和清理审计日志
- 监控审计日志的使用情况
- 分析审计日志，发现异常活动
- 结合监控工具，实现审计日志的实时分析
- 定期审查审计配置，确保审计范围完整
5.2 SQLServer审计日志常见问题
- 存储空间不足：原因是审计日志文件过大，解决方法是设置合理的大小和保留策略
- 性能影响：原因是审计粒度过细，解决方法是合理配置审计范围
- 审计日志丢失：原因是未备份审计日志，解决方法是定期备份
- 审计配置错误：原因是审计规范未正确配置，解决方法是定期审查审计配置
- 分析困难：原因是审计日志量大，解决方法是使用分析工具或创建分析表
5.3 SQLServer审计日志性能优化
- 合理配置审计范围，只审计必要的活动
- 使用异步审计，减少对主业务的影响
- 将审计日志存储在独立的存储设备上
- 定期清理过期的审计日志
- 使用分析表和索引提高审计日志查询性能
- 结合监控工具，实现审计日志的实时分析
- 定期维护审计日志，确保其可用性
- 使用分区表存储审计日志，提高查询性能
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
