Configuring Row Access and Column Masking Policies on the Same Column in Snowflake

Security is crucial for data consolidation, especially in data warehouses where reporting tools are connected to show different dashboards. Snowflake provides two main features to help with these requirements:

• Row Access Policy: Allows a filter to be applied to the result of a query and return only those rows that a role is authorized to see.  For example, in the following image, the same query was executed by an authorized role and an unauthorized role. On the left side are the rows that an authorized role can see. On the right side, an unauthorized role can see only one row after the row access policy has been applied.

• Column Level Access Policy: Allows a masking string to be applied on a column, so that an authorized role can see the data completely, but an unauthorized role can see the data partially. For example, in the following image, the same query was executed by an authorized role and an unauthorized role. On the left side are the rows that an authorized role can see. In the right side, an unauthorized role can see the data in column Phone partially and the date in the column SSN is totally masked.

Applying Row Access Policy and Column Masking Policy:

Each business has its own requirements. There could be scenarios where a business needs to apply both a Row Access Policy and a Column Masking Policy to the same table. For example, in the following image, on the right side, an unauthorized role can see only those rows allowed to see, but on top of that, the column Phone is partially masked, and the column SSN is completely masked.

Currently, Snowflake has a limitation that doesn’t allow those scenarios. Snowflake doesn’t allow the application of both types of Policies, Row Access and Column Masking, on the same column(s). Snowflake documentation says the following:

If you try to apply both type of Policies in the same Column, you will get the following error:

Solution:

The Solution is simple for this scenario; all we need to do is to create a View on top of the table. Materialized view is not needed, just a simple view. The Row Access Policy will be applied to the table, and the Column Masking Policy will be applied to the column. The view should be used by end users. Let’s see the result:

Creating the table that has business data:

create table BUSINESS.employee (
  ID     INT,
  Phone  varchar,
  SSN    varchar
);
insert into BUSINESS.employee values (101,'408-123-5534','387-78-3456');
insert into BUSINESS.employee values (102,'510-334-3564','226-44-8908');
insert into BUSINESS.employee values (103,'214-553-9787','359-9987-0098');

Creating the Table with Row Access Rules

CREATE TABLE SECURITY.SALESACCESS (
  manager varchar,
  employee_id  int 

);
insert into security.salesaccess values ('JOHN',102);

Creating the Masking Policy

create or replace masking policy SECURITY.column_mask_phone as (r_phone varchar) returns varchar ->
  case
    when CURRENT_USER() in ('JOHN') then regexp_replace(r_phone, '.+\-','****')
    when CURRENT_USER() in ('DGOMEZ') then r_phone
  end;
  
create or replace masking policy SECURITY.column_mask_ssn as (r_ssn varchar) returns varchar ->
  case
    when CURRENT_USER() in ('JOHN') then  '*********'
    when CURRENT_USER() in ('DGOMEZ') then r_ssn
  end;

Applying the Row Access Policy to the table

alter table  BUSINESS.employee add row access policy security.ra_policy_employee on (ID);

Applying the Column Masking Policy to the View

alter VIEW BUSINESS.v_employee modify column phone set masking policy SECURITY.column_mask_phone;
alter VIEW BUSINESS.v_employee modify column ssn set masking policy SECURITY.column_mask_ssn;

Checking the result with unauthorized role:

Checking the result with authorized role:

Is your organization adapting to remain competitive? In the Architect Room, we design a roadmap for the future of your digital organization, while still capitalizing on current technology investments. Our knowledge spans a variety of offerings across all of the major public cloud providers. Visit Red Pill Analytics or feel free to reach out on Twitter, Facebook, and LinkedIn.