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

本文提供了有关使用行筛选器、列过滤和映射表筛选表中敏感数据的指导和示例。

什么是行筛选器？

行筛选器让你可以向表应用筛选器，以便后续查询仅返回筛选器谓词的计算结果为 true 的行。 行筛选器作为 SQL 用户定义函数 (UDF) 实现。

若要创建行筛选器，请先编写 SQL UDF 来定义筛选器策略，然后使用 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> 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>;

行筛选器示例

创建应用于 US 区域中的 admin 组成员的 SQL 用户定义函数。

使用此函数，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);

什么是列过滤？

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

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

若要应用列过滤，请创建一个函数，并使用 ALTER TABLE 语句将其应用于表列。 或者，可以在创建表时应用过滤函数。

列过滤语法

在 MASK 子句中，可以使用任何 Azure Databricks 内置运行时函数或调用其他用户定义函数。 常见用例包括使用 current_user( ) 检查运行函数的用户的标识，或者使用 is_account_group_member( ) 检查该用户所属的组。

创建列过滤：

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 <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>;

列过滤示例

在此示例中，你将创建一个用户定义的函数来屏蔽 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');

创建新筛选器：

在此示例中，该函数检查当前用户是否位于 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);

可支持性

• 支持用于适用于 SQL 工作负载的 Databricks SQL 和 Databricks 笔记本。
• 支持具有 MODIFY 特权的用户的 DML 命令。 筛选器和过滤应用于 UPDATEE 和 DELET 读取的数据，不应用于写入的数据（包括 INSERTed 数据）。
• 支持的格式：Delta 和 Parquet。 仅托管表或外部表支持 Parquet。
• 支持具有列过滤或行筛选器的表的视图。
• 只要架构与可能应用于目标表的行筛选器和列过滤兼容，就支持 Delta Lake 变更数据馈送。
• 支持外表。

限制

• 12.2 LTS 之前的 Databricks Runtime 版本不支持行筛选器或列过滤。 这些运行时安全失败，这意味着如果尝试从这些运行时的不受支持的版本访问表，则不会返回任何数据。
• 增量实时表具体化视图和流式处理表不支持行筛选器或列过滤。
• 不支持将 Python 或 Scala UDF 直接作为行筛选器或列过滤函数。 但是，只要这些定义永久存储在目录中（换句话说，不是临时存储在会话中），就可以在 SQL UDF 中引用它们。
• 增量共享不适用于行级安全性或列过滤。
• 按时间顺序查看不适用于行级安全性或列过滤。
• 表采样不适用于行级安全性或列过滤。
• 目前不支持对具有策略的表中文件的基于路径的访问。
• 不支持循环依赖项回到原始策略的行筛选器或列过滤策略。
• 不支持 MERGE 和浅表克隆。

单用户群集限制

不要将行筛选器或列过滤添加到要从单个用户群集访问的任何表。 这通常在 Azure Databricks 作业的上下文中完成。 在公共预览期间，应用筛选器或过滤后，将无法从单个用户群集访问表。