Skip to main content

Privacera Platform

Table of Contents

Trino UDFs for encryption and masking on Privacera Platform

:

This topic provides instruction on how to install and configure the Privacera crypto plugin for Trino. Doing so will allow you to use Privacera-supplied encryption user-defined functions (UDFs) in Trino to encrypt or decrypt data.

The protect and unprotect UDFs work with privacera_starburstenterprise but not with privacera_hive. Starburst has three possible configurations (Hive, System, and Hive + System), of which only the system-level has been verified.

Privacera Encryption UDFs for Trino

The Privacera crypto plugin includes the following UDFs:

  • Encrypt: With the quoted <encryption_scheme_name>, the protect UDF encrypts all values of <column_name> in a table:

    select protect(<column_name>, '<encryption_scheme_name>') from <table_name>;

  • Decrypt: With the <encryption_scheme_name>, the unprotect UDF decrypts all values of <column_name> in a table:

    select unprotect(<column_name>, '<encryption_scheme_name>') from <table_name>;

  • Decrypt with obfuscation: With the quoted <encryption_scheme_name>, the unprotect UDF decrypts all values of <column_name> in a table, further obfuscates the decrypted data via <presentation_scheme_name>, and writes the decrypted, obfuscated data to <optional_column_name_for_obfuscated_data>:

    select unprotect(<column_name>, '<encryption_scheme_name>', <presentation_scheme_name>) <optional_column_name_for_obfuscated_data> from <table_name>;

  • Decrypt with obfuscation: With the quoted <encryption_scheme_name>, the unprotect UDF decrypts all values of <column_name> in a table, further obfuscates the decrypted data via <presentation_scheme_name>, and writes the decrypted, obfuscated data to <optional_column_name_for_obfuscated_data>:

    select unprotect(<column_name>, '<encryption_scheme_name>', <presentation_scheme_name>) <optional_column_name_for_obfuscated_data> from <table_name>;

    For example usage, see Example Queries to Verify Privacera-supplied UDFs.

Prerequisites for installing Privacera crypto plugin for Trino

Before installing the Privacera crypto plugin for Trino, do the following:

  • Install Trino. In this topic, the location of the installed Trino software is shown as:

    <absolute_path_to_trino_home_directory>

  • Identify the users who will use the UDFs and ensure they have access to the pertinent tables.

  • Determine the required paths to the crypto JAR and crypto.properties file. The Encryption plugin for Trino relies on these files. The paths for each file depend on whether you have deployed Trino in a container (such as Docker). These different paths are detailed in the following sections.

Install the Privacera crypto plugin for Trino using Privacera Manager

To install the Privacera crypto plugin, follow these steps:

See the following sections for details about how to complete each step.

Upgrade Privacera Manager

To install the Privacera crypto plugin, you first need to update Privacera Manager to get a shell script. This shell script downloads the Privacera Encryption crypto plugin for Trino.

To do so, run the following commands:

# Change to Privacera Manager directory
cd ~/privacera/privacera-manager

# Upgrade Privacera Manager itself
 ./privacera-manager.sh upgrade-manager
Configure the Privacera crypto plugin for Trino
 # Copy the Trino properties file to Privacera Manager config/custom-vars directory
 cp config/sample-vars/vars.starburst.enterprise.trino.yml config/custom-vars/

 # Set property STARBURST_TRINO_ENABLE to true
 vi config/custom-vars/vars.starburst.enterprise.trino.yml
 ...
 STARBURST_TRINO_ENABLE: "true"
 ...
 # Save the file
 # Edit starburst-trino-crypto.yml to specify Trino home directory
 vi ansible/privacera-docker/roles/defaults/main/starburst-trino-crypto.yml
 ...
 STARBURST_TRINO_INSTALL_DIR: <absolute_path_to_trino_home_directory>
 ...
 # Save the file
Run shell script to install Privacera crypto plugin
 # Change to Privacera Manager directory
 cd ~/privacera/privacera-manager

 # Update Privacera Manager to get shell script
 ./privacera-manager.sh update

 # Change to new directory created by privacera-manager update
 cd output/starburst-trino-crypto/

 # Make the script executable
 chmod +x privacera_crypto_trino_setup.sh
 #
 ######################################
 # NOTE: You must copy the script to your Trino or Starburst instance
 ######################################
 #
 #  Run the script on your instance from where you copied it
 ./privacera_crypto_trino_setup.sh
Verify that the shell script ran correctly

Verify the following:

  • The location of the Privacera crypto JAR:

    # For non-container deployment
ls -l <absolute_path_to_trino_home_directory>/plugin/privacera/privacera-crypto-jar-with-dependencies.jar

# For container deployment
ls -l /data/starburst/plugin/privacera/privacera-crypto-jar-with-dependencies.jar

  • The location of the crypto.properties file in Trino's etc directory:

    # Verify existence of crypto.properties file
# For non-container deployment
ls -l <absolute_path_to_trino_home_directory>/etc/crypto.properties

# For non-container deployment
ls -l /data/starburst/etc/crypto.properties
Restart Trino to register the Privacera crypto UDFs for Trino
# Go to Trino bin directory
cd /<trino_installation_directory>/bin

# Restart Trino
./launcher restart
privacera.unprotect with optional presentation scheme

The unprotect UDF supports an optional specification of a presentation scheme that further obfuscates the decrypted data.

Syntax:

select <id>, privacera.unprotect(<COLUMN_NAME>, <ENCRYPTION_SCHEME_NAME>, <PRESENTATION_SCHEME_NAME>) <OPTIONAL_NAME_FOR_COLUMN_TO_WRITE_OBFUSCATED_OUPUT> from <DB_NAME>.<TABLE_NAME>;

where:

  • <PRESENTATION_SCHEME_NAME> is the name of the chosen Privacera presentation scheme with which to further obfuscate the decrypted data.

  • <OPTIONAL_NAME_FOR_COLUMN_TO_WRITE_OBFUSCATED_OUTPUT> is a "pretty" name for the column that the obfuscated data is written to.

  • Other arguments are the same as in the preceding unprotect example.

Example queries to verify Privacera-supplied UDFs

See the syntax detailed in Syntax of Privacera Encryption UDFs for Trino.

  • Encrypt: The following example query with the protect UDF encrypts the cleartext CUSTOMER_EMAIL column of the CUSTOMERS table using the quoted'EMAIL' encryption scheme:

    select protect(CUSTOMER_EMAIL, `EMAIL`) from CUSTOMERS;

  • Decrypt: The following example query with the unprotect UDF decrypts the encrypted CUSTOMER_EMAIL column of the CUSTOMERS table using the quoted 'EMAIL' encryption scheme:

    select unprotect(CUSTOMER_EMAIL, 'EMAIL') from CUSTOMERS;

  • Decrypt with obfuscation: The following example query with the unprotect UDF decrypts the encrypted CUSTOMER_EMAIL column of the CUSTOMERS table using the quoted 'EMAIL' encryption scheme, obfuscates the decrypted data with the presentation scheme PRESENTATION_EMAIL, and writes the decrypted, obfuscated data to OPTIONAL_OUTPUT_COLUMN_FOR_OBFUSCATED_DATA:

    select unprotect(CUSTOMER_EMAIL, 'EMAIL', PRESENTATION_EMAIL) OPTIONAL_OUTPUT_COLUMN_FOR_OBFUSCATED_DATA from CUSTOMERS;