# 检查SQLServer版本
SELECT @@VERSION;
GO

# 检查数据库兼容性级别
SELECT name, compatibility_level FROM sys.databases;
GO

name compatibility_level
———– ——————-
fgedudb 150
master 150
model 150
tempdb 150
msdb 150

对于数据掩码环境，需要确保SQLServer版本为2016或更高，数据库兼容性级别为130或更高。from SQLServer视频:www.itpux.com。

2.2 SQLServer数据掩码策略设计

SQLServer数据掩码策略设计应考虑以下因素：

1. 数据敏感度评估：对数据进行分类，确定哪些数据需要掩码

2. 掩码规则设计：根据数据类型和敏感度选择合适的掩码规则

3. 权限管理：确保授权用户能够查看完整数据

4. 性能影响：评估数据掩码对查询性能的影响

5. 合规要求：确保数据掩码满足相关合规要求

更多视频教程www.fgedu.net.cn。

Part03-生产环境项目实施方案

3.1 SQLServer数据掩码高级配置实施

# 创建测试数据库
CREATE DATABASE fgedu_masking;
GO

# 创建测试表
USE fgedu_masking;
GO

CREATE TABLE dbo.fgedu_users (
id INT PRIMARY KEY IDENTITY,
username NVARCHAR(50) NOT NULL,
email NVARCHAR(100) NOT NULL,
phone NVARCHAR(20) NOT NULL,
address NVARCHAR(200) NOT NULL,
birthdate DATE NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
create_time DATETIME DEFAULT GETDATE()
);
GO

# 插入测试数据
INSERT INTO dbo.fgedu_users (
username, email, phone, address, birthdate, salary
) VALUES
(‘user1’, ‘user1@example.com’, ‘13800138001’, ‘北京市朝阳区’, ‘1990-01-01’, 10000.00),
(‘user2’, ‘user2@example.com’, ‘13900139002’, ‘上海市浦东新区’, ‘1991-02-02’, 15000.00),
(‘user3’, ‘user3@example.com’, ‘13700137003’, ‘广州市天河区’, ‘1992-03-03’, 20000.00),
(‘user4’, ‘user4@example.com’, ‘13600136004’, ‘深圳市南山区’, ‘1993-04-04’, 25000.00),
(‘user5’, ‘user5@example.com’, ‘13500135005’, ‘杭州市西湖区’, ‘1994-05-05′, 30000.00);
GO

# 创建用户
CREATE USER fgedu_user WITHOUT LOGIN;
GO

# 授予权限
GRANT SELECT ON dbo.fgedu_users TO fgedu_user;
GO

# 配置动态数据掩码
ALTER TABLE dbo.fgedu_users
ALTER COLUMN email ADD MASKED WITH (FUNCTION = ’email()’);
GO

ALTER TABLE dbo.fgedu_users
ALTER COLUMN phone ADD MASKED WITH (FUNCTION = ‘partial(0, “***”, 4)’);
GO

ALTER TABLE dbo.fgedu_users
ALTER COLUMN address ADD MASKED WITH (FUNCTION = ‘default()’);
GO

ALTER TABLE dbo.fgedu_users
ALTER COLUMN salary ADD MASKED WITH (FUNCTION = ‘random(1000, 5000)’);
GO

# 查看数据掩码配置
SELECT
c.name AS column_name,
t.name AS table_name,
m.masking_function
FROM sys.masked_columns m
JOIN sys.columns c ON m.column_id = c.column_id AND m.object_id = c.object_id
JOIN sys.tables t ON c.object_id = t.object_id;
GO

column_name table_name masking_function
———— ———– —————-
email fgedu_users email()
phone fgedu_users partial(0, “***”, 4)
address fgedu_users default()
salary fgedu_users random(1000, 5000)

# 测试数据掩码对查询性能的影响

# 启用查询统计
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO

# 测试未掩码查询
SELECT * FROM dbo.fgedu_users;
GO

# 测试掩码查询（使用普通用户）
EXECUTE AS USER = ‘fgedu_user’;
SELECT * FROM dbo.fgedu_users;
REVERT;
GO

# 优化数据掩码性能

# 1. 只对必要的列应用掩码
ALTER TABLE dbo.fgedu_users
ALTER COLUMN birthdate DROP MASKED;
GO

# 2. 使用合适的掩码函数
# 对于高频查询的列，使用简单的掩码函数
ALTER TABLE dbo.fgedu_users
ALTER COLUMN phone DROP MASKED;
GO

ALTER TABLE dbo.fgedu_users
ALTER COLUMN phone ADD MASKED WITH (FUNCTION = ‘partial(3, “****”, 4)’);
GO

# 3. 为掩码列创建索引
CREATE INDEX IX_fgedu_users_email ON dbo.fgedu_users(email);
GO

# 4. 避免在掩码列上进行过滤
— 避免这样的查询
SELECT * FROM dbo.fgedu_users WHERE email = ‘user1@example.com’;

— 推荐这样的查询
SELECT * FROM dbo.fgedu_users WHERE id = 1;

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer数据掩码高级实战案例

# 案例1：动态数据掩码

# 查看未掩码数据（管理员）
SELECT * FROM dbo.fgedu_users;
GO

id username email phone address birthdate salary
— ——— —————– ————- ————- ———– ——
1 user1 user1@example.com 13800138001 北京市朝阳区 1990-01-01 10000.00
2 user2 user2@example.com 13900139002 上海市浦东新区 1991-02-02 15000.00
3 user3 user3@example.com 13700137003 广州市天河区 1992-03-03 20000.00
4 user4 user4@example.com 13600136004 深圳市南山区 1993-04-04 25000.00
5 user5 user5@example.com 13500135005 杭州市西湖区 1994-05-05 30000.00

# 查看掩码数据（普通用户）
EXECUTE AS USER = ‘fgedu_user’;
SELECT * FROM dbo.fgedu_users;
REVERT;
GO

id username email phone address birthdate salary
— ——— —————– ————- ————- ———– ——
1 user1 u***@example.com 138****8001 xxxx 1990-01-01 3456.00
2 user2 u***@example.com 139****9002 xxxx 1991-02-02 2345.00
3 user3 u***@example.com 137****7003 xxxx 1992-03-03 4567.00
4 user4 u***@example.com 136****6004 xxxx 1993-04-04 1234.00
5 user5 u***@example.com 135****5005 xxxx 1994-05-05 4321.00

# 案例2：行级安全

# 创建行级安全函数
CREATE FUNCTION dbo.fn_securitypredicate(@username AS NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @username = USER_NAME() OR USER_NAME() = ‘dbo’;
GO

# 创建行级安全策略
CREATE SECURITY POLICY fgedu_security_policy
ADD FILTER PREDICATE dbo.fn_securitypredicate(username) ON dbo.fgedu_users
WITH (STATE = ON);
GO

# 创建测试用户
CREATE USER user1 WITHOUT LOGIN;
CREATE USER user2 WITHOUT LOGIN;
GO

# 授予权限
GRANT SELECT ON dbo.fgedu_users TO user1;
GRANT SELECT ON dbo.fgedu_users TO user2;
GO

# 测试行级安全
EXECUTE AS USER = ‘user1’;
SELECT * FROM dbo.fgedu_users;
REVERT;
GO

id username email phone address birthdate salary
— ——— —————– ————- ————- ———– ——
1 user1 u***@example.com 138****8001 xxxx 1990-01-01 3456.00

EXECUTE AS USER = ‘user2’;
SELECT * FROM dbo.fgedu_users;
REVERT;
GO

id username email phone address birthdate salary
— ——— —————– ————- ————- ———– ——
2 user2 u***@example.com 139****9002 xxxx 1991-02-02 2345.00

# 案例3：列级安全

# 创建列级安全函数
CREATE FUNCTION dbo.fn_column_security(@column_name AS SYSNAME)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
DECLARE @result BIT = 0;
IF USER_NAME() = ‘dbo’ OR USER_NAME() = ‘fgedu_admin’
SET @result = 1;
RETURN @result;
END;
GO

# 创建列级安全策略
CREATE SECURITY POLICY fgedu_column_security_policy
ADD FILTER PREDICATE dbo.fn_column_security(‘salary’) ON dbo.fgedu_users(salary)
WITH (STATE = ON);
GO

# 创建管理员用户
CREATE USER fgedu_admin WITHOUT LOGIN;
GO

# 授予权限
GRANT SELECT ON dbo.fgedu_users TO fgedu_admin;
GO

# 测试列级安全
EXECUTE AS USER = ‘fgedu_user’;
SELECT * FROM dbo.fgedu_users;
REVERT;
GO

# 测试管理员访问
EXECUTE AS USER = ‘fgedu_admin’;
SELECT * FROM dbo.fgedu_users;
REVERT;
GO

# 创建数据掩码自动化脚本
CREATE PROCEDURE dbo.usp_data_masking_automation
@database_name NVARCHAR(50),
@schema_name NVARCHAR(50) = ‘dbo’,
@table_name NVARCHAR(50)
AS
BEGIN
— 检查数据库是否存在
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @database_name)
BEGIN
RAISERROR(‘Database %s does not exist.’, 16, 1, @database_name);
RETURN;
END;

— 检查表是否存在
IF NOT EXISTS (
SELECT 1 FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name
)
BEGIN
RAISERROR(‘Table %s.%s does not exist.’, 16, 1, @schema_name, @table_name);
RETURN;
END;

— 为敏感列添加掩码
DECLARE @sql NVARCHAR(MAX);

— 处理邮箱列
IF EXISTS (
SELECT 1 FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%email%’
)
BEGIN
SET @sql = ‘ALTER TABLE ‘ + QUOTENAME(@schema_name) + ‘.’ + QUOTENAME(@table_name) +
‘ ALTER COLUMN ‘ + QUOTENAME((SELECT c.name FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%email%’)) +
‘ ADD MASKED WITH (FUNCTION = ”email()”)’;
EXEC sp_executesql @sql;
END;

— 处理电话列
IF EXISTS (
SELECT 1 FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%phone%’
)
BEGIN
SET @sql = ‘ALTER TABLE ‘ + QUOTENAME(@schema_name) + ‘.’ + QUOTENAME(@table_name) +
‘ ALTER COLUMN ‘ + QUOTENAME((SELECT c.name FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%phone%’)) +
‘ ADD MASKED WITH (FUNCTION = ”partial(3, “****”, 4)”)’;
EXEC sp_executesql @sql;
END;

— 处理地址列
IF EXISTS (
SELECT 1 FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%address%’
)
BEGIN
SET @sql = ‘ALTER TABLE ‘ + QUOTENAME(@schema_name) + ‘.’ + QUOTENAME(@table_name) +
‘ ALTER COLUMN ‘ + QUOTENAME((SELECT c.name FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%address%’)) +
‘ ADD MASKED WITH (FUNCTION = ”default()”)’;
EXEC sp_executesql @sql;
END;

— 处理薪资列
IF EXISTS (
SELECT 1 FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%salary%’
)
BEGIN
SET @sql = ‘ALTER TABLE ‘ + QUOTENAME(@schema_name) + ‘.’ + QUOTENAME(@table_name) +
‘ ALTER COLUMN ‘ + QUOTENAME((SELECT c.name FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name AND c.name LIKE ‘%salary%’)) +
‘ ADD MASKED WITH (FUNCTION = ”random(1000, 5000)”)’;
EXEC sp_executesql @sql;
END;

— 查看掩码配置
SELECT
c.name AS column_name,
t.name AS table_name,
m.masking_function
FROM sys.masked_columns m
JOIN sys.columns c ON m.column_id = c.column_id AND m.object_id = c.object_id
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name AND s.name = @schema_name;
END;
GO

# 执行数据掩码自动化
EXEC dbo.usp_data_masking_automation
@database_name = ‘fgedu_masking’,
@schema_name = ‘dbo’,
@table_name = ‘fgedu_users’;
GO

# 查看数据掩码配置
SELECT
c.name AS column_name,
t.name AS table_name,
m.masking_function
FROM sys.masked_columns m
JOIN sys.columns c ON m.column_id = c.column_id AND m.object_id = c.object_id
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = ‘fgedu_users’ AND s.name = ‘dbo’;
GO

column_name table_name masking_function
———— ———– —————-
email fgedu_users email()
phone fgedu_users partial(3, “****”, 4)
address fgedu_users default()
salary fgedu_users random(1000, 5000)

风哥提示：定期检查数据掩码配置，确保敏感数据得到有效保护。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer数据掩码最佳实践

1. 对敏感数据进行分类，确定掩码策略

2. 选择合适的数据掩码技术，根据数据类型和敏感度

3. 为授权用户提供查看完整数据的权限

4. 定期测试数据掩码效果，确保掩码正确应用

5. 评估数据掩码对查询性能的影响，进行必要的优化

6. 结合行级安全和列级安全，提供更细粒度的访问控制

7. 建立数据掩码文档，记录掩码配置和操作流程

5.2 SQLServer数据掩码常见问题与解决方案

# 问题1：数据掩码不生效
# 解决方案：检查用户权限、掩码配置、数据库兼容性级别

# 问题2：数据掩码影响查询性能
# 解决方案：优化掩码函数、为掩码列创建索引、避免在掩码列上进行过滤

# 问题3：授权用户无法查看完整数据
# 解决方案：确保用户具有UNMASK权限

# 问题4：数据掩码配置复杂
# 解决方案：使用自动化脚本，简化配置过程

# 问题5：数据掩码与其他安全功能冲突
# 解决方案：合理规划安全策略，避免冲突

# 授予UNMASK权限
GRANT UNMASK TO fgedu_admin;
GO

# 测试UNMASK权限
EXECUTE AS USER = ‘fgedu_admin’;
SELECT * FROM dbo.fgedu_users;
REVERT;
GO

id username email phone address birthdate salary
— ——— —————– ————- ————- ———– ——
1 user1 user1@example.com 13800138001 北京市朝阳区 1990-01-01 10000.00
2 user2 user2@example.com 13900139002 上海市浦东新区 1991-02-02 15000.00
3 user3 user3@example.com 13700137003 广州市天河区 1992-03-03 20000.00
4 user4 user4@example.com 13600136004 深圳市南山区 1993-04-04 25000.00
5 user5 user5@example.com 13500135005 杭州市西湖区 1994-05-05 30000.00

更多学习教程公众号风哥教程itpux_com。