使用 SQL 宏封装加密业务逻辑

VoltageSecureProtect 和 VoltageSecureAccess 函数的级别非常低，我们需要做足准备才能正确使用。这包括但不限于了解有关正在解密/加密的数据类型、要使用的 Voltage 格式、将输入值转换为 VARCHAR 然后恢复为所需的数据类型以及身份管理的信息。使用这么多动态组件编写查询可能很乏味。

为了简化此过程，您可以将此信息封装在 SQL 宏中，并使用 case 表达式以更动态的方式决定其行为。此方法有以下优势：

您可以将 Voltage 格式与数据的用途相关联，Vertica 将相应地自动加密和解密该值。
您可以自动执行所需的类型转换，从所需的数据类型转换为 VARCHAR。
THROW_ERROR 函数可以为宏提供一种输入验证形式。
您可以在加密期间为给定的 case 表达式指定身份，以便将解密权限限制为相同的身份。

注意

SQL 宏与用户定义的转换函数不兼容，例如 VoltageSecureProtectAllKeys。

要查看您的宏，请查询 USER_FUNCTIONS 系统表。

加密和解密宏模板

在以下宏中，data_purpose 参数描述了数据的用途（例如 temperature），value 参数指示了正在加密的值及其 data_type（例如 INT）。data_type 在整个宏中必须相同。

data_purpose 参数还与 voltage_format 和 voltage_identity 参数相关联，后两个参数分别用于控制数据的加密/解密方式以及可以访问数据的身份。

如果将不具有相应 case 表达式的 data_purpose 传递给函数，则 THROW_ERROR 函数将返回用户指定的错误，该错误本身必须转换为函数的返回 data_type。

请注意，如果遇到与指定转换不兼容的类型，标准转换运算符 :: 将完全终止查询，这在转换较大数据集时可能会出现问题。为防止查询终止，您可以使用 ::! 运算符首先尝试指定的转换，对于不兼容的类型则返回 NULL 值。有关详细信息，请参阅转换失败。

使用 VoltageSecureProtect 的加密宏

=> CREATE FUNCTION encryptDataType(data_purpose VARCHAR, value data_type) RETURN data_type
    AS BEGIN
    RETURN(CASE
        WHEN (data_purpose='data_purpose')
            then VoltageSecureProtect(value::VARCHAR USING PARAMETERS
                format='voltage_format',
                identity='voltage_identity')::data_type
        ELSE
            THROW_ERROR('no matching data_purpose')::data_type
        END);
    END;

使用 VoltageSecureAccess 的解密宏

=> CREATE FUNCTION decryptDataType(data_purpose VARCHAR, value data_type) RETURN data_type
    AS BEGIN
    RETURN(CASE
        WHEN (data_purpose='data_purpose')
            then VoltageSecureAccess(value::VARCHAR USING PARAMETERS
                format='voltage_format',
                identity='voltage_identity')::data_type
        ELSE
            THROW_ERROR('no matching data_purpose')::data_type
        END);
    END;

示例

以下示例显示如何管理含出生日期的列的加密和解密。在这种情况下，您可能需要定义一个单独的身份来对客户和员工数据进行加密。

=> SELECT * FROM customer_dob;
   dates
-----------
 1955-11-04
 1991-12-01
 1977-07-07
(3 rows)

对于加密，定义以下宏。

=> CREATE FUNCTION encryptDOB(data_purpose VARCHAR, value DATE) RETURN DATE
    AS BEGIN
    RETURN
        (CASE
            --The data_purpose parameter controls which identity is used during encryption;
            WHEN (data_purpose='customer')
            --Format, identities, and casting from DATE to VARCHAR and back to DATE are all encapsulated in the case expression;
                then VoltageSecureProtect(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='customer_data@example.com')::DATE
            WHEN (data_purpose='employee')
                then VoltageSecureProtect(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='employee_data@example.com')::DATE
            ELSE
                THROW_ERROR('Unsupported data_purpose -- You must pass ''customer'' when
                encrypting customer data or ''employee'' when encrypting employee data')::DATE
                --Because the return type of this macro is DATE, THROW_ERROR must also be casted to type DATE;
        END);
    END;

对 customer_dob 表中的日期列进行加密：

=> SELECT encryptDOB('customer', dates) FROM customer_dob;
 encryptDOB
------------
 2048-08-09
 1917-03-05
 2022-01-07

单独对某个值进行加密：

=> SELECT encryptDOB('customer', '1955-11-04');
 encryptDOB
------------
 2048-08-09

您可以为解密定义一个匹配的宏。由于加密数据只能使用匹配的身份进行解密，因此这些 case 表达式使用相同的 data_purpose 和身份进行解密。

对于解密，定义以下宏：

=> CREATE FUNCTION decryptDOB(data_purpose VARCHAR, value DATE) RETURN DATE
    AS BEGIN
    RETURN
        (CASE
            --The case expressions and parameters must match the ones for encryption;
            WHEN (data_purpose='customer')
                then VoltageSecureAccess(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='customer_data@example.com')::DATE
            WHEN (data_purpose='employee')
                then VoltageSecureAccess(value::VARCHAR USING PARAMETERS
                    format='birthday',
                    identity='employee_data@example.com')::DATE
            ELSE
                THROW_ERROR('Unsupported data_purpose -- You must pass ''customer'' when
                decrypting customer data or ''employee'' when decrypting employee data')::DATE
        END);
    END;

对嵌套调用中的加密列进行解密：

=> SELECT decryptDOB('customer', encryptDOB('customer', dates)) FROM customer_dob;
 decryptDOB
------------
 1955-11-04
 1991-12-01
 1977-07-07
(3 rows)

单独对某个值进行解密：

=> SELECT decryptDOB('customer', '2048-08-09');
 decryptDOB
------------
 1955-11-04

删除这些宏：

=> DROP FUNCTION encryptDOB(VARCHAR, DATE);
DROP FUNCTION
=> DROP FUNCTION decryptDOB(VARCHAR, DATE);
DROP FUNCTION