Format-Preserving Encryption Snowflake Native App

Caution

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

Note

This documentation outlines our Snowflake Native App version of format-preserving encryption feature. If you need documentation for our in-product version of format-preserving encryption, see Format-Preserving Encryption .

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.

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

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.

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.

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 privileges on 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');

FPE’s deployment data is split into two schemas:

• KEY_HANDLER— contains all objects that pertain to key management
• ENCRYPTION_HANDLER— contains all objects that pertain to data encryption

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;

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.

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

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

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.

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.”

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');