Azure Synapse文件SQL数据库级用户访问过滤配置需求

1. 基础对象准备（文件映射为外部表） #

先将ADLS中的5个文件映射为Synapse SQL池的外部表，假设文件为Parquet格式，示例代码如下：

-- 创建外部数据源（替换为你的存储账户信息）
CREATE EXTERNAL DATA SOURCE ADLS_DataSource
WITH (
    LOCATION = 'abfss://your-container@your-storage-account.dfs.core.windows.net/',
    TYPE = HADOOP
);

-- 创建外部文件格式
CREATE EXTERNAL FILE FORMAT Parquet_Format
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

-- 为每个文件创建外部表（替换为实际列结构）
CREATE EXTERNAL TABLE dbo.file1
(
    id INT,
    content VARCHAR(100)
)
WITH (
    LOCATION = 'file1.parquet',
    DATA_SOURCE = ADLS_DataSource,
    FILE_FORMAT = Parquet_Format
);

-- 同理创建file2、file3、file4、file5的外部表

2. 创建数据库角色 #

为三个访问需求分别创建专属角色：

CREATE ROLE User1_Access_Role;
CREATE ROLE User2_Access_Role;
CREATE ROLE User3_Access_Role;

3. 构建限定访问的视图 #

针对每个角色创建仅包含允许访问文件的视图：

-- User1专属视图：仅包含file1和file2数据
CREATE VIEW dbo.User1_Allowed_Files
AS
SELECT * FROM dbo.file1
UNION ALL
SELECT * FROM dbo.file2;

-- User2专属视图：仅包含file3和file4数据
CREATE VIEW dbo.User2_Allowed_Files
AS
SELECT * FROM dbo.file3
UNION ALL
SELECT * FROM dbo.file4;

-- User3专属视图：仅包含file5数据
CREATE VIEW dbo.User3_Allowed_Files
AS
SELECT * FROM dbo.file5;

4. 配置角色权限 #

给每个角色授予对应视图的查询权限，同时限制直接访问原始外部表：

-- 授予视图查询权限
GRANT SELECT ON dbo.User1_Allowed_Files TO User1_Access_Role;
GRANT SELECT ON dbo.User2_Allowed_Files TO User2_Access_Role;
GRANT SELECT ON dbo.User3_Allowed_Files TO User3_Access_Role;

-- 拒绝角色直接访问未授权的外部表（增强安全性）
DENY SELECT ON dbo.file1 TO User2_Access_Role, User3_Access_Role;
DENY SELECT ON dbo.file2 TO User2_Access_Role, User3_Access_Role;
DENY SELECT ON dbo.file3 TO User1_Access_Role, User3_Access_Role;
DENY SELECT ON dbo.file4 TO User1_Access_Role, User3_Access_Role;
DENY SELECT ON dbo.file5 TO User1_Access_Role, User2_Access_Role;

5. 单一用户切换角色访问 #

使用同一个数据库用户，通过EXECUTE AS切换角色实现不同权限的访问：

-- 模拟User1访问
EXECUTE AS ROLE = 'User1_Access_Role';
SELECT * FROM dbo.User1_Allowed_Files;
REVERT; -- 恢复到原用户身份

-- 模拟User2访问
EXECUTE AS ROLE = 'User2_Access_Role';
SELECT * FROM dbo.User2_Allowed_Files;
REVERT;

-- 模拟User3访问
EXECUTE AS ROLE = 'User3_Access_Role';
SELECT * FROM dbo.User3_Allowed_Files;
REVERT;

替代方案：会话上下文+行级安全（RLS） #

如果希望通过传递用户标识自动过滤数据，无需手动切换角色，可使用RLS：

-- 创建包含所有文件的统一视图，增加FileName列标识来源
CREATE VIEW dbo.All_Files
AS
SELECT *, 'file1' AS FileName FROM dbo.file1
UNION ALL
SELECT *, 'file2' AS FileName FROM dbo.file2
UNION ALL
SELECT *, 'file3' AS FileName FROM dbo.file3
UNION ALL
SELECT *, 'file4' AS FileName FROM dbo.file4
UNION ALL
SELECT *, 'file5' AS FileName FROM dbo.file5;

-- 创建访问规则函数
CREATE FUNCTION dbo.File_Access_Predicate(@FileName VARCHAR(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Access_Allowed
WHERE 
    (SESSION_CONTEXT(N'User_ID') = 'User1' AND @FileName IN ('file1', 'file2'))
    OR (SESSION_CONTEXT(N'User_ID') = 'User2' AND @FileName IN ('file3', 'file4'))
    OR (SESSION_CONTEXT(N'User_ID') = 'User3' AND @FileName = 'file5');

-- 启用行级安全策略
CREATE SECURITY POLICY File_Access_Policy
ADD FILTER PREDICATE dbo.File_Access_Predicate(FileName)
ON dbo.All_Files
WITH (STATE = ON);

访问时设置会话上下文即可自动过滤：

-- User1访问
EXEC sp_set_session_context @key = N'User_ID', @value = 'User1';
SELECT * FROM dbo.All_Files; -- 仅返回file1、file2数据

-- User2访问
EXEC sp_set_session_context @key = N'User_ID', @value = 'User2';
SELECT * FROM dbo.All_Files; -- 仅返回file3、file4数据

-- User3访问
EXEC sp_set_session_context @key = N'User_ID', @value = 'User3';
SELECT * FROM dbo.All_Files; -- 仅返回file5数据

内容的提问来源于stack exchange，提问作者Arockia Jegan