Step By Step Guide Using Protegrity For External Tokenization

Author: Geetika Bansal & Hima Naga Bindu Narni

This blog provides information about implementing External Tokenization in Snowflake using Protegrity. It also briefly explains setting up an account, creating API Integration, external functions, and masking policies

Introduction:

External Tokenization allows organizations to tokenize their sensitive data before loading that data into Snowflake and dynamically detokenize data at query runtime using masking policies with External Functions.

Why use Protegrity for Snowflake:

Protegrity for Snowflake includes Snowflake’s superior data-warehouse capabilities with the ability to extend internal data security policies to Snowflake to keep sensitive data private

• Continuous compliance.

• Hybrid infrastructure support.

• Continuous data democratization.

Source: Protegrity for Snowflake[3]

An example of how the tokenized data looks & what tokenization offers:

BEFORE TOKENIZATION

AFTER TOKENIZATION

As shown in the above images External tokenization is used to tokenize the sensitive data. By using this feature we can protect our sensitive data from unauthorized access.

Encryption v/s Tokenization:

Generally Encryption and Tokenization both are used to protect the data. In Encryption, keys are required to perform data encryption, but in tokenization, it does not require any keys. Algorithms used in Tokenization are much stronger than algorithms used in Encryption. Hence, Tokenization is more secure as compared to Encryption. In addition to this, External Tokenization can be implemented using both external functions and masking policies.

Steps to be followed:

1. Subscribe to Protegrity from AWS account

AWS Marketplace -> Search Protegrity -> Protegrity Professional Service -> Cloud protect for snowflake -> Continue to Subscribe

2. Follow the steps mentioned below after clicking on Continue to Subscribe :

• Create new account

• Do Email verification

• Start Deployment setup

• Enter Account details

• Create Security policy

• Follow these steps and get the KMS KEY ARN

• Install it

• Run the below scripts in Snowflake

• Run the scripts to create api integration and external function in snowflake

• API Integration Script:

CREATE OR REPLACE API INTEGRATION protegrity_api

api_provider = aws_api_gateway

api_aws_role_arn = 'arn:aws:iam::362153220565:role/Protegrity-dbRole'

enabled = true

api_allowed_prefixes=

('https://uv8ciz3wok.execute-api.ap-south-1.amazonaws.com/pty/v1/snowflake');

• External Function Script:

CREATE OR REPLACE SECURE EXTERNAL FUNCTION protect_alphanum (val varchar)

RETURNS varchar

IMMUTABLE

API_INTEGRATION = protegrity_api

HEADERS=('X-Protegrity-HCoP-Rules'='{"payload_type":"JSON","jsonpaths":[{"jsonpath":\"\$\.data\[\*\][1]","op_type":"protect","data_element":"deTokAlphaNum"}]}')

CONTEXT_HEADERS=(current_user, current_timestamp, current_account)

AS 'https://uv8ciz3wok.execute-api.ap-south-1.amazonaws.com/pty/v1/snowflake';

describe api integration protegrity_api;

• Run “Describe api integration <api_integration_name> ;” to find out User ARN and External ID and paste it in fields

• Enter those details and click on continue

• Follow the steps mentioned in below screenshot

• Copy the code

• Click on Test-db Role >>Trust relationships >> edit trust policy >> (paste code from Protegrity page that you copied)

• Click on update policy and open your snowflake account

3. Create a custom role APP_ROLE

4. Grant required privileges to that role on database, schema and table

5. Create a masking policy for the column you want to hide and alter that column by applying that policy.

6. Here only app_role can see the original passwords and remaining all roles can only see the tokenized values of the passwords.

• Scripts for implementing above steps (3,4,5 and 6).

---create custom role—

use role useradmin;

create role APP_ROLE;

--grant that role to user

grant role APP_ROLE TO user <Username>;

--grant required privileges to custom role on database,schema,table,warehouse

grant usage on database dev_db to role APP_ROLE;

grant usage on schema USER_DETAILS_SCHEMA to role APP_ROLE;

grant select on table USER_DETAILS to role APP_ROLE;

grant select on table USER_QUES_INPUT to role APP_ROLE;

grant select on table USER_QUES_INPUT_STAGE to role APP_ROLE;

grant usage on warehouse APP_USER_WAREHOUSE to role APP_ROLE

-----create masking policy-----

create or replace masking policy mask_login_passwd as (val string) returns string ->

case

when current_role() in ('APP_ROLE') then val

else protect_alphanum (val)

End;

—-apply masking policy on required columns of your table—-

alter table USER_DETAILS modify column login_password set masking policy mask_login_passwd;

----check the implementation with diff roles----

use role APP_ROLE;

select * from USER_DETAILS;

use role accountadmin;

select * from USER_DETAILS;

use role securityadmin;

select * from USER_DETAILS;

7. Use ‘APP_ROLE’ and notice the details of masked column - details should be unmasked values

8. Use roles other than APP_ROLE and notice the details of masked columns - details should be masked or tokenized ones.

a. Use accountadmin role

b. Use securityadmin role

References:

1. https://www.protegrity.com/snowflake

2. https://docs.snowflake.com/en/user-guide/security-column-ext-token-use.html

3. https://assets.website-files.com/61b2686c9600210a03426844/61b2686c960021d9ce4269d9_ Protegrity_102220_Snowflake-Solution-Brief.pdf

Conclusion:

• Protecting sensitive data using Protegrity is more flexible and easy to implement.

• Protegrity also provides basic monitoring, better security and dynamic data masking.