Core FPE

ALTR Core FPE is a Snowflake Native App that provides a fast implementation of the FF3-1 format-preserving encryption algorithm and a secure key management layer. It exposes a set of user-defined functions (UDFs) that can be leveraged in SQL queries to quickly encrypt and decrypt data using managed keys.

This app is designed to be simple and usable. It can be obtained by anyone from the Snowflake Marketplace and set up without any previous configuration or customization. You are not required to be set up with any other ALTR software components including the ALTR security platform.

Prerequisites

Your Snowflake account must be

Required Privileges

This app requires a few global and object-level privileges in order to function in your account, which are described in this section. For information on how to configure the required objects and perform these grants, see the Setup Guide section.

CREATE COMPUTE POOL

This global privilege allows the app to create a dedicated compute pool within the Snowflake account. A compute pool is created once when initially setting up the app; you will be prompted to call the <APP>.CONFIG.CREATE_COMPUTE_POOL(<compute pool name>) procedure, which creates the compute pool.

Key Operation Warehouse: USAGE

The core service in this app accesses internal app state stored in your Snowflake account. To query stored data, the service requires USAGE on a warehouse.

Key management external access integration: USAGE

The app manages keys through an API hosted by ALTR, and thus requires an external access integration to allow network communication.

Key management credentials: USAGE

This app requires credentials to authenticate with ALTR’s APIs.

Grant required privileges for the application using the app UI in Snowsight. Navigate to these locations for a description of each required privilege and to grant them. The app walks you through the creation of required objects or allows you to select existing objects if you wish.
1. App > Privileges > Account Level Privileges
  1. CREATE COMPUTE POOL
2. App > Privileges > Object Access Privileges
  1. Key Operation Warehouse: USAGE
3. App > Connections > Connections
  1. Key management external access integration: USAGE
4. App > Connections > Credentials
  1. Key management credentials: USAGE , READ
Run the following statements to create a compute pool for the app, initialize the FPE service and create connected service functions. When creating the compute pool, you must specify a valid instance family . (E.g., cpu_x64_xs)
```
call config.create_compute_pool('<compute pool name>', '<instance family>');
call config.create_fpe_service('<compute pool name>');
call config.create_endpoint_functions();
```
Run the following query to initialize a master key in ALTR’s key management system.
```
select key_handler.initialize_master_key();
```
The following creates a data encryption key and securely stores it in the application.
```
select key_handler.insert_key_tweak('MY-KEY');
```
The following tests the encryption of plaintext using a 62-character alphabet. This alphabet includes 0-9, A-Z, and a-z.
```
select encryption_handler.encrypt_varchar_r62('hello', 'MY-KEY'); --> 'a7wlv'
```
The following decrypts ciphertext using a 62-character alphabet. The below example uses a fake ciphertext and should be replaced with the output from the above query.
```
select encryption_handler.decrypt_varchar_r62('a7wlv', 'MY-KEY'); --> 'hello'
```
The following tests the encryption of plaintext using a 10-character alphabet. This alphabet includes 0-9.
```
select encryption_handler.encrypt_varchar_r10('111-22-3333', 'MY-KEY'); --> '574-44-9457'
```
The following decrypts ciphertext using a 10-character alphabet. The below example uses a fake ciphertext and should be replaced with the output from the above query.
```
select encryption_handler.decrypt_varchar_r10('574-44-9457', 'MY-KEY'); --> '111-22-3333'
```
The following retrieves the keys stored within the application.
```
select key_handler.get_keys();
```
The following retrieves only the labels of the keys stored within the application.
```
select key_handler.get_key_labels();
```

The application has four app roles that can be used to provide access to subsets of the app’s functionality.

ENCRYPTOR

Description : Usage on all UDFs that can be used encrypt data.

Recommended Grant : To roles that need to encrypt data. By granting only this app role and not the DECRYPTOR app role, you can ensure that a role does not have the ability to decrypt sensitive data but only to encrypt data.

DECRYPTOR

Description : Usage on all UDFs that can be used decrypt data.

Recommended Grant : To roles that need to decrypt data. By granting only this app role and not the ENCRYPTOR app role, you can ensure that a role does not have the ability to encrypt sensitive data but only to decrypt data.

KEY_ADMIN

Description : Usage on all UDFs that can be used to manage the app’s data encryption keys.

Recommended Grant : To an administrator or other privileged role who needs the ability to manage keys.

APP_ADMIN

Description : Usage on all app functionality and exposed SPCS objects. Users with this app role can perform any action in the app and manage the FPE service and app compute pool.

Recommended Grant : To an administrator or other privileged role who needs the ability to operate the app service or perform other privileged actions.

The app uses Snowpark Container Services to implement high-performance compute operations. For flexibility, the app exposes these SPCS objects to the application role APP_ADMIN . Using this role, you can inspect and control the status and parameters of these objects.

View status of the FPE service and app compute pool:

-- View the status and details of the Core FPE service
desc service services.fpe_service;

-- View the status and details of the app compute pool
desc compute pool <compute pool name>;

Alter parameters of the FPE service and compute pool:

-- Alter parameters related to service scaling
alter compute pool <compute pool name> set
   min_nodes = 1
   max_nodes = 3;
alter service services.fpe_service set
   min_instances = 1
   max_instances = 3;

-- Alter parameters related to autosupension
alter compute pool <compute pool name> set
   auto_suspend_secs = 360
   auto_resume = true;
alter service services.fpe_service set
   auto_suspend_secs = 360
   auto_resume = true;

Control service and compute pool status:

-- Suspend the service and compute pool when not in use
-- for long periods of time
alter service services.fpe_service suspend;
alter compute pool <compute pool name> suspend;

-- Resume the service
alter compute pool <compute pool name> resume;
alter service services.fpe_service resume;

UDFs in the encryption_handler schema facilitate encryption and decryption. Generally, these functions are available to the APP_ADMIN , ENCRYPTOR and DECRYPTOR roles as appropriate.

encryption_handler.encrypt_varchar_r62(plaintext varchar, key_label varchar) returns varchar

Encrypt a plaintext value over the alphanumeric alphabet ( 0-9a-zA-Z ) using the key identified by key_label .

select encryption_handler.encrypt_varchar_r62('<user@example.com>', 'MY-KEY');
-- <J7mD@vE1qFTR.ekx>

encryption_handler.encrypt_varchar_r10(plaintext varchar, key_label varchar) returns varchar

Encrypt a plaintext value over the numeric alphabet ( 0-9 ) using the key identified by key_label .

select encryption_handler.encrypt_varchar_r10('123-12-1234', 'MY-KEY');
-- 929-15-4986

encryption_handler.decrypt_varchar_r62(plaintext varchar, key_label varchar) returns varchar

Decrypt a plaintext value over the alphanumeric alphabet ( 0-9a-zA-Z ) using the key identified by key_label .

select encryption_handler.decrypt_varchar_r62('<J7mD@vE1qFTR.ekx>', 'MY-KEY');
-- <user@example.com>

encryption_handler.decrypt_varchar_r10(plaintext varchar, key_label varchar) returns varchar

Decrypt a plaintext value over the numeric alphabet ( 0-9 ) using the key identified by key_label .

select encryption_handler.decrypt_varchar_r10('929-15-4986', 'MY-KEY');
-- 123-12-1234

UDFs in the key_handler schema allow the caller to perform key management operations like creating, viewing, rotating and deleting data encryption keys.

key_handler.initialize_master_key() returns varchar

Initialize the app state and return a status message.

select key_handler.initialize_master_key();

key_handler.insert_key_tweak(key_label varchar) returns varchar

Create a new data encryption key and tweak that can be used to encrypt and decrypt data. Returns a status message. If the key named key_label already exists, this function skips creating a key and succeeds.

select key_handler.insert_key_tweak('MY-KEY');

key_handler.get_key_fingerprint(key_label varchar) returns varchar

Get the fingerprint of a data encryption key.

select key_handler.get_key_fingerprint('MY-KEY');
-- a7:42:f6:af:64:0e:88:7e:2c:7e:c7:f6:1a:35:f7:4a:e2:8f:18:76:d5:23:33:50:06:74:1a:c1:92:ac:71:b9

key_handler.get_keys() returns array

Get the labels of all data encryption keys as an array of strings.

select key_handler.get_key_labels();
-- ["MY-KEY", ...]

key_handler.delete_key_tweak(key_label varchar, key_fingerprint varchar)

Delete an existing data encryption key specified by key_label . As a safeguard, this function requires the key’s fingerprint to ensure that deletion is not inadvertent. Use key_handler.get_key_fingerprint to retrieve this value.

-- Get the key's fingerprint
select key_handler.get_key_fingerprint('MY-KEY');
-- a7:42:f6:af:64:0e:88:7e:2c:7e:c7:f6:1a:35:f7:4a:e2:8f:18:76:d5:23:33:50:06:74:1a:c1:92:ac:71:b9

-- Delete the key
select key_handler.delete_key_tweak(
   'MY-KEY',
   'a7:42:f6:af:64:0e:88:7e:2c:7e:c7:f6:1a:35:f7:4a:e2:8f:18:76:d5:23:33:50:06:74:1a:c1:92:ac:71:b9'
);

key_handler.rotate() returns varchar

Rotate all data encryption keys and return a status message.

select key_handler.rotate();