Everyone Queries the Same Table and Sees a Different Answer

Dynamic data masking, row access policies, and tag-based governance — the Enterprise features that decide which rows and which column values a role actually sees, long after RBAC has said yes.

Back in chapter 07 I gave you a role model and told you it was the whole access story. It isn’t, and I owe you the rest of it.

Roles and grants are a binary switch per object. Dana’s analyst role either holds SELECT on sales.public.customer or it doesn’t. If it does, she sees every row and every column — every name, every account balance, every region. If it doesn’t, she sees a “does not exist or not authorized” and files a ticket. There is no third state. And the third state is the one real organizations spend most of their governance budget on: she should see the table, but not all of it.

The support agent needs the customer’s name to answer the phone but has no business seeing their account balance. The European analyst should see Europe’s orders and not Asia’s — not because Asia is secret, but because giving her Asia’s numbers means her regional revenue report is quietly wrong. The contractor gets the shape of the data and nothing that would identify a human. Every one of those is a within-object rule, and every one of them is invisible to GRANT SELECT.

The previous chapter was about who can reach your data across an account boundary. This one is about what they actually see once they’ve reached it. Snowflake’s answer is policies: small, named SQL objects that attach to a column or a table and get evaluated at query time, rewriting what comes back based on who’s asking. Two kinds do most of the work — masking policies change what a column’s values look like, row access policies change which rows exist at all — and a third, object tagging, is what keeps you from hand-attaching those policies until you die.

Fair warning up front: all of this is Enterprise Edition and above. It’s one of the more defensible reasons to be on Enterprise, and it’s been sitting in the edition-comparison table since chapter 01 as a single bullet with no chapter behind it. This is that chapter.

Where RBAC runs out

Hold the two mechanisms side by side; the division of labour is clean and worth memorizing.

  • Grants answer “can this role touch this object?” Checked once, at compile time, against the object. Yes or no. Coarse by design — which is what makes them fast and auditable.
  • Policies answer “what does this object look like to this role?” SQL expressions Snowflake splices into your query, evaluated per query, per row, per value. The answer is a transformed value or a boolean.

The consequence that catches people: a policy is not a filter you can forget to apply. A view is opt-in — you have to remember to point people at customer_safe instead of customer, and the day someone grants SELECT on the base table directly, your careful view becomes decorative. A policy attaches to the column (or the table), and Snowflake rewrites every query that touches it: projections, WHERE predicates, join keys, GROUP BY, views built on top, a SELECT * typed by someone who never read your governance doc. There is no path to the column that dodges it. That property — unbypassable by construction — is the entire reason policies exist rather than “just use views.”

Let’s build both against the tables you already have.

Setting up: a role that writes rules but can’t read data

One piece of structure first, because it’s the part teams skip and then can’t undo: the person who writes the access rules must not be the person who benefits from them. So — a policy_admin role to own the policies, plus a support functional role to demonstrate against. Slot them into the hierarchy exactly the way chapter 07 taught.

USE ROLE USERADMIN;
CREATE ROLE policy_admin;
CREATE ROLE support;

USE ROLE SECURITYADMIN;
-- support is a functional role: it gets the read access role, and a warehouse
GRANT ROLE sales_read               TO ROLE support;
GRANT USAGE ON WAREHOUSE compute_wh TO ROLE support;

-- both roll up into SYSADMIN, like every other functional role
GRANT ROLE support      TO ROLE SYSADMIN;
GRANT ROLE policy_admin TO ROLE SYSADMIN;

-- Sam picks up the support hat alongside loader; Dana stays an analyst
GRANT ROLE support TO USER sam;

-- policy_admin needs a home for its objects, and nothing else
GRANT USAGE, CREATE SCHEMA ON DATABASE sales TO ROLE policy_admin;

Note what policy_admin is not granted: sales_read. It cannot SELECT from sales.public.customer. It writes the rules about a table it can’t read — which will feel wrong the first time, until you realize it’s the same reason your auditors don’t also sign the cheques.

Now give it the account-level privileges that let it attach policies to objects it doesn’t own. These are global, granted by ACCOUNTADMIN, and they’re the centralized-governance model Snowflake documents: one role sets policies everywhere.

USE ROLE ACCOUNTADMIN;
GRANT APPLY MASKING POLICY    ON ACCOUNT TO ROLE policy_admin;
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE policy_admin;
GRANT APPLY TAG               ON ACCOUNT TO ROLE policy_admin;

And a schema for the policies to live in, created by the role that should own it:

USE ROLE policy_admin;
CREATE SCHEMA IF NOT EXISTS sales.security;

Policies in their own schema — next to the data they protect, but not inside it. That matters later for two reasons: the mapping tables live here too and must not be readable by the people the policies constrain, and Snowflake’s docs want policy mapping tables in the same database as the protected tables, for performance.

Dynamic data masking: the rule lives on the column

A masking policy is a named function with a very specific shape: it takes the column’s value (and optionally other columns), returns a value of the same type, and Snowflake substitutes its result wherever that column appears.

USE ROLE policy_admin;

CREATE OR REPLACE MASKING POLICY sales.security.mask_customer_name AS
  (val VARCHAR) RETURNS VARCHAR ->
    CASE
      WHEN IS_ROLE_IN_SESSION('LOADER')  THEN val   -- the pipeline needs the real thing
      WHEN IS_ROLE_IN_SESSION('SUPPORT') THEN val   -- so does the human on the phone
      ELSE '*** REDACTED ***'
    END;

Read the syntax carefully, because it is not quite anything else in SQL. AS (val VARCHAR) declares the argument — this is the column’s value. RETURNS VARCHAR -> is the arrow that introduces the body, and the body is a single SQL expression, not a procedural block. Whatever that expression evaluates to is what the caller gets.

The rule that trips everyone once: the return type must equal the input type. You cannot take a NUMBER and return '***MASKED***', because that’s a VARCHAR, and Snowflake will refuse the policy outright. A masked number has to still be a number. That constraint is annoying for about ten minutes and then becomes clarifying — it forces you to decide what a degraded but still typed version of the value is, which is usually a better answer than redaction anyway:

CREATE OR REPLACE MASKING POLICY sales.security.mask_acctbal AS
  (val NUMBER(12,2)) RETURNS NUMBER(12,2) ->
    CASE
      WHEN IS_ROLE_IN_SESSION('LOADER')  THEN val
      WHEN IS_ROLE_IN_SESSION('ANALYST') THEN ROUND(val, -3)  -- nearest thousand
      ELSE NULL
    END;

Three tiers in six lines. The loader sees the real balance. An analyst sees it rounded to the nearest thousand — enough to bucket customers, not enough to identify one. Everyone else gets NULL. And note that the analyst’s version is a transformation, not a redaction: masking is a function, so “hash it,” “keep the last four digits,” “round it,” and “return the string '***'” are all equally valid bodies. Redaction is the boring case, not the only one.

(val is a name I chose, not a keyword. Snowflake doesn’t care what you call the argument — only that the first one is the column being masked.)

Attach it to the column

The policy is inert until you bind it. Binding is an ALTER TABLE, and the column is where it lands:

USE ROLE policy_admin;

ALTER TABLE sales.public.customer
  MODIFY COLUMN c_name SET MASKING POLICY sales.security.mask_customer_name;

ALTER TABLE sales.public.customer
  MODIFY COLUMN c_acctbal SET MASKING POLICY sales.security.mask_acctbal;

policy_admin doesn’t own sales.public.customer — SYSADMIN does, per chapter 07 — and it can still do this, because of that global APPLY MASKING POLICY ON ACCOUNT grant. That’s the whole point of the privilege: policy administration is orthogonal to object ownership.

The reverse move, when you need it, is UNSET, and it takes no policy name because a column can only carry one:

ALTER TABLE sales.public.customer MODIFY COLUMN c_name UNSET MASKING POLICY;

Become the other user and look

Chapter 07’s best habit was “test access by being the other role.” It pays off double here, because a masking policy has no error message — it doesn’t fail, it just quietly returns different data, which means the only way to know it works is to look at what comes back.

USE ROLE analyst;                 -- Dana's role
SELECT c_custkey, c_name, c_mktsegment, c_acctbal
FROM sales.public.customer
ORDER BY c_custkey
LIMIT 3;
C_CUSTKEY | C_NAME            | C_MKTSEGMENT | C_ACCTBAL
----------+-------------------+--------------+----------
        1 | *** REDACTED ***  | BUILDING     |    712.00
        2 | *** REDACTED ***  | AUTOMOBILE   |    122.00
        3 | *** REDACTED ***  | AUTOMOBILE   |   7499.00

Now the same query as support:

USE ROLE support;                 -- Sam's other hat
SELECT c_custkey, c_name, c_mktsegment, c_acctbal
FROM sales.public.customer
ORDER BY c_custkey
LIMIT 3;
C_CUSTKEY | C_NAME             | C_MKTSEGMENT | C_ACCTBAL
----------+--------------------+--------------+----------
        1 | Customer#000000001 | BUILDING     |      NULL
        2 | Customer#000000002 | AUTOMOBILE   |      NULL
        3 | Customer#000000003 | AUTOMOBILE   |      NULL

Two roles, one table, one query text, two different truths. Support gets the name and not the money; the analyst gets (rounded) money and not the name. Nobody had to be pointed at a different object.

One more experiment, and it’s the one that makes people believe: run it as ACCOUNTADMIN. You’ll get *** REDACTED *** too — because ACCOUNTADMIN isn’t in the policy’s CASE, and a policy doesn’t care how senior you are. That is either exactly what you wanted or a nasty surprise at 2am, so decide deliberately whether your break-glass role belongs in the body.

Why the column, and not a view

Here’s the property I promised. Because the policy is bound to c_name and Snowflake rewrites the column wherever it appears, there is no query shape that gets around it:

USE ROLE analyst;

SELECT * FROM sales.public.customer LIMIT 1;         -- masked
CREATE VIEW my_own_view AS SELECT * FROM sales.public.customer;
SELECT * FROM my_own_view LIMIT 1;                   -- still masked

A view built on a masked column inherits the mask. So does a view built on that view. So does a CTE, a subquery, a join. The mask goes with the column, not with the object you happened to name in the FROM clause — which is exactly the guarantee a secure view can’t give you, because a secure view protects itself, and anyone with a grant on the base table walks straight past it.

But “rewritten everywhere” cuts both ways, and this is the sharp edge:

USE ROLE analyst;
SELECT COUNT(*) FROM sales.public.customer
WHERE c_name = 'Customer#000000042';
-- 0

Zero. Not because the customer doesn’t exist, but because the predicate is evaluated against the masked value — Snowflake rewrote c_name in the WHERE clause too, so it compared '*** REDACTED ***' to your literal and found nothing. The same applies to a join on a masked key, and to GROUP BY c_name (which will collapse every customer into one glorious *** REDACTED *** bucket).

This is correct behaviour — if the predicate saw the real value, a determined analyst could binary-search the column and reconstruct it, which is exactly the leak masking exists to close. But it means a masked column is not a usable join key or filter for the roles that see it masked. Don’t mask an identifier people need to join on; mask the payload, keep the surrogate key clean, and if you must give a masked audience a joinable handle, mask to a deterministic hash rather than to a constant so equal values still compare equal.

Conditional masking: mask based on another column

Sometimes the rule isn’t “who’s asking” but “what row is this.” Snowflake calls this conditional masking, and it works by giving the policy extra arguments — additional columns from the same row, which the body can inspect.

Say order totals are commercially sensitive while an order is still open, but a closed order’s total is just history. TPCH’s o_orderstatus gives us exactly that: 'O' open, 'F' fulfilled, 'P' partial.

CREATE OR REPLACE MASKING POLICY sales.security.mask_open_order_price AS
  (val NUMBER(12,2), status VARCHAR(1)) RETURNS NUMBER(12,2) ->
    CASE
      WHEN IS_ROLE_IN_SESSION('LOADER') THEN val   -- pipeline sees everything
      WHEN status = 'F'                 THEN val   -- closed: it's history
      ELSE NULL                                    -- open: not your business
    END;

The first argument is still the column being masked. The second is a condition column, and you tell Snowflake which one it is at bind time, with a USING clause:

ALTER TABLE sales.public.orders
  MODIFY COLUMN o_totalprice
  SET MASKING POLICY sales.security.mask_open_order_price
  USING (o_totalprice, o_orderstatus);

The USING list maps the policy’s arguments to actual columns, positionally, and the first entry must be the column you’re setting the policy on. Omit USING and Snowflake treats the policy as an ordinary single-argument one — which for a two-argument policy is an error, not a silent fallback, so you’ll find out. The condition columns are matched by type, which is why the signature says VARCHAR(1): that’s exactly how o_orderstatus was declared in chapter 07, and keeping signatures identical to the column DDL is the cheap way to never debug a type mismatch at query time.

The condition column needn’t be a flag. It can be a classification code, a customer’s consent boolean, a data-residency marker — anything the row itself carries. This is how you get “mask the email unless the customer opted in,” which you cannot express with roles at all.

(One wrinkle worth knowing exists: if the condition column is itself masked, the body sees the masked version — rarely what you want. That’s what EXEMPT_OTHER_POLICIES = TRUE on CREATE MASKING POLICY is for. You’ll meet it once, and now you’ll know its name.)

CURRENT_ROLE() versus IS_ROLE_IN_SESSION()

You’ll see both in the wild, and they are not interchangeable.

CURRENT_ROLE() returns the name of the session’s primary role — one string. So WHEN CURRENT_ROLE() = 'SUPPORT' is true only if support is literally the active role.

IS_ROLE_IN_SESSION('SUPPORT') returns a boolean and is true if support is the primary role, or a secondary role, or anywhere in the inherited hierarchy of either. Remember chapter 07’s role tree: support rolls up into SYSADMIN, and SYSADMIN into ACCOUNTADMIN. With IS_ROLE_IN_SESSION, a SYSADMIN inherits the exemption; with CURRENT_ROLE(), they don’t.

Ask the question out loud and the choice makes itself. “Does this session’s identity include the support entitlement?” → IS_ROLE_IN_SESSION: it composes with the role hierarchy the way the rest of Snowflake does, and it survives someone turning on USE SECONDARY ROLES ALL (which, per chapter 07, BI tools love to do — and which silently defeats a CURRENT_ROLE() check by leaving the primary role as something innocuous). “Is the session acting as this exact role right now?” → CURRENT_ROLE(): stricter, more brittle, and occasionally exactly what a compliance requirement demands.

Default to IS_ROLE_IN_SESSION; reach for CURRENT_ROLE() when you can articulate why the hierarchy shouldn’t apply. And mind the case: role names are stored upper-cased (chapter 07’s case-folding lesson), so the literal must be 'SUPPORT', not 'support'. A lowercase literal is a policy that silently never matches — which fails closed, and therefore looks like it’s working.

Row access policies: which rows exist for you

Masking changes values. A row access policy changes the population. It’s a function that takes one or more of the table’s columns and returns a boolean: true means this row is visible to you, false means — as far as your query is concerned — it does not exist. Not “access denied.” Does not exist.

The signature is the same arrow shape:

USE ROLE policy_admin;

CREATE OR REPLACE ROW ACCESS POLICY sales.security.orders_region_v1 AS
  (order_region VARCHAR) RETURNS BOOLEAN ->
    CASE
      WHEN IS_ROLE_IN_SESSION('LOADER')  THEN TRUE
      WHEN IS_ROLE_IN_SESSION('ANALYST') THEN order_region = 'EUROPE'
      ELSE FALSE
    END;

That works. It is also the version 90% of tutorials stop at, and it’s a trap. Look at what it costs: every new role, every new region, every reorg is an edit to a policy body — a code change to a security object, reviewed and deployed, because someone joined the Asia team. Multiply by forty roles and five regions and you have a CASE statement nobody dares touch and everybody routes around. The policy becomes the bottleneck it was supposed to remove.

The mapping table, which is the version that scales

The scalable form puts the entitlements in data and lets the policy body join to them. It’s the pattern Snowflake’s own docs lead with, and the only one where “give Priya access to Asia” is an INSERT rather than a deployment.

Build the mapping table in the security schema — where analyst has no grants:

USE ROLE policy_admin;

CREATE OR REPLACE TABLE sales.security.role_entitlements (
  role_name STRING,   -- an account role, as Snowflake stores it: UPPER CASE
  region    STRING    -- AFRICA | AMERICA | ASIA | EUROPE | MIDDLE EAST
);

INSERT INTO sales.security.role_entitlements VALUES
  ('ANALYST', 'EUROPE'),
  ('ANALYST', 'AMERICA'),
  ('SUPPORT', 'ASIA'),
  ('SUPPORT', 'MIDDLE EAST'),
  ('SUPPORT', 'AFRICA');

Those are the real region values sitting in orders.region, put there by chapter 07’s TPCH load — AFRICA, AMERICA, ASIA, EUROPE, MIDDLE EAST. Not EMEA, not AMER. Entitlement tables that don’t match the data they entitle are the single most common way this pattern fails, and it fails closed and silently: the join matches nothing, everyone sees zero rows, and you spend a morning convinced the policy engine is broken.

Now the policy:

CREATE OR REPLACE ROW ACCESS POLICY sales.security.orders_by_region AS
  (order_region VARCHAR) RETURNS BOOLEAN ->
    IS_ROLE_IN_SESSION('LOADER')          -- the pipeline sees everything
    OR EXISTS (
         SELECT 1
         FROM sales.security.role_entitlements e
         WHERE IS_ROLE_IN_SESSION(e.role_name)
           AND e.region = order_region
       );

Three details in that body earn their keep.

EXISTS, not IN or a join. Snowflake’s guidance for subqueries in policy bodies is to put them in an EXISTS inside the branch of a CASE or a boolean OR — which is also the shape the optimizer handles best. Write it any other way and you’re fighting the engine.

IS_ROLE_IN_SESSION(e.role_name) takes a column, not a literal. That’s the whole trick: the function evaluates once per candidate entitlement row, and any row whose role is anywhere in your session’s hierarchy matches. So a single mapping table serves every role, and role inheritance still works — grant analyst to a new senior_analyst role and it inherits EUROPE and AMERICA without a single row being inserted.

The argument is named order_region, not region. This one is a genuine footgun and I want you to feel it. If you name the policy argument region, then inside the correlated subquery, the unqualified region in WHERE e.region = region is ambiguous — and SQL’s scoping rules resolve inner names before outer ones, so it can bind to e.region and give you e.region = e.region, which is always true, which means every role sees every row and the policy looks like it’s working because nobody gets an error. A security control that fails open is worse than no security control, because you’ll trust it. Name policy arguments distinctly from every column in every table they might join to, and qualify everything you can.

Attach it, then go be someone else

ALTER TABLE sales.public.orders
  ADD ROW ACCESS POLICY sales.security.orders_by_region ON (region);

The ON (region) clause maps the table’s columns to the policy’s arguments, positionally — same idea as USING for conditional masking. And now the test, which is the same “become the other user” move:

USE ROLE analyst;
SELECT region, COUNT(*) AS orders
FROM sales.public.orders
GROUP BY region ORDER BY region;
REGION  | ORDERS
--------+--------
AMERICA |  46 122
EUROPE  |  46 508
USE ROLE support;
SELECT region, COUNT(*) AS orders
FROM sales.public.orders
GROUP BY region ORDER BY region;
REGION      | ORDERS
------------+--------
AFRICA      |  45 989
ASIA        |  46 231
MIDDLE EAST |  46 070

(Exact counts depend on your TPCH slice; the shape is the point.) Neither role got an error. Neither role can tell the other regions exist. And loader — Sam’s pipeline role — still sees all five, because the policy’s first clause says so, which is what keeps your ELT from silently loading against a filtered view of its own target table. That last point deserves a beat: a row access policy applies to writes too. An UPDATE or DELETE can only touch rows the policy lets you see. Forget to exempt the pipeline role and your nightly merge starts quietly no-op-ing on four fifths of the table.

To remove it:

ALTER TABLE sales.public.orders
  DROP ROW ACCESS POLICY sales.security.orders_by_region;

Whose privileges evaluate the body?

Here’s the question you should be asking: analyst has no grant on sales.security.role_entitlements. How does a query run by analyst read it?

It doesn’t. The policy body is evaluated with the policy owner’s rights, not the caller’s — Snowflake wraps the protected table in what its docs call a dynamic secure view and runs the policy expression as the policy’s owner. policy_admin owns both the policy and the mapping table, so the join resolves; Dana never touches the table herself. Prove it:

USE ROLE analyst;
SELECT * FROM sales.security.role_entitlements;
-- SQL compilation error: Object 'ROLE_ENTITLEMENTS' does not exist or not authorized.

She’s constrained by a table she cannot read, cannot enumerate, and cannot even confirm exists. That’s the property that makes the mapping-table pattern safe and not just convenient — the entitlement data is as protected as the data it protects.

The rules the engine enforces

Four constraints, all of which you will hit:

  • One row access policy per table. Not one per column — one per table. If you need two conditions, they go in one policy body, AND-ed together. (Views can stack: a policy on a view over a policied table gives you two, evaluated in order.)
  • A column can be in a row access policy signature or a masking policy signature — not both. orders.region is now spoken for by orders_by_region; you cannot also mask it. Design the two together, because discovering this at bind time means rethinking your column layout.
  • Materialized views can’t be built over a policied table, and won’t be. If a dashboard depends on an MV over a table you’re about to protect, that dependency breaks. (Chapter 12’s decision guide points you at a dynamic table instead, which is the right answer anyway.)
  • The policy is evaluated on every query, which is a cost, which is the honest-limits section below.

Tag-based masking: the part that survives a real warehouse

Everything so far attaches a policy to one named column. Now count the columns in a real warehouse: forty tables, six with an email, a dozen with a name somewhere, plus every staging copy, every mart, and every table someone creates next Tuesday. Hand-attaching a masking policy to each of those, forever, is not a governance program. It’s a full-time job with no end state — and it fails the first Friday somebody ships a new table.

Object tagging is the fix, and it’s what turns masking from a demo into a system. A tag is a named key-value label you can stick on almost any object — a column, a table, a schema, a warehouse. Chapter 07 mentioned them for cost attribution (SET TAG cost_center = 'sales'), which is real but isn’t the interesting use. The interesting use is this: you can attach a masking policy to a tag. Every column that carries that tag — now or ever — inherits the policy automatically. You stop protecting columns. You start classifying them, and protection falls out.

USE ROLE policy_admin;

-- 1. a classification vocabulary, with the values constrained so nobody
--    invents 'PII?' or 'pii-maybe' six months from now
CREATE OR REPLACE TAG sales.security.pii
  ALLOWED_VALUES 'name', 'email', 'phone', 'address'
  COMMENT = 'Personally identifiable information';

-- 2. one generic policy per data type
CREATE OR REPLACE MASKING POLICY sales.security.mask_pii_string AS
  (val VARCHAR) RETURNS VARCHAR ->
    CASE
      WHEN IS_ROLE_IN_SESSION('LOADER')  THEN val
      WHEN IS_ROLE_IN_SESSION('SUPPORT') THEN val
      ELSE '*** REDACTED ***'
    END;

-- 3. bind the policy to the tag — this is the whole idea
ALTER TAG sales.security.pii SET MASKING POLICY sales.security.mask_pii_string;

That third statement is the load-bearing one. From here on, protecting a column is an act of labelling:

-- the column already has a direct policy from earlier; drop it first,
-- because a directly-assigned policy always beats a tag-based one
ALTER TABLE sales.public.customer MODIFY COLUMN c_name UNSET MASKING POLICY;

-- now just say what the column *is*
ALTER TABLE sales.public.customer
  MODIFY COLUMN c_name SET TAG sales.security.pii = 'name';

Re-run Dana’s query and c_name is redacted again — same result, but nobody wrote a policy binding. The column is masked because it is tagged as a name, and names are masked. Tag the email column on the next table you build and it’s protected the instant it’s tagged: no security review, no deployment, nobody remembering to do anything.

That precedence rule in the comment is worth stating plainly, because you’ll debug it eventually: if a column has both a direct masking policy and a tag whose policy would apply, the direct policy wins. Silently. A column that “isn’t getting the tag policy” is nearly always a column someone hand-attached a policy to two years ago.

The other rule: one masking policy per data type per tag. A single pii tag can carry a VARCHAR policy and a NUMBER policy and a DATE policy — but not two VARCHAR policies. The tag decides what kind of thing this is; the data type decides which mask applies. Your policy count then grows with the number of data types you protect, not the number of columns you protect.

Pushing the tag up the hierarchy — carefully

Tags inherit downward. Set the tag on a table and it lands on every column; set it on a schema or database and it lands on every column in every table there, including tables created later. Snowflake’s docs call this out as the main benefit of tagging at the schema level: new tables are automatically protected on arrival.

-- every VARCHAR column in this schema, now and forever, gets the PII mask
ALTER SCHEMA sales.public SET TAG sales.security.pii = 'name';

Now put the brakes on, because that statement is a hand grenade and I’m not going to pretend otherwise. It would mask every VARCHAR column in sales.public — including c_mktsegment (not PII, and now useless), and including orders.region, which is already the signature column of your row access policy. Recall the rule: a column cannot be in both a masking and a row access policy signature. The blanket tag walks straight into that wall.

Schema- and database-level tagging is for schemas where the blanket assumption is right — a raw landing zone of customer records, a restricted PII vault. For a general-purpose analytics schema, tag columns, and make “tag it on creation” part of your table-creation checklist. The lever is powerful; it is not subtle. Know which one you need.

Who is allowed to write the rules

The privileges here are the whole segregation-of-duties story, so let’s be precise about them:

  • CREATE MASKING POLICY / CREATE ROW ACCESS POLICY / CREATE TAG on a schema — who can author policy objects. policy_admin owns sales.security, and a schema owner can create objects in it, which is why we didn’t grant these explicitly.

  • APPLY MASKING POLICY / APPLY ROW ACCESS POLICY / APPLY TAG on the account — the global, centralized version: this role can bind policies to any object in the account, regardless of ownership. That’s what we granted policy_admin, and it’s the model you want when a small security team governs a large warehouse.

  • APPLY on a specific policy — the decentralized version. You keep policy authorship central but let a data steward attach a blessed policy to their own tables:

    USE ROLE policy_admin;
GRANT APPLY ON MASKING POLICY sales.security.mask_pii_string TO ROLE loader;

    Now loader can set that policy on columns of tables it owns — and cannot write a new, more permissive policy, and cannot remove policies from tables it doesn’t own. It’s the right shape for “the pipeline team tags its own new columns.”

The line that must not blur: the role that can ALTER MASKING POLICY must not be a role that benefits from a weaker policy. If your analytics lead owns the policies and holds analyst, they can grant themselves the world in one statement, with no approval and no reviewer. Put policy authorship in a role held by people who don’t query the data, audit changes to it (ALTER MASKING POLICY shows up in QUERY_HISTORY), and treat the bodies as code — reviewed, versioned, deployed. They are code. They just happen to live in the catalog.

Policies versus secure views

The last chapter solved a version of this problem differently, and you should know when to reach for which.

Chapter 15’s pattern was a secure view filtering on CURRENT_ACCOUNT() against an entitlements table — a different object (orders_shared), granted to a share, with the base table never exposed. This chapter’s pattern is a policy on the base table itself — same object name for everyone, different results.

They’re not competitors so much as tools for different boundaries:

Secure viewPolicy
Where the rule livesIn a new object you must point people atOn the existing column or table
Can it be bypassed?Yes — anyone with a grant on the base table walks around itNo — the rewrite follows the column everywhere
Audience scalingOne view per audience shape (view sprawl)One policy, N roles in a mapping table
EditionAny, including StandardEnterprise+
Cross-account (sharing)The natural fit — CURRENT_ACCOUNT() worksWorks, with a catch (below)

The decision rule I’d give you: inside your account, use policies — they don’t sprawl, they can’t be routed around, and the mapping table makes entitlement an INSERT. Across an account boundary, use a secure view — you need an object to grant to the share anyway, and CURRENT_ACCOUNT() is the only identity the consumer reliably has.

What happens in a share

Which brings us to the catch, and it’s the kind that produces a support ticket at the worst possible moment. A masking or row access policy on a shared table travels with the share — the consumer gets the protected column, protected. Good. But the policy body executes in the consumer’s account, and CURRENT_ROLE() and CURRENT_USER() return NULL there. The consumer’s roles are not your roles; there’s no ANALYST in their account for IS_ROLE_IN_SESSION to find.

So a policy written entirely in terms of roles, when shared, evaluates its ELSE branch for everyone on the far side. If your ELSE is '*** REDACTED ***', congratulations, you fail closed and the consumer sees nothing useful. If your ELSE is val — if you wrote the policy as “mask for these roles, otherwise show” — you have just shipped your PII to another company.

Write policy bodies to fail closed. The default branch should be the most restrictive outcome, and role checks should be the exceptions that open it up. Every policy in this chapter is written that way; go back and look. And when the data is destined for a share, key the policy on CURRENT_ACCOUNT() — exactly as chapter 15’s secure view did — or use database roles inside the share with IS_DATABASE_ROLE_IN_SESSION, which is the sharing-native version of the function.

Auditing: which policies exist, and where are they attached?

Six months from now, “is the email column masked?” needs to be a query, not an archaeology project. Three surfaces answer it, and they’re the same live-versus-historical split you’ve seen all book.

Live inventory — what policy objects exist, and what’s in them:

SHOW MASKING POLICIES IN SCHEMA sales.security;
SHOW ROW ACCESS POLICIES IN SCHEMA sales.security;
SHOW TAGS IN SCHEMA sales.security;

DESCRIBE MASKING POLICY sales.security.mask_pii_string;   -- signature + body

Live attachment — the question that actually matters, answered by the POLICY_REFERENCES table function in INFORMATION_SCHEMA. It runs in both directions. Forward, where is this policy attached?:

SELECT *
FROM TABLE(sales.information_schema.policy_references(
        policy_name => 'sales.security.mask_pii_string'));

And backward — the one to bookmark — what is protecting this table?:

SELECT *
FROM TABLE(sales.information_schema.policy_references(
        ref_entity_name   => 'sales.public.customer',
        ref_entity_domain => 'table'));

That second call is the answer to “why is this column returning stars,” and it’s the first thing to run when a number doesn’t match.

Account-wide and historical — SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES, which is the whole account’s policy attachment map as a queryable table. Same trade-off as every Account Usage view (chapter 20): it’s SQL you can join, trend, and diff, at the price of latency — up to two hours here.

SELECT policy_kind,
       policy_name,
       ref_database_name || '.' || ref_schema_name || '.' || ref_entity_name AS object,
       ref_entity_domain,
       ref_column_name,
       tag_name,                 -- non-null when the policy arrived via a tag
       policy_status
FROM snowflake.account_usage.policy_references
ORDER BY policy_kind, policy_name;

The tag_name column is the one that makes tag-based masking auditable: it tells you whether a column is protected directly or because it’s tagged, which is precisely the distinction that will otherwise cost you an afternoon. Pair this with ACCESS_HISTORY from chapter 20 — which records the actual columns each query touched — and you can answer the two questions a compliance review always asks: what protects this column, and who has read it.

The honest limits

I’ve sold you this hard. Now the bill.

It’s Enterprise, and there’s no partial credit. Masking policies, row access policies, tag-based masking, and object tagging are all Enterprise Edition or above. On Standard there is no equivalent — the fallback is secure views plus per-audience grants, which sprawl and which anyone with a base-table grant walks around. If column- and row-level security is a requirement, it’s an edition decision, and it belongs in the cost conversation from chapter 14, not the architecture conversation.

Policies cost query time. Every query against a policied table carries the policy expression. A CASE on IS_ROLE_IN_SESSION is cheap. A row access policy with an EXISTS against a mapping table is a correlated subquery evaluated on every query against that table — and because Snowflake implements it as a dynamic secure view, you inherit the secure-view optimizer restrictions from chapter 12: less aggressive pushdown, less pruning. Keep mapping tables small, keep them in the same database as the protected table, and expect a real (if usually modest) regression on your hottest tables. Measure it; don’t assume it’s free.

Masking is access control, not encryption. The plaintext is still sitting in the micro-partitions; a policy only decides who Snowflake shows it to. The ways around it are more mundane than you’d like: a role you forgot to exclude, a clone, an unload to a stage — or a CREATE TABLE AS SELECT run by an unmasked role, which produces an unprotected copy in another schema, because the mask applies to the read and the policy does not follow the data into the new table. If the requirement is “even an ACCOUNTADMIN cannot read this,” you need encryption or external tokenization, not a CASE statement. Know which requirement you’re actually meeting.

And the one that will actually bite you: policies make numbers disagree, silently. This is the footgun, and it’s a governance problem wearing a data problem’s clothes.

Dana runs SELECT SUM(o_totalprice) FROM sales.public.orders and gets one number. Sam runs the identical statement and gets a bigger one. Neither query errored. Neither of them did anything wrong. The row access policy filtered Dana to two regions and Sam to three, and the aggregate faithfully summed what each of them could see.

Now imagine that number is in a board deck.

The failure mode isn’t the policy — the policy did its job. The failure mode is that a filtered aggregate looks exactly like an unfiltered one. There’s no asterisk, no warning, no row count that screams. And the same applies to masking: my mask_acctbal policy rounds balances to the nearest thousand for analysts, so AVG(c_acctbal) is wrong for Dana and right for Sam, and the NULL branch is worse, because AVG silently skips nulls and gives her a mean over the rows she happened to be allowed to see.

The mitigations are cultural more than technical, and you need all three:

  1. Aggregates cross a governance boundary — so label them. Any report built on a policied table should say which entitlement produced it. “Revenue (EUROPE, AMERICA)” is a chart title. “Revenue” is a lie waiting to be discovered.
  2. Give shared numbers an unpolicied path. Compute the company-wide aggregates with a role the policy exempts — a dedicated reporting role, a dynamic table refreshed by it — and let everyone read that. Everyone agrees on the number because everyone reads the same precomputed row, not because everyone can see the same rows.
  3. Make “why doesn’t my number match yours” a policy question first. Before anyone hunts a data bug, run POLICY_REFERENCES on the table and check whether the two of you are even looking at the same population. Nine times in ten that’s the answer, and it takes thirty seconds.

That reframing is the real cost of fine-grained access control, and nobody puts it on the feature comparison chart. The moment a role can see a different slice, your warehouse no longer has a single answer to a question — it has an answer per entitlement. That is correct, and it is what you asked for. It is also something your organization has to learn to talk about.

Final thoughts

Roles say whether. Policies say what. That’s the sentence to keep.

The mechanics are small enough to hold in your head. A masking policy is a same-type-in, same-type-out function bound to a column and rewritten into every query that touches it, so no view, no SELECT *, and no clever predicate gets around it. A row access policy is a boolean function bound to a table, and its scalable form is never a CASE full of role names — it’s an EXISTS against a mapping table the constrained roles cannot even see, evaluated with the policy owner’s rights. Tags are what stop you hand-attaching policies until you retire: classify the column once, and the policy that protects that classification applies itself — to every column you’ve tagged, and every one you tag next year.

Write the bodies to fail closed — restrictive default, role checks as the exceptions that open it — because that’s the difference between a policy that leaks nothing when it meets a case you didn’t anticipate and one that leaks everything. Put authorship in a role that doesn’t read the data. And test every policy the way chapter 07 taught you to test a grant: USE ROLE, re-run the query, and look at what comes back — because a policy never tells you it’s working. It just quietly hands each person a different world.

So the last thing to internalize isn’t syntax at all. Once you’ve shipped your first row access policy, “why doesn’t my number match yours” stops being a bug report and becomes a perfectly good question with a perfectly good answer — and the team that learns to ask it of the policy before they ask it of the data is the team that gets to keep both.

Next: Programmability: UDFs, Procedures, Streams, Tasks, and Snowpark

Comments