印刻数据库

外观

SQLServer 行级安全性 ​

行级安全性（Row-Level Security，RLS）是 SQL Server 提供的一种数据访问控制机制，允许根据用户的身份或执行上下文来控制对表中行数据的访问权限。RLS 可以确保用户只能访问他们有权查看的数据行，从而实现更精细的数据安全控制。

行级安全性概念 ​

核心组件 ​

行级安全性主要包含以下核心组件：

  • 安全策略（Security Policy）：将筛选谓词或阻止谓词应用到表上的对象
  • 筛选谓词（Filter Predicate）：限制用户能够查看的行，类似于 WHERE 子句
  • 阻止谓词（Block Predicate）：限制用户能够插入、更新或删除的行
  • 内联表值函数（Inline Table-Valued Function）：定义用于行级过滤的逻辑

工作原理 ​

  1. 当用户访问受 RLS 保护的表时，SQL Server 会自动将筛选谓词应用到查询中
  2. 筛选谓词会根据用户的执行上下文（如用户名、角色、会话上下文）来决定哪些行对用户可见
  3. 阻止谓词则会在数据修改操作时检查，确保用户只能修改他们有权访问的行

行级安全性配置 ​

1. 创建测试表 ​

首先创建一个包含敏感数据的测试表：

sql
CREATE TABLE Sales (
    SalesID INT PRIMARY KEY IDENTITY(1,1),
    Product VARCHAR(50),
    Region VARCHAR(50),
    Amount DECIMAL(10, 2),
    Salesperson VARCHAR(50)
);

INSERT INTO Sales (Product, Region, Amount, Salesperson)
VALUES 
    ('Laptop', 'North', 1200.00, 'Alice'),
    ('Desktop', 'North', 900.00, 'Alice'),
    ('Tablet', 'South', 600.00, 'Bob'),
    ('Smartphone', 'South', 800.00, 'Bob'),
    ('Monitor', 'East', 300.00, 'Charlie'),
    ('Keyboard', 'West', 50.00, 'David');

2. 创建内联表值函数 ​

创建一个内联表值函数来定义行级过滤逻辑：

sql
CREATE FUNCTION dbo.fn_securitypredicate(@Salesperson AS VARCHAR(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE @Salesperson = USER_NAME()
        OR USER_NAME() = 'dbo'; -- dbo 可以查看所有行

3. 创建安全策略 ​

创建安全策略并将筛选谓词应用到表上：

sql
CREATE SECURITY POLICY SalesFilterPolicy
    ADD FILTER PREDICATE dbo.fn_securitypredicate(Salesperson) 
    ON dbo.Sales
    WITH (STATE = ON);

4. 创建测试用户并分配权限 ​

sql
CREATE USER Alice WITHOUT LOGIN;
CREATE USER Bob WITHOUT LOGIN;
CREATE USER Charlie WITHOUT LOGIN;
CREATE USER David WITHOUT LOGIN;

GRANT SELECT ON Sales TO Alice, Bob, Charlie, David;

5. 测试行级安全性 ​

sql
-- 以 Alice 身份执行
EXECUTE AS USER = 'Alice';
SELECT * FROM Sales;
-- 只返回 Alice 的销售数据
REVERT;

-- 以 Bob 身份执行
EXECUTE AS USER = 'Bob';
SELECT * FROM Sales;
-- 只返回 Bob 的销售数据
REVERT;

-- 以 dbo 身份执行
SELECT * FROM Sales;
-- 返回所有销售数据

6. 添加阻止谓词 ​

阻止用户修改不属于自己的数据：

sql
ALTER SECURITY POLICY SalesFilterPolicy
    ADD BLOCK PREDICATE dbo.fn_securitypredicate(Salesperson) 
    ON dbo.Sales
    FOR INSERT, UPDATE, DELETE
    WITH (STATE = ON);

高级行级安全性配置 ​

使用会话上下文 ​

可以使用会话上下文来传递更复杂的上下文信息，如部门、角色等：

sql
-- 创建使用会话上下文的函数
CREATE FUNCTION dbo.fn_securitypredicate_by_region(@Region AS VARCHAR(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE @Region = SESSION_CONTEXT(N'Region')
        OR USER_NAME() = 'dbo';

-- 创建安全策略
CREATE SECURITY POLICY SalesRegionFilterPolicy
    ADD FILTER PREDICATE dbo.fn_securitypredicate_by_region(Region) 
    ON dbo.Sales
    WITH (STATE = OFF);

-- 设置会话上下文并测试
EXEC sp_set_session_context 'Region', 'North';
SELECT * FROM Sales;

多表关联的行级安全 ​

对于复杂的数据模型，可以在多个相关表上应用行级安全性：

sql
-- 创建部门表
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    Manager VARCHAR(50)
);

-- 创建员工表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID),
    Salary DECIMAL(10, 2)
);

-- 创建安全函数
CREATE FUNCTION dbo.fn_employee_security(@DepartmentID AS INT)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_employee_security_result
    WHERE EXISTS (
        SELECT 1 FROM dbo.Departments
        WHERE DepartmentID = @DepartmentID
        AND Manager = USER_NAME()
    ) OR USER_NAME() = 'dbo';

-- 应用安全策略
CREATE SECURITY POLICY EmployeeFilterPolicy
    ADD FILTER PREDICATE dbo.fn_employee_security(DepartmentID) 
    ON dbo.Employees
    WITH (STATE = ON);

行级安全性性能影响 ​

性能考量 ​

行级安全性会对查询性能产生一定影响，主要包括：

  • 谓词应用开销：SQL Server 需要为每个查询添加筛选谓词
  • 执行计划复杂性：可能导致执行计划更复杂，特别是对于复杂的安全函数
  • 索引使用：如果安全函数使用了索引列，可能会影响索引的使用效率

性能优化建议 ​

  1. 保持安全函数简单：避免在安全函数中使用复杂逻辑或多表关联
  2. 使用索引列：在安全函数中使用表的索引列，以提高谓词求值效率
  3. 避免在安全函数中使用函数：如 GETDATE()、NEWID() 等，这些函数可能导致查询无法使用索引
  4. 定期监控性能：使用 Query Store、Extended Events 等工具监控 RLS 对查询性能的影响
  5. 考虑分区策略：对于大型表，可以结合分区策略和 RLS，进一步提高查询性能

版本差异 ​

SQL Server 版本行级安全性支持情况
SQL Server 2012不支持
SQL Server 2014不支持
SQL Server 2016支持基本行级安全性
SQL Server 2017支持，性能优化
SQL Server 2019支持，增强了对内存优化表的支持
SQL Server 2022支持，增强了与 Azure SQL Database 的兼容性
Azure SQL Database支持
Azure SQL Managed Instance支持

行级安全性最佳实践 ​

设计最佳实践 ​

  1. 明确定义安全需求：在实施 RLS 前，清晰定义数据访问控制规则和业务需求
  2. 分层安全设计：将 RLS 与其他安全机制（如加密、角色管理）结合使用
  3. 使用最小权限原则：只授予用户完成工作所需的最小权限
  4. 考虑数据完整性：确保 RLS 不会影响数据的完整性和一致性
  5. 定期审计：定期审计 RLS 策略和数据访问情况

实施最佳实践 ​

  1. 测试充分：在生产环境部署前，充分测试 RLS 对查询性能和数据访问的影响
  2. 文档化策略：详细记录 RLS 策略、函数和设计决策
  3. 定期审查：定期审查 RLS 策略，确保它们仍然符合业务需求
  4. 考虑备份恢复：在备份和恢复操作中，确保 RLS 策略和函数被正确处理
  5. 使用 SCHEMABINDING：在安全函数中使用 SCHEMABINDING，防止底层对象被意外修改

故障排除 ​

  1. 权限问题：确保用户有足够的权限访问安全函数和策略
  2. 函数逻辑错误：检查安全函数的逻辑是否正确，特别是 NULL 值处理
  3. 性能问题：使用 Query Store 分析查询性能，识别 RLS 相关的性能瓶颈
  4. 策略状态：检查安全策略是否处于启用状态
  5. 上下文传递：确保会话上下文或执行上下文被正确设置

常见问题（FAQ） ​

Q1: 行级安全性会影响所有查询吗？ ​

A1: 是的，行级安全性会影响所有访问受保护表的查询，包括 SELECT、INSERT、UPDATE 和 DELETE 操作。SQL Server 会自动将安全谓词应用到这些查询中。

Q2: 行级安全性可以与其他安全机制结合使用吗？ ​

A2: 是的，行级安全性可以与其他安全机制（如角色管理、加密、动态数据屏蔽）结合使用，以实现更全面的数据安全保护。

Q3: 行级安全性会影响索引视图吗？ ​

A3: 行级安全性可以应用到索引视图上，但需要注意的是，索引视图的安全性策略必须与基表的安全性策略兼容，否则可能导致查询结果不一致。

Q4: 如何禁用行级安全性？ ​

A4: 可以使用以下命令禁用安全策略：

sql
ALTER SECURITY POLICY SalesFilterPolicy WITH (STATE = OFF);

Q5: 行级安全性会影响备份和恢复操作吗？ ​

A5: 行级安全性策略和函数会作为数据库的一部分被备份和恢复，因此在恢复数据库后，RLS 策略会自动生效。但需要注意的是，恢复到不同的环境时，可能需要重新配置用户和权限。

Q6: 可以在内存优化表上使用行级安全性吗？ ​

A6: 从 SQL Server 2016 开始，支持在内存优化表上使用行级安全性，但需要注意的是，内存优化表的 RLS 实现与传统表有所不同，可能会有不同的性能特征。

Q7: 如何审计行级安全性的使用情况？ ​

A7: 可以使用 SQL Server Audit 或 Extended Events 来审计行级安全性的使用情况，包括策略的创建、修改和删除，以及受 RLS 保护的表的访问情况。

Q8: 行级安全性会影响查询计划缓存吗？ ​

A8: 是的，行级安全性会影响查询计划缓存。由于不同用户可能有不同的执行上下文，SQL Server 可能需要为不同用户生成不同的查询计划，这可能导致缓存膨胀。

总结 ​

行级安全性是 SQL Server 提供的一种强大的数据访问控制机制，可以帮助组织实现更精细的数据安全管理。通过合理设计和实施 RLS 策略，可以确保用户只能访问他们有权查看和修改的数据，从而保护敏感数据的安全性和完整性。

在实施行级安全性时，需要充分考虑性能影响，并遵循最佳实践，以确保 RLS 策略既能够满足安全需求，又不会对系统性能造成过大影响。同时，需要定期审查和更新 RLS 策略，以适应不断变化的业务需求和安全威胁。