Row-level security policy

As explained in Snowflake Data Engineering

A row access policy is a Snowflake policy object that determines whether rows are returned based on the querying role and conditions evaluated against row values.

A Snowflake row access policy controls which rows a query returns. Its Boolean body evaluates the executing role and values from one or more mapped row columns; rows are returned only when the policy evaluates to TRUE. Policies are schema objects and can be attached to tables or views. section 10.2

Use row-level filtering when multiple business units, regions, or similar groups share database objects but must see only their own records. It complements role-based access control: roles receive object access, while the policy restricts the rows visible through that access. section 10.0 section 10.4

Implement a role-based policy incrementally:

• Create a governance schema, such as DG, and grant the implementing role CREATE ROW ACCESS POLICY on that schema plus the account-level APPLY ROW ACCESS POLICY privilege. section 10.2
• Create functional roles for the permitted groups and grant each the access role that allows reads of the target schema. section 10.2
• Define the policy with a column argument and CASE logic. Test session roles with IS_ROLE_IN_SESSION, compare the row value to its allowed group, and return FALSE for all other cases. section 10.2
• Attach the policy to the target view or table on the matching column, then query under each functional role to verify the returned rows. section 10.2

Business-unit employee view

A policy on EMPLOYEE(DEPARTMENT) allows DATA_ENGINEER to see all rows, DATA_ANALYST_BREAD to see Bread rows, and DATA_ANALYST_PASTRY to see Pastry rows. Both analyst roles can read the RPT schema through BAKERY_READ, but their query results differ by department. section 10.2

Figure 10.2 Functional roles DATA_ANALYST_BREAD and DATA_ANALYST_PASTRY are granted the BAKERY_READ access role, which allows them to read data in the RPT schema. A row access policy is applied to the EMPLOYEE view in the RPT schema, filtering rows based on the user’s functional role.

The role diagram separates schema read access from policy-driven row filtering, showing why both access roles and a row access policy are needed. section 10.2

Choose a centralized model when governance officers define and apply policies, a decentralized model when teams do both, or a hybrid model when governance defines policies and teams apply them. Keep policy ownership and application responsibilities explicit. section 10.2

How Snowflake Data Engineering covers it

How deep it goes

Works through schema setup, privileges, CASE-based policy creation, view attachment, and role-specific query results.

Examples

Uses an EMPLOYEE view filtered into Bread and Pastry rows for two analyst roles.

What it assumes

Assumes Snowflake roles, schemas, views, warehouses, and privileges while demonstrating policy-specific grants and SQL.

What you can do after

Apply it via Snowflake by creating, attaching, and verifying a role- and column-based row access policy.

How it connects

Connects role-based access control, functional roles, access roles, managed access schemas, and masking policies.

liveBookmanning.com

Read Snowflake Data Engineering on liveBook Get the book at manning.com

Related concepts

• roleuses · A role is a defined function, responsibility, perspective, or set of permissions associated with a participant in an organization or system.
• rootcontrasts with · A root or superuser is a privileged account or principal with broad administrative authority that can bypass ordinary permission restrictions within a system.
• query engineused by · A query engine is a software component that processes queries against data or knowledge sources and produces results.
• role-based access controlimplements · Role-based access control is an authorization model that assigns permissions to roles and grants users or other principals access through their assigned roles.
• multitenant SaaS applicationaddresses · A multitenant SaaS application serves multiple independent customers or organizations through a shared service while isolating their data or access.

This explainer was generated by Manning's knowledge graph from the text of Snowflake Data Engineering. The section links point to the parts of the book it draws on.