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:
- Add Instance: This is the name to identify the instance created.
- In the Create tokenization keys section:
- 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.
- Multiple token objects can be created as per your needs.
Figure 2: Save Instance
- 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.
- Integration type: HTTP
- Endpoint URL: Point it to your Plugin URL
https://<Your_DSM_Service_URL>/sys/v1/plugins/<<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
For each resource method, configure Integration Request with the following configuration:
- Click “Add mapping template” and do the following:
- Select Content-Type as “application/json”.
- Select Request body passthrough as “Never”.
- 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')
}
- Test AWS API Gateway using the following input:
- Resource: /tokenize
- Query String: none or leave blank.
- 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,,,>> - Request Body:
{"data": [
[0, "SUSAN"],
[1, "SEAN"],
[2, "SAMUEL"],
[3, "HOLEE"]
]}
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; - 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;
Comments
Please sign in to leave a comment.