Course contents

SnowPro Core COF-C03 Study GuidePart 3: Access control and securitySecure Views and Governance Odds & Ends

Secure Views and Governance Odds & Ends

Secure Views and Governance Odds & Ends

In short

A secure view hides its own SQL and stops the optimizer from leaking rows the view filters out. Policies protect the data, a secure view protects the logic.

The problem

A regular view hides its base tables behind a name, but two things still leak. Anyone with access can read its definition with GET_DDL or SHOW VIEWS, including the filter logic and the table names. And the optimizer, the part of Snowflake that decides in which order to run the steps of a query, is free to reorder the user's WHERE conditions (also called predicates) and the view's own filter. A curious user can exploit that to learn about rows the view excludes.

How it works: the probe a secure view blocks

Youness gives ANALYST SELECT on the view TEAM_DIRECTORY, not on the EMPLOYEES table behind it. The view shows the employees of the reader's own department, which for Alice is BI. Suppose it were a standard view. Alice adds a condition built to fail on one value: WHERE 1/IFF(city = 'Lisbon', 0, 1) = 1. For a Lisbon row, IFF returns 0 and the division fails. If the optimizer tests her condition before the view's department filter, it also runs on other departments' rows. A "Division by zero" error then tells her that someone in Lisbon exists, although she can't see that row. A user-defined function (UDF, a function she writes herself) can run the same kind of test on anything. With a secure view, Snowflake applies the view's filter first, so her condition only ever sees rows she is entitled to. The definition is also hidden from her. Figure 3.16 puts the two orders side by side.

Why a secure view filters first. The view TEAM_DIRECTORY shows employees of Alice's department, BI. Alice adds the condition 1/IFF(city = 'Lisbon', 0, 1) = 1. With a standard view the optimizer may test her condition on all four stored rows before the view's filter; the hidden Lisbon row causes a division-by-zero error, which tells her a hidden Lisbon employee exists. With a secure view the department filter runs first, her condition only sees the two BI rows, and the query returns them with no error.STANDARD VIEW · ORDER CHOSEN BY THE OPTIMIZERSECURE VIEW · VIEW FILTER ALWAYS FIRST4 STORED ROWSEMPLOYEESAna · BI · ParisLéo · BI · ParisRui · Finance · LisbonEva · HR · LyonSTEP 1Alice’s conditionon all 4 rows: Lisbonrow divides by zeroSTEP 2 · NEVER REACHEDView filterdepartment = BILEAKError"Division by zero"so a hidden Lisbonemployee exists4 STORED ROWSEMPLOYEESAna · BI · ParisLéo · BI · ParisRui · Finance · LisbonEva · HR · LyonSTEP 1View filterdepartment = BI, 2 rows leftSTEP 2Alice’s conditiontested on 2 BI rowsRESULTAna, Léono error,nothing learned

STANDARD VIEW · ORDER CHOSEN BY THE OPTIMIZER

4 STORED ROWSEMPLOYEESAna · BI · ParisLéo · BI · ParisRui · Finance · LisbonEva · HR · Lyon

STEP 1Alice’s conditionon all 4 rows: Lisbon row divides by zero

LEAKError"Division by zero" so a hidden Lisbon employee exists

STEP 2 · NEVER REACHEDView filterdepartment = BI

SECURE VIEW · VIEW FILTER ALWAYS FIRST

4 STORED ROWSEMPLOYEESAna · BI · ParisLéo · BI · ParisRui · Finance · LisbonEva · HR · Lyon

STEP 1View filterdepartment = BI, 2 rows left

STEP 2Alice’s conditiontested on 2 BI rows

RESULTAna, Léono error, nothing learned

Figure 3.16 The probe a secure view blocks. With a standard view the optimizer may run Alice's condition on rows the view would filter out, and an error leaks that a hidden row exists. A secure view always applies its own filter first.
CREATE SECURE VIEW hr_db.core.team_directory AS
SELECT employee_name, department, city
FROM hr_db.core.employees
WHERE department = (SELECT department FROM hr_db.core.user_department
                    WHERE user_name = CURRENT_USER());
GRANT SELECT ON VIEW hr_db.core.team_directory TO ROLE analyst;  -- the view, not the table
-- ANALYST already holds USAGE on HR_DB, HR_DB.CORE and BI_WH (grant sheet)

-- Alice's probe: harmless against a secure view
SELECT * FROM hr_db.core.team_directory WHERE 1/IFF(city = 'Lisbon', 0, 1) = 1;

Expected result. Alice sees her department's directory. For her, GET_DDL, SHOW VIEWS and the INFORMATION_SCHEMA views don't display the definition; only roles granted the view's owner role see it. Her probe returns BI rows and never an error that depends on hidden rows.

The mental model: three protections, three different jobs

Figure 3.17 runs one query against the same small SALES table under each protection, used alone. Compare what Alice gets back.

Row access policy removes whole rows
idregionemail
1EUROPElea@…
2AMERICASsam@…
3APACken@…
4EUROPEana@…
Alice gets rows 1 and 4. The others don't exist for her.
Masking policy rewrites values in a column
idregionemail
1EUROPE*****@…
2AMERICAS*****@…
3APAC*****@…
4EUROPE*****@…
Alice gets every row. The email column is masked.
Secure view through SALES_EU only, hides how
idregionemail
1EUROPElea@…
4EUROPEana@…
SELECT GET_DDL('VIEW', 'sales_eu');definition not shown to Alice
A secure view SALES_EU that keeps the European rows. Alice gets rows 1 and 4 and can't read how they were chosen.
Figure 3.17 Same table, three protections, each used alone. Row access policies decide which rows exist, masking policies decide what a column shows, and a secure view returns what its query selects while hiding how. Policies attach to the base table and apply on every path. A secure view protects only access through that view.

Limits and traps

The cost of a secure view is performance. The optimizations it skips are real, so a secure view over a large table can be slower than the same standard view. Use a standard view unless the definition or the excluded data is itself sensitive. Secure views are available in every edition. Options claiming secure views "encrypt data in transit" or "run faster" get it backwards.

Sharing note: by default a share accepts only secure views and other secure objects (SECURE_OBJECTS_ONLY = TRUE). A share explicitly created with SECURE_OBJECTS_ONLY = FALSE can carry non-secure views too, as Part 5 explains, but Snowflake recommends secure views whenever possible.

Which one to use

NeedBest fitWhy
Hide rows by role, on every path to a tableRow access policyAttaches to the base table. Querying the table directly can't bypass it
Hide column values by roleMasking policyFollows the column everywhere
Hide a view's SQL and block optimizer probingSecure viewOnly a view has a definition to hide
Expose a curated subset through a shareSecure view, often with policies on the base tablesConsumers can't see how the subset was defined

Other governance words

FeatureWhat it does
Object taggingAttaches key-value labels to objects and columns, for example pii = 'email'. A tag's ALLOWED_VALUES list restricts which values it may take (up to 5,000), so labels stay consistent. Tags are the hook for tag-based masking.
Data classificationDetects likely sensitive columns (email, name, national ID…) and can label them with Snowflake's built-in system tags, SEMANTIC_CATEGORY and PRIVACY_CATEGORY.
ACCESS_HISTORYA view in SNOWFLAKE.ACCOUNT_USAGE, the schema of account-wide history views in the shared SNOWFLAKE database. It records which columns each query read and wrote. It is the evidence for "who looked at salaries last quarter?" (Enterprise edition).
Aggregation policyQueries on the table must aggregate. Groups with fewer rows than the minimum are merged into one remainder group whose grouping key is NULL. With a minimum of 5, SELECT city, COUNT(*) … GROUP BY city works and SELECT * is refused.
Projection policyA column may be used in WHERE or JOIN but can't appear in the result. WHERE email = 'x@y.com' works, SELECT email is refused.
Exam takeaway
  • Secure view = hidden definition + no optimizer leaks, at some performance cost, in every edition.
  • For row or column restrictions on a table, policies beat views because they apply on every path.
  • Shares accept secure objects by default. Tag ALLOWED_VALUES holds up to 5,000 values. ACCESS_HISTORY answers "who read what."