SQL Server 管理系列（第二期）：安全加固——保护你的数据资产

SQL Server 管理系列（第二期）：安全加固——保护你的数据资产

上一期我们完成了 SQL Server 的安装配置。这一期，我们进入安全加固——这是 DBA 最重要的职责之一。你是否遇到过：开发人员直接使用 sa 账户连接应用？离职员工的账号还在数据库里？敏感数据（如身份证、手机号）在查询中明文显示？合规审计要求数据加密却不知从何入手？这一期，我们从最小权限、数据保护、审计追踪三个维度，构建完整的安全体系。

一、安全架构概览

SQL Server 安全采用分层防御模型：

外层：网络防火墙 + Windows 防火墙
  ↓
第1层：SQL Server 认证（Windows / SQL 登录）
  ↓
第2层：服务器角色权限（sysadmin, securityadmin, serveradmin...）
  ↓
第3层：数据库用户映射
  ↓
第4层：数据库角色权限（db_owner, db_datareader, db_datawriter...）
  ↓
第5层：对象级权限（表、视图、存储过程、列）
  ↓
第6层：行级安全 + 数据脱敏
  ↓
内层：透明数据加密（TDE）+ 备份加密

核心原则：

• 最小权限：只授予完成任务所需的最小权限
• 分层防御：即使一层被突破，其他层仍能保护数据
• 默认拒绝：未明确授权的操作一律禁止
• 审计合规：所有敏感操作必须可追溯

二、认证与授权基础

2.1 认证模式选择

模式	优点	缺点	适用场景
Windows 认证	集成域安全、密码策略、Kerberos	需要域环境	企业内部应用
SQL Server 认证	跨域、应用连接字符串简单	密码存储在应用配置	第三方应用、云环境
混合模式	兼容两者	管理两个密码体系	大多数生产环境

-- 查看当前认证模式
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsWindowsOnly
-- 0 = 混合模式，1 = Windows 认证模式

-- 修改认证模式（需重启服务）
-- 使用 SQL Server 配置管理器 → SQL Server 属性 → 安全性

2.2 登录（Server Level）vs 用户（Database Level）

-- 创建登录（服务器级别）
CREATE LOGIN AppUser WITH 
    PASSWORD = 'Str0ngP@ssw0rd!',
    CHECK_POLICY = ON,          -- 强制 Windows 密码策略
    CHECK_EXPIRATION = ON,      -- 密码过期
    DEFAULT_DATABASE = YourDB

-- 创建用户（数据库级别）并映射登录
USE YourDB
CREATE USER AppUser FOR LOGIN AppUser

-- 查看所有登录
SELECT name, type_desc, is_disabled, create_date 
FROM sys.sql_logins 
WHERE type = 'S'  -- SQL 登录

-- 查看所有用户
SELECT name, type_desc, create_date 
FROM sys.database_principals 
WHERE type IN ('S', 'U')  -- SQL 用户、Windows 用户

三、权限管理（最小权限原则）

3.1 服务器角色

角色	权限	建议
sysadmin	完全控制	仅限 2-3 个 DBA
securityadmin	管理登录/权限	限制使用
serveradmin	服务器级配置	限制使用
setupadmin	管理链接服务器	限制使用
processadmin	终止进程	按需授予
diskadmin	管理磁盘文件	按需授予
dbcreator	创建/修改数据库	开发环境可用
bulkadmin	执行 BULK INSERT	按需授予

-- 查看服务器角色成员
EXEC sp_helpsrvrolemember 'sysadmin'

-- 添加/删除成员
EXEC sp_addsrvrolemember 'DOMAIN\DBA_User', 'sysadmin'
EXEC sp_dropsrvrolemember 'DOMAIN\OldDBA', 'sysadmin'

-- 创建自定义服务器角色（SQL Server 2012+）
CREATE SERVER ROLE DBA_ReadOnly
GRANT VIEW SERVER STATE TO DBA_ReadOnly
GRANT VIEW ANY DEFINITION TO DBA_ReadOnly

3.2 数据库角色

角色	权限	建议
db_owner	完全控制数据库	仅限 DBA
db_securityadmin	管理数据库权限	限制使用
db_accessadmin	管理数据库用户	限制使用
db_backupoperator	备份数据库	备份账户
db_ddladmin	执行 DDL	开发/迁移账户
db_datawriter	INSERT/UPDATE/DELETE	应用账户
db_datareader	SELECT	报表账户
db_denydatawriter	禁止写入	特殊限制
db_denydatareader	禁止读取	特殊限制

-- 查看数据库角色成员
EXEC sp_helprolemember 'db_datareader'

-- 添加用户到角色
EXEC sp_addrolemember 'db_datareader', 'AppUser'
EXEC sp_addrolemember 'db_datawriter', 'AppUser'

-- 创建自定义数据库角色
CREATE ROLE ReportReader
GRANT SELECT ON Orders TO ReportReader
GRANT SELECT ON Customers TO ReportReader
EXEC sp_addrolemember 'ReportReader', 'ReportingUser'

3.3 对象级权限（细粒度控制）

-- 表级别
GRANT SELECT ON Orders TO SalesReader
GRANT INSERT, UPDATE ON Orders TO SalesWriter
DENY DELETE ON Orders TO SalesWriter  -- 禁止删除

-- 列级别（敏感数据）
GRANT SELECT ON Customers(FirstName, LastName, Email) TO SupportTeam
DENY SELECT ON Customers(SSN, CreditCard) TO SupportTeam

-- 存储过程级别（推荐：通过存储过程封装所有数据访问）
GRANT EXECUTE ON usp_GetCustomerData TO AppUser
-- 不授予表直接访问权限
DENY SELECT ON Customers TO AppUser

-- 架构级别
GRANT SELECT ON SCHEMA::Sales TO SalesReader
GRANT EXECUTE ON SCHEMA::dbo TO AppUser

-- 查看权限
SELECT 
    pr.name AS PrincipalName,
    pr.type_desc,
    pe.permission_name,
    pe.state_desc,
    OBJECT_NAME(pe.major_id) AS ObjectName
FROM sys.database_permissions pe
JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pe.class = 1  -- 对象级权限
ORDER BY pr.name, pe.permission_name

四、高级安全特性

4.1 行级安全（Row-Level Security, RLS）

场景：销售人员只能看自己的客户数据

-- 创建过滤谓词函数
CREATE FUNCTION dbo.fn_SalespersonFilter(@SalespersonID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS IsAccessible
    WHERE @SalespersonID = CAST(SESSION_CONTEXT(N'SalespersonID') AS INT)
        OR IS_ROLEMEMBER('db_owner') = 1
)

-- 创建安全策略
CREATE SECURITY POLICY SalespersonPolicy
ADD FILTER PREDICATE dbo.fn_SalespersonFilter(SalespersonID) ON dbo.Customers
WITH (STATE = ON)

-- 应用设置会话上下文
EXEC sp_set_session_context N'SalespersonID', 123
SELECT * FROM Customers  -- 只看到 SalespersonID=123 的客户

-- 查看策略
SELECT * FROM sys.security_policies
SELECT * FROM sys.security_predicates

4.2 动态数据脱敏（Dynamic Data Masking）

场景：客服人员查询时，敏感字段自动脱敏

-- 创建表时定义脱敏
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100) MASKED WITH (FUNCTION = 'partial(2, "***", 2)'),
    Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
    Phone VARCHAR(20) MASKED WITH (FUNCTION = 'default()'),
    CreditCard CHAR(16) MASKED WITH (FUNCTION = 'partial(4, "****-****-****-", 4)')
)

-- 事后添加脱敏
ALTER TABLE Customers ALTER COLUMN Phone 
ADD MASKED WITH (FUNCTION = 'partial(0, "*******", 4)')

-- 测试脱敏效果
-- 普通用户查询
SELECT * FROM Customers  
-- 结果：王**，a***@***.com，*******，****-****-****-1234

-- 授权查看真实数据
GRANT UNMASK TO DataAnalyst
REVOKE UNMASK FROM SupportTeam

-- 查看脱敏配置
SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.is_masked,
    c.masking_function
FROM sys.masked_columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.is_masked = 1

4.3 透明数据加密（Transparent Data Encryption, TDE）

场景：防止磁盘丢失导致数据泄露

-- 1. 创建数据库主密钥
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ngM@sterKey!'

-- 2. 创建证书
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate for YourDB'

-- 3. 备份证书（重要！恢复数据库时需要）
BACKUP CERTIFICATE TDECert TO FILE = 'G:\Backup\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'G:\Backup\TDECert_PrivateKey.pvk',
    ENCRYPTION BY PASSWORD = 'CertPrivateKey!@#',
    DECRYPTION BY PASSWORD = 'CertPrivateKey!@#'
)

-- 4. 启用数据库加密
USE YourDB
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert

ALTER DATABASE YourDB SET ENCRYPTION ON

-- 5. 查看加密进度
SELECT 
    db.name,
    dek.encryption_state,
    CASE dek.encryption_state
        WHEN 0 THEN 'No encryption'
        WHEN 1 THEN 'Encryption in progress'
        WHEN 2 THEN 'Encrypted'
        WHEN 3 THEN 'Decryption in progress'
    END AS EncryptionState
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases db ON dek.database_id = db.database_id
WHERE db.name = 'YourDB'

4.4 备份加密

-- 使用证书加密备份
BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\YourDB_Encrypted.bak'
WITH 
    COMPRESSION,
    ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = TDECert
    )

-- 使用密码加密备份（简单但安全性较低）
BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\YourDB_Password.bak'
WITH PASSWORD = 'BackupP@ssw0rd!'

-- 恢复加密备份需要证书
CREATE CERTIFICATE TDECert FROM FILE = 'G:\Backup\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'G:\Backup\TDECert_PrivateKey.pvk',
    DECRYPTION BY PASSWORD = 'CertPrivateKey!@#'
)

五、审计与合规

5.1 SQL Server Audit（企业版）

-- 创建审计对象
USE master
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'G:\Audit\')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)

-- 启用审计
ALTER SERVER AUDIT ServerAudit WITH (STATE = ON)

-- 创建服务器级审计规范（监控登录失败）
CREATE SERVER AUDIT SPECIFICATION LoginAudit
FOR SERVER AUDIT ServerAudit
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)

-- 创建数据库级审计规范（监控 DDL 操作）
USE YourDB
CREATE DATABASE AUDIT SPECIFICATION DDLAudit
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)

-- 查看审计日志
SELECT 
    event_time,
    action_id,
    session_server_principal_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('G:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC

5.2 通用审计脚本（非企业版）

-- 创建审计表
CREATE TABLE DBA_AuditLog (
    AuditID BIGINT IDENTITY PRIMARY KEY,
    EventTime DATETIME2 DEFAULT GETDATE(),
    LoginName NVARCHAR(128),
    HostName NVARCHAR(128),
    AppName NVARCHAR(128),
    EventType NVARCHAR(50),
    ObjectName NVARCHAR(255),
    SQLText NVARCHAR(MAX)
)

-- 创建触发器记录 DDL 操作
CREATE TRIGGER trg_AuditDDL
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    DECLARE @data XML = EVENTDATA()
    
    INSERT INTO DBA_AuditLog (
        LoginName,
        HostName,
        AppName,
        EventType,
        ObjectName,
        SQLText
    )
    VALUES (
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)'),
        @data.value('(/EVENT_INSTANCE/HostName)[1]', 'NVARCHAR(128)'),
        @data.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'NVARCHAR(128)'),
        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)'),
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)')
    )
END

六、SQL 注入防护（数据库层面）

-- 1. 使用参数化查询（存储过程强制参数化）
CREATE PROC usp_GetUser @UserID INT
AS
BEGIN
    SELECT * FROM Users WHERE UserID = @UserID
END

-- 2. 强制参数化（防止字符串拼接）
ALTER DATABASE YourDB SET PARAMETERIZATION FORCED

-- 3. 使用 QUOTENAME 处理动态表名/列名
DECLARE @TableName NVARCHAR(128) = QUOTENAME(@UserInput)
SET @sql = 'SELECT * FROM ' + @TableName

-- 4. 最小权限：应用账户只有 EXECUTE 权限，没有直接表 SELECT
DENY SELECT ON SCHEMA::dbo TO AppUser
GRANT EXECUTE ON SCHEMA::dbo TO AppUser

-- 5. 启用 CLR 安全（如不使用）
EXEC sp_configure 'clr enabled', 0
RECONFIGURE

-- 6. 禁用 OLE Automation（如不使用）
EXEC sp_configure 'Ole Automation Procedures', 0
RECONFIGURE

-- 7. 禁用 xp_cmdshell（强烈建议）
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

七、安全配置检查清单

账户管理

• 禁用 sa 账户或设置超强密码
• 删除默认账户（如 ##MS_Policy* 等系统账户保留）
• 离职员工账户立即禁用/删除
• 应用使用专用账户，不使用 DBA 账户
• 密码策略：复杂度 + 过期 + 锁定阈值

权限管理

• sysadmin 角色不超过 3 人
• 应用账户只有 db_datareader + db_datawriter + EXECUTE
• 开发人员不直接访问生产数据库
• 使用存储过程封装所有数据访问

数据保护

• 敏感列使用动态脱敏
• 行级安全隔离多租户数据
• TDE 加密数据文件
• 备份文件加密
• 传输加密（SSL/TLS）

审计追踪

• 启用登录审计（成功+失败）
• 审计 DDL 操作
• 审计权限变更
• 审计敏感表访问

攻击面最小化

• 禁用 xp_cmdshell
• 禁用 OLE Automation
• 禁用 CLR（如不需要）
• 限制链接服务器
• 隐藏 SQL Server 实例

八、核心总结

安全层	核心技术	关键配置
认证	Windows + SQL 混合	禁用 sa，使用域账户
授权	最小权限原则	应用用 db_datareader + db_datawriter
行级	RLS	安全策略 + 谓词函数
列级	动态脱敏	MASKED 列 + UNMASK 权限
加密	TDE	数据库加密 + 证书备份
审计	SQL Audit / 触发器	记录登录、DDL、权限变更
防注入	参数化	存储过程 + 最小权限

一句话记住本期内容：

SQL Server 安全是分层防御——禁用 sa 和 xp_cmdshell，应用账户最小权限，敏感数据脱敏加密，所有操作有审计。

动手练习

-- 场景：电商系统安全配置

-- 1. 创建应用账户，只能通过存储过程访问 Orders 表（不能直接 SELECT/UPDATE）

-- 2. 对 Customers 表的 Phone、Email 列添加动态脱敏

-- 3. 创建 RLS 策略，让销售人员只能看到自己的客户

-- 4. 配置审计，记录谁在周末删除了数据

点击查看参考答案

1. 应用账户配置：

CREATE LOGIN AppUser WITH PASSWORD = 'AppP@ssw0rd'
USE YourDB
CREATE USER AppUser FOR LOGIN AppUser
DENY SELECT, INSERT, UPDATE, DELETE ON Orders TO AppUser
GRANT EXECUTE ON usp_GetOrders TO AppUser
GRANT EXECUTE ON usp_UpdateOrder TO AppUser

2. 动态脱敏：

ALTER TABLE Customers ALTER COLUMN Phone 
ADD MASKED WITH (FUNCTION = 'partial(0, "*******", 4)')
ALTER TABLE Customers ALTER COLUMN Email 
ADD MASKED WITH (FUNCTION = 'email()')

3. RLS：参考 4.1 节代码

4. 审计：

CREATE TABLE WeekendDeleteAudit (...)
CREATE TRIGGER trg_WeekendDelete ON Orders AFTER DELETE
AS
IF DATEPART(WEEKDAY, GETDATE()) IN (1,7)
    INSERT INTO WeekendDeleteAudit SELECT *, GETDATE() FROM deleted

下一期预告

备份与恢复——数据安全的最后防线

• 完整备份、差异备份、日志备份的深度解析
• 备份策略设计：RPO/RTO 权衡
• 恢复演练：从备份还原数据库
• 备份加密与压缩
• 灾难恢复：异地备份、日志传送、Always On

---

📌 本文配置适用于 SQL Server 2016-2022。生产环境安全策略需结合企业合规要求（GDPR、等保等）调整。

管理系列持续更新中，点击关注不错过第三期。