Using Data Security Manager with Snowflake

  • Updated on May 28, 2025
  • Published on Jun 27, 2024
  • 8 minute(s) read
Prev Next

1.0 Introduction

This article 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.

At the time of creation, Snowflake External Functions must specify which Fortanix DSM keys will be used to tokenize or detokenize individual columns. Thereby, the API Gateway remains stateless and forwards the request from Snowflake to the Fortanix DSM plugin, requiring only the mapping of specific headers.

We will use the HEADERS property of CREATE EXTERNAL FUNCTION to pass key name information. The header name and value should be as follows:

  • Header name: key-names

  • Header value: A comma-separated string of key names. The first entry corresponds to the first column in the data, the second to the second column, and so on.

The keys specified in the header must already exist in Fortanix DSM. The plugin does not create keys; it verifies their existence. Additionally, the Fortanix DSM plugin ensures that the number of key names passed in the header matches the number of columns in the data sent by Snowflake. If there are fewer keys than the columns, then the operation will fail.

2.0 Prerequisites

Ensure the following:

  • Fortanix DSM version 3.27 or later is installed and operational.

3.0 Configure Fortanix DSM

A Fortanix DSM service must be configured, and the URL must be accessible. To create a Fortanix DSM account and group, refer to the following sections:

3.1 Signing Up

To get started with the Fortanix DSM cloud service, you must register an account at <Your_DSM_Service_URL>. For example, https://eu.smartkey.io.

For detailed steps on how to set up the Fortanix DSM, refer to the User's Guide: Sign Up for Fortanix Data Security Manager SaaS documentation.

3.2 Creating an Account

Access <Your_DSM_Service_URL> in a web browser and enter your credentials to log in to Fortanix DSM.

Figure 1: Logging in

For more information on how to set up an account in Fortanix DSM, refer to the User's Guide: Getting Started with Fortanix Data Security Manager - UI.

3.3 Creating an Instance

Perform the following steps to create an instance in the Fortanix DSM:

  1. In the DSM left navigation panel, click the Instances menu item, and then click the select the Tokenization check box. Click ADD INSTANCE on the Snowflake tile.

    Figure1: Add Snowflake instance

  2. On the Add instance page, do the following

    1. Title: Enter a name for your instance.

    2. In the Create tokenization keys section, select the applicable Data Type based on the type of data you want to protect. Fortanix DSM allows you to create security objects from any of the four tokenization data type groups. Multiple token objects can be created as per your needs.

      NOTE

      You must select the Apply dynamic data masking pattern check box during security object creation. This policy allows the Fortanix DSM application (app) to perform partial reveal or dynamic masking during de-tokenization. You cannot enable the masking policy after the object has been created, so plan accordingly during the creation process. This behavior may change in the future if key replication supports copying objects with an enabled masking policy.

    Figure 3: Instance details

  3. Click SAVE INSTANCE.

  4. Copy the API Key from the INSTANCE DETAILS dialog box. You will need this key when configuring your API gateway.

    Figure 4: Copy API key

  5. Click CLOSE.

3.4 Snowflake Instance Detailed View

Navigate to the Integrations menu item → Snowflake wizard → Snowflake instances table. In the instance detailed view page, the following information is represented:

  • API KEY: Click VIEW API KEY DETAILS to view the details of the API key, such as username and password. This is applicable only if the app authentication method is API Key.

  • MANAGE KEYS: Click MANAGE to oversee the keys created.

  • DELETE: To delete the instance, click the overflow menu and select the DELETE option. Note that deleting an instance will result in the removal of the app, group, and all security objects associated with the instance, rendering all key material inaccessible.

Figure 5: List of all instance

4.0 Setting up AWS API Gateway Proxy Service

4.1 Create and Test AWS API Gateway

Create and test AWS API Gateway with the following configuration:

  1. REST API Endpoints that may be public or private:

  2. Two resources for /tokenize and /detokenize.

    NOTE

    POST method is required for both resources.

    For each resource method, configure the Integration Request with the following configuration:

    • Integration type: HTTP

    • Endpoint URL: Point it to your Plugin URL

      https://<Your_DSM_Service_URL>/sys/v1/plugins/<<PLUGIN_UUID>>

      NOTE

      Change the Fortanix DSM fully qualified domain name (FQDN) and the Plugin UUID.

      • The Fortanix DSM FQDN (Your_DSM_Service_URL) should be the same as selected in Section 3.1: Signing Up.

      • The PLUGIN_UUID refers to the UUID of the Snowflake plugin, which was created on the Fortanix DSM Plugins page. Navigate to the plugin and the UUID can be found at the top of the page.

    • Content handling: Passthrough

    • HTTP Headers – Add the “Authorization” header and leave the value empty, as it will be sent by Snowflake through an External Function custom header.

    AWSAPIGateway1.png

    Figure 6: AWS API gateway

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

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

      • Where, KEY_NAME_1, KEY_NAME_2, KEY_NAME_3..and so on are the tokenization keys created in DSM using the easy wizard

      • 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 the same Headers, but different Request Body.

    TestAPI.png

    Figure 7: Test AWS API gateway

4.2 Finalize the API Gateway Integration

Finalize the API Gateway integration by:

  1. Creating an AWS IAM Role that Snowflake will assume for execution.

  2. Deploying the proxy service on a demo stage and noting the public or private URI:

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

Where, DEMO_STAGE is an environment in which you can deploy your API. For example, you can create development or production stages. You can configure different settings for each stage of your API. For changes to take effect, you must first deploy your API.

Stage-snowflake.png

Figure 8: Stages

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

  1. 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::<<AWS_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. Where,

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

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

  3. Set up 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;

  4. Create an External Function for the 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;

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

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

    NOTE

    External Function output data is accessed using column index [0], [1], and [2] to get the first, second, and last element of the array, which corresponds to First Name, SSN, and Credit Card Number.

Related articles