Skip to main content

Format-Preserving Encryption

Important

Contact ALTR Support for the Format-Preserving Encryption Service Level Agreement.

ALTR's format-preserving encryption (FPE) allows sensitive data to be encrypted and decrypted using a Snowflake Native App that integrates with ALTR's SaaS-based platform, which replaces on-prem applications. FPE allows queries to avoid calls between Snowflake and the on-prem applications, resulting in a faster response time while also ensuring at-rest data is secure. ALTR uses Snowpark to keep the encryption and decryption processes, and your data, in Snowflake. From there you can use ALTR’s policy engine to transparently decide which users can access decrypted information.

Features

Encryption

  • Uses the FF3-1 algorithm, a NIST standard with broad industry adoption, to perform FPE

  • Integrates seamlessly with ALTR's Data Access Governance. Decryption can be performed conditionally according to access policy configured in ALTR

  • Is deterministic; users can safely continue to query and join on data protected with FPE without needing to decrypt

  • Can be performed either in Snowflake or further left in your data pipeline using the Snowflake REST API

Key Management

  • Follows an extremely flexible name-value model and can be used to establish any encryption scheme of your choosing

  • Optionally supports bring your own key (BYOK) if you wish to have more control over the encryption and decryption of your data

  • Facilitates key rotation without performing an expensive re-encryption of your data

Flow Diagrams

The following diagrams illustrate the Encryption + Import and the Decryption + Return processes performed with format-preserving encryption.

Encryption and Import

The encryption and import process starts with data stored outside of Snowflake with sensitive data that does not have any protections or policy on it. Using Snowflake's API, data encryption can (optionally) happen prior to the data's ingest into Snowflake. This step gives an extra layer of at-rest encryption for highly sensitive data while still maintaining the data's referential integrity for analytics use cases, such as joins. You may opt to provide your own master key (BYOK) as an additional layer of centralized management.

Encryption___Import__1_.pdf

Decryption and Return

The decryption and return process starts from the perspective of what happens at runtime when you query Format-Preserving Encrypted data in Snowflake through ALTR's policy engine. First, ALTR verifies the policy on the encrypted data; if you do not have authorization to decrypt, the encrypted values are returned in their obfuscated format. If you do have authorization to decrypt, ALTR provides the decryption key, and the decryption happens within Snowflake. At no point does your data ever leave Snowflake.

Decrypt___Return.jpg

Setup Guide

Note

Setting up FPE requires contacting ALTR to provide several necessary values that we intentionally do not distribute with this documentation. To continue, you must contact ALTR Support to set up a session with a member of the ALTR team to fully enable this feature. To contact support, email support@altr.com with the subject line "FPE Setup Assistance."

Before setting up FPE, you must have ALTR enabled in your environment. Follow our Snowflake Partner Connect guide (only completing step 3 of the quick start guide is necessary for FPE). Once complete, follow the steps below to set up and enable FPE in your environment.

  1. Create a database and generate a random string.

    CREATE DATABASE format_preserving_integration;
CREATE SCHEMA key_handler;
CREATE SCHEMA encryption_handler;

-- Save this result to use in later steps
SELECT RANDSTR(32, RANDOM());

  2. Create an API INTEGRATION.

    -- Contact ALTR for API_AWS_ROLE_ARN and API_ALLOWED_PREFIXES (support@altr.com)
CREATE API INTEGRATION altr_fpe_api_integration
	API_PROVIDER = AWS_API_GATEWAY
	API_KEY = 'Random string from step 1'
	API_AWS_ROLE_ARN = ''
	API_ALLOWED_PREFIXES = ('')
	ENABLED = TRUE;

-- After the API integration is created, send the values for API_AWS_IAM_USER_ARN
-- and API_AWS_EXTERNAL_ID to support@altr.com with the subject line
-- "FPE Enablement: API Integration"
DESCRIBE API INTEGRATION altr_fpe_api_integration;

  3. Create two SECRETS.

    CREATE SECRET format_preserving_integration.key_handler.client_id 
	TYPE = PASSWORD 
	USERNAME= ''
	PASSWORD= 'Random string from step 1';

-- Create a Management API Key in the ALTR console (Settings > Preferences > API)
-- https://docs.altr.com/en/api.html
CREATE OR REPLACE SECRET format_preserving_integration.key_handler.mapi_keys 
	TYPE = PASSWORD
	USERNAME= 'Second value presented when creating key' -- ex. ALTR-F24D...
	PASSWORD= 'First value presented when creating key';

  4. Create a NETWORK RULE.

    -- Like secrets, NETWORK RULE must be created outside of the app
-- We recommend creating the network rule in the same schema as the secrets previously created
-- Contact ALTR for VALUE_LIST
CREATE OR REPLACE NETWORK RULE format_preserving_integration.key_handler.altr_fpe_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('');

  5. Create an EXTERNAL ACCESS INTEGRATION.

    CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION altr_fpe_external_access_integration
  ALLOWED_NETWORK_RULES = (key_handler.altr_fpe_network_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (key_handler.mapi_keys, key_handler.client_id)
  ENABLED = TRUE;

  6. Create EXTERNAL FUNCTIONs.

    -- Replace <altr org id> with the ID of your ALTR organization
-- Contact ALTR for <endpoint>

CREATE OR REPLACE EXTERNAL FUNCTION key_handler.initialize_key()
    RETURNS VARCHAR
    IMMUTABLE
    HEADERS = ( 'org-id' = '<altr org id>' )
    API_INTEGRATION = altr_fpe_api_integration
    AS '<endpoint>/v1/initialize-key';

CREATE OR REPLACE EXTERNAL FUNCTION key_handler.create_key()
    RETURNS VARCHAR
    IMMUTABLE
    HEADERS = ( 'org-id' = '<altr org id>' )
    API_INTEGRATION = altr_fpe_api_integration
    AS '<endpoint>/v1/create-key';

CREATE OR REPLACE EXTERNAL FUNCTION key_handler.create_tweak()
    RETURNS VARCHAR
    IMMUTABLE
    HEADERS = ( 'org-id' = '<altr org id>' )
    API_INTEGRATION = altr_fpe_api_integration
    AS '<endpoint>/v1/create-tweak';

-- Decrypts data encryption key *or* tweak
CREATE OR REPLACE EXTERNAL FUNCTION key_handler.decrypt_key_tweak(val VARCHAR)
    RETURNS VARCHAR
    IMMUTABLE
    HEADERS = ( 'org-id' = '<altr org id>' )
    API_INTEGRATION = altr_fpe_api_integration
    AS '<endpoint>/v1/decrypt-key';

CREATE OR REPLACE EXTERNAL FUNCTION key_handler.get_public_ref_id()
    RETURNS VARCHAR
    IMMUTABLE
    HEADERS = ( 'org-id' = '<altr org id>' )
    API_INTEGRATION = altr_fpe_api_integration
    AS '<endpoint>/v1/get-public-ref-id';

  7. Create UDFs.

    CREATE STAGE PUBLIC.PYTHON_CODE;

-- Contact ALTR for the encryption handler files. 
-- Using Snowsight, upload the files you receive from ALTR to
-- the newly created stage @PUBLIC.PYTHON_CODE.

USE SCHEMA encryption_handler;

-- Create UDFs to transparently call into python handlers

CREATE OR REPLACE FUNCTION encryption_handler.encrypt_varchar_r62
(plain_text VARCHAR, key VARCHAR, tweak VARCHAR)
    RETURNS VARCHAR
    LANGUAGE PYTHON
    IMMUTABLE
    RUNTIME_VERSION = '3.11'
    PACKAGES = ('pycryptodome')
    IMPORTS = ('@public.python_code/ff3.zip', '@public.python_code/udf.py')
    HANDLER = 'udf.encrypt_varchar_r62';

CREATE OR REPLACE FUNCTION encryption_handler.decrypt_varchar_r62
(plain_text VARCHAR, key VARCHAR, tweak VARCHAR)
    RETURNS VARCHAR
    LANGUAGE PYTHON
    IMMUTABLE
    RUNTIME_VERSION = '3.11'
    PACKAGES = ('pycryptodome')
    IMPORTS = ('@public.python_code/ff3.zip', '@public.python_code/udf.py')
    HANDLER = 'udf.decrypt_varchar_r62';

CREATE OR REPLACE FUNCTION encryption_handler.encrypt_varchar_r10
(plain_text VARCHAR, key VARCHAR, tweak VARCHAR)
    RETURNS VARCHAR
    LANGUAGE PYTHON
    IMMUTABLE
    RUNTIME_VERSION = '3.11'
    PACKAGES = ('pycryptodome')
    IMPORTS = ('@public.python_code/ff3.zip', '@public.python_code/udf.py')
    HANDLER = 'udf.encrypt_varchar_r10';

CREATE OR REPLACE FUNCTION encryption_handler.decrypt_varchar_r10
(plain_text VARCHAR, key VARCHAR, tweak VARCHAR)
    RETURNS VARCHAR
    LANGUAGE PYTHON
    IMMUTABLE
    RUNTIME_VERSION = '3.11'
    PACKAGES = ('pycryptodome')
    IMPORTS = ('@public.python_code/ff3.zip', '@public.python_code/udf.py')
    HANDLER = 'udf.decrypt_varchar_r10';

CREATE OR REPLACE TABLE FORMAT_PRESERVING_INTEGRATION.KEY_HANDLER.FPE_KEYS_TWEAKS (
	KEY_LABEL VARCHAR(16777216),
	ENCRYPTED_KEY VARCHAR(16777216),
	ENCRYPTED_TWEAK VARCHAR(16777216)
);

-- Create higher-level functions to encrypt/decrypt using ALTR's key management framework

CREATE OR REPLACE FUNCTION encryption_handler.encrypt_varchar_r62
    (value VARCHAR, key_lab VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    IMMUTABLE
    AS 
    $$
        encryption_handler.encrypt_varchar_r62(
            value,
            key_handler.decrypt_key_tweak(
                select encrypted_key from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            ),
            key_handler.decrypt_key_tweak(
                select encrypted_tweak from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            )
        )
    $$;

CREATE OR REPLACE FUNCTION encryption_handler.decrypt_varchar_r62
    (value VARCHAR, key_lab VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    IMMUTABLE
    AS 
    $$
        encryption_handler.decrypt_varchar_r62(
            value,
            key_handler.decrypt_key_tweak(
                select encrypted_key from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            ),
            key_handler.decrypt_key_tweak(
                select encrypted_tweak from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            )
        )
    $$;

CREATE OR REPLACE FUNCTION encryption_handler.encrypt_varchar_r10
    (value VARCHAR, key_lab VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    IMMUTABLE
    AS 
    $$
        encryption_handler.encrypt_varchar_r10(
            value,
            key_handler.decrypt_key_tweak(
                select encrypted_key from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            ),
            key_handler.decrypt_key_tweak(
                select encrypted_tweak from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            )
        )
    $$;

CREATE OR REPLACE FUNCTION encryption_handler.decrypt_varchar_r10
    (value VARCHAR, key_lab VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    IMMUTABLE
    AS 
    $$
        encryption_handler.decrypt_varchar_r10(
            value,
            key_handler.decrypt_key_tweak(
                select encrypted_key from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            ),
            key_handler.decrypt_key_tweak(
                select encrypted_tweak from key_handler.fpe_keys_tweaks 
                where key_label = key_lab
            )
        )
    $$;

  8. Create a stored procedure for key rotation.

    CREATE OR REPLACE PROCEDURE key_handler.rotate_keys()
        RETURNS VARCHAR
        LANGUAGE PYTHON
        RUNTIME_VERSION = '3.8'
        PACKAGES = ('snowflake-snowpark-python', 'requests')
        IMPORTS = ('@public.python_code/sp.py')
        EXTERNAL_ACCESS_INTEGRATIONS = (altr_fpe_external_access_integration)
        SECRETS = ('mapi_keys' = key_handler.mapi_keys, 'client_id' = key_handler.client_id)
        HANDLER = 'sp.rotate_keys';

  9. Grant privilege the newly created objects For the ALTR Service role.

    GRANT USAGE ON DATABASE format_preserving_integration TO ROLE PC_ALTR_ROLE;

GRANT USAGE ON SCHEMA key_handler TO ROLE PC_ALTR_ROLE;
GRANT USAGE ON SCHEMA encryption_handler TO ROLE PC_ALTR_ROLE;

GRANT SELECT ON TABLE key_handler.fpe_keys_tweaks TO ROLE PC_ALTR_ROLE;

GRANT USAGE ON FUNCTION key_handler.decrypt_key_tweak(VARCHAR) TO ROLE PC_ALTR_ROLE;
GRANT USAGE ON FUNCTION encryption_handler.decrypt_varchar_r62(VARCHAR, VARCHAR) TO ROLE PC_ALTR_ROLE;
GRANT USAGE ON FUNCTION encryption_handler.decrypt_varchar_r10(VARCHAR, VARCHAR) TO ROLE PC_ALTR_ROLE;

  10. Create a stored procedure for automated decryption.

    -- These stored procedures convert the masking policy of a column that has been 
-- added to ALTR to decrypt acccording to ALTR's governance framework

CREATE OR REPLACE PROCEDURE public.fpe_modify_column_masking_policy_varchar_r62(db_name VARCHAR, schema_name VARCHAR, table_name VARCHAR, column_name VARCHAR, padding_length FLOAT, key_lab VARCHAR)
RETURNS STRING
LANGUAGE JAVASCRIPT
VOLATILE
EXECUTE AS CALLER
AS $$
  
  function execQuery(queryString) {
    return snowflake.execute({sqlText:queryString});
  }
  
  try {
    execQuery(`alter masking policy ${DB_NAME}.ALTR_DSAAS."ALTR_MASKING_POLICY_[${DB_NAME}].[${SCHEMA_NAME}].[${TABLE_NAME}].[${COLUMN_NAME}]" set body ->
      CASE "${DB_NAME}"."ALTR_DSAAS"."ALTR_GOVERN_COLUMN_LAUNCHER_[${DB_NAME}].[${SCHEMA_NAME}].[${TABLE_NAME}].[${COLUMN_NAME}]"(
            SHA2(CONCAT(
                 COALESCE(LAST_QUERY_ID(),       'null')
                ,COALESCE(CURRENT_VERSION(),     'null')
                ,COALESCE(CURRENT_CLIENT(),      'null')
                ,COALESCE(CURRENT_REGION(),      'null')
                ,COALESCE(CURRENT_ACCOUNT(),     'null')
                ,COALESCE(CURRENT_ROLE(),        'null')
                ,COALESCE(CURRENT_USER(),        'null')
                ,COALESCE(CURRENT_SESSION(),     'null')
                ,COALESCE(CURRENT_TRANSACTION(), 'null')
                ,COALESCE(CURRENT_WAREHOUSE(),   'null')
                ,COALESCE(CURRENT_DATABASE(),    'null')
                ,COALESCE(CURRENT_SCHEMA(),      'null')
                ,COALESCE(CURRENT_STATEMENT(),   'null')
            ), 256), NULL)
        WHEN 'A' THEN LEFT(format_preserving_integration.ENCRYPTION_HANDLER.DECRYPT_VARCHAR_R62(VALUE, '${KEY_LAB}'), LENGTH(VALUE) - ${PADDING_LENGTH})
        ELSE VALUE
      END;`);
    return "Masking policy modified successfully.";
  } catch (e) {
    return "ERROR: " + e;
  }
$$;

CREATE OR REPLACE PROCEDURE public.fpe_modify_column_masking_policy_varchar_r10(db_name VARCHAR, schema_name VARCHAR, table_name VARCHAR, column_name VARCHAR, padding_length FLOAT, key_lab VARCHAR)
RETURNS STRING
LANGUAGE JAVASCRIPT
VOLATILE
EXECUTE AS CALLER
AS $$
  
  function execQuery(queryString) {
    return snowflake.execute({sqlText:queryString});
  }
  
  try {
    execQuery(`alter masking policy ${DB_NAME}.ALTR_DSAAS."ALTR_MASKING_POLICY_[${DB_NAME}].[${SCHEMA_NAME}].[${TABLE_NAME}].[${COLUMN_NAME}]" set body ->
      CASE "${DB_NAME}"."ALTR_DSAAS"."ALTR_GOVERN_COLUMN_LAUNCHER_[${DB_NAME}].[${SCHEMA_NAME}].[${TABLE_NAME}].[${COLUMN_NAME}]"(
            SHA2(CONCAT(
                 COALESCE(LAST_QUERY_ID(),       'null')
                ,COALESCE(CURRENT_VERSION(),     'null')
                ,COALESCE(CURRENT_CLIENT(),      'null')
                ,COALESCE(CURRENT_REGION(),      'null')
                ,COALESCE(CURRENT_ACCOUNT(),     'null')
                ,COALESCE(CURRENT_ROLE(),        'null')
                ,COALESCE(CURRENT_USER(),        'null')
                ,COALESCE(CURRENT_SESSION(),     'null')
                ,COALESCE(CURRENT_TRANSACTION(), 'null')
                ,COALESCE(CURRENT_WAREHOUSE(),   'null')
                ,COALESCE(CURRENT_DATABASE(),    'null')
                ,COALESCE(CURRENT_SCHEMA(),      'null')
                ,COALESCE(CURRENT_STATEMENT(),   'null')
            ), 256), NULL)
        WHEN 'A' THEN LEFT(format_preserving_integration.ENCRYPTION_HANDLER.DECRYPT_VARCHAR_R10(VALUE, '${KEY_LAB}'), LENGTH(VALUE) - ${PADDING_LENGTH})
        ELSE VALUE
      END;`);
    return "Masking policy modified successfully.";
  } catch (e) {
    return "ERROR: " + e;
  }
$$;

  11. Test.

    -- Before testing we recommend you check the API section of this documentation to get an understanding of the EFs and UDFs

-- Test EFs
SELECT key_handler.initialize_key();
SELECT key_handler.get_public_ref_id();
SELECT key_handler.create_key();
SELECT key_handler.create_tweak();
SELECT key_handler.decrypt_key_tweak(SELECT key_handler.create_key());
SELECT key_handler.decrypt_key_tweak(SELECT key_handler.create_tweak());

-- Test UDFs
SELECT encryption_handler.encrypt_varchar_r62(
    'test@gmail.com', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');
SELECT encryption_handler.decrypt_varchar_r62(
    'BVc1@zvxOE.WfT', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');
SELECT encryption_handler.encrypt_varchar_r10(
    '(555)-123-1234', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');
SELECT encryption_handler.decrypt_varchar_r10(
    '(678)-336-1779', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');

Schema Structure

FPE's deployment data is split into two schemas:

  • KEY_HANDLER—contains all objects that pertain to key management

    Note

    Within KEY_HANDLER, there is a table named FPE_KEYS_TWEAKS. This table stores the data encryption keys and tweaks that are created via EFs. When these keys are created they are returned to you encrypted and stored in the table in their encrypted form.

  • ENCRYPTION_HANDLER—contains all objects that pertain to data encryption

Usage Example

The following example illustrates how to

  1. Encrypt data in a column.

  2. Set up a policy in ALTR to automatically decrypt data based on role that is querying it.

-- inspect the data to encrypt
-- Note: this column should already be added to ALTR
select <test column> from <test db>.<test schema>.<test table> limit 10;

-- create a new key with a label of our choosing
INSERT INTO format_preserving_integration.key_handler.fpe_keys_tweaks 
    SELECT 'EMAIL', 
    format_preserving_integration.key_handler.create_key(), 
    format_preserving_integration.key_handler.create_tweak();

-- inspect the newly created key
select * from format_preserving_integration.key_handler.fpe_keys_tweaks where KEY_LABEL = 'EMAIL';

-- testing encryption without modifying the data
select format_preserving_integration.encryption_handler.encrypt_varchar_r62(id, 'EMAIL') from <test db>.<test schema>.<test table> limit 10;

-- replace the data with its encrypted version
update <test db>.<test schema>.<test table> set
    id = format_preserving_integration.encryption_handler.encrypt_varchar_r62(id, 'EMAIL');

-- verify that the data has been encrypted
select <test column> from <test db>.<test schema>.<test table> limit 10;

-- add column to ALTR by modifying its masking policy
call format_preserving_integration.public.fpe_modify_column_masking_policy_varchar_r62(
    '<test db>', 
    '<test schema>',
    '<test table>',
    '<test column>',
    0,
    'EMAIL'
);

-- use a privileged role to verify that data is decrypted according to lock policy
use role <privileged role>;
select <test column> from <test db>.<test schema>.<test table> limit 10;

-- use a non-privileged role to verify that data is returned encrypted according to lock policy
use role <non-privileged role>;
select <test column> from <test db>.<test schema>.<test table> limit 10;

Data Encryption Key Partitioning

You are free to partition your data encryption keys and tweaks how you would like. Here are some examples of how you could partition:

  • 1 Key/Tweak per column

  • 1 Key/Tweak per data type (ex. emails, phone_numbers ...)

  • ...

We recommend you acclimate yourself with the NIST standards for key partition when using FF3-1 encryption.

Rotate API KEY in API Integration

To rotate the API_KEY of the API INTEGRATION:

  1. Pause all processes encrypting data.

  2. Get current public reference ID.

     -- Save this value
SELECT key_handler.get_public_ref_id();

  3. Set new API KEY value.

    SELECT RANDSTR(32, RANDOM());
ALTER API INTEGRATION IF EXISTS altr_fpe_api_integration SET
    API_KEY = 'New value';

  4. Initialize new credential.

    -- initialize_key() response will include your new public reference ID
-- Save this value
SELECT key_handler.initialize_key();

  5. Email ALTR Support at support@altr.com with the following details:

    • Subject: FPE Rotate Credential

    • Body:

      • Previous public reference ID: 3rj3f-dcs53-...

      • New public reference ID: zzx3f-v303f-...

  6. ALTR will respond with confirmation that rotation was successful.

    Caution

    Do not resume encryption operations until ALTR responds with confirmation.

Rotate Encryption of Data Encryption Keys and Tweaks

To rotate encryption of data encryption keys and tweaks:

  1. Pause all processes encrypting data.

  2. Rotate keys.

    CALL key_handler.rotate_keys();

  3. Continue normal operations.

Bring Your Own Key

ALTR provides you the capability to bring your own key (BYOK). This is an AWS KMS key that you control.

If you in intend to use your own KMS key, email ALTR Support at support@altr.com with the subject line "FPE BYOK."

API

This section documents the API external functions, stored procedures, and user-defined functions.

External Functions

key_handler.initialize_key()

-- Initializes credential
-- Returns: VARCHAR
    -- ex. Successfully initialized credential. Public Reference ID: 23cdf-d2fk-...
    -- ex. Failed to initialize credential.

SELECT key_handler.initialize_key();

key_handler.create_key()

-- Creates and returns a new encrypted data encryption key
-- Returns: VARCHAR
    -- ex. trRK0C/eKjBQFdvu...LAdRsicg==

SELECT key_handler.create_key();

key_handler.create_tweak()

-- Creates and returns a new encrypted data encryption tweak
-- Returns: VARCHAR
    -- ex. 2ULpILzdV...eEx5rQyNA==

SELECT key_handler.create_tweak();

key_handler.decrypt_key_tweak(VARCHAR)

-- Decrypts an encrypted data encryption key or tweak
-- Param 1: Encrypted key or tweak
    -- ex. VB07oYqORNGa...P0bh2Q==
-- Returns: VARCHAR
SELECT key_handler.decrypt_key_tweak('key or tweak');

Stored Procedures

key_handler.rotate_keys()

-- Rotate encryption on key and tweaks in key_handler.fpe_keys_tweaks table
-- Returns: VARCHAR
    -- ex. Successfully rotated keys.
    -- ex. Failed to rotate keys.

CALL key_handler.rotate_keys();

User-Defined Functions

encryption_handler.encrypt_varchar_r62(VARCHAR, VARCHAR, VARCHAR)

-- Encrypts value using the alphanumeric alphabet (0-9, a-z, A-Z)
-- Param 1: Value
    -- ex. test@gmail.com
-- Param 2: Decrypted data encryption key
    -- ex. d956adfb6ebd493ec1ad49b1d88be04a
-- Param 3: Decrypted data encryption tweak
    -- ex. 53fbe45a7046b9
-- Returns: VARCHAR
    -- ex. BVc1@zvxOE.WfT

SELECT encryption_handler.encrypt_varchar_r62(
    'test@gmail.com', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');

encryption_handler.decrypt_varchar_r62(VARCHAR, VARCHAR, VARCHAR)

-- Decrypts value using the alphanumeric alphabet (0-9, a-z, A-Z)
-- Param 1: Value
    -- ex. BVc1@zvxOE.WfT
-- Param 2: Decrypted data encryption key
    -- ex. d956adfb6ebd493ec1ad49b1d88be04a
-- Param 3: Decrypted data encryption tweak
    -- ex. 53fbe45a7046b9
-- Returns: VARCHAR
    -- ex. test@gmail.com

SELECT encryption_handler.decrypt_varchar_r62(
    'BVc1@zvxOE.WfT', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');

encryption_handler.encrypt_varchar_r10(VARCHAR, VARCHAR, VARCHAR)

-- Encrypts values using the numeric alphabet (0-9)
-- Param 1: Value
    -- ex. '(555)-867-5309'
-- Param 2: Decrypted data encryption key
    -- ex. d956adfb6ebd493ec1ad49b1d88be04a
-- Param 3: Decrypted data encryption tweak
    -- ex. 53fbe45a7046b9
-- Returns: VARCHAR
    -- ex. '(300)-733-1451'

SELECT encryption_handler.encrypt_varchar_r10(
    '(555)-867-5309', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');

encryption_handler.decrypt_varchar_r10(VARCHAR, VARCHAR, VARCHAR)

-- Decrypts values using the numeric alphabet (0-9)
-- Param 1: Value
    -- ex. '(300)-733-1451'
-- Param 2: Decrypted data encryption key
    -- ex. d956adfb6ebd493ec1ad49b1d88be04a
-- Param 3: Decrypted data encryption tweak
    -- ex. 53fbe45a7046b9
-- Returns: VARCHAR
    -- ex. '(555)-867-5309'

SELECT encryption_handler.decrypt_varchar_r10(
    '(300)-733-1451', 
    'd956adfb6ebd493ec1ad49b1d88be04a', 
    '53fbe45a7046b9');

Convenience User-defined functions

The following UDFs are convenience functions. State the key stored in the fpe_keys_tweaks table you want to use; the function handles decrypting the key and tweak for you.

encryption_handler.encrypt_varchar_r62(VARCHAR, VARCHAR)

-- Encrypts value using the alphanumeric alphabet (0-9, a-z, A-Z) and a key identified by the label passed in
-- Param 1: Value
    -- ex. test@gmail.com
-- Param 2: Key Label
    -- ex. EMAIL
-- Returns: VARCHAR
    -- ex. BVc1@zvxOE.WfT
SELECT encryption_handler.encrypt_varchar_r62('test@gmail.com', 'EMAIL');

encryption_handler.decrypt_varchar_r62(VARCHAR, VARCHAR)

-- Encrypts value using the alphanumeric alphabet (0-9, a-z, A-Z) and a key identified by the label passed in
-- Param 1: Value
    -- ex. BVc1@zvxOE.WfT
-- Param 2: Key Label
    -- ex. EMAIL
-- Returns: VARCHAR
    -- ex. test@gmail.com
SELECT encryption_handler.decrypt_varchar_r62('BVc1@zvxOE.WfT', 'EMAIL');

encryption_handler.encrypt_varchar_r10(VARCHAR, VARCHAR)

-- Encrypts value using the numeric alphabet (0-9) and a key identified by the label passed in
-- Param 1: Value
    -- ex.  '(555)-867-5309'
-- Param 2: Key Label
    -- ex. PHONE_NO
-- Returns: VARCHAR
    -- ex. '(300)-733-1451'

SELECT encryption_handler.encrypt_varchar_r10(
    '(555)-867-5309', 
    'PHONE_NO');

encryption_handler.decrypt_varchar_r10(VARCHAR, VARCHAR)

-- Decrypts value using the numeric alphabet (0-9) and a key identified by the label passed in
-- Param 1: Value
    -- ex.  '(300)-733-1451'
-- Param 2: Key Label
    -- ex. PHONE_NO
-- Returns: VARCHAR
    -- ex. '(555)-867-5309'

SELECT encryption_handler.decrypt_varchar_r10(
    '(300)-733-1451', 
    'PHONE_NO');
In this section: