VoltageSecureProtectAllKeys

This function helps you locate values in a column encrypted using an Embedded Format Preserving Encryption (eFPE) format.

This function helps you locate values in a column encrypted using an Embedded Format Preserving Encryption (eFPE) format. These formats use key rotation, so the encrypted value you get back for a piece of plain text changes over time. You pass this function an unencrypted value. It returns a table consisting of two columns: the unencrypted value and the value encrypted with each of the keys defined for the eFPE. The number of rows in the table are determined by the number of keys the eFPE format contains. Usually, you use the output of this function in a join to locate a matching encrypted value in a table.

Syntax

VoltageSecureProtectAllKeys(value USING PARAMETERS format='eFPE_format'
                            [, config_dfs_path=config_file]
                            [, identity=sd_identity] )

Parameters

value VARCHAR containing the value to encrypt. You must cast other data types (for example DATE values) to VARCHAR when calling this function.
format=eFPE_format String containing the name of an eFPE format defined by SecureData. This format must be an eFPE format defined by your SecureData Appliance, or the function returns an error. This format must also match the format of value. VoltageSecureProtectAllKeys returns an error if value's format does not match the one you specify in eFPE_format.
config_dfs_path=config_file String containing the file name of the configuration file to use when authenticating with the SecureData appliance. You must create this file using VoltageSecureConfigure. If you do not supply this parameter, you must set session parameters to configure access to SecureData. See Configuring access to SecureData. Any values set in session parameters override the values in this file.
identity=sd_identity String containing the identity to use when authenticating with SecureData. SecureData uses this value as a basis for the encryption key. This value usually takes the form of an email address. If supplied, it overrides any values set in the configuration file or session parameters.

Examples

The following example demonstrates a simple call to VoltageSecureProtectAllKeys.

=> SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                      format='cc_num',
                                      config_dfs_path='/voltagesecure/conf')
          OVER ();

      data       |    protected
-----------------+-----------------
 376765616314013 | XMVMRU9RJVU4013
 376765616314013 | X5FD4KO1UEE4013
 376765616314013 | M7ZXTIQVCPB4013
 376765616314013 | UBOSC9K3EXZ4013
 376765616314013 | ZJ1C50C9L9R4013
(5 rows)

In this example, the cc_num eFPE format has five keys defined for it, so the return value is a table containing five rows.

The following example shows a more common use: querying a table column that is encrypted using an eFPE format.

=> SELECT id, first_name, last_name FROM customers3 u
          JOIN (SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                                   format='cc_num',
                                                   config_dfs_path='/voltagesecure/conf')
          OVER ()) pak
          ON u.cc_num = pak.protected;

  id  | first_name | last_name
------+------------+-----------
 5345 | Thane      | Ross

(1 row)

In the previous example, the customers3 table is joined to the output from VoltageSecureProtectAllKeys. Any rows in the customers3 table where the encryted cc_num column value matches values from the protected column of VoltageSecureProtectAllKeys matches appear in the output.

This function returns an error if you use it on a non-eFPE format:

=> SELECT first_name, last_name, ssn FROM customers u
        JOIN (
            SELECT VoltageSecureProtectAllKeys('232-28-0657' USING PARAMETERS format='ssn',
                                               config_dfs_path='/voltagesecure/conf')
            OVER ()
        )
        pak ON u.ssn = pak.protected;
ERROR 5861:  Error calling processPartition() in User Function VoltageSecureProtectAllKeys
at [ProtectAllKeys.cpp:21], error code: 1711, message: Error getting key numbers:
eFPE format required

See also