Format-Preserving Encryption on Snowflake
Format-Preserving Encryption (FPE) on Snowflake encrypts data in place, so encrypted values keep the format your queries, joins, and downstream tools expect. You encrypt the data with the ALTR_FPE_ENCRYPT function, using a key and a tweak you create in ALTR. Contact ALTR to enable FPE. ALTR creates the function in the ALTR_DSAAS_DB database when it connects a Snowflake data source for an organization with FPE enabled.
To set up FPE on Snowflake:
- Create a key and a tweak.
- Grant a Snowflake role access to the encryption functions.
- Encrypt the data.
- Connect the encrypted column or tag to ALTR with the FPE protection type, and select the Key, Tweak, and Alphabet Type you encrypted it with. If you encrypted with padding, also select Encrypted with padding.
- Set up decryption for the roles that should see plaintext.
Create a Key and a Tweak
Section titled “Create a Key and a Tweak”You create keys and tweaks on the Key Management page in ALTR. Key and tweak names are case sensitive, 256 characters or fewer, and can’t start or end with a space. Each key name and each tweak name must be unique.
To create a key:
- Select Data Configuration > Key Management in the navigation menu.
- Click Create Key.
- Enter a Key Name.
- Click Save.
To create a tweak:
- Select Data Configuration > Key Management in the navigation menu.
- Select Tweaks in the Type list.
- Click Create Tweak.
- Enter a Tweak Name.
- Click Save.
Grant Access to the Encryption Functions
Section titled “Grant Access to the Encryption Functions”A Snowflake role that encrypts data needs the USAGE privilege on the database, the schema, and each function it calls.
The encryption functions live in the ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID> schema, where <ORG_ID> is your ALTR Organization ID in uppercase with dashes replaced by underscores. Your ALTR Organization ID is on the Organization tab under Settings > Preferences – see Settings and Preferences.
To grant access, run these GRANT statements as a role that can grant privileges on these objects, such as ACCOUNTADMIN, replacing <ROLE_NAME> with the role that encrypts data:
GRANT USAGE ON DATABASE ALTR_DSAAS_DB TO ROLE <ROLE_NAME>;GRANT USAGE ON SCHEMA ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID> TO ROLE <ROLE_NAME>;GRANT USAGE ON FUNCTION ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID>.ALTR_FPE_ENCRYPT(VARCHAR, VARCHAR, VARCHAR, VARCHAR) TO ROLE <ROLE_NAME>;GRANT USAGE ON FUNCTION ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID>.ALTR_FPE_ENCRYPT_ARRAY(VARCHAR, VARCHAR, VARCHAR, VARCHAR) TO ROLE <ROLE_NAME>;Encrypt a Column
Section titled “Encrypt a Column”ALTR_FPE_ENCRYPT takes 4 arguments: the value to encrypt, the key name, the tweak name, and the alphabet – alphabetic, numeric, or alphanumeric. It returns the encrypted value.
To encrypt every value in a column, run:
UPDATE <TABLE>SET <COLUMN> = ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID>.ALTR_FPE_ENCRYPT( <COLUMN>, '<KEY_NAME>', '<TWEAK_NAME>', '<ALPHABET>');To encrypt only rows loaded since the last run, add a WHERE clause on a column that identifies those rows, such as a load timestamp, so no value is encrypted twice. An encrypted value has the same format as its plaintext, so the column’s own values can’t tell you which rows are already encrypted.
Encrypt an Array of Values
Section titled “Encrypt an Array of Values”ALTR_FPE_ENCRYPT_ARRAY encrypts several values in one call. It takes a JSON array of values as a string, followed by the key name, the tweak name, and the alphabet, and returns an array of encrypted values.
Encrypt with Padding
Section titled “Encrypt with Padding”If padding is enabled for your organization, ALTR_FPE_ENCRYPT and ALTR_FPE_ENCRYPT_ARRAY also accept a fifth argument, deterministic or nondeterministic, that sets the padding type. A role that passes the fifth argument also needs USAGE on the 5-argument version of each function it calls, for example ALTR_FPE_ENCRYPT(VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR).