对数据进行加密、解密和哈希操作

使用 SecureData Appliance (SDA) 设置身份验证后，请调用 VoltageSecureProtect 和 VoltageSecureAccess 函数分别对数据进行加密/哈希操作或解密。至少，您需向这些函数传递要进行加密、哈希操作或解密的值，以及 Voltage SDA 中定义的值的 FPE 格式。您还可以捕获 SQL 宏和访问策略中的这些参数。

将 Vertica 连接到 SecureData Appliance

以下示例演示如何使用 Vertica 获取 SDA 客户端策略，然后演示如何设置会话参数以对 SDA 进行身份验证。

将 SecureData CA 证书添加到 Vertica。

使用 VoltageSecureConfigureGlobal 设置客户端策略。

=> SELECT VoltageSecureConfigureGlobal(USING PARAMETERS
    policy_url='https://voltage-pp-0000.example.com/policy/clientPolicy.xml')
    OVER ();
                           policy_url                            | allow_short_fpe | enable_file_cache
-----------------------------------------------------------------+-----------------+-------------------
 https://voltage-pp-0000.example.com/policy/clientPolicy.xml     |                 |
(1 row)

（可选）使用 VoltageSecureRefreshPolicy 检索客户端策略的最新版本。

=> SELECT VoltageSecureRefreshPolicy() OVER ();
                                  PolicyRefresh
-------------------------------------------------------------------------------------
Successfully refreshed policy on node [v_node0001]. Policy on other nodes
will be refreshed the next time a Voltage operation is run on them.
(1 row)

设置身份验证参数，以便使用 ALTER SESSION 或 VoltageSecureConfigure 以 Vertica 用户身份对 SDA 进行身份验证。有关此过程的详细信息，请参阅配置对 SecureData 的访问权限。

ALTER SESSION 将在会话期间设置这些参数，并覆盖配置文件中设置的参数。


=> ALTER SESSION SET UDPARAMETER FOR voltagesecurelib identity='alice@example.com';
ALTER SESSION
=> ALTER SESSION SET UDPARAMETER FOR voltagesecurelib username='alice';
ALTER SESSION
=> ALTER SESSION SET UDPARAMETER FOR voltagesecurelib shared_secret='my_secret';
ALTER SESSION

VoltageSecureConfigure 将这些参数保存到指定的 config_dfs_path。稍后，此配置文件也可以传递给其他 Voltage 函数。

=> SELECT VoltageSecureConfigure(USING PARAMETERS config_dfs_path='voltage.conf',
   username='alice', identity='alice@example.com', store_password=false) OVER ();

 config_dfs_path | identity          | username
-----------------+-------------------+----------
 voltage.conf    | alice@example.com | alice

=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS
    format='ssn',
    config_dfs_path='voltage.conf');

 VoltageSecureProtect
----------------------
 376-69-6789

对数据进行加密和解密

VoltageSecureProtect 采用在 SDA 和 VARCHAR 数据中定义的“格式保留加密 (FPE)”格式并返回保留格式的密文，稍后可以使用 VoltageSecureAccess 对此密文进行解密。也就是说，如果纯文本遵循电话号码的形式 (###-###-####)，则生成的密文将采用相同的形式。可以通过多种方式自定义格式，例如：屏蔽某些值、不加密字符串的某些部分等。

SDA 中的许多预定义格式之一是 ssn。ssn 格式将对除社会保险号 (SSN) 的最后四位之外的所有数字进行加密。这对客户支持等某些角色很有用，因为他们可能需要 SSN 的最后四位数字来通过电话验证个人身份。


=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='ssn');
 VoltageSecureProtect
----------------------
 376-69-6789
(1 row)

=> SELECT VoltageSecureAccess('376-69-6789' USING PARAMETERS format='ssn');
 VoltageSecureAccess
---------------------
 123-45-6789
(1 row)

另一种预定义格式是 auto，它可以处理各种数据（包括 SSN）。与 ssn 相比，auto 在 SDA 中配置为对纯文本中的所有字符进行加密。


=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='auto');
 VoltageSecureProtect
----------------------
 820-31-5110
 (1 row)

请注意，对密文进行解密时，您只能使用生成密文时所用的相同格式。因此，使用 ssn 创建的密文必须使用 ssn 进行解密。例如，使用 ssn 生成的密文传递格式 auto 只会返回不正确的纯文本。

在加载期间对数据进行加密

处理敏感数据时，您通常希望在加载时进行加密，从而使未加密的值不会存储在您的数据库中。

以下示例演示使用 COPY 加载数据。假设您的数据已填充客户信息，包含以下字段：身份证号、名字、姓氏、社会保险号、卡验证码和出生日期：

5345,Thane,Ross,559-32-0670,376765616314013,618,05-09-1996
5346,Talon,Wilkins,540-48-0784,4716511603424923,111,09-17-1941
5347,Daquan,Phelps,785-34-0092,342226134491834,294,05-08-1963
5348,Basia,Lopez,011-85-0705,4595818418314603,503,04-29-1940
5349,Kaseem,Hendrix,672-57-0309,4556 078 73 7944,693,03-11-1942
5350,Omar,Lott,825-45-0131,6462 0541 0799 6261,555,02-17-1956
5351,Nell,Cooke,637-50-0105,646 59756 30903 530,818,02-14-1995
5352,Illana,Middleton,831-47-0929,648 23640 86684 267,883,12-29-1949
5353,Garrett,Williamson,408-73-0207,5334 2702 1360 8370,869,11-06-1955
5354,Hanna,Ware,694-97-0394,543 38494 19219 254,586,08-08-1967

要对社会保险号和信用卡号列进行加密，请调用 VoltageSecureProtect 对用于加载数据的 COPY 语句中的上述列进行加密：

=> CREATE TABLE customers (id INTEGER, first_name VARCHAR, last_name VARCHAR,
                           ssn VARCHAR(11), cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
CREATE TABLE
=> COPY customers (id, first_name, last_name, ssn_raw FILLER VARCHAR(11),
        cc_num_raw FILLER VARCHAR(25), cvv, dob,
        ssn AS VoltageSecureProtect(ssn_raw USING PARAMETERS format='ssn',
                                    config_dfs_path='voltage.conf'),
     cc_num AS VoltageSecureProtect(cc_num_raw USING PARAMETERS format='cc',
                                       config_dfs_path='voltage.conf'))
     FROM '/home/dbadmin/customer_data.csv' DELIMITER ',';
 Rows Loaded
-------------
         100
(1 row)

=> SELECT * FROM customers ORDER BY id ASC LIMIT 10;
  id  | first_name | last_name  |     ssn     |       cc_num        | cvv |    dob
------+------------+------------+-------------+---------------------+-----+------------
 5345 | Thane      | Ross       | 072-52-0670 | 405939553794013     | 618 | 1996-05-09
 5346 | Talon      | Wilkins    | 348-30-0784 | 5350908688294923    | 111 | 1941-09-17
 5347 | Daquan     | Phelps     | 983-53-0092 | 133383311411834     | 294 | 1963-05-08
 5348 | Basia      | Lopez      | 490-63-0705 | 7979155436134603    | 503 | 1940-04-29
 5349 | Kaseem     | Hendrix    | 268-74-0309 | 3212 314 45 7944    | 693 | 1942-03-11
 5350 | Omar       | Lott       | 872-03-0131 | 4914 1839 6801 6261 | 555 | 1956-02-17
 5351 | Nell       | Cooke      | 785-90-0105 | 332 34312 95233 530 | 818 | 1995-02-14
 5352 | Illana     | Middleton  | 947-60-0929 | 219 06376 36044 267 | 883 | 1949-12-29
 5353 | Garrett    | Williamson | 333-23-0207 | 1126 1022 5922 8370 | 869 | 1955-11-06
 5354 | Hanna      | Ware       | 661-57-0394 | 106 09915 59049 254 | 586 | 1967-08-08
(10 rows)

当心

始终使用相同的 FPE 格式来加密列中的数据。 如果在同一列中使用不同的 FPE 格式（例如使用 ssn 加载某些数据，而使用 auto 加载另一些数据），则无法判断任一特定行使用了哪种格式，且无法区分加密正确或错误的密文。

对非 VARCHAR 数据进行加密

VoltageSecureProtect 函数仅对 VARCHAR 值进行加密，因此如果您需要对其他数据类型（例如 DATE 或 INTEGER）进行加密，则必须在函数调用中将这些值转换为 VARCHAR，然后在存储时将其转换回 DATE 或 INTEGER。

在上述示例的基础上，以下内容对包含日期类型 DATE 的出生日期 (dob) 列进行加密。

=> CREATE TABLE customers2 (id INTEGER, first_name VARCHAR, last_name VARCHAR, ssn VARCHAR(11),
                            cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
CREATE TABLE
=> COPY customers2 (id, first_name, last_name, ssn, cc_num, cvv, dob_raw FILLER DATE,
                    dob AS VoltageSecureProtect(dob_raw::VARCHAR USING PARAMETERS
                                                format='birthday',
                                                config_dfs_path='voltage.conf')::DATE)
        FROM '/home/dbadmin/customer_data.csv' DELIMITER ',';
 Rows Loaded
-------------
         100
(1 row)

=> SELECT * FROM customers2 ORDER BY id ASC LIMIT 10;
  id  | first_name | last_name  |     ssn     |       cc_num        | cvv |    dob
------+------------+------------+-------------+---------------------+-----+------------
 5345 | Thane      | Ross       | 559-32-0670 | 376765616314013     | 618 | 1902-03-09
 5346 | Talon      | Wilkins    | 540-48-0784 | 4716511603424923    | 111 | 2023-07-22
 5347 | Daquan     | Phelps     | 785-34-0092 | 342226134491834     | 294 | 2091-01-18
 5348 | Basia      | Lopez      | 011-85-0705 | 4595818418314603    | 503 | 1921-08-17
 5349 | Kaseem     | Hendrix    | 672-57-0309 | 4556 078 73 7944    | 693 | 1962-08-23
 5350 | Omar       | Lott       | 825-45-0131 | 6462 0541 0799 6261 | 555 | 1930-01-12
 5351 | Nell       | Cooke      | 637-50-0105 | 646 59756 30903 530 | 818 | 2098-01-01
 5352 | Illana     | Middleton  | 831-47-0929 | 648 23640 86684 267 | 883 | 1956-09-07
 5353 | Garrett    | Williamson | 408-73-0207 | 5334 2702 1360 8370 | 869 | 2079-03-25
 5354 | Hanna      | Ware       | 694-97-0394 | 543 38494 19219 254 | 586 | 1903-07-16
(10 rows)

注意

要对日期进行加密，您必须创建自己的 FPE 格式。确保将您自定义的 FPE 格式与 YYYY-MM-DD 的标准 Vertica 日期格式相匹配。该示例的 birthday 便是这样一种自定义格式。

请注意，您不能对 DATE 类型使用 auto 格式；auto 生成的密文将使用整个范围的数字和字母，且仅保留字符数和任何分隔符，因此生成的密文通常不能转换为日期。

=> SELECT VoltageSecureProtect('07-16-1969' USING PARAMETERS format='auto',
                               config_dfs_path='/voltagesecure/conf');
 VoltageSecureProtect
----------------------
 45-86-8651
(1 row)

=> SELECT VoltageSecureProtect('07-16-1969' USING PARAMETERS format='auto',
                               config_dfs_path='/voltagesecure/conf')::DATE;
ERROR 2992:  Date/time field value out of range: "45-86-8651"
HINT:  Perhaps you need a different "datestyle" setting

对查询中的值进行解密

要对表中存储的密文进行解密，请对查询中的已加密列调用 VoltageSecureAccess。

以下示例将查询表并对 SSN 列进行解密：


=> SELECT id,
          first_name,
          last_name,
          VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                              config_dfs_path='/voltagesecure/conf') AS ssn_plaintext,
          dob
      FROM customers
      WHERE dob < '1970-1-1'
      ORDER BY id ASC
      LIMIT 10;

  id  | first_name | last_name  |     ssn_plaintext     |    dob
------+------------+------------+-----------------------+------------
 5346 | Talon      | Wilkins    | 540-48-0784           | 1941-09-17
 5347 | Daquan     | Phelps     | 785-34-0092           | 1963-05-08
 5348 | Basia      | Lopez      | 011-85-0705           | 1940-04-29
 5349 | Kaseem     | Hendrix    | 672-57-0309           | 1942-03-11
 5350 | Omar       | Lott       | 825-45-0131           | 1956-02-17
 5352 | Illana     | Middleton  | 831-47-0929           | 1949-12-29
 5353 | Garrett    | Williamson | 408-73-0207           | 1955-11-06
 5354 | Hanna      | Ware       | 694-97-0394           | 1967-08-08
 5355 | Quinn      | Pruitt     | 818-91-0359           | 1965-11-14
 5356 | Clayton    | Santiago   | 102-56-0010           | 1958-02-02
(10 rows)

重要

VoltageSecureAccess 函数无法确定您传递给它的值实际上是否已加密；该函数只提取输入并使用解密密钥进行转换。如果您将未加密列中的值传递给该函数，您将得到干扰值。例如：

=> SELECT first_name,
          VoltageSecureAccess(first_name USING PARAMETERS format='auto',
                              config_dfs_path='/voltagesecure/conf')
                              AS decrypted_first_name
          FROM customers LIMIT 10;
 first_name | decrypted_first_name
------------+----------------------
 Omar       | Rftd
 Illana     | Clfkow
 Hanna      | Bodng
 Keith      | Hklnw
 Constance  | Cicgtmgtw
 Kirk       | Jwdv
 Eagan      | Hiksm
 Branden    | Ytqgngp
 Hope       | Tqzc
 Keane      | Pdcax
(10 rows)

对非 VARCHAR 列进行解密

与 VoltageSecureProtect 一样，VoltageSecureAccess 只能处理 VARCHAR 值。对非 VARCHAR 列中的密文进行解密时，必须先将其转换为 VARCHAR，然后再传递给 VoltageSecureAccess。如果您的查询或客户端应用程序依赖于该列的原始数据类型，则必须将返回的纯文本转换回该列的原始数据类型。

以下示例将列数据从 DATE 转换为 VARCHAR，将其传递给 VoltageSecureAccess，然后将纯文本转换回 DATE，从而完成对 dob 列的解密。


=> SELECT id, first_name, last_name,
          VoltageSecureAccess(dob::VARCHAR USING PARAMETERS format='birthday',
                              config_dfs_path='/voltagesecure/conf')::DATE AS dob
          FROM customers2 ORDER BY id LIMIT 10;
  id  | first_name | last_name  |    dob
------+------------+------------+------------
 5345 | Thane      | Ross       | 1996-05-09
 5346 | Talon      | Wilkins    | 1941-09-17
 5347 | Daquan     | Phelps     | 1963-05-08
 5348 | Basia      | Lopez      | 1940-04-29
 5349 | Kaseem     | Hendrix    | 1942-03-11
 5350 | Omar       | Lott       | 1956-02-17
 5351 | Nell       | Cooke      | 1995-02-14
 5352 | Illana     | Middleton  | 1949-12-29
 5353 | Garrett    | Williamson | 1955-11-06
 5354 | Hanna      | Ware       | 1967-08-08
(10 rows)

屏蔽已解密值

如果密文是使用指定“屏蔽”形式的格式创建的，则可使用 mask=true 参数屏蔽由 VoltageSecureAccess 返回的纯文本。如果未指定此参数，则无论格式如何，均会返回完整的纯文本。

如 Voltage SDA 中所定义，这种格式隐藏了除已解密纯文本的最后两个字符以外的所有字符。


=> SELECT VoltageSecureAccess('g3kbx6ru19', USING PARAMETERS
                                  format='maskedFormat',
                                  config_dfs_path='voltage.conf');
 VoltageSecureAccess
----------------------
 1234567890


=> SELECT VoltageSecureAccess('g3kbx6ru19', USING PARAMETERS
                                  format='maskedFormat',
                                  config_dfs_path='voltage.conf',
                                  mask=true);
 VoltageSecureAccess
----------------------
 XXXXXXXX90

调整密文

与 [salt](https://en.wikipedia.org/wiki/Salt_(cryptography) 类似，您可以额外传递“调整”值以添加到 VoltageSecureProtect 使用的密钥，以便从相同的纯文本中创建唯一的密文。稍后，“经过调整”的密文只能通过将相同的格式和“调整”值传递给 VoltageSecureAccess 来解密。可在 SDA 中设置是否支持此功能。

以下示例使用自定义格式 ssn-tweak 和值 tweakvalue123 来调整密文。


=> SELECT VoltageSecureProtect('681-09-2913', 'tweakvalue123' USING PARAMETERS
    format='ssn-tweak');

 VoltageSecureProtect
----------------------
 721-21-2913

=> SELECT VoltageSecureAccess('721-21-2913', 'tweakvalue123' USING PARAMETERS
    format='ssn-tweak');

 VoltageSecureProtect
----------------------
 681-09-2913

调整整列

常见的用例是使用一列中的值来调整另一列中的密文。

以下示例采用与上文类似的方式加载数据，但使用 last_name 列中的值来调整每个客户的出生日期。


=> CREATE TABLE customers (id INTEGER, first_name VARCHAR, last_name VARCHAR, ssn VARCHAR(11),
    cc_num VARCHAR(25), cvv VARCHAR(5), dob DATE);
CREATE TABLE

=> COPY customers (id, first_name, last_name, ssn_raw FILLER VARCHAR(11),
           cc_num_raw FILLER VARCHAR(25), cvv,
           dob_raw FILLER DATE, ssn AS VoltageSecureProtect(ssn_raw USING PARAMETERS format='ssn',
                                                            config_dfs_path='voltage.conf'),
           /* cast the dob_raw to VARCHAR and tweak with last_name, then cast back to DATE */
           dob AS VoltageSecureProtect(dob_raw::VARCHAR , last_name USING PARAMETERS format='date-tweak',
                                       config_dfs_path='voltage.conf')::DATE,
           cc_num AS VoltageSecureProtect(cc_num_raw USING PARAMETERS format='cc', config_dfs_path='voltage.conf'))
           FROM 'customers.csv' DELIMITER ',';

 Rows Loaded
-------------
         100
(1 row)

=> SELECT id, first_name, last_name, dob FROM customers ORDER BY id;
  id  | first_name | last_name  |    dob
------+------------+------------+------------
 5345 | Thane      | Ross       | 2086-03-07
 5346 | Talon      | Wilkins    | 1936-06-09
 5347 | Daquan     | Phelps     | 1953-12-01
 5348 | Basia      | Lopez      | 1979-11-30
 5349 | Kaseem     | Hendrix    | 2090-01-07
 5350 | Omar       | Lott       | 1984-07-27
 5351 | Nell       | Cooke      | 2053-03-20
 5352 | Illana     | Middleton  | 1988-03-06
 5353 | Garrett    | Williamson | 1927-03-03
 5354 | Hanna      | Ware       | 1998-08-28
 5355 | James      | May        | 1941-10-24
(11 rows)

要对该列进行解密，请在 VoltageSecureAccess 的调用中包含调整列。调整列必须包含与加密列时使用的相同数据。


=> SELECT id, first_name, last_name,
   VoltageSecureAccess(dob::VARCHAR , last_name USING PARAMETERS format='date-tweak',
   config_dfs_path='voltage.conf')::DATE AS dob
   FROM customers ORDER BY id;
 id  | first_name | last_name  |    dob
------+------------+------------+------------
 5345 | Thane      | Ross       | 1996-05-09
 5346 | Talon      | Wilkins    | 1941-09-17
 5347 | Daquan     | Phelps     | 1963-05-08
 5348 | Basia      | Lopez      | 1940-04-29
 5349 | Kaseem     | Hendrix    | 1942-03-11
 5350 | Omar       | Lott       | 1956-02-17
 5351 | Nell       | Cooke      | 1995-02-14
 5352 | Illana     | Middleton  | 1949-12-29
 5353 | Garrett    | Williamson | 1955-11-06
 5354 | Hanna      | Ware       | 1967-08-08
 5355 | James      | May        | 1967-08-08
(11 rows)

使用存储过程调整列

由于调整值成为加密密钥的一部分，因此调整列的内容在加密和解密时必须相同。为避免失去对纯文本的访问权限，在修改调整列时，您必须：

对密文进行解密
修改调整列
使用修改后的调整列的内容对纯文本数据重新进行加密

您可以使用存储过程自动执行此过程。例如，在使用姓氏进行调整时，如需更新客户出生日期，则存储过程可能如下所示：

=> CREATE PROCEDURE update_dob (employee_id INT, tweak_value VARCHAR) AS $$
  BEGIN
    PERFORM UPDATE customers
      SET dob = (
        WITH dob_ AS (SELECT VoltageSecureAccess(dob::VARCHAR , last_name
          USING PARAMETERS format='date-tweak', config_dfs_path='voltage.conf')::DATE AS dob
            FROM customers
            WHERE id = employee_id)
        SELECT VoltageSecureProtect(dob::VARCHAR, tweak_value
          USING PARAMETERS format='date-tweak', config_dfs_path='voltage.conf')::DATE
            FROM dob_)
      WHERE ID = employee_id;

    PERFORM UPDATE customers SET last_name = tweak_value WHERE ID = employee_id;

    PERFORM COMMIT;
  END;
$$;

稍后，您可以调用该过程来更新已加密值和调整值：

=> CALL update_dob(5349, 'Henderson');

对数据进行哈希操作

VoltageSecureProtect 还可以在保留数据格式的同时对数据进行哈希操作。当您需要对数据进行匿名处理以符合隐私标准，且与此同时保持客户端代码可访问性时，这一功能非常有用。

您应该记住以下几点：

哈希是一种单向操作，因此原始纯文本不可恢复
这种保留格式的哈希函数和熵较低的纯文本（姓名、日期、SSN 等）的性质，意味着它比标准哈希函数更有可能发生冲突

以下示例使用 customFPH 格式 ssnHash 对 SSN 进行加密。


=> SELECT VoltageSecureProtect('681-09-2913' USING PARAMETERS
                                  format='ssnHash',
                                  config_dfs_path='voltage.conf');
 VoltageSecureProtect
----------------------
 841-68-2913