Share this article:

Vertica Integration with SecureData - Protecting Enterprise Data

Click for a PDF version of this article.

Securing Your Vertica Data with SecureData - Overview

Traditional security solutions focus on encryption for data at rest, which protects data in storage only.  SecureData protects Vertica data at rest, in motion, and in use. 

SecureData can protect sensitive data as soon as it is acquired and ensures that the data is always used, transferred, and stored in protected form. Selected applications decrypt the data only at the time when it is required. Other applications work with encrypted or masked data.

SecureData product line provides two technologies for protecting data.

  • Format-Preserving Encryption (FPE)
  • Secure Stateless Tokenization (SST)

Replacing the original data with either an encrypted value or a random token narrows the possible exposure of data and can reduce audit scope and compliance costs.

Format-Preserving Encryption (FPE)

SecureData provides Format-Preserving Encryption (FPE), which maintains the data format. Using FPE does not require changes to the application. FPE can selectively encrypt sensitive data like SSNs or credit card numbers, while protecting the data and meeting the compliance standards. Because FPE maintains data format, logical value, and referential integrity, you can perform analytics on the de-identified data.

Embedded Format-Preserving Encryption (eFPE) enables key rotation policy to be embedded into data and eliminates the need to re-encrypt the data with key change.

Secure Stateless Tokenization (SST)

Secure Stateless Tokenization (SST) replaces data values with a token—a random string of text. SST is “stateless” because it eliminates the token database and removes the need to store cardholder names or other sensitive data. SST produces a unique, random token for each clear text Primary Account Number (PAN) input, resulting in a token that has no relationship to the original PAN. No token database is required with SST technology, thus improving the speed, scalability, security, and manageability of the tokenization process. As with FPE, you can also perform analytics on the de-identified data with SST.

This document explains how Vertica uses encryption and tokenization functions to achieve data-centric security on data fields and sub-fields that contain personally identifiable information (PII), payment card information (PCI), protected health information (PHI), and other sensitive data. These functions leverage the standard Vertica UDX architecture for writing custom functions. These functions also use the SecureData Simple API functions and SecureData Web Services API functions to protect your data.

About This Document

This document explains:

  • How to configure SecureData for use with the protection functions
  • How to compile and register the protection functions in Vertica
  • How to protect your data as it moves in and out of Vertica

You can download the protection functions that leverage SecureData to achieve data-centric security from the Big Data Marketplace. The download package includes sample scripts and sample data that demonstrate the capabilities of the protection functions. You can customize and extend this code to develop new capabilities.

Important  Vertica does not support the code examples in this document. The code examples underwent limited testing and should not be used in production.

SecureData Integration with Vertica

The protection functions have been tested with:

  • Vertica 7.1.x
  • SecureData Appliance version 5.7.2 or later. This appliance is a Linux ISO image that you install on a server. The image file contains a hardened Linux-based operating system.
  • SecureData Simple API (for Java) and SecureData Web Services API version 4.0 or later

The following high-level workflow shows the steps required to integrate SecureData with Vertica. The subsequent sections describe these steps in detail:

  1. Configure your SecureData Appliance with the required data formats and credential authentication information.
  2. Validate the appliance configuration.
  3. Compile and register the protection functions that support encryption and tokenization.
  4. Review the sample data and scripts and examples that demonstrate the capabilities of protection functions.

Configuring the SecureData Appliance

On the SecureData Appliance, you must define the formats that specify the data protection type as encryption or tokenization. In addition, you need to configure authentication methods on the SecureData Appliance to authenticate client applications to dynamically generate keys. The sample scripts and the data that you download use shared secret for authentication. (A shared secret is information known only to the parties involved in the transaction, such as passwords or private keys.)

Configuring Data Formats

You can use the following data formats to perform encryption and tokenization on your data fields with the protection functions:

  • Alphanumeric
  • cc-fpe-6-4
  • cc-sst-6-4
  • DATE-ISO-8601
  • SSN

The alphanumeric and SSN formats are preconfigured on all SecureData servers.

The following example shows how to configure the cc-fpe-6-4 data, using the Luhn algorithm to encrypt credit card numbers:

image002.png

The next example shows how to specify the encryption settings for the DATE-ISO-8601 data type:

image003.png

The next example shows how to specify tokenization for the cc-sst-6-4 credit card format:

tokenization_credit_card.png

Configuring Authentication

Client applications such as the protection functions must request keys from the SecureData appliance. In the following figure, you define the rules for authenticating the identity of the key requester. In this dialog box, you specify the identity pattern, IP address, and a secret string that the client application needs to know when requesting a key from the appliance. if that information matches what you have configured, the client application receives the key.

image004.png

Once the deployment completes, the Deployment Needed message disappears.

image008.png

Note The default values in the SecureData configuration file vsconfig.properties use an identity of test and a Shared Secret authentication method, with the secret string voltage123. If you use different values on your server, you must update vsconfig.properties with those values. The secret string must be passed from the client application.

Validating the SecureData Appliance

To verify that the Vertica cluster can access the SecureData Appliance, use the curl command for the URL of the appliance, as shown in the following example:

$ curl https://voltage-pp-0000.dataprotection.voltage.com/policy/clientPolicy.xml

This curl command returns a set of XML data, ending with the </clientPolicy> tag. The receipt of XML file confirms that you have successfully established a connection to the server.

Compiling and Registering the Vertica Java Functions

Vertica provides protection functions that use the SecureData Simple API and SecureData Web Services API to perform encryption and tokenization to protect your data.

  • PROTECT and ACCESS perform Format-Preserving Encryption (FPE) and Secure Stateless Tokenization.
  • AESEncrypt and AESDecrypt perform traditional AES encryption.
  • Base64Encode and Base64Decode perform encoding and decoding on the data fields.

You can download all the protection functions from the Big Data Marketplace.

In addition, Vertica provides the capability to develop your own functions.

About Vertica UDFs

User-Defined Functions (UDFs) in Vertica are functions that are stored in external shared libraries. You use the Vertica SDK to develop UDFs in C++, R, or Java. To define the external libraries in the Vertica catalog, use the CREATE LIBRARY statement.

The primary strengths of UDFs are:

  • They can be used anywhere that an internal function can be used.
  • They take full advantage of Vertica's distributed computing features. The extensions usually execute in parallel on each node in the cluster.
  • Vertica handles the distribution of the UDF library to the individual nodes. You only need to copy the library to the initiator node.
  • Vertica handles all the complicated aspects of developing a distributed piece of analytic code. Your main programming task is to read the data, process it, and then write it out using the Vertica SDK APIs.

Installing Java on All Nodes

To run Java functions on your Vertica database, you must install Java on every host in your cluster. For Java-based features, Vertica requires a 64-bit Java 6 (Java version 1.6) or later.

Installing Java on your Vertica cluster is a multi-step process:

  1. Download Java from http://www.java.com/en/.
  2. Install Java on all the hosts in your cluster.
  3. Set the JavaBinaryForUDx Vertica configuration parameter to the location of the Java executable.

Once you have installed Java on each host, verify that the java command is in the search path and references the correct JVM:

$ java -version

This command should return results similar to: 

java version "1.6.0_33"
 OpenJDK Runtime Environment (IcedTea6 1.13.5) (rhel-1.13.5.1.el6_6-x86_64)
 OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)

Setting the JavaBinaryForUDx Configuration Parameter

The Vertica JavaBinaryForUDx configuration parameter specifies the location of the Java executable on each node in the cluster. To set this parameter, you must be a database superuser. Execute the following statement:

=> SELECT SET_CONFIG_PARAMETER('JavaBinaryForUDx','/usr/bin/java');

To view the current setting of this configuration parameter, query the CONFIGURATION_PARAMETERS system table:

=> \x
 Expanded display is on.
 => SELECT * FROM CONFIGURATION_PARAMETERS WHERE parameter_name = 'JavaBinaryForUDx';
 -[ RECORD 1 ]-----------------+----------------------------------------------------------
 node_name                     | ALL
 parameter_name                | JavaBinaryForUDx
 current_value                 | /usr/bin/java
 default_value                 |
 change_under_support_guidance | f
 change_requires_restart       | f
 description                   | Path to the java binary for executing UDx written in java

Compiling and Registering Protection Functions

After you download the protection functions from the Big Data Marketplace, the subfolders under the Voltage_Java_UDF folder have the following contents:

  • src—Source code for protection functions
  • test—Sample data and SQL scripts to demonstrate protection function capabilities.
  • trustStore—Trusted root certificates as .pem files, and a symbolic link to each .pem file with a name derived from a hash of the contained certificate (*.0).

Note Suppose the certificate that the SecureData server uses is signed by another certificate authority, such as one specific to your organization. In that case, you need to obtain that certificate and import it into the trustStore folder.

You can modify these settings based on the configuration of your SecureData environment:

  • Lib—Copy the SecureData Simple API jar and the Web Services API jar files to this folder.
  • Makefile—Makefile to build, configure, install, and demonstrate the protection functions.

To compile and install the protection functions on Vertica, running the makefile performs the following steps:

  1. Uninstall the existing SecureData-related functions, if any.
  2. Set the Vertica JavaBinaryForUDx configuration parameter.
  3. Compile the Java code.
  4. Run the c_rehash utility to generate a symbolic link to each .pem file (trusted root certificates) with a name derived from a hash of the contained certificate (*.0). For example, c_rehash creates the symbolic link 5d58b78d.0 -> vertica.pem for the certificate vertica.pem.
  5. Install the protection functions with Vertica.

Protecting Your Data

After you have compiled and installed the protection functions, verify that they work with Vertica SQL statements as expected. The test folder contains the sample data and the sample SQL scripts.

Sample Data and Sample Scripts

The test.sql file in the test subfolder contains a set of SQL statements that use protection functions to perform encryption and tokenization on the sample data.

The testudf makefile, also in the test subfolder, loads the sample data into HP Vertica and runs the sample queries. After it completes, the output is stored in testresult.txt.

>make testudf

The CSV files contain the following data.

plaintext.csv

The plaintext.csv file includes 10,000 rows of plaintext data in the following columns:

  • 0—Column identifier
  • 1—First and last name
  • 2—Street name
  • 3—City name
  • 4—State abbreviation
  • 5—Zip code
  • 6—Phone number
  • 7—Email address
  • 8—Date of birth
  • 9—Credit card number
  • 10—Credit card CVV
  • 11—US Social Security number

The sample data is stored in the Vertica table voltage_sample.

The row for id 1 contains the following data.

=> SELECT * FROM voltage_sample WHERE id=1;
  id |       name       |   street   |      city      | state | postcode |     phone     
          email          | birth_date |        cc        | cvv |     ssn
 ----+------------------+------------+----------------+-------+----------+---------------+------------------------+------------+------------------+-----+-------------
   1 | Tyshawn Medhurst | Verl Plaza | New Lianemouth | LA    | 44638    | (405)920-0731 | oheidenreich@gmail.com | 2007-03-02 | 8559604272399885 | 825 | 336-68-2285
 (1 row)

creditscore.csv

The creditscore.csv file includes 10,000 rows of plaintext data in two columns:

  • US Social Security number: These values are identical to the values in column 11 of the csv file.
  • Three-digit credit score

The Vertica table voltage_sample_creditscore stores the data. One row from that table contains the following data.

=> SELECT * FROM voltage_sample_creditscore WHERE ssn = '336-68-2285';
      ssn     | creditscore
 -------------+-------------
  336-68-2285 | 621
 (1 row)

You can join the voltage_sample_creditscore and voltage_sample tables on the ssn column.

Protection Functions

The following sections describe the protection supported as part of integration with Vertica.

SecureData provides Simple API calls that perform the protection on the client side. The Web Services calls take place on the SecureData server and require that the client send the data across the network.

Simple API calls are faster than Web Services calls because they do not require the network communication. They connect with the SecureData server at the beginning to authenticate the client. When the authentication completes successfully, the SecureData server generates the key dynamically. Then, SecureData caches the key on the client side for further reference and performs the rest of the operations locally.

You can perform tokenization only through Web Services calls because the SST tokenization table resides on trusted and hardened systems only. This architectural approach:

  • Protects the tokenization table from cyber-attacks.
  • Significantly reduces the PCI audit scope.
  • Reduces the attack surface by removing live payment card information from virtually all systems and application except for the trusted host.

Format-Preserving Encryption (FPE)

Format-Preserving Encryption (FPE) does not change the format of data on encryption. Use the PROTECT and ACCESS functions to perform FPE.

PROTECT and ACCESS take three optional parameters:

  • FORMAT—Format for performing the encryption. Default is ‘Auto’.
  • ENCTYPE—Type of encryption: FPE or EFPE. Default is ‘FPE’.
  • API—Specifies if protection should occur through Simple APIs or WS calls. Default is ‘Simple’.

The following examples show how to encrypt and decrypt Social Security numbers using FPE. The first example does not specify any parameter, so the default values are used. In the second example, the PROTECT and ACCESS calls specify parameter values:

=> SELECT PROTECT('232-34-2345');
    PROTECT
 -------------
  368-76-6383
 (1 row)
 => SELECT ACCESS('368-76-6383');
    ACCESS
 -------------
  232-34-2345
 (1 row)
=> SELECT PROTECT('232-34-2345' USING PARAMETERS FORMAT='ssn', ENCTYPE='fpe',    API='simple');
    PROTECT
 -------------
  953-57-2345
 (1 row)
=> SELECT ACCESS('953-57-2345' USING PARAMETERS FORMAT='ssn' , ENCTYPE='fpe' , API='simple');
    ACCESS
 ------------
  232-34-2345
 (1 row)

Embedded Format-Preserving Encryption (eFPE)

Embedded FPE allows you to embed the key policy along with the encrypted data. As a result, you do not need to re-encrypt your data if the key changes in the future.

=> SELECT PROTECT('675-03-4941' USING PARAMETERS FORMAT='SSNeFPE', ENCTYPE='efpe');
    PROTECT
 -------------
  J54-RS-T0LE
 (1 row)
 => SELECT ACCESS('J54-RS-T0LE' USING PARAMETERS FORMAT='SSNeFPE');
  ACCESS
 -------------
  675-03-4941
 (1 row)

Secure Stateless Tokenization (SST)

Secure Stateless Tokenization replaces the data field with a random token.  Tokenization can happen only through Web Services calls. The following examples show how to protect a credit card number using SST:

=> SELECT PROTECT('123456789123456' USING PARAMETERS FORMAT='cc-sst-6-4', API='ws');
 PROTECT
 -----------------
  123456871603456
 (1 row)
=> SELECT ACCESS('123456871603456' USING PARAMETERS FORMAT='cc-sst-6-4', API='ws');
      ACCESS
 -----------------
  123456789123456
 (1 row)

AES Encryption

AES encryption allows you to encrypt data using the traditional encryption techniques. The following example shows how AES encryption works:

=> SELECT AESENCRYPT('hello');                                                                                                                                                   AESENCRYPT                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  30819D06092A864886F70D010706A0818F30818C02010030818606092A864886F70D0107013067060A6086480186FD1E050106305904000C557465737440746573742E696E743A3037303130313030303030305A3A6461746170726F74656374696F6E2E766F6C746167652E636F6D23313138383531353237393A646174613A4145532D454D45533A3235363A3A80109B1349B4742BBE8422FD39F45D5FAECD
 (1 row)
=> SELECT AESDYCRYPT('30819D06092A864886F70D010706A0818F30818C02010030818606092A864886F70D0107013067060A6086480186FD1E050106305904000C557465737440746573742E696E743A3037303130313030303030305A3A6461746170726F74656374696F6E2E766F6C746167652E636F6D23313138383531353237393A646174613A4145532D454D45533A3235363A3A80109B1349B4742BBE8422FD39F45D5FAECD');
  AESENCRYPT
 ------------
  hello
 (1 row)

Base64 Encoding

Base64 encoding encodes non-ASCII data as ASCII text before sending it to Vertica. The following example shows how Base64 encoding works.

=> SELECT BASE64ENCODE('¡¢£¤¥§');
    BASE64ENCODE
 ------------------
  wqHCosKjwqTCpcKn
 (1 row)
 => SELECT BASE64DECODE('wqHCosKjwqTCpcKn');
  BASE64DECODE
 --------------
  ¡¢£¤¥§
 (1 row)

The next example uses the PROTECT/ACCESS and BASE64ENCODE/BASE64DECODE functions to secure the binary data before sending it to Vertica:

=> SELECT PROTECT(BASE64ENCODE('¡¢£¤¥§'));
      PROTECT
 ------------------
  jqNZbpEdesDRpmWm
 (1 row)
 => SELECT BASE64DECODE(ACCESS('jqNZbpEdesDRpmWm'));
  BASE64DECODE
 --------------
  ¡¢£¤¥§
 (1 row)

The following sections further demonstrate the capabilities of the protection functions with Vertica.

Protecting the Data with SecureData Capabilities

The following sections describe examples of how to use the capabilities of SecureData to protect your Vertica data.  

Protecting Data When Using COPY to Load Data

The following statement encrypts the Social Security number column while loading the data from a CSV file into Vertica:

=>\set input_file '''':t_pwd'/plaintext.csv'''
=> COPY voltage_sample(id, name, street, city, state, postcode, 
   phone, email, birth_date, cc, cvv, ssnfiller FILLER varchar, 
   ssn 
   AS PROTECT(ssnfiller USING PARAMETERS format='SSN')) FROM 
   :input_file DELIMITER ',' NULL '' direct;

Protection functions use the SecureData Simple APIs or WS calls to protect your data. These are CPU-intensive operations. For loading huge amounts of data, you can split the data into chunks or smaller files. When you specify ON ANY NODE in the COPY command, Vertica loads the data onto all nodes to perform parallel loading, utilizing the resources on all the nodes:

=> \set input_file '''':t_pwd'/plaintext*.csv'''
=> COPY voltage_sample_encrypt( id, name, street, city, state, 
   postcode, phone, email, birth_date, cc, cvv, ssnfiller FILLER VARCHAR,
   ssn 
   AS PROTECT(ssnfiller USING PARAMETERS format='SSN')) FROM 
   :input_file ON ANY NODE DELIMITER ',' NULL '' direct;

If the data resides on nodes outside the Vertica cluster, you can still divide large files into smaller files. Use COPY LOCAL to optimize the load performance.

Protecting Data When Using INSERT to Load Data

The following statement uses INSERT to load the data, and uses the PROTECT function to encrypt the Social Security number before storing it in the table:

=> INSERT INTO voltage_sample VALUES(
   1,
   'Tyshawn Medhurst',
   'Verl Plaza',
   'New Lianemouth',
   'LA',
   46638,
   '(405)920-0731',
   'oheidenreich@gmail.com',
   '2007-03-02',
   8559604272399885,
   825,
   PROTECT('336-68-2285' USING PARAMETERS format = 'SSN'));

Similarly, you can encrypt certain data that you need to protect when loading with COPY or INSERT. The following example shows how to use the PROTECT function to protect the credit card number using SST. Note that the API parameter must be 'ws' when using tokenization:

=> \set input_file '''':t_pwd'/plaintext.csv'''
=> COPY voltage_sample(id, name, street, city, state, postcode, phone, 
   email, birth_date, ccfiller FILLER VARCHAR, cc 
   AS PROTECT(ccfiller USING PARAMETERS FORMAT='cc-sst-6-4',API='ws'),
   cvv, ssnfiller FILLER VARCHAR, ssn AS PROTECT(ssnfiller USING PARAMETERS FORMAT='SSN')) FROM :input_file DELIMITER ',' NULL '' DIRECT;

Protecting Preexisting Vertica Data

You can use the protection functions to protect data that is already stored in a database table. The following example creates a new table from the existing table, encrypts the Social Security number, and performs tokenization on credit card numbers before storing the data in the new table:

=> CREATE TABLE voltage_sample_copy AS SELECT 
    id,
    name,
    street,
    city,
    state,
    postcode,
    phone,
    email,
    birth_date,
    PROTECT (cc USING PARAMETERS FORMAT='cc-sst-6-4', API='ws') 
    AS cc,cvv,PROTECT(ssn USING PARAMETERS FORMAT='SSN') 
    AS ssn FROM voltage_sample;

Using Deidentified Data in Vertica

After the protected data has been loaded into Vertica, you can perform all analytics and processing on the protected data. There is usually no need to decrypt the data before referencing it in a SQL query.  

For example, suppose that you have encrypted the ssn column in both the voltage_sample and voltage_sample_creditscore tables. In the following statement, the join operation occurs on the deidentified data in the ssn column. There is no need to decrypt the data before performing the join:

=> SELECT 
    s.id,
    s.name,
    s.email,
    s.birth_date,
    s.cc,
    s.ssn,cs.creditscore 
    FROM voltage_sample s JOIN voltage_sample_creditscore cs 
    ON (s.ssn = cs.ssn) WHERE s.id <= 10;

Use the ACCESS function if you need to decrypt (reidentify) the encrypted data.

=> SELECT
    s.id,
    s.name,
    s.email,
    s.birth_date,
    ACCESS(s.cc USING PARAMETERS FORMAT = 'cc-sst-6-4',api='ws'),
    ACCESS(s.ssn USING PARAMETERS FORMAT ='SSN'),
    cs.creditscore 
    FROM voltage_sample s JOIN voltage_sample_creditscore cs 
    ON (s.ssn = cs.ssn) WHERE s.id <= 10;

Join Operation on Protected Data

The following SQL statement joins the two tables on the deidentified column—ssn. There is no need to decrypt the data to perform the join.

=> SELECT 
    s.id,
    s.name,
    s.email,
    s.birth_date,
    s.cc,
    s.ssn,
    cs.creditscore 
    FROM voltage_sample s JOIN voltage_sample_creditscore cs ON (s.ssn = cs.ssn) 
    WHERE s.id <= 5;
 id |        name        |          email           | birth_date |        cc        |     ssn     | creditscore
----+--------------------+--------------------------+------------+------------------+-------------+-------------
  1 | Tyshawn Medhurst   | oheidenreich@gmail.com   | 2007-03-02 | 5225629286144452 | 246-07-4941 | 621
  2 | Mr. Ted Dooley PhD | monna67@goodwinbins.com  | 1970-01-28 | 5564798613729487 | 253-07-7616 | 346
  3 | Karren Wintheiser  | ewisoky@cormier.org      | 2012-05-03 | 5548475402447835 | 919-64-8563 | 480
  4 | Hasel Weissnat     | ozell96@hotmail.com      | 2011-12-14 | 5169534213191201 | 242-41-9704 | 790
  5 | Kathi Wolf         | becker.drury@hotmail.com | 1992-07-07 | 5327551042465470 | 367-58-0501 | 540

Filtering on the Literal Value

The following SELECT statement encrypts the Social Security number 675-30-4941 in order to compare it to the encrypted data in the ssn column in voltage_sample. You do not have to decrypt the column to filter the data on ssn='675-30-4941'.

=> SELECT 
    s.id,
    s.name,
    s.email,
    s.birth_date,
    ACCESS(s.cc USING PARAMETERS FORMAT ='cc-sst-6-4',api='ws'),
    ACCESS(s.ssn USING PARAMETERS FORMAT ='SSN')
    FROM voltage_sample s
    WHERE s.ssn = PROTECT('675-03-4941' USING PARAMETERS FORMAT ='SSN');
  id |       name       |         email          | birth_date |      ACCESS      |   ACCESS    
 ----+------------------+------------------------+------------+------------------+-------------
   1 | Tyshawn Medhurst | oheidenreich@gmail.com | 2007-03-02 | 5225629041834452 | 675-03-4941
 (1 row)

The next SELECT statement uses tokenization to protect the cc column (a credit card number) using SST:

=> SELECT 
    s.id,
    s.name,
    s.email,
    s.birth_date,
    ACCESS(s.cc USING PARAMETERS FORMAT='cc-sst-6-4',API='ws'),
    ACCESS(s.ssn USING PARAMETERS FORMAT='SSN') 
    FROM voltage_sample s 
    WHERE s.cc = PROTECT('5225629041834452' USING PARAMETERS FORMAT='cc-sst-6-',API='ws');
  id |       name       |         email          | birth_date |      ACCESS      | ACCESS
 ----+------------------+------------------------+------------+------------------+-------------  1 | Tyshawn Medhurst | xxxxxxxxxxxx@gmail.com | 2007-03-02 | 5225629041834452 | 675-03-4941

Formatting Alphanumeric Columns

The following statements show how to encrypt and decrypt alphanumeric strings. As expected, the second operation—decryption—returns the pre-encryption value:

=> SELECT PROTECT('0F79yw' USING PARAMETERS FORMAT='alphanumeric'); 
  PROTECT
 ---------
  abe123
 (1 row)
=> SELECT ACCESS('abe123' USING PARAMETERS FORMAT='alphanumeric');
  PROTECT
 --------
  0F79yw
 (1 row)

If you do not specify any format, by default, the PROTECT and ACCESS functions use AUTO formatting. You can also specify AUTO formatting with the USING PARAMETERS option. Omitting the formatting has the same effect as specifying AUTO formatting:

=> SELECT PROTECT('0F79yw');
  PROTECT
 ---------
  3H34mo
 (1 row)
=> SELECT ACCESS('3H34mo');
  ACCESS
 --------
  0F79yw
 (1 row)
=> SELECT PROTECT('0F79yw' USING PARAMETERS FORMAT='auto');
  PROTECT
 ---------
  3H34mo
 (1 row)
=> SELECT ACCESS('3H34mo' USING PARAMETERS FORMAT='auto');
  ACCESS
 --------
  0F79yw
 (1 row)

Formatting the ssn Column

The following example shows the results of encrypting and then decrypting a Social Security number. As expected, the second operation—decryption—returns the pre-encryption value:

=> SELECT PROTECT('675-03-4941' USING PARAMETERS FORMAT = 'ssn');
    PROTECT
 -------------
 336-68-2285
 (1 row)
=> SELECT ACCESS('336-68-2285' USING PARAMETERS FORMAT = 'ssn');
    ACCESS
 -------------
  675-03-4941
 (1 row)

Formatting the cc Field

The following example shows how to encrypt and decrypt a credit card number:

=> SELECT PROTECT('8559604272399885' USING PARAMETERS FORMAT = 'cc');
      PROTECT
 ------------------
  5045935061651521
 (1 row)
=> SELECT ACCESS('5045935061651521' USING PARAMETERS FORMAT = 'cc');
       ACCESS
 ------------------
  8559604272399885
 (1 row)

Formatting cc-fpe-6-4 Data

You can configure a credit card encryption format on the HP SecureData Appliance that encrypts only a subset of the data. In the following figure, for the cc-fpe-6-4 format, you specify that only certain digits should be encrypted. With this configuration, PROTECT encrypts the digits after the first six digits and before the last four digits:

  • Original value: 8559604272399885 (855960 427239 9885)
  • Encrypted value: 8559608514469885 (855960 851446 9885)

Troubleshooting

HP SecureData creates UDF-related log files in the UDxLogs folder in the database catalog folder. Because you are using a Java UDF, any UDF errors or exceptions are recorded in the UDxFencedProcessesJava.log file.

Reregistering the Vertica Java Functions

After you register the protection functions, they are listed  in the Libraries folder under the database catalog folder as public_Voltage_45035996273800830. The folder also contains the following files:

  • 5d58b79d.0
  • currentdistrict
  • libvibesimplejava.so
  • params
  • policyxml
  • public_Voltage_45035996273800830.jar
  • symkeys
  • vibesimplejava.jar
  • voltage.pem
  • volutage.properties

If changes occur to the .properties file, .pem file, or any files in this list, you need to rerun the testudf makefile.

Credential Problems in the voltage.properties File

The following errors occur if there are issues in the voltage.properties file credentials:

=> SELECT ACCESS ('8559608514469885' USING PARAMETERS format = 'cc-fpe-6-4');
 ERROR 3399:  Failure in UDx RPC call InvokeProcessBlock(): Error in User 
 Defined Object [access], error code: 566
 Failed to create new shared object for FPE access operations.: C++ toolkit exception thrown; status code: 566; description: VE_ERROR_AUTHORIZATION_DENIED
 Details: C API error stack from Simple API 4.2.1:
Status: 566 VE_ERROR_AUTHORIZATION_DENIED
 In: vsAuth_CreateFPEObject src/vibesimple.c(13797)
 In: VsBuildFPDecryptor src/vibesimple.c(13714)
Description: Failed to initialize for FPE encryption.
 In: VsInitFPEDecObj src/vibesimple.c(13391)
 In: VtFPDecryptInit vtk-core/ibe/api/fpeobj.c(823)
 In: VoltFPEInit vtk-core/ibe/api/fpeobj.c(1536)
 In: VoltFPEBuildAlgObjFromId vtk-core/ibe/api/fpeobj.c(1634)
 In: VtObtainSymmetricKey vtk-core/ibe/api/ibeprotocol.c(2501)
 In: VoltTokenDownloadSymmetricKey vtk-core/providers/transport/secure/tokentransport.c(696)
 In: VoltTokenCommonDownloadKeyAndCert vtk-core/providers/transport/secure/tokentransport.c(1035)
 In: mIcServerRequest vtk-core/providers/transport/secure/vsserver.c(237)

If you are running Vertica on a single node, to fix this problem, update the voltage.properties file in the database catalog public_Voltage_xxxxxxxxxxxxxx > Libraries folder. If you are running on a multi-node cluster, reregister the functions so that this file is updated on all the nodes in your Vertica cluster.

Format Type Not Defined in HP SecureData Server

If you try to use a data format that you have not defined in the HP SecureData server, a "FORMAT NOT FOUND" error occurs. To fix this error, choose an existing data format, or if needed, define a new format:

=> SELECT ACCESS('8559608514469885' USING PARAMETERS format = 'cc-fpe-6-');
 ERROR 3399:  Failure in UDx RPC call InvokeProcessBlock(): Error in User Defined Object [access], error code: 684
 Failed to create new shared object for FPE access operations.: C++ toolkit exception thrown; status code: 684; description: VE_ERROR_CENTRALIZED_FORMAT_NOT_FOUND
 Details: C API error stack from Simple API 4.2.1:
Status: 684 VE_ERROR_CENTRALIZED_FORMAT_NOT_FOUND
 In: vsAuth_CreateFPEObject src/vibesimple.c(13797)
 In: VsBuildFPDecryptor src/vibesimple.c(13714)
 In: VsInitFPEDecObj src/vibesimple.c(13253)
 In: VsintCreateFPEObject src/vibesimple.c(5407)
 In: VtCreateFPEObject vtk-core/ibe/api/fpeobj.c(272)
 In: VtFPEImplCustomFormat vtk-core/ibe/fpe/fpecustom.c(160)

Deployment Needed

Whenever any configuration changes happen to the SecureData server, Deployment Needed appears on the upper-right side of the SecureData admin console.

deployment_needed1.png

To redeploy SecureData, click Deployment Needed. On the Deploy page, click Deploy.

deployment_needed2.png

Once the deployment completes, the Deployment Needed message disappears.

For More Information

For more information about SecureData, see https://www.voltage.com/resource/hpe-securedata-enterprise/.  

Share this article: