搭配 SUPER 資料類型路徑使用動態資料遮罩

Amazon Redshift 支援將動態資料遮罩政策附加到 SUPER 類型資料欄的路徑。如需 SUPER 資料類型的相關資訊，請參閱 Amazon Redshift 中的半結構化資料。

將遮罩政策附加至 SUPER 類型資料欄的路徑時，請考慮下列事項。

• 將遮罩政策附加至資料欄上的路徑時，必須將該資料欄定義為 SUPER 資料類型。您只能將遮罩政策套用至 SUPER 路徑上的純量值。您無法將遮罩政策套用至複雜的結構或陣列。

• 只要 SUPER 路徑不衝突，您就可以將不同的遮罩政策套用至單一 SUPER 資料欄上的多個純量值。例如，SUPER 路徑 a.b 和 a.b.c 衝突，因為它們位於相同路徑，而且 a.b 是 a.b.c 的上層。SUPER 路徑a.b.c 和 a.b.d 沒有衝突。

• 在使用者查詢執行期套用政策之前，Amazon Redshift 無法檢查遮罩政策附加的路徑是否存在於資料中，且為預期類型的路徑。例如，當您將遮罩 TEXT 值的遮罩政策附加到包含 INT 值的 SUPER 路徑時，Amazon Redshift 會嘗試在路徑上轉換為該值的類型。

  在這種情況下，Amazon Redshift 在執行期的行為取決於您用於查詢 SUPER 物件的組態設定。根據預設，Amazon Redshift NULL處於寬鬆模式，並且會像指定的 SUPER 路徑一樣解析遺失的路徑和無效轉換。如需與 SUPER 相關之組態設定的詳細資訊，請參閱 SUPER 組態。

• SUPER 是一種無結構描述類型，這表示 Amazon Redshift 無法確認在指定的 SUPER 路徑上的值是否存在。如果您將遮罩政策附加到不存在的 SUPER 路徑，且 Amazon Redshift 處於寬鬆模式，則 Amazon Redshift 會將路徑解析為 NULL 值。建議您在將遮罩政策附加至 SUPER 資料欄的路徑時，考慮 SUPER 物件的預期格式，以及具有未預期屬性的可能性。如果您認為 SUPER 資料欄中可能有未預期的結構描述，請考慮將遮罩政策直接附加至 SUPER 資料欄。您可以使用 SUPER 類型資訊函數來檢查屬性和類型，並使用 OBJECT_TRANSFORM 遮罩值。如需 SUPER 類型資訊函數的詳細資訊，請參閱 SUPER 類型資訊函數。

範例

將遮罩政策附加至 SUPER 路徑

下列範例會將多個遮罩政策附加至一個資料欄中的多個 SUPER 類型路徑。

CREATE TABLE employees (
    col_person SUPER
);

INSERT INTO employees
VALUES
    (
        json_parse('
            {
                "name": {
                    "first": "John",
                    "last": "Doe"
                },
                "age": 25,
                "ssn": "111-22-3333",
                "company": "Company Inc."
            }
        ')
    ),
    (
        json_parse('
            {
                "name": {
                    "first": "Jane",
                    "last": "Appleseed"
                },
                "age": 34,
                "ssn": "444-55-7777",
                "company": "Organization Org."
            }
        ')
    )
;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO PUBLIC;

-- Create the masking policies.

-- This policy converts the given name to all uppercase letters.
CREATE MASKING POLICY mask_first_name
WITH(first_name TEXT)
USING ( UPPER(first_name) );

-- This policy replaces the given name with the fixed string 'XXXX'.
CREATE MASKING POLICY mask_last_name
WITH(last_name TEXT)
USING ( 'XXXX'::TEXT );

-- This policy rounds down the given age to the nearest 10.
CREATE MASKING POLICY mask_age
WITH(age INT)
USING ( (FLOOR(age::FLOAT / 10) * 10)::INT );

-- This policy converts the first five digits of the given SSN to 'XXX-XX'.
CREATE MASKING POLICY mask_ssn
WITH(ssn TEXT)
USING ( 'XXX-XX-'::TEXT || SUBSTRING(ssn::TEXT FROM 8 FOR 4) );

-- Attach the masking policies to the employees table.
ATTACH MASKING POLICY mask_first_name
ON employees(col_person.name.first)
TO PUBLIC;

ATTACH MASKING POLICY mask_last_name
ON employees(col_person.name.last)
TO PUBLIC;

ATTACH MASKING POLICY mask_age
ON employees(col_person.age)
TO PUBLIC;

ATTACH MASKING POLICY mask_ssn
ON employees(col_person.ssn)
TO PUBLIC;

-- Verify that your masking policies are attached.
SELECT
    policy_name,
    TABLE_NAME,
    priority,
    input_columns,
    output_columns
FROM
    svv_attached_masking_policy;

   policy_name   | table_name | priority |           input_columns           |          output_columns
-----------------+------------+----------+-----------------------------------+-----------------------------------
 mask_age        | employees  |        0 | ["col_person.\"age\""]            | ["col_person.\"age\""]
 mask_first_name | employees  |        0 | ["col_person.\"name\".\"first\""] | ["col_person.\"name\".\"first\""]
 mask_last_name  | employees  |        0 | ["col_person.\"name\".\"last\""]  | ["col_person.\"name\".\"last\""]
 mask_ssn        | employees  |        0 | ["col_person.\"ssn\""]            | ["col_person.\"ssn\""]
(4 rows)

-- Observe the masking policies taking effect.
SELECT col_person FROM employees ORDER BY col_person.age;

-- This result is formatted for ease of reading.
         col_person
--------------------------------
{
    "name": {
        "first": "JOHN",
        "last": "XXXX"
    },
    "age": 20,
    "ssn": "XXX-XX-3333",
    "company": "Company Inc."
}
{
    "name": {
        "first": "JANE",
        "last": "XXXX"
    },
    "age": 30,
    "ssn": "XXX-XX-7777",
    "company": "Organization Org."
}

以下是 SUPER 路徑的無效遮罩政策附件的一些範例。

-- This attachment fails because there is already a policy
-- with equal priority attached to employees.name.last, which is
-- on the same SUPER path as employees.name.
ATTACH MASKING POLICY mask_ssn
ON employees(col_person.name)
TO PUBLIC;
ERROR:  DDM policy "mask_last_name" is already attached on relation "employees" column "col_person."name"."last"" with same priority
               
-- Create a masking policy that masks DATETIME objects.
CREATE MASKING POLICY mask_date
WITH(INPUT DATETIME)
USING ( INPUT );
               
-- This attachment fails because SUPER type columns can't contain DATETIME objects.
ATTACH MASKING POLICY mask_date
ON employees(col_person.company)
TO PUBLIC;
ERROR:  cannot attach masking policy for output of type "timestamp without time zone" to column "col_person."company"" of type "super

以下是將遮罩政策附加至不存在的 SUPER 路徑的範例。預設情況下，Amazon Redshift 會將路徑解析到 NULL。

ATTACH MASKING POLICY mask_first_name
ON employees(col_person.not_exists)
TO PUBLIC;

SELECT col_person FROM employees LIMIT 1;

-- This result is formatted for ease of reading.
         col_person
-----------------------------------
{
    "name": {
        "first": "JOHN",
        "last": "XXXX"
    },
    "age": 20,
    "ssn": "XXX-XX-3333",
    "company": "Company Inc.",
    "not_exists": null
}