使用行筛选器和列过滤筛选敏感表数据

项目
08/09/2024

本文提供了有关使用行筛选器、列过滤和映射表筛选表中敏感数据的指导和示例。这些功能需要 Unity 目录。

什么是行筛选器？

行筛选器让你可以向表应用筛选器，以便查询仅返回符合筛选器的行。行筛选器作为 SQL 用户定义函数 (UDF) 实现。 Python 和 Scala UDF 也受支持，但仅当它们包装在 SQL UDF 中时才受支持。

什么是列过滤？

列过滤让你可以将过滤函数应用于表列。掩码函数在查询运行时进行计算，将目标列的每个引用替换为掩码函数的结果。对于大多数用例，列过滤决定了是返回原始列值还是根据调用用户的标识对其进行修订。列掩码是编写为 SQL UDF 或包装在 SQL UDF 中的 Python 或 Scala UDF 的表达式。

每个表列只能应用一个掩码函数。过滤函数接收列的未过滤值作为输入，并返回过滤值作为结果。过滤函数的返回值应与要过滤的列的类型相同。过滤函数还可以接收其他列作为输入参数，并在其过滤逻辑中使用它们。

这些筛选器和动态视图之间有什么区别？

动态视图、行过滤器和列掩码都允许将复杂的逻辑应用于表并在查询运行时处理其过滤决策。

动态视图是一个或多个源表的抽象只读视图。用户无需直接访问源表即可访问动态视图。创建动态视图会定义一个新的表名称，该名称必须与任何源表的名称或存在于同一架构中的其他任何表和视图的名称匹配。

另一方面，将行筛选器或列掩码关联到目标表会将相应的逻辑直接应用于表本身，而无需引入新的表名称。后续查询可能会继续使用其原始名称直接引用目标表。

如果需要将转换逻辑（如筛选器和过滤）应用于只读表，并且接受用户使用不同的名称引用动态视图，请使用动态视图。如果要在使用 Delta Sharing 共享时筛选数据，则必须使用动态视图。如果要对特定数据筛选或计算表达式，但仍允许用户使用表的原始名称访问表，请使用行筛选器和列过滤。

开始之前

若要向表添加行筛选器和列掩码，必须具有：

为 Unity Catalog 启用的工作区。
在 Unity 目录中注册的函数。这可以是 SQL UDF，也可以是已在 Unity Catalog 中注册并包装在 SQL UDF 中的 Python 或 Scala UDF。有关详细信息，请参阅什么是用户定义的函数 (UDF)？、列掩码子句和 ROW FILTER 子句。

此外，还必须满足以下要求：

若要分配向表添加行过滤器或列掩码的函数，必须对该函数具有 EXECUTE 权限、对架构具有 USE SCHEMA 权限、对父目录具有 USE CATALOG 权限。
若要在创建新表时添加过滤器或掩码，还必须具有该架构的 CREATE TABLE 权限。
若要通过改变现有表来添加过滤器或掩码，你还必须是表所有者或对该表具有 MODIFY 权限。

若要访问具有行筛选器或列掩码的表，计算资源必须满足下列要求之一：

一个 SQL 仓库
在 Databricks Runtime 12.2 LTS 或更高版本中采用共享访问模式
在 Databricks Runtime 15.4 LTS 或更高版本（公共预览版）中采用单用户访问模式。

在 Databricks Runtime 15.3 或更低版本中，无法使用单用户计算读取行筛选器或列掩码。

若要利用 Databricks Runtime 15.4 LTS 及更高版本中提供的数据筛选，你还必须验证你的工作区是否已启用无服务器计算，因为支持行筛选器和列掩码的数据筛选功能在无服务器计算上运行。因此，当你使用单用户计算读取使用行筛选器或列掩码的表时，可能需要支付无服务器计算资源的费用。请参阅单用户计算上的精细访问控制。

应用行筛选器

若要创建行筛选器，请编写一个函数 (UDF)，以定义筛选器策略，然后将其应用于表。每个表只能有一个行筛选器。行筛选器接受零个或多个输入参数，其中每个输入参数绑定到相应表的一列。

可以使用目录资源管理器或 SQL 命令应用行筛选器。目录资源管理器说明假定你已经创建了一个函数，并且已在 Unity 目录中注册该函数。 SQL 说明包括创建行筛选器函数并将其应用于表的示例。

目录资源管理器

在 Azure Databricks 工作区中，单击 “目录”。
浏览或搜索要筛选的表。
在“概述”选项卡上，单击“行筛选器：添加筛选器”。
在“添加行筛选器”对话框中，选择包含筛选器函数的目录和架构，然后选择该函数。
在展开的对话框中，查看函数定义并选择与函数语句中包含的列匹配的表列。
单击“添加” 。

若要从表中移除筛选器，请单击“fx 行筛选器”，然后单击“删除”。

SQL

若要创建行筛选器，然后将其添加到现有表，请使用 CREATE FUNCTION 并使用 ALTER TABLE 应用函数。还可以在使用 CREATE TABLE 创建表时应用函数。

创建行筛选器：

CREATE FUNCTION <function_name> (<parameter_name> <parameter_type>, ...)
RETURN {filter clause whose output must be a boolean};

使用列名将行筛选器应用于表：

ALTER TABLE <table_name> SET ROW FILTER <function_name> ON (<column_name>, ...);

其他语法示例：

使用与函数参数匹配的常量文本将行筛选器应用于表：

ALTER TABLE <table_name> SET ROW FILTER <function_name> ON (<constant_literal>, ...);

从表中删除行筛选器：

ALTER TABLE <table_name> DROP ROW FILTER;

修改行筛选器：

Run a DROP FUNCTION statement to drop the existing function, or use CREATE OR REPLACE FUNCTION to replace it.

删除行筛选器：
```
ALTER TABLE <table_name> DROP ROW FILTER;
DROP FUNCTION <function_name>;
```
注意

在删除函数之前，必须执行 ALTER TABLE ... DROP ROW FILTER 命令。否则，该表将处于不可访问状态。

如果以这种方式无法访问表，请使用 ALTER TABLE <table_name> DROP ROW FILTER; 更改表并删除孤立行筛选器引用。

另请参阅 ROW FILTER 子句。

行筛选器示例

此示例创建一个适用于国家/地区 US 的 admin 组成员的 SQL 用户定义函数。

当为 sales 表应用此示例函数时，admin 组的成员可以访问表中的所有记录。如果函数由非管理员调用，则 RETURN_IF 条件将失败，并计算 region='US' 表达式，从而筛选表以仅显示 US 区域中的记录。

CREATE FUNCTION us_filter(region STRING)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), true, region='US');

将函数作为行筛选器应用于表。 sales 表中的后续查询则会返回行子集。

CREATE TABLE sales (region STRING, id INT);
ALTER TABLE sales SET ROW FILTER us_filter ON (region);

禁用行筛选器。 sales 表中的未来用户查询则会返回表中的所有行。

ALTER TABLE sales DROP ROW FILTER;

创建一个表，将该表的函数作为 CREATE TABLE 语句的一部分应用为行筛选器。 sales 表中的每个未来查询则会返回行子集。

CREATE TABLE sales (region STRING, id INT)
WITH ROW FILTER us_filter ON (region);

应用列掩码

若要应用列掩码，请创建一个函数 (UDF)，然后将其应用于表列。

可以使用目录资源管理器或 SQL 命令应用列掩码。目录资源管理器说明假定你已经创建了一个函数，并且已在 Unity 目录中注册该函数。 SQL 说明包括创建列掩码函数并将其应用于表列的示例。

目录资源管理器

在 Azure Databricks 工作区中，单击 “目录”。
浏览或搜索表。
在“概述”选项卡上，找到要向其应用列掩码的行，然后单击 “掩码”编辑图标。
在“添加列掩码”对话框中，选择包含筛选器函数的目录和架构，然后选择该函数。
在展开的对话框中，查看函数定义。如果函数包含除要屏蔽的列之外的任何参数，请选择要将这些附加函数参数转换为的表列。
单击“添加” 。

要从表中删除列掩码，请单击表行中的“fx 列掩码”，然后单击“移除”。

SQL

若要创建列掩码并将其添加到现有表列，请使用 CREATE FUNCTION 并使用 ALTER TABLE 应用掩码函数。还可以在使用 CREATE TABLE 创建表时应用函数。

使用 SET MASK 应用掩码函数。在 MASK 子句中，可以使用任何 Azure Databricks 内置运行时函数或调用其他用户定义函数。常见用例包括通过使用 current_user( ) 检查运行该函数的调用用户的身份或使用 is_account_group_member( ) 获取他们所属的组。有关详细信息，请参阅列 mask 子句和内置函数。

创建列过滤：

CREATE FUNCTION <function_name> (<parameter_name> <parameter_type>, ...)
RETURN {expression with the same type as the first parameter};

将列掩码应用于现有表中的列：

ALTER TABLE <table_name> ALTER COLUMN <col_name> SET MASK <mask_func_name> USING COLUMNS <additional_columns>;

其他语法示例：

使用与函数参数匹配的常量文本将列掩码应用于现有表中的列：

ALTER TABLE <table_name> ALTER COLUMN <col_name> SET MASK <mask_func_name> USING COLUMNS (<constant_name>, ...);

从表的列中删除列过滤：

ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;

修改列掩码：对现有函数执行 DROP 操作，或使用 CREATE OR REPLACE TABLE。
删除列任务：
```
ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;
DROP FUNCTION <function_name>;
```
注意

在删除函数前必须执行 ALTER TABLE 命令，否则表将处于不可访问状态。

如果以这种方式无法访问表，请使用 ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK; 更改表并删除孤立过滤引用。

列过滤示例

在此示例中，你将创建一个用户定义的函数来屏蔽 ssn 列，以便只有属于 HumanResourceDept 组成员的用户才能查看该列中的值。

CREATE FUNCTION ssn_mask(ssn STRING)
  RETURN CASE WHEN is_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;

将新函数作为列过滤应用于表。可以在创建表时或之后添加列掩码。

--Create the `users` table and apply the column mask in a single step:

CREATE TABLE users (
  name STRING,
  ssn STRING MASK ssn_mask);

--Create the `users` table and apply the column mask after:

CREATE TABLE users
  (name STRING, ssn STRING);

ALTER TABLE users ALTER COLUMN ssn SET MASK ssn_mask;

当查询用户不是 HumanResourceDept 组的成员时，对该表的查询现在会返回屏蔽的 ssn 列值：

SELECT * FROM users;
  James  ***-**-****

若要禁用列掩码，以便查询返回 ssn 列中的原始值，请执行以下操作：

ALTER TABLE users ALTER COLUMN ssn DROP MASK;

使用映射表创建访问控制列表

若要实现行级别安全性，请考虑定义映射表（或访问控制列表）。每个映射表都是一个全面的映射表，用于对原始表中可供某些用户或组访问的数据行进行编码。映射表非常有用，因为它们通过直接联接提供与事实数据表的简单集成。

此方法在解决许多具有自定义要求的用例方面非常有用。示例包括：

根据登录用户施加限制，同时顾及特定用户组的不同规则。
创建需要不同规则集的复杂层次结构（如组织结构）。
从外部源系统复制复杂的安全模型。

通过以这种方式采用映射表，可以有效地处理这些具有挑战性的场景，并确保可靠的行级和列级安全实现。

映射表示例

使用映射表检查当前用户是否在列表中：

USE CATALOG main;

创建新的映射表：

DROP TABLE IF EXISTS valid_users;

CREATE TABLE valid_users(username string);
INSERT INTO valid_users
VALUES
  ('fred@databricks.com'),
  ('barney@databricks.com');

创建新筛选器：

注意

除以调用方身份运行的用于检查用户上下文的函数（例如 CURRENT_USER 和 IS_MEMBER 函数）之外，所有筛选器均使用定义方的权限运行。

在此示例中，该函数检查当前用户是否位于 valid_users 表中。如果找到用户，该函数将返回 true。

DROP FUNCTION IF EXISTS row_filter;

CREATE FUNCTION row_filter()
  RETURN EXISTS(
    SELECT 1 FROM valid_users v
    WHERE v.username = CURRENT_USER()
);

以下示例在创建表期间应用行筛选器。以后还可以使用 ALTER TABLE 语句添加筛选器。应用于整个表时，请使用 ON () 语法。对于特定行，请使用 ON (row);。

DROP TABLE IF EXISTS data_table;

CREATE TABLE data_table
  (x INT, y INT, z INT)
  WITH ROW FILTER row_filter ON ();

INSERT INTO data_table VALUES
  (1, 2, 3),
  (4, 5, 6),
  (7, 8, 9);

从表中选择数据。只有当用户位于 valid_users 表中时，才会返回数据。

SELECT * FROM data_table;

创建一个映射表，其中包含应始终有权查看表中所有行的帐户，而不考虑列值：

CREATE TABLE valid_accounts(account string);
INSERT INTO valid_accounts
VALUES
  ('admin'),
  ('cstaff');

现在，创建一个 SQL UDF，如果行中所有列的值小于 5，或者调用用户是上述映射表的成员，则该 SQL UDF 返回 true。

CREATE FUNCTION row_filter_small_values (x INT, y INT, z INT)
  RETURN (x < 5 AND y < 5 AND z < 5)
  OR EXISTS(
    SELECT 1 FROM valid_accounts v
    WHERE IS_ACCOUNT_GROUP_MEMBER(v.account));

最后，将 SQL UDF 作为行筛选器应用于表：

ALTER TABLE data_table SET ROW FILTER row_filter_small_values ON (x, y, z);

支持和限制

所有 Azure Databricks 功能或所有计算资源都不支持行筛选器和列掩码。本部分列出了受支持的功能和限制。

支持的功能和格式

此受支持功能列表并不详尽。会列出某些项，是因为它们在公共预览版期间不受支持。

支持用于适用于 SQL 工作负载的 Databricks SQL 和 Databricks 笔记本。
支持具有 MODIFY 特权的用户的 DML 命令。筛选器和掩码适用于 UPDATE 和 DELETE 语句读取的数据，不适用于写入的数据（包括 INSERT）。
支持的数据格式：
- 用于托管表和外部表的 Delta 和 Parquet。
- 使用 Lakehouse Federation 在 Unity 目录中注册的外部表的多种其他数据格式。
策略参数中的常量表达式（字符串、数字、间隔、布尔值、null）。
SQL、Python 和 Scala UDF 只要在 Unity Catalog 中注册，即可作为行过滤器或列掩码函数受到支持。 Python 和 Scala UDF 必须包装在 SQL UDF 中。
可以对引用列掩码或行筛选器的表创建视图，但不能向视图添加列掩码或行筛选器。
只要架构与可能应用于目标表的行筛选器和列掩码兼容，就支持 Delta Lake 变更数据馈送。
支持外表。
支持进行表采样。
当源表、目标表或两者使用行过滤器和列掩码时，支持 MERGE 语句。这包括具有包含简单子查询的行过滤函数的表，但是存在一些限制，如下面的部分所示。
Databricks SQL 具体化视图和 Databricks SQL 流式处理表支持行筛选器和列掩码（公共预览版）：
- 可以向 Databricks SQL 具体化视图或流式处理表中添加行筛选器和列掩码。
- 可以在包含行筛选器和列掩码的表上定义 Databricks SQL 具体化视图或流式处理表。

性能注意事项

通过确保在筛选和屏蔽操作之前，用户无法查看基表值的内容，行筛选器和列掩码保证了数据的可见性。它们的设计旨在在最常见的用例下响应查询时表现良好。在频率较低的应用程序中，查询引擎必须选择优化查询性能并防止从筛选/掩码值泄露信息，它将始终做出安全决策，但会对查询性能产生一定影响。若要最大程度地降低这种性能影响，请遵循以下原则：

使用简单的策略函数：具有较少的策略函数的表达式通常性能优于更复杂的表达式。避免使用映射表和表达式子查询来支持简单的 CASE 函数。
减少函数参数的数目：即使查询中未使用这些列，Azure Databricks 也无法优化对由策略函数参数生成的源表的列引用。使用参数较少的策略函数，因为这些表中的查询通常性能更好。
避免添加包含过多 AND 连接符的行筛选器：由于每个表仅支持添加最多一行筛选器，因此一种常见方法是将多个所需策略函数与 AND 合并。但是，对于每个连接，连接包含本表其他地方提到的可能影响性能的组件（如使用映射表）的可能性会增加。使用更少的连接来提高性能。
使用无法从这些表中引发错误的确定性表达式：如果提供的输入无效（例如 ANSI 除法），某些表达式可能会引发错误。在这种情况下，SQL 编译器不得将这些表达式（如筛选器）向下推送操作（例如筛选器）在查询计划中推得太靠后，以避免在筛选和/或掩码操作之前泄漏有关值的“除以零”等错误的可能性。使用确定性且永不引发错误的表达式，例如此示例中的 try_divide。
通过表运行测试查询以衡量性能：构造实际查询，这些查询表示表所需的工作负荷，其中包含行筛选器和/或列掩码并测量性能。对策略函数进行小修改并观察其效果，直到在筛选和屏蔽逻辑的性能和表现力之间取得良好平衡。

限制

12.2 LTS 之前的 Databricks Runtime 版本不支持行筛选器或列过滤。这些运行时安全失败，这意味着如果尝试从这些运行时的不受支持的版本访问表，则不会返回任何数据。
增量共享不适用于行级安全性或列过滤。
不能将行级别安全性或列掩码应用于视图。
按时间顺序查看不适用于行级安全性或列过滤。
不支持对具有策略的表中文件的基于路径的访问。
不支持循环依赖项回到原始策略的行筛选器或列过滤策略。
不支持深克隆和浅克隆。
MERGE 语句不支持包含嵌套、聚合、窗口、限制或非确定性函数的行过滤策略的表。
不支持 Delta Lake API。

单用户计算限制

在 Databricks Runtime 15.3 或更低版本中，无法从单用户计算资源访问具有行筛选器或列掩码的表。在 Databricks Runtime 15.4 LTS 或更高版本（公共预览版）中，如果为你的工作区启用了无服务器计算，则可以使用单用户访问模式。有关详细信息，请参阅单用户计算上的精细访问控制。

通过

使用行筛选器和列过滤筛选敏感表数据

什么是行筛选器？

什么是列过滤？

这些筛选器和动态视图之间有什么区别？

开始之前

应用行筛选器

目录资源管理器

SQL

行筛选器示例

应用列掩码

目录资源管理器

SQL

列过滤示例

使用映射表创建访问控制列表

映射表示例

支持和限制

支持的功能和格式

性能注意事项

限制

单用户计算限制

反馈

其他资源