Row and Column Level Security Guide: Designing Fine-Grained Data Authorisation That Holds

Row and column level security fail less often because a policy is written wrongly than because nobody designed where the entitlement lives, who may change it, and which identity actually reaches the warehouse. This guide is about designing fine-grained data authorisation in warehouses, lakehouses and BI semantic models as a system: the entitlement model behind row policies, the choice between column grants, classification tags and masks, separation of duties for policy administration, identity propagation through applications, BI tools and AI agents, testing, performance and monitoring. It is for data platform engineers, security architects and governance leads who already know what row-level security (RLS) and dynamic data masking do. The mechanics, the inference paths and the head-to-head comparison are covered in Dynamic Data Masking vs Row-Level Security, and this page does not repeat them.

Platforms named in this guide are listed alphabetically; the order is not a ranking.

Key takeaways

• Design the entitlement source before the policy. A row policy is only as good as the mapping table, group or attribute it reads; most real failures are stale or editable entitlements.
• Classify columns once, enforce by tag. Tag-driven policies (classification tags, policy tags, governed tags) scale across hundreds of tables where per-column policies drift.
• Separate policy authors from data owners. Several platforms deliberately stop table owners from removing policies; keep that separation in your role design.
• The identity that reaches the engine decides everything. A BI service principal, pooled application login or agent service account turns per-user rules into one shared view of the data.
• Test as the user, not as the admin. Use the platform's policy simulation features and real accounts in CI, because administrators are often exempt and see everything.

Choosing the column control

Column level security is three different controls that are often confused. Pick per column, based on whether the role needs the column at all.

PostgreSQL: column privileges are native. The documentation shows GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw, and notes that SELECT on a column is also needed to reference its existing value in UPDATE, DELETE or MERGE. INSERT granted on specific columns means other columns receive default values.

BigQuery: column-level access control uses policy tags grouped in taxonomies (up to five levels deep, one policy tag per column). Restrictions take effect when access control is enforced on the taxonomy, and readers need the Fine-Grained Reader role (datacatalog.categoryFineGrainedReader) on the policy tag in addition to dataset access. Without it, SELECT * fails with an error naming the inaccessible columns, so users must write SELECT * EXCEPT (...) or request access. Dynamic data masking is a separate, optional layer on top of policy tags.

Snowflake: column-level security is delivered through masking policies, which are schema-level objects applied to columns, and through External Tokenization. A masking policy can be attached through an object tag (tag-based masking); a policy assigned directly to a column takes precedence over a tag-based one. The documentation notes that a masking policy applies wherever the column is referenced, including WHERE, JOIN, ORDER BY and GROUP BY.

Availability: Snowflake documents Enterprise Edition or higher for column-level security and row access policies. Check the current edition and region requirements for each platform you use.

Designing the row entitlement model

A row policy answers "which rows may this principal see" by reading something. Where that something lives is the main design decision.

• Protect the mapping table as a security object. Store it in a schema that data readers and table owners cannot write, version its changes, and review writers as privileged access. Snowflake recommends keeping a centralised mapping table in the same database as the protected table, especially when the policy calls IS_DATABASE_ROLE_IN_SESSION.
• Feed it from IAM, not from tickets. Populate entitlements from identity governance or the identity provider so a leaver loses rows when they lose the account. See the Least Privilege Techniques Guide.
• Default deny. A principal with no entitlement row should see nothing. Power BI's documentation notes that users not assigned to any role typically see no data once RLS is enforced; design every platform to behave the same way.
• Relationship models: for sharing semantics, keep the decision in the authorisation service and sync derived keys to the warehouse. See the Fine-Grained Authorisation Guide and the Authorisation Models Guide.

Scaling with tag and attribute based policies

Per-table policies work for a dozen tables. Beyond that, attach policy to classification and let it follow the data.

• BigQuery: a taxonomy holds up to 100 policy tags and a table can carry up to 1,000 unique policy tags. Taxonomies are regional and must be in the same location as the table.
• Databricks: Unity Catalog attribute-based access control (ABAC) uses governed tags; row filter and column mask policies attach at catalog, schema or table level and apply automatically to objects carrying the targeted tags. As checked in November 2026, metastore-level policies, ABAC on views and DENY policies are marked Beta. Databricks recommends ABAC over manually assigning filters and masks for consistency across many tables.
• Snowflake: tag-based masking binds a masking policy to a tag, so any column given the tag is masked; direct column assignments override it.

Tag-based enforcement moves the risk to classification. Treat "untagged sensitive column" as a finding, run discovery on new tables before they are shared, and restrict who can set or remove a tag as tightly as who can edit a policy. See the Data Classification Guide and ABAC vs PBAC.

Policy administration and separation of duties

Whoever can alter a policy can switch it off. Build three roles and keep them apart (NIST SP 800-53 AC-5 Separation of Duties).

1. Policy author: creates and changes row and column policies. A security or governance team, small and reviewed.
2. Policy applier: attaches approved policies to tables. Snowflake describes a hybrid model in which the security officer creates policies and teams apply them through GRANT APPLY ON MASKING POLICY; it warns that a fully decentralised model risks inconsistent masking.
3. Data owner: owns tables and loads data but cannot remove policies. Snowflake documents that by default object owners cannot unset masking policies or see masked data. In Databricks, table-level filters and masks are managed by the table owner or a principal with MANAGE, so restrict table ownership to deployment identities rather than people.

Keep policies in version control and deploy them through the same pipeline as schema changes. Snowflake's ALTER MASKING POLICY changes a policy without unsetting it from columns, which makes in-place edits easy and therefore worth alerting on. Platform security baselines are in the Securing the data warehouse and lakehouse itself guide.

Identity propagation: BI tools, applications and agents

Row and column rules evaluate the identity the engine sees. Map every access path and write down which identity arrives.

Power BI shows how sharp these edges are. Its documentation states that RLS restricts only users with Viewer permissions and does not apply to workspace Admin, Member or Contributor roles; that service principals cannot be added to an RLS role, so RLS is not applied for apps using a service principal as the final effective identity; and that for app-owns-data embedding an EffectiveIdentity must be passed when generating the embed token. Its USERPRINCIPALNAME() function returns the sign-in UPN, which is not necessarily the email address, so mapping tables must store the UPN. Snowflake notes that policies calling CURRENT_ROLE() or CURRENT_USER() return NULL in data sharing consumer accounts, and recommends CURRENT_ACCOUNT() or IS_DATABASE_ROLE_IN_SESSION instead.

Where identity fits

Every ETL job, BI refresh, reverse-ETL sync and agent that reads the warehouse is a non-human identity, and each one either carries a fine-grained policy or bypasses it. Inventory them, give each its own identity rather than a shared login, never run them as table owner or with an exemption privilege, and record in the registry whether each one is policy-bound or exempt. Exempt identities are privileged accounts and belong in PAM. Platform-specific guidance is in the Service Accounts in Snowflake Guide and the Databricks Service Principals and Token Security Guide.

Platform design notes

Each platform has its own security guide on nhimg.org for wider hardening: Amazon Redshift Security Guide, BigQuery Security Guide, Databricks Security Guide, PostgreSQL Security Guide, Power BI Security Guide and Snowflake Security Guide.

Implementation approach

1. Inventory and classify: list tables with personal, financial, health or tenant-separated data and tag sensitive columns. Record every access path from the propagation table.
2. Define entitlements: choose the entitlement source per domain, build the mapping tables or attributes from IAM, and agree default deny.
3. Write policies as code: keep row and column policies in a repository with owners, peer review and deployment through CI. Prefer tag-driven policies where the platform supports them.
4. Test before enforcing: build a test matrix of personas (each role, a leaver, an unmapped user, a BI service identity, an agent) against expected row counts and column visibility. Use the platform's simulation where available: Snowflake's POLICY_CONTEXT simulates roles for masking and row access policies, and Power BI's "Test as role" previews roles. Power BI notes that for dynamic RLS USERPRINCIPALNAME() returns the tester's own UPN and that Test as role does not work for DirectQuery models with SSO, so keep real test accounts.
5. Pilot on one domain: enforce on a single schema with its consumers for [two weeks], watching query errors (BigQuery SELECT * failures are expected) and performance.
6. Measure performance: mapping-table joins add cost on every query. Snowflake suggests memoizable functions in place of mapping table references; Databricks recommends simple CASE expressions over mapping tables and SQL over Python UDFs.
7. Roll out and lock down: remove direct table access that bypasses governed views, revoke owner access from people, and turn on alerts for policy changes.
8. Rollback: keep the previous policy version deployable. Never roll back by detaching a policy, which exposes the full table; deploy the earlier version instead.

Decision guidance

• Engine versus semantic layer: enforce in the engine when users or tools query it directly; enforce in the BI model only when the model's identity is a shared service account and users cannot reach the engine. Many estates need both, with the same entitlement table feeding each.
• Policies versus separate tables: where users can write arbitrary SQL against highly sensitive data, separate tables or views per audience give stronger isolation than predicates; policies suit large, mixed audiences.
• Small organisations: start with column grants and one mapping table per domain; skip ABAC until tagging is reliable.
• Large organisations: centralise policy authoring, use tag-driven enforcement, and make classification part of the table creation pipeline.

Detection and measurement

• Log: policy create, alter, drop and attach events; mapping table writes; tag set and unset; grants of exemption or unmask privileges; queries by exempt identities on protected tables. Snowflake exposes POLICY_REFERENCES and MASKING_POLICIES to list policies and assignments.
• Coverage: share of tables with sensitive tags that have an enforced row or column policy.
• Untagged sensitive columns: discovery findings not yet classified, with age.
• Exempt identity count: principals that bypass policies, trended down, each with an owner.
• Entitlement freshness: mapping table rows for disabled accounts; target zero.
• Policy drift: deployed policy definitions that differ from the repository.
• Persona test pass rate in CI after each policy or schema change.

Common failure patterns

• Editable entitlements: the mapping table sits in a schema analysts can write, so anyone can grant themselves rows.
• Shared BI identity: a report refreshes with a service account that sees all rows, and RLS was never rebuilt in the semantic model.
• Pipeline rebuilds: a job recreates a table and the copy carries no policy. Redshift notes CREATE TABLE LIKE does not inherit RLS policies; the comparison guide lists similar behaviour elsewhere.
• Workspace roles exceed intent: report consumers are given Member or Contributor rights for convenience, and RLS stops applying.
• Agent as a super user: a data assistant connects with one broad service account, so every user of the assistant sees the agent's full view.
• Silent type casts: a filter function receives a mismatched type and returns NULL, filtering or masking the wrong rows.

Framework mapping

Practitioner checklist

• Every sensitive column is tagged, and untagged findings are tracked to closure.
• Mapping tables live in a protected schema, are fed from IAM and default to deny.
• Policy authors, appliers and data owners are separate roles; people do not own protected tables.
• Policies are in version control and deployed by pipeline; changes alert the security team.
• Every access path is mapped to the identity the engine sees, including BI and agents.
• Embedded analytics passes the end user's effective identity on every request.
• Exempt identities are listed, owned and managed as privileged access.
• Persona tests run in CI with real test accounts, not only administrator simulation.
• Table rebuild jobs are checked for policy loss after each run.

Standards and references

• NIST: SP 800-53 Rev. 5 Security and Privacy Controls
• AWS: Row-level security in Amazon Redshift
• Databricks: Unity Catalog attribute-based access control
• Google Cloud: Introduction to BigQuery column-level access control
• Microsoft: Row-level security with Power BI
• PostgreSQL: Privileges
• Snowflake: Understanding Column-level Security

Related NHI Mgmt Group resources: Dynamic Data Masking vs Row-Level Security, Coarse-Grained vs Fine-Grained Authorisation, Tokenisation and Data Masking Guide, Database Security Guide.