Understanding the Difference Between Data Masking and Row-Level Security in Snowflake

In this article, we will explore how to keep your data safe in Snowflake, focusing on two key security mechanisms, Data Masking and Row Level Security

As data professionals, protecting data is more important than ever. Fortunately, Snowflake offers some great tools to help us do just that.

When it comes to securing sensitive data in Snowflake, using Data Masking and Row-Level Security (RLS) are essential to limit access to sensitive information, but each work in different ways and are useful in different scenarios.

So, what’s the difference between them? Let’s break it down and look at how both can help improve data security in your organisation.

Open Table of Contents

Data Masking vs Row-Level Security (RLS)
- Data Masking
  - Key Features:
  - Example of a Masking Policy:
- Row-Level Security (RLS)
  - Key Features:
  - Example of a Row Access Policy:
Key Differences Between Data Masking and RLS
- Example of Combined Usage:

Data Masking vs Row-Level Security (RLS)

Data Masking

Data Masking is a feature in Snowflake that allows you to obscure sensitive information at the column level. For example, we want to allow finance to see sensitive fiscal information, but not Martin in Marketing. By using Data Masking we are able to ensure that only authorised users can see the actual data, while others (sorry Martin) see a masked version.

This is especially useful for protecting Personally Identifiable Information (PII), financial details, or any sensitive columns within a table.

Key Features:

Column-Level Protection: Only specific columns with sensitive data are masked, while the rest of the data remains unchanged, we want Martin to see the rest of the table, just not the fiscal information.

Role-Based Control: The visibility of the actual or masked data depends on the user’s role. For example, if Martin is in a Marketing Role he will not be able to see the information.

Example of a Masking Policy:

Features change from time to time with new features being added regularly, it is recommended that you review the documentation for the latest on data masking.

-- USE THE ACCOUNTADMIN ROLE
USE ROLE ACCOUNTADMIN;

-- CREATE THE MASKING POLICY FOR CREDIT CARD INFORMATION
CREATE
OR REPLACE MASKING POLICY MASK_CREDIT_CARD AS (CREDIT_CARD STRING) RETURNS STRING -> CASE
    WHEN CURRENT_ROLE() IN ('ADMIN') THEN CREDIT_CARD
    ELSE 'XXXX-XXXX-XXXX-1234'
END;

-- APPLY THE MASKING POLICY TO THE 'CREDIT_CARD' COLUMN IN THE 'CUSTOMERS' TABLE
ALTER TABLE
    CUSTOMERS
MODIFY
    COLUMN CREDIT_CARD
SET
    MASKING POLICY MASK_CREDIT_CARD;

In this case, only users with the ADMIN role can view the real credit card number, while other roles will see a masked version.

Row-Level Security (RLS)

Row-Level Security (RLS), on the other hand, controls access at the row level.

RLS determines which rows of data a user is allowed to see, based on specific conditions such as the user’s role, department, or region. For example, Suzan managing the Operations team needs access to the team’s contact details, but not the finance team in head office.

This makes RLS particularly important for organisations that need to restrict access to data based on a user’s responsibilities, in our case, Suzan in Operations.

Key Features:

Row-Level Protection: Users only see the rows they are authorised to access.
Context-Based Filtering: Access to data can be dynamically controlled based on user roles or other contextual conditions.

Example of a Row Access Policy:

Features change from time to time with new features being added regularly, it is recommended that you review the documentation for the latest on Row Access Policies (RLS).

Change <YOUR_DATABASE> to the desired database you want to query.

-- CREATE A ROLE FOR ACCESS
CREATE ROLE ACCESS_POLICY_ADMIN;

-- GRANT ACCESS TO CREATE ROW ACCESS POLCIES WITHIN A SPECIFC DATABASE
GRANT CREATE ROW ACCESS POLICY ON DATABASE <YOUR_DATABASE> TO ROLE ACCESS_POLICY_ADMIN;

-- CREATE A ROW ACCESS POLICY TO VIEW ROWS IN THE BANK_ACCOUNTS TABLE
CREATE ROW ACCESS POLICY ADMIN_POLICY AS (ACCOUNT_ID STRING) RETURNS BOOLEAN - > CASE
    WHEN CURRENT_ROLE () = 'ACCESS_POLICY_ADMIN' THEN TRUE
    ELSE FALSE
END;

-- ALTER TABLE TO APPLY THE ACCESS POLICY
ALTER TABLE
    BANK_ACCOUNTS
MODIFY
    ROW ACCESS POLICY ADMIN_POLICY ON ACCOUNT_ID;

Here, only users with the ADMIN role can view rows in the bank_accounts table, while other users will see no rows.

Key Differences Between Data Masking and RLS

Granularity:

Data Masking works at the column level, obscuring sensitive values without hiding entire rows, think vetically.
RLS restricts access at the row level, ensuring users can only see the rows relevant to them, think horizontally.

Use Cases:

Data Masking is ideal for protecting sensitive information, such as credit card numbers, by masking specific fields.

RLS is more suitable when you need to limit visibility of entire records, such as allowing users to see only their department’s data.

Evaluation Order:

Row-Level Security is applied first. If a user is not permitted to view a row, it will be excluded from the result set before any data masking is applied.

Example of Combined Usage:

In some cases, you might want to apply both data masking and RLS for maximum security. For instance, you can restrict users from seeing certain rows of a customer database while masking sensitive information like email addresses for those who do have access.

-- CREATE THE ROW ACCESS POLICY FOR REGIONAL_MANAGERS
CREATE ROW ACCESS POLICY REGION_POLICY AS
(CUSTOMER_ID STRING) RETURNS BOOLEAN ->
CASE
    WHEN CURRENT_ROLE() = 'REGIONAL_MANAGER' THEN TRUE
    ELSE FALSE
END;

-- APPLY THE ROW ACCESS POLICY
ALTER TABLE CUSTOMERS MODIFY ROW ACCESS POLICY REGION_POLICY ON CUSTOMER_ID;

-- CREATE THE MASKING POLICY
CREATE MASKING POLICY EMAIL_MASK AS
(EMAIL STRING) RETURNS STRING ->
CASE
    WHEN CURRENT_ROLE() = 'ADMIN' THEN EMAIL
    ELSE '*****@domain.com'
END;

-- ALTER TABLE TO APPLY MASKING POLICY
ALTER TABLE CUSTOMERS MODIFY COLUMN EMAIL SET MASKING POLICY EMAIL_MASK;

The above applies everything we have just learnt, to make a robust way to manage data in complex teams and/or scenarios.

Both Data Masking and Row-Level Security in Snowflake are essential tools for securing sensitive data, but they serve different purposes. Data Masking protects individual columns, while RLS controls access at the row level. Together, these features allow for a comprehensive, multi-layered security approach, ensuring that users only access the data they are authorised to see.