Skip to main content

ALTR Core FPE (Snowflake Native App)

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

This application requires some global and object-level privileges in order to function in your account. These privileges are described below. For information on how to configure the required objects and perform these grants, see the Setup Guide section.

This global privilege allows the application 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.

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

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

This application requires credentials to authenticate with ALTR's APIs.

  1. 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 will walk you through the creation of required objects or allow 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

  2. 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)

    ```sql
    call config.create_compute_pool('<compute pool name>', '<instance family>');
    call config.create_fpe_service('<compute pool name>');
    call config.create_endpoint_functions();
    ```
  3. Finally, run the following query to initialize a master key in ALTR's key management system.

    ```sql
    select key_handler.initialize_master_key();
    ```

1. The following will create a data encryption key and securely store it in the application.

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

2. The following will test the encryption of plaintext using a 62-character alphabet. This alphabet includes 0-9, A-Z, and a-z.

```sql
select encryption_handler.encrypt_varchar_r62('hello', 'MY-KEY'); --> 'a7wlv'
```

3. The following will decrypt ciphertext using a 62-character alphabet. The below example uses a fake ciphertext and should be replaced with the output from the above query.

```sql
select encryption_handler.decrypt_varchar_r62('a7wlv', 'MY-KEY'); --> 'hello'
```

4. The following will test the encryption of plaintext using a 10-character alphabet. This alphabet includes 0-9.

```sql
select encryption_handler.encrypt_varchar_r10('111-22-3333', 'MY-KEY'); --> '574-44-9457'
```

5. The following will decrypt ciphertext using a 10-character alphabet. The below example uses a fake ciphertext and should be replaced with the output from the above query.

```sql
select encryption_handler.decrypt_varchar_r10('574-44-9457', 'MY-KEY'); --> '111-22-3333'
```

6. The following will retrieve the keys stored within the application.

```sql
select key_handler.get_keys();
```

7. The following will retrieve only the labels of the keys stored within the application.

```sql
select key_handler.get_key_labels();
```

Note

For a complete reference of all exposed functions, refer to the API Reference section.

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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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;
```

Note

For a complete reference of available parameters and operations on services and compute pools, refer to Snowflake's documentation:

Encryption/Decryption

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

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

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

Note

Access with: ENCRYPTOR, APP_ADMIN

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

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

Note

Access with: ENCRYPTOR, APP_ADMIN

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

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

Note

Access with: DECRYPTOR, APP_ADMIN

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

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

Note

Access with: DECRYPTOR, APP_ADMIN

Key Management

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

Initialize the app state and return a status message.

```sql
select key_handler.initialize_master_key();
```

Note

Access with: KEY_ADMIN, APP_ADMIN

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 will skip creating a key and succeed.

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

Note

Access with: KEY_ADMIN, APP_ADMIN

Get the fingerprint of a data encryption key.

```sql
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
```

Note

Access with: KEY_ADMIN, APP_ADMIN

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

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

Note

Access with: KEY_ADMIN, APP_ADMIN

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.

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

Note

Access with: KEY_ADMIN, APP_ADMIN

Rotate all data encryption keys and return a status message.

```sql
select key_handler.rotate();
```

Note

Access with: KEY_ADMIN, APP_ADMIN