在 Amazon Redshift 中对 SUPER 数据类型应用细粒度访问和转换

作者：Ritesh Sinha Tahir Aziz Omama Khurshid发表日期：2024年6月19日分类：Amazon Redshift、分析、中级 (200)永久链接

关键要点

Amazon Redshift 现支持在 SUPER 数据类型列的路径上附加动态数据掩码DDM政策。数据掩码可以应用于 SUPER 列中的特定元素，实现更细致的数据保护。新增的 OBJECTTRANSFORM 函数允许更有效地对 JSON 数据进行转换。本文结合实际案例，演示如何根据用户的权限控制对敏感信息的访问。

Amazon Redshift 是一个快速、可扩展、安全且完全托管的云数据仓库，使用标准 SQL 和现有的 ETL提取、转换和加载、商业智能BI和报告工具，简单且经济高效地分析所有数据。数以万计的客户每天利用 Amazon Redshift 处理超过艾字节的数据，并驱动 BI、预测分析和实时流分析等分析工作负载。

数据掩码的应用场景

Amazon Redshift 支持在 SUPER 数据类型列的路径上附加 动态数据掩码DDM政策，并结合使用 OBJECTTRANSFORM 函数处理 SUPER 数据类型。

SUPER 数据类型的列包含 JSON 文档等半结构化数据。从前，Redshift 的数据掩码仅适用于普通表列，现在可以将掩码政策专门应用于 SUPER 列中的敏感字段，例如 JSON 文档中的信用卡号码。这种改进让 Redshift 在数据掩码的应用上提供了更大的灵活性。

通过在 Amazon Redshift 中支持 DDM，您可以：

定义掩码政策以处理信用卡、个人身份信息PII、HIPAA 或 GDPR 需求等自定义模糊处理策略。在查询时转换数据以应用掩码政策。将掩码政策附加到 角色 或用户上。将不同级别的多个掩码政策附加到同一表的同一列上并分配优先级以避免冲突。利用条件列实现单元级掩码。使用用户自定义函数UDFs来部分或完全删除数据或进行哈希处理。

解决方案概述

在本案例中，我们提供以下数据访问要求：

客服团队的用户可以查看订单数据，但无法查看 PII 信息。销售团队的用户可以查看客户 ID 和所有订单信息。高管团队的用户可以查看所有数据。员工无权查看任何数据。

下图展示了 Amazon Redshift 中 DDM 支持如何与角色和用户配合使用，以满足我们的零售用例。

该解决方案通过创建具有不同掩码规则的掩码政策，并将其附加到同一角色和表上分配优先级，从而消除潜在冲突。这些政策可以实现结果的假名化或选择性地使结果无效，符合零售商的安全要求。多个掩码政策附加到表上称为多模态掩码政策，包含以下三个部分：

定义数据模糊规则的数据掩码政策根据业务案例不同访问级别的角色能够在用户或角色与表的组合上附加多个掩码政策并设置优先级以解决冲突

前提条件

要实现该解决方案，您需要满足以下前提条件：

拥有 AWS 账户。拥有一台 Amazon Redshift 集群或一个 Amazon Redshift Serverless 端点。在 Amazon Redshift 数据仓库中具有超级用户权限或 syssecadmin 角色。

准备数据

要设置我们的用例，请完成以下步骤：

在 Amazon Redshift 控制台中，选择 查询编辑器 v2。

如果您熟悉 SQL Notebooks，可以下载演示的 SQL notebook 并导入，以快速入门。

创建表并填充数据：

sql 1 创建订单表 DROP TABLE IF EXISTS publicordertransaction CREATE TABLE publicordertransaction ( datajson SUPER )

2 填充样本值 INSERT INTO publicordertransaction VALUES ( JSONPARSE( { ccustkey 328558 cname Customer#000328558 cphone 5864367415 ccreditcard 4596209611290987 orders { oorderkey 8014018 oorderstatus F ototalprice 12085771 oorderdate 20240101 } }) ) ( JSONPARSE( { ccustkey 328559 cname Customer#000328559 cphone 7892327421 ccreditcard 8709000219329924 orders { oorderkey 8014019 oorderstatus S ototalprice 901598 oorderdate 20240101 } }) ) ( JSONPARSE( { ccustkey 328560 cname Customer#000328560 cphone 2765649023 ccreditcard 8765994378650090 orders { oorderkey 8014020 oorderstatus C ototalprice 1876556 oorderdate 20240101 } }) )

实现解决方案

为满足安全要求，我们需要确保每个用户根据其权限以不同方式查看相同的数据。为此，我们结合用户角色与掩码政策，实施如下步骤：

创建用户和角色，并将用户添加到相应角色中：

sql 创建四个用户 SET SESSION AUTHORIZATION admin CREATE USER Katecust WITH PASSWORD disable CREATE USER Kensales WITH PASSWORD disable CREATE USER Bobexec WITH PASSWORD disable CREATE USER Janestaff WITH PASSWORD disable

创建用户角色 CREATE ROLE custsrvcrole CREATE ROLE salessrvcrole CREATE ROLE executivesrole CREATE ROLE staffrole

授予角色给用户 GRANT ROLE custsrvcrole TO Katecust GRANT ROLE salessrvcrole TO Kensales GRANT ROLE executivesrole TO Bobexec GRANT ROLE staffrole TO Janestaff

授予角色权限 GRANT ALL ON ALL TABLES IN SCHEMA public TO ROLE custsrvcrole GRANT ALL ON ALL TABLES IN SCHEMA public TO ROLE salessrvcrole GRANT ALL ON ALL TABLES IN SCHEMA public TO ROLE executivesrole GRANT ALL ON ALL TABLES IN SCHEMA public TO ROLE staffrole

创建掩码政策：

sql 掩码完整数据 CREATE MASKING POLICY maskfull WITH(piidata VARCHAR(256)) USING (000000XXXX0000TEXT)

该政策将给定价格向下舍入到最接近的10 CREATE MASKING POLICY maskprice WITH(price INT) USING ((FLOOR(priceFLOAT / 10) 10)INT)

该政策将给定信用卡的前12位转换为 XXXXXXXXXXXX CREATE MASKING POLICY maskcreditcard WITH(creditcard TEXT) USING (XXXXXXXXXXXXTEXT SUBSTRING(creditcardTEXT FROM 13 FOR 4))

该政策掩码给定日期 CREATE MASKING POLICY maskdate WITH(orderdate TEXT) USING (XXXXXXXXTEXT)

该政策掩码给定电话号码 CREATE MASKING POLICY maskphone WITH(phonenumber TEXT) USING (XXXXXXTEXT SUBSTRING(phonenumberTEXT FROM 9 FOR 4))

附加掩码政策：客服用例的掩码政策：

sql SET SESSION AUTHORIZATION admin

ATTACH MASKING POLICY maskfull ON publicordertransaction(datajsonccustkey) TO ROLE custsrvcrole

ATTACH MASKING POLICY maskphone ON publicordertransaction(datajsoncphone) TO ROLE custsrvcrole

ATTACH MASKING POLICY maskcreditcard ON publicordertransaction(datajsonccreditcard) TO ROLE custsrvcrole

ATTACH MASKING POLICY maskprice ON publicordertransaction(datajsonordersototalprice) TO ROLE custsrvcrole

ATTACH MASKING POLICY maskdate ON publicordertransaction(datajsonordersoorderdate) TO ROLE custsrvcrole

销售用例的掩码政策：

sql SET SESSION AUTHORIZATION admin

ATTACH MASKING POLICY maskphone ON publicordertransaction(datajsoncphone) TO ROLE salessrvcrole

员工用例的掩码政策：

sql SET SESSION AUTHORIZATION admin

ATTACH MASKING POLICY maskfull ON publicordertransaction(datajsonordersoorderkey) TO ROLE staffrole

ATTACH MASKING POLICY maskfull ON publicordertransaction(datajsonordersoorderstatus) TO ROLE staffrole

ATTACH MASKING POLICY maskfull ON publicordertransaction(datajsonordersototalprice) TO ROLE staffrole

ATTACH MASKING POLICY maskdate ON publicordertransaction(datajsonordersoorderdate) TO ROLE staffrole

测试解决方案

让我们确认已经创建并附加掩码政策。

检查掩码政策是否已创建：

sql 确认掩码政策已创建 SELECT FROM svvmaskingpolicy

检查掩码政策是否已附加：

sql 验证表/列到用户/角色的附加掩码政策 SELECT FROM svvattachedmaskingpolicy

测试客服团队是否无法查看客户的 PII 数据，但能查看订单 ID、订单详情和状态：

sql SET SESSION AUTHORIZATION Katecust SELECT FROM ordertransaction

测试销售团队是否能查看客户 ID非 PII 数据和所有订单信息：

sql SET SESSION AUTHORIZATION Kensales SELECT FROM ordertransaction

测试高管是否能查看所有数据：

sql SET SESSION AUTHORIZATION Bobexec SELECT FROM ordertransaction

测试员工是否无法查看订单的任何数据，所有列都应为掩码状态：

sql SET SESSION AUTHORIZATION Janestaff SELECT FROM ordertransaction

OBJECTTRANSFORM 函数

在这里，我们深入探讨 OBJECTTRANSFORM 函数的能力和优势，并了解它如何帮助您高效地重塑数据以进行分析。Amazon Redshift 中的 OBJECTTRANSFORM 函数旨在通过直接操作数据库内的 JSON 数据，简化数据转换。使用该函数，您可以对半结构化数据或 SUPER 数据类型应用转换，从而更轻松地处理关系数据库环境中的复杂数据结构。

让我们看一些使用示例。

首先，创建表并填充内容：

sql 1 创建客户表

DROP TABLE IF EXISTS customerjson

CREATE TABLE customerjson ( colsuper SUPER coltext CHARACTER VARYING(100) ENCODE lzo) DISTSTYLE AUTO

2 填充样本数据

INSERT INTO customerjsonVALUES ( JSONPARSE( { person { name GREGORY HOUSE salary 120000 age 17 state MA ssn } } ) GREGORY HOUSE ) ( JSONPARSE( { person { name LISA CUDDY salary 180000 age 30 state CA ssn } } ) LISA CUDDY ) ( JSONPARSE( { person { name JAMES WILSON salary 150000 age 35 state WA ssn } } ) JAMES WILSON )

3 查询数据

SELECT FROM customerjson

使用 OBJECTTRANSFORM 函数应用转换：

sqlSELECT OBJECTTRANSFORM( colsuper KEEP personname personage personstate

    SET        personname LOWER(colsuperpersonnameTEXT)        personsalary colsuperpersonsalary  colsuperpersonsalary  01) AS colsupertransformed

FROM customerjson

如上述示例所示，通过使用 OBJECTTRANSFORM 应用转换，人员名称被格式化为小写，薪资提高了10。这展示了转换在处理半结构化或嵌套数据类型时的简便性。

清理

完成解决方案后，请清理您的资源：

从表中分离掩码政策：

sql 清理 重置会话授权为默认值 RESET SESSION AUTHORIZATION

白鲸加速器安装教程删除掩码政策：

sql DROP MASKING POLICY maskpiidata CASCADE

撤销或删除角色和用户：

sql REVOKE ROLE custsrvcrole FROM Katecust REVOKE ROLE salessrvcrole FROM Kensales REVOKE ROLE executivesrole FROM Bobexec REVOKE ROLE staffrole FROM Janestaff DROP ROLE custsrvcrole DROP ROLE salessrvcrole DROP ROLE executivesrole DROP ROLE staffrole DROP USER Katecust DROP USER Kensales DROP USER Bobexec DROP USER Janestaff

删除表：

sql DROP TABLE ordertransaction CASCADE DROP TABLE IF EXISTS customerjson

考虑事项和最佳实践

在实施此解决方案时，请考虑以下事项：

当将掩码政策附加到列的路径时，该列必须定义为 SUPER 数据类型。您只能将掩码政策应用于 SUPER 路径上的标量值，无法应用于复杂结构或数组。您可以向单个 SUPER 列的多个标量值应用不同的掩码政策，只要 SUPER 路径不冲突。例如，SUPER 路径 ab 和 abc 冲突，因为它们位于相同路径上，而 ab 是 abc 的父路径。SUPER 路径 abc 和 abd 不会冲突。

有关更多详细信息，请参阅 使用动态数据掩码与 SUPER 数据类型路径。

结论

在本文中，我们讨论了如何利用 Amazon Redshift 中对 SUPER 数据类型的 DDM 支持，定义基于配置的一致、安全和不可逆的数据掩码值。有了 DDM 支持，您可以使用熟悉的 SQL 语言控制数据掩码方法。您可以利用 Amazon Redshift 基于角色的访问控制功能来实现不同级别的数据掩码。您可以创建掩码政策，以识别需要被掩码的列，并灵活选择掩码数据的方式。例如，您可以完全隐藏数据的所有信息，部分真实值用通配符替换，或通过 SQL