Course contents

SnowPro Core COF-C03 Study GuidePart 3: Access control and securityRow Access Policies

Row Access Policies

Row Access Policies

In short

A row access policy is a TRUE or FALSE test run for every row. Rows where it returns FALSE don't exist for that query.

The problem

IsmaFrost's SALES table holds rows for Europe, the Americas and APAC. Alice should see only the European rows. Thomas's pipeline, working as DATA_ENGINEER, must see all of them. Amine has no regional entitlement at all. One view per region would multiply objects and invite mistakes.

The mental model

Imagine an invisible WHERE clause that Snowflake adds to every query on the table, written once by the security team. That clause is the row access policy. It requires Enterprise edition or above.

How it works

A row access policy is a schema-level object with a signature (the columns it reads) and a body that returns BOOLEAN. When you attach it to a table or view, you map its arguments to columns. At query time Snowflake applies it on every access path: direct queries, views and shares. When a table also has masking policies, Snowflake evaluates the row access policy first and then masks columns on the rows that remain. That order is documented. Figure 3.15 follows Alice's query.

A separate rule is easy to confuse with that order: the same column can't be in both a row access policy signature and a masking policy signature. A masking policy always receives the column it masks as its first argument. So REGION, which the row access policy reads, can't be masked, and can't be a condition column of a masking policy either. Snowflake states the rule without giving a reason, so learn it as a fact.

Alice's query on SALES. Her session has ANALYST and EUROPE_ANALYST active. Step 1: the row access policy keeps the European rows, 12 million of 40 million stored. Step 2: the masking policy rewrites CUSTOMER_EMAIL on those rows. Step 3: the result is 12 million rows with masked emails. Row counts are illustrative.QUERYSELECT * FROM salesAlice: ANALYST +EUROPE_ANALYSTTABLESALES40M rows stored1 · POLICYRow accesskeep region = EUROPE12M rows remain2 · POLICYMaskingCUSTOMER_EMAILmasked on those rows3Result12M rows,emails maskedsurviving rows

QUERYSELECT * FROM salesAlice: ANALYST + EUROPE_ANALYST

TABLESALES40M rows stored

1 · POLICYRow accesskeep region = EUROPE12M rows remain

surviving rows

2 · POLICYMaskingCUSTOMER_EMAILmasked on those rows

3Result12M rows, emails masked

Figure 3.15 Alice's query on SALES. The row access policy keeps the European rows, then masking rewrites the email column on those rows. Row counts are illustrative.

Worked example: entitlements as data

Setup, from the grant sheet: EUROPE_ANALYST is granted to Alice next to ANALYST, and with secondary roles ALL both are active in her session. Customer emails get their own policy, CUSTOMER_EMAIL_MASK, built like EMAIL_MASK but exempting DATA_ENGINEER: the pipeline needs clear emails to match customers. SYSADMIN inherits DATA_ENGINEER, so it sees them too; IsmaFrost accepts that for customer emails, unlike salaries.

CREATE MASKING POLICY governance_db.policies.customer_email_mask
  AS (val STRING) RETURNS STRING ->
  CASE WHEN IS_ROLE_IN_SESSION('DATA_ENGINEER') THEN val
       ELSE REGEXP_REPLACE(val, '.+@', '*****@') END;
ALTER TABLE sales_db.core.sales MODIFY COLUMN customer_email
  SET MASKING POLICY governance_db.policies.customer_email_mask;

Writing role names inside the policy body works for two regions and breaks at twenty. The production pattern keeps entitlements in a mapping table that the policy reads. Onboarding a region then becomes an INSERT that Youness can delegate and audit, instead of a policy change.

CREATE TABLE governance_db.policies.region_map (role_name STRING, region STRING);
INSERT INTO governance_db.policies.region_map VALUES
  ('EUROPE_ANALYST', 'EUROPE'),
  ('DATA_ENGINEER',  'ALL');

CREATE ROW ACCESS POLICY governance_db.policies.region_access
  AS (sales_region STRING) RETURNS BOOLEAN ->
  EXISTS (
    SELECT 1 FROM governance_db.policies.region_map m
    WHERE IS_ROLE_IN_SESSION(m.role_name)
      AND (m.region = sales_region OR m.region = 'ALL')
  );

ALTER TABLE sales_db.core.sales
  ADD ROW ACCESS POLICY governance_db.policies.region_access ON (region);

The policy body runs with the privileges of the policy's owner. Alice doesn't need, and doesn't get, SELECT on REGION_MAP.

Who queriesPolicy resultWhat comes back
Thomas · DATA_ENGINEERTRUE for every rowEvery row
Alice · ANALYST + EUROPE_ANALYSTTRUE where region = 'EUROPE'The European rows only
Amine · ANALYST only, no mapping rowFALSE for every row0 rows, and the query succeeds

Limits and traps

Look at the last row of the table. Row access policies fail silently: no error, just an empty result. That is the design (Amine can't guess what he is missing) and also a debugging trap (an empty dashboard looks like a data problem). Before blaming the pipeline, list the policies attached to the table with the table function POLICY_REFERENCES:

SELECT *
FROM TABLE(sales_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
  REF_ENTITY_NAME => 'sales_db.core.sales', REF_ENTITY_DOMAIN => 'table'));

To drop a policy, detach it first with ALTER TABLE … DROP ROW ACCESS POLICY.

Which protection does a scenario need?

?What must be restricted?
Where connections come from
Network policy

Addresses and private endpoints. Says nothing about data.

What a query returns
Keep going ↓

A governance policy or a secure view.

?Whole rows, or values inside a column?
Whole rows disappear
Row access policy

"EUROPE_ANALYST sees only European rows."

The row returns, a value is hidden
Masking policy

Dynamic masking, or external tokenization when clear values can't be stored.

✓If the requirement is to hide a view's definition or to stop a user from probing rows the view hides, the answer is a secure view (next section). For row or column restrictions, policies attach to the base table and apply on every path, so they're preferred when both are offered.
Exam takeaway
  • A row access policy is a BOOLEAN per row, attached to a table or view, Enterprise and above.
  • Row access is evaluated before masking. The same column can't be in both a row access and a masking policy signature.
  • Sessions without entitlement get an empty result, not an error.