Using Tags to Mask Data in Snowflake

---

---

Snow­flake‘s estab­lished pos­i­tion as a lead­ing cloud data plat­form can be attrib­uted to sev­eral unique fea­tures and advant­ages it offers. Safe­guard­ing sens­it­ive inform­a­tion is para­mount in today’s data-cent­ric world, and Snow­flake has risen to the chal­lenge by intro­du­cing tags and mask­ing policies. Tags in Snow­flake allow users to cat­egor­ise and label data­base objects, mak­ing it easier to identify and man­age spe­cific data­sets, whilst mask­ing policies are designed to con­ceal the ori­ginal data, dis­play­ing only masked val­ues to unau­thor­ised roles, guar­an­tee­ing data security.

In con­junc­tion, these two fea­tures offer a power­ful mech­an­ism: by using tags to identify sens­it­ive data and then employ­ing mask­ing policies, Snow­flake users can sys­tem­at­ic­ally pro­tect crit­ical inform­a­tion, ensur­ing that only author­ised roles can access the ori­ginal data. In this blog post, we will go deeper into the cre­ation of mask­ing policies using tags, focus­ing on the advant­ages and the con­straints of this fea­ture in dif­fer­ent work­ing scenarios.

Require­ments

• Snow­flake Enter­prise Edi­tion (or higher)
• Access to an ACCOUNTADMIN role in your Snow­flake session

Work­ing Scenario

Let’s ima­gine there is a sales com­pany called “New Town Shoes” that is using Snow­flake to save all their items and cus­tom­ers data. The com­pany has a data­base called TOWNSHOES_DB where there is a schema called TOWNSHOES_SALES. Inside this schema, there are 2 tables: CUSTOMER (with cus­tomer data) and ITEMS (with inform­a­tion about the items they sell). There is also a view named CUSTOMER_100 where they keep the data of the top 100 customers.

The com­pany is work­ing with a third-party con­sult­ant, who holds a SYSADMIN role in Snow­flake, respons­ible for cre­at­ing BI reports. How­ever, the man­age­ment is con­cerned about the poten­tial expos­ure of con­fid­en­tial data within the data­base, and they have reques­ted their IT man­ager, hold­ing an ACCOUNTADMIN role in Snow­flake, to mask cer­tain data. This meas­ure will enable the con­sult­ant to con­tinue their work without access­ing sens­it­ive information.

The IT man­ager decides to cre­ate a new role named TAG_MASKING_ADMIN that will allow them to cent­ral­ise and del­eg­ate the respons­ib­il­ity of man­aging data mask­ing to one of their team mem­bers. This role will exclus­ively man­age the mask­ing policies, determ­in­ing which data seg­ments are con­sidered confidential.

The sub­sequent sec­tions provide a detailed walk­through on imple­ment­ing this secur­ity model, ensur­ing that data is pro­tec­ted whilst still being avail­able for cer­tain roles.

Cre­ate A Cus­tom Role for Data Mask­ing Using Tags (TAG_MASKING_ADMIN)

Cre­ate A Tag Role

First, we’ll set up a spe­cial­ised role designed for data tag­ging, using the USERADMIN role.

How­ever, cre­at­ing this role isn’t the end of the pro­cess; the role requires CREATE and APPLY priv­ileges related to data tag­ging. To grant these cru­cial priv­ileges, we’ll employ the ACCOUNTADMIN role as it pos­sesses the required permissions:

USE ROLE USERADMIN; // USING USERADMIN TO CREATE NEW ROLES
CREATE OR REPLACE ROLE TAG_ADMIN; // CREATION OF TAG_ADMIN ROLE THAT WILL BE ABLE TO TAG OBJECTS

USE ROLE ACCOUNTADMIN;
GRANT CREATE TAG ON SCHEMA TOWNSHOES_DB.TOWNSHOES_SALES TO ROLE TAG_ADMIN; //NOTE THAT CREATE TAG PRIVILEGES MUST BE GIVEN IN SCHEMA LEVEL
GRANT APPLY TAG ON ACCOUNT TO ROLE TAG_ADMIN; //NOTE THAT APPLY TAG PRIVILEGES MUST BE GIVEN IN ACCOUNT LEVEL

Cre­ate A Data Mask­ing Role

To imple­ment data secur­ity using mask­ing policies, we’ll estab­lish a ded­ic­ated role with the neces­sary priv­ileges. By lever­aging the USERADMIN role, we can cre­ate this new role spe­cific­ally to man­age mask­ing policies. Just as in our pre­vi­ous setup, this role requires both CREATE and APPLY privileges.

To ensure it has the appro­pri­ate per­mis­sions, we turn back to the ACCOUNTADMIN role:

USE ROLE USERADMIN; // USING USERADMIN TO CREATE NEW ROLES
CREATE OR REPLACE ROLE MASKING_ADMIN; // CREATION OF MASKING_ADMIN ROLE THAT WILL BE ABLE TO APPLY MASKING POLICIES

USE ROLE ACCOUNTADMIN;
GRANT CREATE MASKING POLICY ON SCHEMA TOWNSHOES_DB.TOWNSHOES_SALES TO ROLE MASKING_ADMIN; //NOTE THAT CREATE TAG PRIVILEGES MUST BE GIVEN IN SCHEMA LEVEL

GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE MASKING_ADMIN; //NOTE THAT APPLY TAG PRIVILEGES MUST BE GIVEN IN ACCOUNT LEVEL

TAG_MASKING_ADMIN Cre­ation (Join­ing of TAG_ADMIN and MASKING_ADMIN)

To estab­lish a com­pre­hens­ive role, we are intro­du­cing TAG_MASKING_ADMIN. This role com­bines the priv­ileges from the newly cre­ated roles TAG_ADMIN and MASKING_ADMIN. Remem­ber that for TAG_MASKING_ADMIN to func­tion seam­lessly, it needs access to all data­base objects, includ­ing schemas, tables, views, ware­houses, and more. Addi­tion­ally, to make this role action­able, it is imper­at­ive to grant it to a spe­cific user:

USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE ROLE TAG_MASKING_ADMIN; // CREATION OF THE ROLE

// WE GRANT PRIVILEGES TO THIS ROLE FOR THE DIFFERENT DATABASES, SCHEMAS, TABLES, VIEWS OR WAREHOUSE WHERE WE WILL APPLY THE TAG MASKING POLICIES
GRANT ALL ON DATABASE TOWNSHOES_DB TO ROLE TAG_MASKING_ADMIN;
GRANT ALL ON SCHEMA TOWNSHOES_DB.TOWNSHOES_SALES TO ROLE TAG_MASKING_ADMIN;
GRANT ALL ON TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER TO ROLE TAG_MASKING_ADMIN;
GRANT ALL ON TABLE TOWNSHOES_DB. TOWNSHOES_SALES.ITEM TO ROLE TAG_MASKING_ADMIN;
GRANT ALL ON VIEW TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100 TO ROLE TAG_MASKING_ADMIN;
GRANT ALL ON WAREHOUSE TOWNSHOES_WH TO ROLE TAG_MASKING_ADMIN; //TOWNSHOES_WH IS THE NAME OF THE WAREHOUSE USED

GRANT ROLE TAG_MASKING_ADMIN TO USER PABLODONIGA; //WE GRANT THIS ROLE TO THE SELECTED USER

// FINALLY, WE GIVE TO TAG_MASKING_ADMIN, THE PRIVILEGES FROM THE 2 PREVIOUS CUSTOM ROLES

USE ROLE ACCOUNTADMIN;
GRANT ROLE TAG_ADMIN TO ROLE TAG_MASKING_ADMIN;
GRANT ROLE MASKING_ADMIN TO ROLE TAG_MASKING_ADMIN;

Cre­at­ing Tags and Mask­ing Policies

Cre­at­ing A Tag

In Snow­flake, tags are metadata labels that can be applied to vari­ous objects (like tables, views, schemas, etc.) to help cat­egor­ise, man­age, and search for these objects more effect­ively. By using tags, you can quickly identify the pur­pose, sens­it­iv­ity, and other char­ac­ter­ist­ics of data objects.

In this case, we are cre­at­ing a tag named MASKED_COLUMNS_TAG with 1 allowed value:

• SYSADMIN_MASKING: The value that will be used to tag the objects we want to hide from the third-party con­sult­ant (with a SYSADMIN role):

USE ROLE TAG_MASKING_ADMIN;

// CREATION OF THE TAG  ‘MASKED_COLUMNS_TAG’
CREATE OR REPLACE TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG
	ALLOWED_VALUES ‘SYSADMIN_MASKING’;

Note that tags are cre­ated inside a schema hier­archy. When work­ing with tags, bear the fol­low­ing points in mind:

• Allowed val­ues can­not be changed or added once the tag has been cre­ated (for example, using ALTER TAG). If you want to use new tag val­ues, you must cre­ate a whole new tag object.
• To be able to drop a tag, no policies can be asso­ci­ated with that tag.

Cre­at­ing A Mask­ing Policy

Mask­ing policies are dynamic data mask­ing tools that pro­tect sens­it­ive data, auto­mat­ic­ally trans­form­ing data when quer­ied, ensur­ing that unau­thor­ised users see obfus­cated or masked val­ues instead of the actual data. This power­ful fea­ture enhances data secur­ity without modi­fy­ing stored data.

A lim­it­a­tion is that a single policy can only be applied to one data­type. For example, in this case we’ll cre­ate a mask­ing policy for string val­ues, named STRING_DATA_MASK:

// CREATION OF THE MASKING POLICY FOR STRINGS 

CREATE OR REPLACE MASKING POLICY TOWNSHOES_DB. TOWNSHOES_SALES.STRING_DATA_MASK AS (VAL STRING) RETURNS STRING ->
	CASE
		WHEN CURRENT_ROLE() NOT IN (‘TAG_MASKING_ADMIN’) THEN ‘***MASKED***’
	ELSE VAL
END;

When this mask­ing policy is applied to an object, the string val­ues of that object will be shown as ***MASKED*** if the role per­form­ing the query is not TAG_MASKING_ADMIN.

Adding the Mask­ing Policy to A Tag

Finally, we’ll apply a mask­ing policy to a tag, so that all objects that are tagged with a spe­cific value are auto­mat­ic­ally sub­jec­ted to the mask­ing policy. Let’s use the fol­low­ing code:

// CREATION OF THE MASKING POLICY FOR STRINGS


ALTER TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG SET

MASKING POLICY TOWNSHOES_DB. TOWNSHOES_SALES. STRING_DATA_MASK;

Now, all objects tagged with some of the allowed val­ues of the STRING_DATA_MASK tag (SYSADMIN_MASKING or DATES) will auto­mat­ic­ally be included in the mask­ing policy.

Work­ing Scen­arios Using Tags and Mask­ing Policies

Tag­ging A Full Schema

After cre­at­ing the TAG_MASKING_ADMIN role, the com­pany IT man­ager wants to check if tag­ging the TOWNSHOES_SALES schema will pre­vent the SYSADMIN account from visu­al­ising the data.

As they have already linked the mask­ing policy to a tag, they only need to tag the objects (in this case the schema) to apply the mask­ing policy. In this scen­ario, they’ll tag the schema with the value SYSADMIN_MASKING:

USE ROLE TAG_MASKING_ADMIN;  

// WE ADD THE TAG ‘SYSADMIN_MASKING’ TO THE SCHEMA ‘TOWNSHOES_SALES’
ALTER SCHEMA TOWNSHOES_SALES SET TAG 
TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;


// NOW WE CAN DO SOME QUERIES TO THE OBJECTS INSIDE THE SCHEMA, AND WE CAN SEE THAT USING THIS ROLE, ALL VALUES ARE SHOWN.
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.ITEM;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100;

// NOW WE DO THE SAME BUT USING THE SYSADMIN ROLE, AND WE CAN SEE THAT THE DATA IS SHOWN MASKED

USE ROLE SYSADMIN;  
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.ITEM;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100;

This is how the CUSTOMER table appears for the SYSADMIN role:

As we can see, all the val­ues from objects inside the schema (tables and views) show masked data for the SYSADMIN role. It is import­ant to note that only the columns with string data­type are masked, as the mask­ing policy we pre­vi­ously cre­ated was applied only to strings.

Now let’s ima­gine that the IT man­ager wants to untag the ITEM table from the tagged schema, while all other objects (except the ITEM table) con­tinue to be affected by the mask­ing policy:

USE ROLE TAG_MASKING_ADMIN;  

// WE ADD THE TAG ‘SYSADMIN_MASKING’ TO THE SCHEMA ‘TOWNSHOES_SALES’
ALTER SCHEMA TOWNSHOES_SALES SET TAG 
TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

// NOW WE ‘UNTAG’ THE ITEM TABLE 
ALTER TABLE TAG TOWNSHOES_DB. TOWNSHOES_SALES.ITEM UNSET TAG 
TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG;



// NOW WE CAN DO SOME QUERIES TO THE OBJECTS INSIDE THE SCHEMA, AND WE CAN SEE THAT USING THIS ROLE, ALL VALUES ARE SHOWN.
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.ITEM;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100;

// NOW WE DO THE SAME BUT USING THE SYSADMIN ROLE, AND WE CAN SEE THAT THE DATA IS SHOWN MASKED

USE ROLE SYSADMIN;  
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.ITEM;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100;

The res­ult from query­ing the ITEM table using the SYSADMIN role will look like this:

As we can see, even though the tag was removed from the table, because the schema itself is still tagged, all objects within the schema con­tinue to be gov­erned by the mask­ing policy. This occurs des­pite the ‘unset’ com­mand hav­ing been applied to the indi­vidual table, so if we want spe­cific tables or views within a schema to adhere to a par­tic­u­lar mask­ing policy, we should apply indi­vidual tags dir­ectly to those objects.

Also notice that, as before, only the string val­ues from the ITEM table are masked.

Tag­ging Tables and Views

As we have just seen, if we want to tag spe­cific tables or views, we must do so one by one. The code used to tag these objects is very sim­ilar to the schema. In our scen­ario, the IT man­ager only wants to mask data from the CUSTOMER table and the CUSTOMER_100 view, and they use this code:

USE ROLE TAG_MASKING_ADMIN;  

// WE ADD THE TAG ‘SYSADMIN_MASKING’ TO THE TABLE CUSTOMER
ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER SET TAG 
TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

// WE ADD THE TAG ‘SYSADMIN_MASKING’ TO THE VIEW CUSTOMER_100
ALTER VIEW TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100 SET TAG 
TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;


// NOW WE CAN DO SOME QUERIES TO THE OBJECTS, AND WE CAN SEE THAT USING THIS ROLE, ALL VALUES ARE SHOWN.
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100;

// NOW WE DO THE SAME BUT USING THE SYSADMIN ROLE, AND WE CAN SEE THAT THE DATA IS SHOWN MASKED

USE ROLE SYSADMIN;  
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER_100;

The res­ults from query­ing the CUSTOMER table using the SYSADMIN role:

Tag­ging Spe­cific Columns from A Table

Now the IT man­ager wants to mask the Cus­tomer ID, First Name and Cus­tomer SK columns from the CUSTOMER table. To tag spe­cific columns, the fol­low­ing code can be used:

USE ROLE TAG_MASKING_ADMIN;  

// WE ADD THE TAG ‘SYSADMIN_MASKING’ TO THE CUSTOMER_ID, FIRST NAME AND CUSTOMER SK COLUMNS
ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘Customer ID’
SET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘First Name’
SET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘Customer SK’
SET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

// NOW WE CAN QUERY THE TABLE
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;

// NOW WE DO THE SAME BUT USING THE SYSADMIN ROLE

USE ROLE SYSADMIN;  
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;

The res­ults from query­ing the CUSTOMER table using the SYSADMIN role are now like this:

As we can see, only two of the tagged columns are masked (as expec­ted). Even though the Cus­tomer SK column has been tagged, it’s still show­ing val­ues because its data­type is not a string, so it doesn’t meet the require­ments of the mask­ing policy.

Adding Dif­fer­ent Mask­ing Policies to A Tag

As we have just observed, only one data­type can be applied under a mask­ing policy. To mask other data­types using the same tag, we need to estab­lish a new mask­ing policy for each one. In this instance, the com­pany IT man­ager intends to cre­ate a new policy for num­bers, enabling them to mask the Cus­tomer SK column that could not be masked previously:

USE ROLE TAG_MASKING_ADMIN;  

// WE CREATE A NUMERIC MASK POLICY
CREATE OR REPLACE MASKING POLICY TOWNSHOES_DB. TOWNSHOES_SALES.NUMERIC_DATA_MASK AS (VAL NUMBER) RETURNS NUMBER ->
	CASE
		WHEN CURRENT_ROLE() NOT IN (‘TAG_MASKING_ADMIN’) THEN 0
	ELSE VAL
END;

// WE ADD THIS POLICY TO THE MASKED_COLUMNS_TAG
ALTER TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG SET
MASKING POLICY TOWNSHOES_DB. TOWNSHOES_SALES. NUMERIC_DATA_MASK;

// NOTICE THAT IN THIS POINT WE HAVE 2 MASKING POLICIES (STRING_DATA_MASK AND NUMERIC_DATA_MASK) APPLIED TO THE TAG MASKED_COLUMNS_TAG

// NOW WE TAG THE DESIRED COLUMNS AS WE DID IN THE PREVIOUS SCENARIO

// WE ADD THE TAG ‘SYSADMIN_MASKING’ TO THE CUSTOMER_ID, FIRST NAME AND CUSTOMER SK COLUMNS
ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘Customer ID’
SET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘First Name’
SET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘Customer SK’
SET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

// NOW WE CAN QUERY THE TABLE
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;

// NOW WE DO THE SAME BUT USING THE SYSADMIN ROLE

USE ROLE SYSADMIN;  
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;

Let’s check the res­ults of the query:

Now all the columns have masked val­ues (string and numeric columns). It is import­ant to notice that when cre­at­ing a mask­ing policy, the masked value must match the data­type of the column. For example, in this scen­ario, the Cus­tomer SK column has been masked with the numeric value ‘0’ as we are not allowed to use a string value, for example ***MASKED***, for that column. This could be con­fus­ing for some new users, who might believe that the masked value rep­res­ents an actual num­ber. Remem­ber that a tag can sup­port only one mask­ing policy for each datatype!

Cre­at­ing A Mask­ing Policy Using the SHA2 Function

In this scen­ario, the third-party BI con­sult­ant work­ing with New Town Shoes reports that they are unable to cre­ate any charts, as all data is masked with the same value. Con­sequently, the IT man­ager decides to cre­ate a mask­ing policy using the SHA2 func­tion, which gen­er­ates a hex-encoded string for every dif­fer­ent value, so the pro­por­tions of the data remain the same. In this case, they will set up a new mask­ing policy using the SHA2 func­tion, then apply it to the Birth Coun­try column:

USE ROLE TAG_MASKING_ADMIN;  

// WE CREATE THE NEW MASK POLICY USING SHA2 FUNCTION
CREATE OR REPLACE MASKING POLICY TOWNSHOES_DB. TOWNSHOES_SALES.SHA2_STRING_DATA_MASK AS (VAL STRING) RETURNS STRING ->
	CASE
		WHEN CURRENT_ROLE() NOT IN (‘TAG_MASKING_ADMIN’) THEN SHA2(VAL)
	ELSE VAL
END;

// WE ADD THIS POLICY TO THE MASKED_COLUMNS_TAG
ALTER TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG SET
MASKING POLICY TOWNSHOES_DB. TOWNSHOES_SALES SHA2_STRING_DATA_MASK;

// NOW WE TAG THE COLUMN “BIRTH COUNTRY” AS WE NEED IN THIS SCENARIO

// WE ADD THE TAG ‘SYSADMIN_MASKING’ TO THE CUSTOMER_ID, FIRST NAME AND CUSTOMER SK COLUMNS
ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘Birth Country’
SET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG = ‘SYSADMIN_MASKING’;

// NOW WE CAN QUERY THE TABLE
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;

// NOW WE DO THE SAME BUT USING THE SYSADMIN ROLE

USE ROLE SYSADMIN;  
SELECT * FROM TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER;

The res­ult of the query executed as SYSADMIN will look like this:

We can see that every coun­try has a dif­fer­ent hex code, so now the BI con­sult­ant can build some charts even though the data is still hidden.

Other Import­ant Functions

Unset Tags and Unset Mask­ing Policies from Tags

As men­tioned before, only one mask­ing policy for the same data­type can be assigned to the same tag:

So, if we wish to replace or modify the exist­ing mask­ing policy, we must first remove the mask­ing policy from the tag. The code would look like this:

// TO UNSET A MASK POLICY FROM 
ALTER TAG TOWNSHOES_DB.TOWNSHOES_SALES.MASKED_COLUMNS_TAG
UNSET MASKING POLICY TOWNSHOES_DB.TOWNSHOES_SALES.SHA2_STRING_MASK;

To unset a tag from dif­fer­ent data­base objects, we can use the fol­low­ing code:

// TO UNSET A SCHEMA
ALTER SCHEMA TOWNSHOES_DB. TOWNSHOES_SALES 
UNSET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG;

// TO UNSET A TABLE
ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER 
UNSET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG;

// TO UNSET A COLUMN FROM A TABLE
ALTER TABLE TOWNSHOES_DB. TOWNSHOES_SALES.CUSTOMER MODIFY COLUMN ‘Birth Country’
UNSET TAG TOWNSHOES_DB. TOWNSHOES_SALES.MASKED_COLUMNS_TAG;

// NOTICE THAT FOR UNSET WE DO NOT NEED TO SELECT THE “ALLOWED VALUE” FROM THE TAG

Check Tags and Mask­ing Policies on Objects

To check which objects from a data­base have been tagged, we can use the TAG_REFERENCES_WITH_LINEAGE:

USE ROLE ACCOUNTADMIN;
SELECT      // USE SELECT * TO GET EVEN MORE INFORMATION FROM TAGS
    TAG_SCHEMA,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LEVEL,
    TAG_NAME,
    TAG_VALUE,
    COLUMN_NAME
FROM
    TABLE(      SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES_WITH_LINEAGE('TOWNSHOES_DB.TOWNSHOES_SALES.MASKED_COLUMNS_TAG')
    );

To verify the mask­ing policies applied to an object, we can use the POLICY_PREFERENCES func­tion. It is import­ant to note that this func­tion is applic­able only at the table, external table, view, or account level. This level must be defined in ref_entity_domain. In this example, we look for the mask­ing policies on the CUSTOMER table:

USE ROLE TAG_MASKING_ADMIN;
SELECT *
FROM TABLE (TOWNSHOES_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
  REF_ENTITY_DOMAIN => 'TABLE',
  REF_ENTITY_NAME => 'TOWNSHOES_DB.TOWNSHOES_SALES.CUSTOMER' )
);

We get the fol­low­ing results:

Con­clu­sion

The com­bin­a­tion of tags and mask­ing policies in Snow­flake provides a robust solu­tion for enhan­cing com­pli­ance with data pri­vacy reg­u­la­tions. This effect­ive com­bin­a­tion facil­it­ates the easy clas­si­fic­a­tion of data accord­ing to its sens­it­iv­ity, enabling organ­isa­tions to man­age and gov­ern their data effi­ciently. What’s more, mask­ing policies can be applied to vari­ous data­base objects, includ­ing schemas, tables, views, and even spe­cific columns, offer­ing gran­u­lar con­trol over data protection.

Whilst there are cer­tain incon­veni­ences to con­sider, such as the need for metic­u­lous policy con­fig­ur­a­tion, per­mit­ting only one data­type per mask­ing policy, or lim­ited masked value options for spe­cific data­types such as numeric or dates, the advant­ages are sig­ni­fic­ant, present­ing a com­pre­hens­ive approach to data secur­ity, gov­ernance, and reg­u­lat­ory com­pli­ance within the Snow­flake platform.

In sum­mary, lever­aging tags and mask­ing policies in Snow­flake enhances data man­age­ment, secur­ity, and com­pli­ance for organ­isa­tions, enabling them to con­fid­ently pro­tect sens­it­ive data, imple­ment effect­ive access con­trols, and eas­ily meet reg­u­lat­ory require­ments. If you’d like fur­ther inform­a­tion or wish to see how these solu­tions can be tailored to your spe­cific needs, please con­tact our team of cer­ti­fied experts.