Encrypting and Decrypting Data
Once you have set up authentication with the SecureData appliance, call the VoltageSecureProtect and VoltageSecureAccess functions to encrypt and decrypt your data. At a minimum, you pass these functions the value to be encrypted or decrypted and value's FPE format (or 'auto' to have SecureData simply encrypt the value while preserving its format). If you specify an FPE format, it must match one of the formats defined by your SecureData Appliance. You can also capture these parameters in SQL Macros.
The following example demonstrates setting up the global policy URL and session parameters. Then it makes simple calls to VoltageSecureProtect and VoltageSecureAccess.
=> 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)
=> 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
=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='ssn');
VoltageSecureProtect
----------------------
376-69-6789
(1 row)
=> SELECT VoltageSecureProtect('123-45-6789' USING PARAMETERS format='auto');
VoltageSecureProtect
----------------------
820-31-5110
(1 row)
=> SELECT VoltageSecureAccess('376-69-6789' USING PARAMETERS format='ssn');
VoltageSecureAccess
---------------------
123-45-6789
(1 row)
Note that in the previous example, the last four digits of the social security number remained unchanged when the value was encrypted using the ssn format. However, it was completely encrypted when using the auto format. Leaving part of the value unencrypted is one of the features of SecureData's format-preservation. In this example, the SecureData Appliance's ssn format is defined to leave the last four digits of the social security number unencrypted. This format allows unprivileged users to use the unencrypted last four digits of the social security number to authenticate an individual without having access to their full social security number.
Encrypting Data During Load
When handling sensitive data, you often want to encrypt it as you load it. Encrypting on load means that the unencrypted values are never stored in your database. The following example demonstrates loading data using a COPY statement. Suppose you have a data filled with customer information with the following fields: id number, first name, last name, social security number, card verification number, and date of birth. Here is a sample of this data:
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
Suppose you want to encrypt the social security number and the credit card number columns. Then you can call VoltageSecureProtect to encrypt these columns in the COPY statement you use to load the data as following example demonstrates:
=> 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)
Always use the same FPE format to encrypt data in a column. If you use different FPE formats in the same column (such as loading some data using 'ssn' and other data using 'auto') there is no way to tell which format was used for any particular row. In this case, you will not be able to recover the unencrypted data because you cannot tell correctly decrypted values from incorrectly decrypted ones. Formats that deal with the same source format (such as ssn and ssn-all) are still incompatible, as they encrypt and decrypt the data in different ways.
Encrypting Non-VARCHAR Data
The VoltageSecureProtect function only encrypts VARCHAR values. If you need to encrypt other data types, such as DATE or INTEGER, you must cast these values to VARCHAR in your function call. The following example demonstrates how you can encrypt the date of birth (dob) column whose data type is DATE from the previous example.
=> 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)
The birthday FPE format used in this example is a custom format. To encrypt dates, you must create your own FPE format. Be sure to match your custom FPE format to standard Vertica date format of YYYY-MM-DD.
In the example, the dob column is cast to VARCHAR when passed to VoltageSecureProtect. The value the function returns is cast back to a DATE for storage in the table.
For data types such as DATE, you cannot use the auto FPE format. When SecureData encrypts a value using auto, it uses the full range of numbers and letters. It only preserves the number of characters and any separators. Attempting to encrypt a date using auto usually results in an invalid date:
=> 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
Decrypting Values in Queries
To decrypt encrypted values, call VoltageSecureAccess on the encrypted column's values in your query. The following example demonstrates querying the table from the previous example and decoding the SSN column. Instead of relying on session variables, this example uses the globally-accessible configuration file.
=> 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 VoltageSecureAccess function has no way of determining if the values you are passing it are actually encrypted or not. All this function does is take the input and transform it using the decryption key. If you pass it values from an unencrypted column, you will get back scrambled values. For example:
=> 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)
Decrypting Non-VARCHAR Columns
Similarly to encryption, you must cast any non-VARCHAR columns to VARCHAR when calling VoltageSecureAccess. If your query depends on the original data type of the column, you must also cast the return value of the function back to the column's original data type. The following example demonstrates decrypting the dob column from the customer2 table from a previous example.
=> 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)
In the previous example, casting the VoltageSecureAccess return value to DATE is not necessary, as the table is simply being displayed. However, if you create a query a for client application, you likely need this final cast to return the data type to the one the client expects.