Azure Synapse Dedicated SQL pool企业权限管理

我有一个组织，组织里一些部门，部门里由多人组成，组织的数据存储在Azure Synapse Dedicated SQL pool数据仓库里，我希望让不同的部门的成员有不同的数据访问权限，包括表权限，列级权限，行级权限和数据掩码及其组合。

在 Azure Synapse Analytics 专用 SQL 池中实现细粒度权限管理，可通过以下结构化方案实现多层级数据访问控制：

配合Azure Purview进行数据资产分类，实现基于敏感度标签的自动策略应用。

---

1. 身份体系构建

-- 创建部门专属角色
CREATE ROLE Dept_Finance;
CREATE ROLE Dept_HR;
CREATE ROLE Dept_Sales;

-- 将AD组/用户映射到角色
EXEC sp_addrolemember 'Dept_Finance', 'finance-group@domain.com';
EXEC sp_addrolemember 'Dept_HR', 'hr-user@domain.com';

---

2. 表级权限控制

-- 财务部拥有SalesData完全权限
GRANT SELECT, INSERT, UPDATE ON SalesData TO Dept_Finance;

-- 销售部只读访问
GRANT SELECT ON SalesData TO Dept_Sales;

-- HR部门禁止访问敏感表
DENY SELECT ON EmployeeSalaries TO Dept_HR;

---

3. 列级安全实现

方案A：精确列授权

-- 允许HR查看员工基本信息列
GRANT SELECT ON Employees(EmployeeID, FirstName, LastName) TO Dept_HR;

-- 屏蔽薪资字段
DENY SELECT ON Employees(Salary, Bonus) TO Dept_HR;

方案B：动态列掩码

-- 创建电话号码掩码
ALTER TABLE Customers 
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(3,"-XXXX",2)');

-- 配置掩码可见权限
GRANT UNMASK TO Dept_Finance;

---

4. 行级安全策略

-- 创建行过滤函数
CREATE FUNCTION dbo.fn_DeptFilter(@DeptID AS int)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS RETURN  
SELECT 1 AS result  
WHERE 
    @DeptID = CAST(SESSION_CONTEXT(N'DepartmentID') AS int) 
    OR IS_MEMBER('AdminRole') = 1;

-- 应用安全策略
CREATE SECURITY POLICY DeptFilter  
ADD FILTER PREDICATE dbo.fn_DeptFilter(DepartmentID) ON dbo.SalesData,
ADD BLOCK PREDICATE dbo.fn_DeptFilter(DepartmentID) ON dbo.SalesData AFTER INSERT;

---

5. 上下文参数传递

# 用户登录时注入部门标识
Invoke-SqlCmd -Query "EXEC sp_set_session_context @key=N'DepartmentID', @value=123"

---

6. 组合权限验证测试

-- 模拟HR用户访问
EXECUTE AS USER = 'hr-user@domain.com';
SELECT * FROM Employees; -- 应仅显示基础列
SELECT * FROM SalesData; -- 应返回空集
REVERT;

-- 验证财务数据访问
EXECUTE AS USER = 'finance-user@domain.com';
SELECT * FROM CustomerPayments; -- 应显示完整数据
REVERT;

---

7. 监控与审计配置

-- 启用SQL审计
CREATE DATABASE AUDIT SPECIFICATION [DataAccessAudit]
FOR SERVER AUDIT [SynapseAudit]
ADD (SELECT, UPDATE, INSERT ON DATABASE::[YourDB] BY [public])
WITH (STATE = ON);

---

实施建议

1. 权限分层设计：采用角色嵌套架构，创建基础权限角色（如DataReader_Base）实现权限复用

2. 动态权限管理：通过自动化脚本同步AD组与数据库角色成员关系

# 自动化角色同步示例
Sync-ADGroupToSQLRole -ADGroup "Finance" -SQLRole "Dept_Finance"

3. 敏感数据保护：对核心字段启用Always Encrypted技术

CREATE COLUMN MASTER KEY [CMK_Auto1] 
WITH (KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
      KEY_PATH = 'https://yourvault.vault.azure.net/keys/CMK/123');

CREATE COLUMN ENCRYPTION KEY [CEK_Salary]
WITH VALUES (
    COLUMN_MASTER_KEY = [CMK_Auto1],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x01AB...);

4. 定期权限审查：使用内置报表分析权限分配

SELECT 
    r.name AS role_name,
    m.name AS member_name,
    o.name AS object_name,
    p.permission_name
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
LEFT JOIN sys.database_permissions p ON p.grantee_principal_id = r.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id;

该方案通过RBAC体系实现：

• 垂直权限控制（表/列级）
• 水平权限控制（行级）
• 动态数据脱敏
• 上下文感知访问