Snowflake Integration

Authors: Mark Tritaris, Iwona Rajca

About this Sample

The Playground can be easily leveraged from your Snowflake environment using Snowflake’s External Network Access functionality. An external access integration allows users to write their own UDFs that access external locations, such as the Playground APIs.

Prerequisites

• Protegrity API Playground activated account
• Access to a Snowflake (test) instance
• Ability to make external calls from Snowflake
• Snowflake grant to: CREATE SECRET, REPLACE SECRET,CREATE SECRET, REPLACE SECRET, CREATE FUNCTION, REPLACE FUNCTION, CREATE EXTERNAL ACCESS INTEGRATION, REPLACE EXTERNAL ACCESS INTEGRATION, CREATE NETWORK RULE, REPLACE NETWORK RULE
• A sample table with some sensitive (test) data that needs protecting

Snowflake Setup

1. Open a new worksheet.
2. Choose the authorized role to run this sample. In our case we are using the accountadmin.
3. Choose the warehouse and the database to use, e.g. USE DATABASE PROTEGRITY.

Playground Setup

1. To get any responses from the Playground, we’ll need to log in to it first. It’s recommended to create a mechanism to retrieve the JWT token from the Playground. This is not required for the integration itself but it will keep the code reusable: the Playground’s authentication token expires after 24 hours. Paste the following to your worksheet, then run it:

   
       -- Generate a new JWT Token for the Playground
       CREATE OR REPLACE FUNCTION pty_login(email text, password text)
       RETURNS STRING
       LANGUAGE PYTHON
       RUNTIME_VERSION = 3.8
       HANDLER = 'pty_login'
       EXTERNAL_ACCESS_INTEGRATIONS = (pty_external_access_integration)
       PACKAGES = ('requests', 'simplejson')
       AS
       $$
       import _snowflake
       import simplejson as json
       import requests 
   
       def pty_login(email, password):
   
           body = {
             "email": email,
             "password": password
           }
   
           url = "https://api.playground.protegrity.com/auth/login"
           headers = {
               "Content-type": "application/json"
               
           }
   
           session = requests.Session()
           response = session.post(url, json=body, headers=headers)
           
           response_as_json = json.loads(response.text)
           
           return response_as_json['jwt_token']
       $$;
       

2. Log in to the Playground by providing your email and password:

   
       SELECT pty_login('YOUR_EMAIL','YOUR_PASSWORD') as jwt;
       

3. Construct a new secret to envelop all your account information. The secret will be very handy for authenticating your requests to the API: our fuction will simply retrieve your details when needed. Provide your API Key in the api_token field and replace the jwt_token value with the token returned from the previous query.

   
       CREATE OR REPLACE SECRET pty_playground_login
           TYPE = GENERIC_STRING
           SECRET_STRING = '{
             "clientName":"API Playground User",
             "api_token":"YOUR_API_KEY",
             "jwt_token": "YOUR_JWT_TOKEN"}';
       

4. Create a network rule that points to the Protegrity API main url, and create an external access integration that allows usage of the network rule and the secret we previously created.

   
       -- Create a network rule to Protegrity API 
       CREATE OR REPLACE NETWORK RULE pty_network_rule
       MODE = EGRESS
       TYPE = HOST_PORT
       VALUE_LIST = ('api.playground.protegrity.com');
   
       -- Create an integration using the network rule and secret
       CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION pty_external_access_integration
       ALLOWED_NETWORK_RULES = (pty_network_rule)
       ALLOWED_AUTHENTICATION_SECRETS = (pty_playground_login)
       ENABLED = true;
       

Data Protection in Action

It’s time we put everything together and start protecting some data.

1. Create a universal UDF to call Protegrity endpoints for protecting data. The function will accept the name of the endpoint to call, and the data to protect.

   
       -- Create a UDF to de-identify a single value
       CREATE OR REPLACE FUNCTION pty_protect(endpoint text, data text)
       RETURNS STRING
       LANGUAGE PYTHON
       RUNTIME_VERSION = 3.8
       HANDLER = 'pty_protect'
       EXTERNAL_ACCESS_INTEGRATIONS = (pty_external_access_integration)
       PACKAGES = ('requests', 'simplejson')
       SECRETS = ('cred' = pty_playground_login)
       AS
       $$
       import _snowflake
       import simplejson as json
       import requests 
   
       def pty_protect(endpoint, data):
           credentials = json.loads(_snowflake.get_generic_secret_string('cred'), strict=False)
           
           body = {
             "operation": "protect",
             "data": [
                 data
             ]
           }
   
           url = "https://api.playground.protegrity.com/v1/" + endpoint
           headers = {
               "x-api-key": credentials["api_token"],
               "Authorization": credentials["jwt_token"],
               "Content-type": "application/json"
           }
   
           session = requests.Session()
           response = session.post(url, json=body, headers=headers)
           
           response_as_json = json.loads(response.text)
           
           return response_as_json[0]
       $$;
       

2. Run the UDF to make sure it’s working as expected. To protect data, you can choose any of our published data protection endpoints, for example name:

   
       SELECT pty_protect('name','Mickey Mouse') as name;
       

   You should receive a tokenized version of Mickey’s name: oWfepC TGEdC. At last his privacy is protected.

3. You can now start protecting your data stored in Snowflake tables. Choose your test table and run a select statement, wrapping the column names in your function calls. Make sure to match the type of the data you are protecting with the appropriate tokenization type: for example, choose the name for first and last names, or ssn for Social Security Numbers. Try the query on a limited sample size first – you wouldn’t want to run out of your Playground credits!

   Example SQL query:

   
       select 
       pty_protect('name',first_name) as protected_first_name, 
       pty_protect('name',last_name) as protected_last_name, 
       pty_protect('email',email) as protected_email, 
       pty_protect('ssn',ssn) as protected_ssn, 
       pty_protect('iban',iban) as protected_iban, 
       pty_protect('dob',birthday)  as protected_dob
       from my_table limit 5;
       

   Note that the query processes each field separately, which adds to the total processing time. In a production scenario, batch processing would be recommended for achieving best performance.

Summary

That’s it! This example builds a sample integration that allows Snowflake users to protect data. You can extend this sample by building an unprotect function and incorporating different user roles. Or, creating a function that accepts options, such as dictionary for French and German.