VoltageSecureAccess

Calls SecureData to decrypt ciphertexts encrypted with VoltageSecureProtect.

Syntax

VoltageSecureAccess('ciphertext' [, 'tweak'] USING PARAMETERS
                    format='format_name'
                    [, mask=is_masked]
                    [, config_dfs_path='config_file']
                    [, identity=sd_identity]);

Parameters

ciphertext A VARCHAR value that was encrypted using SecureData. You must cast other data types (for example DATE values) to VARCHAR when calling this function.
tweak

VARCHAR value analogous to a salt that allows equivalent plaintexts to produce different ciphertexts. The same tweak value must for encryption and decryption of a given plaintext.

When encrypting or hashing an entire column, you can pass another column for a set of tweak values.

Never use two columns as tweak values for each other or else the original plaintext for both columns will be unrecoverable.
format_name

A string specifying the original FPE format used to generate the ciphertext. Note that SecureData has no way to tell if the value passed to it was actually encrypted or not, or what FPE format was used.
is_masked

A boolean, whether to mask the value when decrypting the ciphertext. Masking is defined on a per-format basis on the SecureData Appliance.

Note that since masking is optional, you must specify whether to decrypt with masking enabled. If you omit the masking parameter, the plaintext will be unmasked by default:
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.
sd_identity A string containing the identity to use when decrypting the data. Because SecureData uses the identity to determine encryption keys, this identity much match the identity used to encrypt the data. If supplied, this value overrides any identity value set in the configuration file or session parameter.

Examples

The following example decrypts a Social Security Number (SSN) originally encrypted with a predefined format.

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

 VoltageSecureAccess 
---------------------
 123-45-6789
(1 row)

This example demonstrates decrypting an encrypted column within a query.

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

  id  | first_name | last_name  |     ssn     |    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)

The following example decrypts Unicode using a predefined format. For a full list of predefined formats, consult the Voltage SecureData documentation.

=> SELECT VoltageSecureAccess('607-Òdìçç-ぶてぴねら' using parameters format='PREDEFINED::JU_AUTO_TYPE');

 VoltageSecureAccess
----------------------
 123-Hello-こんにちは

Decrypt a SSN ciphertext with the original FPE format and tweak value:

=> SELECT VoltageSecureAccess('721-21-2913', 'tweakvalue123' USING PARAMETERS 
                                  format='ssn-tweak',
                                  config_dfs_path='voltage.conf');
 VoltageSecureProtect
----------------------
 681-09-2913

Decrypt a ciphertext that was encrypted with a masking format. This format obscures all but the last two characters of the decrypted plaintext.

=> 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

See Also