Explain Secure Views & Row Access Policies in Databases

61 / 66

Snowflake

Secure Views & Row Access Policies

Restrict which rows and query internals users can see based on their role or attributes.

A normal view can leak the data it is meant to hide. Two mechanisms exist because of that, and they solve different halves of the problem.

A row access policy is like a security guard checking ID at each row of a filing cabinet drawer, only handing over the folders that match your clearance, while a secure view is like a one-way mirror that lets you see the result of a computation without seeing the notes on how it was calculated.

Key Concepts

Why a normal view leaks. Anyone who can query a view can read its definition with SHOW or DESCRIBE — so the filter you relied on is public, along with the table and column names behind it.

SHOWDESCRIBE

The subtler leak is through the optimiser. It may push your WHERE clause inside the view, or evaluate a function you supplied before the view's own filter. A user-supplied function that errors on certain values can then reveal which rows exist:

WHERE

    SELECT * FROM masked_view WHERE my_function(salary) = 1;
    -- if my_function runs before the view's filter, its behaviour
    -- discloses values from rows the user should never see

A secure view closes both. The definition is hidden, and the optimiser is restricted from rewrites that could expose the underlying rows.

    CREATE SECURE VIEW customer_safe AS
      SELECT id, name FROM customers WHERE region = CURRENT_REGION();

The cost is real — fewer optimisations means some secure views are slower — and it is the right trade when the view is a security boundary. Use it whenever a view exists so that someone cannot see the base table.

Row access policies do it at the table, for everyone. Rather than a view per audience, attach a policy to the table itself:

    CREATE ROW ACCESS POLICY region_policy AS (region STRING)
      RETURNS BOOLEAN ->
        EXISTS (SELECT 1 FROM role_region_map
                 WHERE role = CURRENT_ROLE() AND allowed_region = region);

    ALTER TABLE orders ADD ROW ACCESS POLICY region_policy ON (region);

Now every query against orders sees only permitted rows — through views, through joins, from any tool, no matter who wrote the query. The rule cannot be forgotten because it is not in the query.

orders

The mapping table is what makes it maintainable. The policy joins a small table of role-to-region rows. Granting a team a new region is an INSERT, not a code change and a deployment.

INSERT

    role          allowed_region
    SALES_IN      IN
    SALES_EU      EU
    SALES_GLOBAL  IN, EU, US ...

How the three fit together. Row access policies decide which rows exist for you. Masking policies decide what the visible rows show. Secure views hide how the answer was computed.

Together they enforce governance at the data, so it applies identically whether someone uses the web UI, a BI tool or a Python driver.

What the interviewer is probing.1. "What makes a view secure in the Snowflake sense?" Probing: the mechanism. Stalls: "Permissions." Moves up: the definition is hidden and the optimiser will not push predicates in ways that could leak rows the user cannot see.

2. "What does a secure view cost?" Probing: the trade. Stalls: "Nothing." Moves up: performance — some optimisations are disabled, so it is for views where data exposure is the concern.

3. "What does a row access policy give you over a view?" Probing: the difference. Stalls: "The same." Moves up: it attaches to the table, so every query is filtered regardless of which view or tool is used.

4. "How do you drive it per user?" Probing: the pattern. Stalls: "Hardcode." Moves up: the policy body reads CURRENT_ROLE or looks the user up in a mapping table.