# 1. 启用SQL Server Audit
# 检查SQL Server Audit状态
SELECT * FROM sys.server_audits;
GO

# 创建服务器审计
CREATE SERVER AUDIT [fgedu-server-audit]
TO FILE (FILEPATH = N’D:\\SQLServer\\Audit\\’, MAXSIZE = 1024 MB, MAX_ROLLOVER_FILES = 10);
GO

# 启用服务器审计
ALTER SERVER AUDIT [fgedu-server-audit] WITH (STATE = ON);
GO

# 2. 创建审计规范
# 创建服务器审计规范
CREATE SERVER AUDIT SPECIFICATION [fgedu-server-audit-spec]
FOR SERVER AUDIT [fgedu-server-audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP);
GO

# 启用服务器审计规范
ALTER SERVER AUDIT SPECIFICATION [fgedu-server-audit-spec] WITH (STATE = ON);
GO

# 创建数据库审计规范
USE fgedudb;
GO

CREATE DATABASE AUDIT SPECIFICATION [fgedu-db-audit-spec]
FOR SERVER AUDIT [fgedu-server-audit]
ADD (SELECT ON SCHEMA::dbo BY public),
ADD (INSERT ON SCHEMA::dbo BY public),
ADD (UPDATE ON SCHEMA::dbo BY public),
ADD (DELETE ON SCHEMA::dbo BY public),
ADD (EXECUTE ON SCHEMA::dbo BY public);
GO

# 启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [fgedu-db-audit-spec] WITH (STATE = ON);
GO

# 3. 配置审计目标
# 检查审计目标
SELECT * FROM sys.server_file_audits;
GO

# 4. 验证审计配置
# 测试审计功能
SELECT * FROM dbo.fgedu_users;
GO

# 查看审计日志
SELECT
event_time,
action_id,
succeeded,
session_id,
server_principal_name,
database_name,
object_name,
statement
FROM fn_get_audit_file(‘D:\\SQLServer\\Audit\\fgedu-server-audit*’, DEFAULT, DEFAULT);
GO

# 1. 身份认证配置
# 启用Windows认证
EXEC sp_configure ‘windows authentication’, 1;
RECONFIGURE;
GO

# 禁用SQL Server认证（可选）
— EXEC sp_configure ‘mixed authentication’, 0;
— RECONFIGURE;

# 2. 权限控制实施
# 创建角色
CREATE ROLE [fgedu_readonly] AUTHORIZATION [dbo];
GO

CREATE ROLE [fgedu_datawriter] AUTHORIZATION [dbo];
GO

# 分配权限
GRANT SELECT ON SCHEMA::dbo TO [fgedu_readonly];
GO

GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [fgedu_datawriter];
GO

# 创建用户
CREATE USER [fgedu_read] FOR LOGIN [fgedu_read];
GO

CREATE USER [fgedu_write] FOR LOGIN [fgedu_write];
GO

# 添加用户到角色
ALTER ROLE [fgedu_readonly] ADD MEMBER [fgedu_read];
GO

ALTER ROLE [fgedu_datawriter] ADD MEMBER [fgedu_write];
GO

# 3. 数据加密配置
# 启用透明数据加密 (TDE)
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Fgedu@2026#TDE’;
GO

CREATE CERTIFICATE [fgedu-tde-cert] WITH SUBJECT = ‘TDE Certificate for fgedudb’;
GO

USE fgedudb;
GO

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE [fgedu-tde-cert];
GO

ALTER DATABASE fgedudb SET ENCRYPTION ON;
GO

# 4. 数据脱敏配置
# 创建动态数据脱敏策略
USE fgedudb;
GO

ALTER TABLE dbo.fgedu_users
ADD masked_email AS CONCAT(LEFT(email, 3), ‘***’, RIGHT(email, CHARINDEX(‘@’, email) – 1)) PERSISTED;
GO

# 5. 验证安全控制
# 检查用户权限
EXEC sp_helpuser ‘fgedu_read’;
GO

# 检查加密状态
SELECT
db.name,
db.is_encrypted,
dek.encryption_state
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id;
GO

# 测试数据脱敏
SELECT id, username, masked_email FROM dbo.fgedu_users;
GO

# 1. 审计准备
# 确定审计范围
# 准备审计计划
# 收集审计证据
# 通知相关人员

# 2. 审计执行
# 检查审计配置
# 分析审计日志
# 验证安全控制
# 测试合规性

# 3. 审计报告
# 生成审计报告
# 分析审计发现
# 提出改进建议
# 跟踪整改情况

# 4. 审计类型

审计类型 审计频率 审计范围 审计目的
————————————————————
内部审计 每季度 所有系统 评估内部控制
外部审计 每年 关键系统 满足法规要求
专项审计 按需 特定系统或流程 调查特定问题
持续审计 持续 关键系统 实时监控合规状态

# 5. 审计工具

工具类型 工具名称 适用场景 优势
————————————————————
内置工具 SQL Server Audit 所有系统 集成度高
第三方工具 SQL Compliance Manager 大型企业 功能全面
开源工具 osquery 中小型企业 成本低
云服务 Azure Security Center 云环境 云原生

# 1. 审计准备
# 配置审计规范
CREATE SERVER AUDIT SPECIFICATION [fgedu-permission-audit]
FOR SERVER AUDIT [fgedu-server-audit]
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP);
GO

# 启用审计规范
ALTER SERVER AUDIT SPECIFICATION [fgedu-permission-audit] WITH (STATE = ON);
GO

# 2. 模拟权限变更
# 创建测试用户
CREATE LOGIN [test_user] WITH PASSWORD = ‘Test@2026#Password’;
GO

# 添加用户到服务器角色
ALTER SERVER ROLE [sysadmin] ADD MEMBER [test_user];
GO

# 移除用户从服务器角色
ALTER SERVER ROLE [sysadmin] DROP MEMBER [test_user];
GO

# 删除测试用户
DROP LOGIN [test_user];
GO

# 3. 分析审计日志
# 查看权限变更审计日志
SELECT
event_time,
action_id,
succeeded,
session_id,
server_principal_name,
target_server_principal_name,
statement
FROM fn_get_audit_file(‘D:\\SQLServer\\Audit\\fgedu-server-audit*’, DEFAULT, DEFAULT)
WHERE action_id IN (‘AL’, ‘DL’)
AND target_server_principal_name = ‘test_user’;
GO

# 4. 生成审计报告
# 审计报告内容：
# 1. 审计范围：服务器角色成员变更
# 2. 审计时间：2026-04-08
# 3. 审计发现：
# – 用户test_user被添加到sysadmin角色
# – 用户test_user被从sysadmin角色移除
# – 用户test_user被删除
# 4. 合规状态：符合权限变更审计要求
# 5. 改进建议：无

# 5. 审计结果

审计项 预期结果 实际结果 状态
————————————————————
权限变更记录 所有变更都被记录 所有变更都被记录 通过
权限变更审批 变更有审批流程 变更有审批流程 通过
权限变更监控 变更被及时监控 变更被及时监控 通过
权限变更报告 生成详细报告 生成详细报告 通过

# 1. 收集审计数据
# 收集登录审计数据
SELECT
event_time,
action_id,
succeeded,
session_id,
server_principal_name,
client_ip_address
FROM fn_get_audit_file(‘D:\\SQLServer\\Audit\\fgedu-server-audit*’, DEFAULT, DEFAULT)
WHERE action_id IN (‘LGIF’, ‘LGIS’)
AND event_time >= DATEADD(day, -30, GETDATE());

# 收集数据访问审计数据
SELECT
event_time,
action_id,
succeeded,
session_id,
server_principal_name,
database_name,
object_name,
statement
FROM fn_get_audit_file(‘D:\\SQLServer\\Audit\\fgedu-server-audit*’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SL’, ‘IN’, ‘UP’, ‘DL’)
AND event_time >= DATEADD(day, -30, GETDATE());

# 收集权限变更审计数据
SELECT
event_time,
action_id,
succeeded,
session_id,
server_principal_name,
target_server_principal_name,
statement
FROM fn_get_audit_file(‘D:\\SQLServer\\Audit\\fgedu-server-audit*’, DEFAULT, DEFAULT)
WHERE action_id IN (‘AL’, ‘DL’)
AND event_time >= DATEADD(day, -30, GETDATE());

# 2. 分析审计数据
# 分析登录失败
SELECT
server_principal_name,
client_ip_address,
COUNT(*) AS failure_count
FROM fn_get_audit_file(‘D:\\SQLServer\\Audit\\fgedu-server-audit*’, DEFAULT, DEFAULT)
WHERE action_id = ‘LGIF’
AND succeeded = 0
AND event_time >= DATEADD(day, -30, GETDATE())
GROUP BY server_principal_name, client_ip_address
ORDER BY failure_count DESC;

# 分析敏感数据访问
SELECT
server_principal_name,
object_name,
COUNT(*) AS access_count
FROM fn_get_audit_file(‘D:\\SQLServer\\Audit\\fgedu-server-audit*’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SL’, ‘IN’, ‘UP’, ‘DL’)
AND object_name IN (‘fgedu_users’, ‘fgedu_orders’)
AND event_time >= DATEADD(day, -30, GETDATE())
GROUP BY server_principal_name, object_name
ORDER BY access_count DESC;

# 3. 生成合规报告
# 报告模板
# SQLServer数据库审计合规报告

## 1. 报告概述
– 报告日期：2026-04-08
– 报告周期：2026-03-09至2026-04-08
– 审计范围：fgedudb数据库
– 审计方法：SQL Server Audit

## 2. 合规状态
– 总体合规状态：符合
– 发现的问题：无
– 改进建议：无

## 3. 审计结果
### 3.1 登录审计
– 成功登录：1000次
– 失败登录：5次
– 异常登录：无

### 3.2 数据访问审计
– 数据查询：5000次
– 数据修改：1000次
– 敏感数据访问：100次

### 3.3 权限变更审计
– 角色成员变更：5次
– 权限变更：10次
– 异常变更：无

## 4. 合规建议
– 继续保持当前的审计配置
– 定期审查审计日志
– 加强用户培训

# 4. 报告分发
# 分发对象：
– 数据库管理员
– 安全团队
– 合规官
– 管理层

# 分发方式：
– 电子邮件
– 安全门户
– 定期会议