Column-Level and Row-Level Security

1. Column-Level and Row-Level Security

Access control at the object level is binary — a role can query a table, or it can't. What happens when you need something more nuanced? That's what this video covers: column-level and row-level security.

2. The Problem With Table-Level Access

Claro's users table contains names, email addresses, credit scores, and credit band. When you grant SELECT to the analyst role, they can see all of it. An analyst running campaign reports doesn't need raw credit scores. Table-level access control can't make that distinction — you need security at a finer grain.

3. What is Column-Level Security?

Column-level security doesn't remove a column from query results. The column is still there — what changes is the value that appears, depending on who is asking. A privileged role sees a real credit score. An unprivileged role sees a masked value. The underlying data is unchanged. Masking happens at query time, applied on the fly based on the querying role. The mechanism is called Dynamic Data Masking.

4. Dynamic Data Masking

Dynamic Data Masking works by attaching a masking policy to a column. The policy is a SQL function that takes the column value as input and returns either the real value or a masked version, depending on the querying role. The analyst role sees NULL where credit scores should appear. The credit admin role however sees those real values. The table and query are identical — the masking policy handles the rest automatically.

5. Creating and Applying a Masking Policy

The masking policy is created as a function: if the querying session includes the CREDIT_ADMIN role, return the real credit score, otherwise return a sentinel value. We use IS_ROLE_IN_SESSION here, which checks all active roles in the session including inherited and secondary roles. CURRENT_ROLE, by contrast, only returns the active primary role, so a user who has CREDIT_ADMIN as a secondary role would get masked data if you used CURRENT_ROLE instead. Once defined, the policy is applied to the column with ALTER TABLE MODIFY COLUMN. Every subsequent query is filtered through it automatically.

6. External Tokenization

Snowflake also supports External Tokenization. Instead of masking values inline, Snowflake calls an External Function that connects to a third-party tokenization provider. The provider replaces the sensitive value with a token. Because the tokenization happens outside Snowflake, the same token can be recognized across multiple systems. This is useful for organizations that process data in several platforms and need consistent tokenized identifiers throughout.

7. What is Row-Level Security?

Row-level security controls which rows appear, not what values appear in columns. Claro has analysts in the US and EU. Both query the users table, but each should only see users from their region. Row-level security makes that happen automatically. The analyst runs a plain SELECT with no WHERE clause. Snowflake applies a row filter behind the scenes based on the analyst's role. The mechanism is called a Row Access Policy.

8. How Row Access Policies Work

When an analyst queries the users table, Snowflake passes the query through the Row Access Policy attached to that table. The policy evaluates the querying role — US_ANALYST — and applies a filter: only rows where region equals US are returned. An analyst with EU_ANALYST would run the same query and see only EU rows.

9. Creating and Applying a Row Access Policy

The Row Access Policy is a function that takes a column value and returns a boolean: true if the row should be visible, false if not. The CASE block uses IS_ROLE_IN_SESSION -- so inherited and secondary roles are checked, not just the primary one. US_ANALYST sees US rows, EU_ANALYST sees EU rows, any other role sees nothing because ELSE returns false. The policy is applied with ALTER TABLE ADD ROW ACCESS POLICY, specifying which column the policy evaluates against.

10. Using Data Masking and Row Access Policies Together

Dynamic Data Masking and Row Access Policies are independent but can both apply to the same table. Snowflake applies the Row Access Policy first, filtering out rows the role shouldn't see. Then Dynamic Data Masking applies to columns within the remaining rows. An analyst might see only their region's users, and within those rows, the credit score column is still masked.

11. Let's practice!

Now it's your turn to apply masking policies and row access policies. Let's practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.