Using Data Security Manager with Snowflake

Last Updated: January 20, 2022 04:39

FORTANIX DATA SECURITY MANAGER WITH SNOWFLAKE FOR TOKENIZATION.pdf
(1 MB)

Introduction

This document describes the Snowflake setup of External Functions to invoke the Fortanix Data Security Manager (DSM) Plugin for tokenization and detokenization using an API Gateway proxy service.

Snowflake External Functions will need to specify at creation time which Fortanix DSM keys are to be used for tokenization or detokenization of various columns. Thereby, the API Gateway remains stateless and simply passes through the request from Snowflake to Fortanix DSM plugin by only requiring mapping certain headers.

We will use the "headers” property of CREATE EXTERNAL FUNCTION to pass key names information. Header name and value should be as follows:

Header name : This will be named “key-names”
Header value : This will be a string of comma separated key names. First entry corresponds to the first column in the data and so on.

Key with the key names specified in the header must already exist. Fortanix DSM plugin will not create the key. It will check and verify if the key exists. Fortanix DSM plugin will also verify that the number of key names passed in the header match the number of columns in the data sent by Snowflake. If there are less keys than the columns, then the operation will fail.

Prerequisites

Fortanix DSM version 3.27 or later is installed and operational.

Setting up the Fortanix Data Security Manager

Sign up at https://smartkey.io/ .
Log in to the Fortanix DSM UI.
Click the Integrations tab in the left panel.
On the Integrations page, click ADD INSTANCE on the Snowflake wizard.Figure1: Add Snowflake Instance
Enter the details as shown in the screenshot below:
1. Add Instance: This is the name to identify the instance created.
2. In the Create tokenization keys section:
  1. Select the Data type applicable to you. Depending on the type of data the users want to protect, they can create security objects belonging to any of the four tokenizer data type groups.
  2. Multiple token objects can be created as per your needs.
    Figure 2: Save Instance
    
    NOTE
    
    Each security object will need to have a masking policy enabled during creation time, so that the Fortanix DSM App can perform a partial reveal or dynamic masking during de-tokenization. Masking policy cannot be enabled on the object afterward and hence it needs to be premeditated for the creation. This may be subject to change in the future if the key can be copied (replicated) with a masking policy enabled during the replication.
Click SAVE INSTANCE.
Note down the API Key from the instance details. You will need the API Key when setting your API gateway. To copy the API Key, click COPY API KEY.
Figure 3: Instance Details
You can view all the instances by clicking the View All on the integration wizard.
Figure 4: View All
Figure 5: List of All Instances

Setting up AWS API Gateway Proxy Service

Create and Test AWS API Gateway

Create and test AWS API Gateway with the following configuration:

REST API Endpoints that may be public or private:
Two resources for /tokenize and /detokenize.

NOTE

POST method is required for both resources.

For each resource method, configure Integration Request with the following configuration:
- Integration type: HTTP
- Endpoint URL: Point it to your Plugin URL
```
https://sdkms.fortanix.com/sys/v1/plugins/<<PLUGIN_UUID>>
```
  NOTE
  
  Change the Fortanix DSM FQDN and the Plugin UUID.
- Content handling: Passthrough
- HTTP Headers – Add the “Authorization” header and leave the value empty as it’ll be sent by Snowflake through an External Function custom header.
Figure 6: AWS API Gateway
Click “Add mapping template” and do the following:
1. Select Content-Type as “application/json”.
2. Select Request body passthrough as “Never”.
3. Set the value of the template as follows:
```
set($apikey = "Basic $input.params('sf-custom-api-key')")
set($context.requestOverride.header.Authorization = $apikey)
set($context.requestOverride.header.sf-custom-api-key = "")
set($context.requestOverride.header.sf-custom-key-names = "")
set($inputRoot = $input.path('$'))
{
"op": "encrypt OR decrypt",
"keys":"$input.params('sf-custom-key-names')",
"data": $input.json('$.data')
}
```
  NOTE
  - Snowflake External Function will send the FORTANIX_DSM_API_KEY in its own custom header, which will need to be mapped to a standard Basic Authentication header. Similarly, the Fortanix DSM tokenization key names will be mapped from another Snowflake custom header to the integration request body.
  - Change the “op” to “encrypt” for /tokenize, and “decrypt” for /detokenize resources.
Test AWS API Gateway using the following input:
1. Resource: /tokenize
2. Query String: none or leave blank.
3. Headers:
```
Accept:application/json
sf-custom-api-key:<<FORTANIX_DSM_API_KEY>>
sf-custom-key-names:<<KEY_NAME_1,KEY_NAME_2,KEY_NAME_3,KEY_NAME_X,,,>>
```
  NOTE
  
  FORTANIX_DSM_API_KEY needs to correspond to the “Data Protection” or “Data Analysis” app so that it can /tokenize or /detokenize through the Plugin accordingly.
4. Request Body:
```
{"data": [
     [0, "SUSAN"],
     [1, "SEAN"],
     [2, "SAMUEL"],
     [3, "HOLEE"]
]}
```
NOTE

Adapt the data based on the data types or columns being tested. Also, test the /detokenize resource with same Headers, but different Request Body.

Figure 7: Test AWS API Gateway

Finalize the API Gateway Integration

Finalize the API Gateway integration by:

Creating an AWS IAM Role that Snowflake will assume for execution.
Deploying the proxy service on a demo stage and note the public or private URI:
```
https://<<XYZ.execute-api.us-east-2>>.amazonaws.com/<<DEMO_STAGE>>/
```

Setup the Snowflake API Integration and External Functions

When creating External Functions, first determine the number of parameters/columns that need to be processed, and the precise data types of such columns.

Fortanix recommends creating separate External Functions for tokenization and detokenization based on the number and type of columns. External Functions can call API Gateway integration endpoints based on tokenization and detokenization as explained previously and as shown below.

Configure the AWS Gateway API Integration deployment stage and IAM Role in Snowflake.

CREATE OR REPLACE API INTEGRATION fortanix_plugin
   API_PROVIDER = aws_api_gateway
   API_AWS_ROLE_ARN = 'arn:aws:iam::<<ACCOUNT_ID>>:role/service-role/<<SNOWFLAKE-ROLE-ID>>'
   API_ALLOWED_PREFIXES = ('https://<<XYZ.execute-api.us-east-2>>.amazonaws.com/<<DEMO_STAGE>>/')
   enabled=true;

DESCRIBE INTEGRATION fortanix_plugin;

NOTE

Substitute with the correct identifiers above.

Create External Functions for single columns, say corresponding to SSN and Credit Card number.

-- Single column tokenization: SSN
DROP FUNCTION dsm_tokenize_ssn(varchar);

CREATE SECURE EXTERNAL FUNCTION dsm_tokenize_ssn(fname varchar)
  RETURNS variant
  IMMUTABLE
  API_INTEGRATION = fortanix_plugin
  HEADERS = (
      'api-key'='<<FORTANIX_DSM_DATA_PROTECTION_API_KEY>>',
      'key-names'='<<SSN_Key_Name>>'
  )
  AS 'https://<<XYZ.execute-api.us-east2>>.amazonaws.com/<<DEMO_STAGE>>/tokenize';

select dsm_tokenize_ssn('123-45-6789');

-- Single column tokenization: Credit Card Number
DROP FUNCTION dsm_tokenize_ccn(varchar);

CREATE SECURE EXTERNAL FUNCTION dsm_tokenize_ccn(fname varchar)
  RETURNS variant
  IMMUTABLE
  API_INTEGRATION = fortanix_plugin
  HEADERS = (
      'api-key'='<<FORTANIX_DSM_DATA_PROTECTION_API_KEY>>',
      'key-names'='<<CCN_Key_Name>>'
)
AS 'https://<<XYZ.execute-api.us-east2>>.amazonaws.com/<<DEMO_STAGE>>/tokenize';

select dsm_tokenize_ssn('4123456789012340');

Setup a test table and insert data using tokenization External Functions:

CREATE or REPLACE TABLE test_table (
     id number autoincrement start 1 increment 1,
     fname varchar,
     ssn varchar,
     addr varchar,
     ccn varchar
);

insert into test_table (id, fname, ssn, addr, ccn) select 1, 'Franky Hou', dsm_tokenize_ssn('001-02-0001')[0]::text, '1 Infinity Loop', dsm_tokenize_ccn('1234123412341234')[0]::text;
insert into test_table (id, fname, ssn, addr, ccn) select 2, 'Joan Lucas', dsm_tokenize_ssn('001-02-0002')[0]::text, '918 Batman Drive', dsm_tokenize_ccn('9876987698769876')[0]::text;
insert into test_table (id, fname, ssn, addr, ccn) select 3, 'James Woods', dsm_tokenize_ssn('001-02-0003')[0]::text, '482 Woody Ave', dsm_tokenize_ccn('1849372849384723')[0]::text;
insert into test_table (id, fname, ssn, addr, ccn) select 4, 'John Wick', dsm_tokenize_ssn('001-02-0004')[0]::text, '711 Nulla St', dsm_tokenize_ccn('3789020817673718')[0]::text;

Create an External Function for de-tokenization of a single column.

-- single column de-tokenization
DROP FUNCTION dsm_detokenize_ssn(varchar);


CREATE EXTERNAL SECURE FUNCTION dsm_detokenize_ssn(ssn varchar)
  RETURNS variant
  IMMUTABLE
  API_INTEGRATION = fortanix_plugin
  HEADERS = (
     'api-key'='<<FORTANIX_DSM_DATA_PROTECTION_API_KEY>>',
     'key-names'='<<SSN_Key>>'
)

AS 'https://<<XYZ.execute-api.us-east-2>>.amazonaws.com/<<DEMO_STAGE>>/detokenize';

select dsm_detokenize_ssn('806-30-1382');
select ssn, dsm_detokenize_ssn(ssn)[0]::text from test_table;

Create External Functions for multi-columns, say corresponding to First Name, SSN, and Credit Card number.

-- multi-column tokenization
DROP FUNCTION dsm_tokenize_multi(varchar, varchar, varchar);

CREATE EXTERNAL SECURE FUNCTION dsm_tokenize_multi(fname varchar, ssn varchar, ccn varchar)
  RETURNS variant
  IMMUTABLE
  API_INTEGRATION = fortanix_plugin
  HEADERS = (
      'api-key'='<<FORTANIX_DSM_DATA_PROTECTION_API_KEY>>',
      'key-names'='<<NAME_key,SSN_Key,CCN_Key>>'
)
AS 'https://<<XYZ.execute-api.us-east-2>>.amazonaws.com/<<DEMO_STAGE>>/tokenize';

select dsm_tokenize_multi('JOHN','123-01-1234','3935252016295727');

insert into test_table (id, fname, ssn, addr, ccn) select 4, 
dsm_tokenize_multi('Maggie','029-30-2901','5015254012895431')[0]::text, 
dsm_tokenize_multi('Maggie','029-30-2901','5015254012895431')[1]::text, 
dsm_tokenize_multi('Maggie','029-30-2901','5015254012895431')[2]::text

-- copy from one table to another and tokenize in place
CREATE or REPLACE TABLE stage_table (
    id number autoincrement start 1 increment 1,
    fname varchar,
    ssn varchar,
    addr varchar,
    ccn varchar
);

insert into stage_table (id, fname, ssn, addr, ccn) select src.id,
dsm_tokenize(src.fname, src.ssn, src.ccn)[0]::text, 
dsm_tokenize(src.fname, src.ssn, src.ccn)[1]::text,
src.addr,
dsm_tokenize(src.fname, src.ssn, src.ccn)[2]::text from test_table as src;

-- multi-column de-tokenization
DROP FUNCTION dsm_detokenize_multi(varchar, varchar, varchar);

CREATE EXTERNAL SECURE FUNCTION dsm_detokenize_ multi(fname varchar, ssn varchar, ccn varchar)
  RETURNS variant
  IMMUTABLE
  API_INTEGRATION = fortanix_plugin
  HEADERS = (
      'api-key'='<<FORTANIX_DSM_DATA_PROTECTION_API_KEY>>',
      'key-names'='<<NAME_Key,SSN_Key,CCN_Key>>'
)
AS 'https://<<XYZ.execute-api.us-east-2>>.amazonaws.com/<<DEMO_STAGE>>/detokenize';

select dsm_detokenize_ multi('A7G2','806-30-1382','6100938584236546');

select fname, ssn, ccn, 
     dsm_detokenize_ multi(fname,ssn,ccn)[0]::text, 
     dsm_detokenize_ multi(fname,ssn,ccn)[1]::text, 
     dsm_detokenize_ multi(fname,ssn,ccn)[2]::text
from test_table;