Dynamic Data Masking vs Row-Level Security: What Each Hides and How to Combine Them

Dynamic data masking and row-level security are often bought as the same "fine-grained access" feature, yet one hides values and the other hides rows, and each fails in a way the other does not cover. Dynamic data masking (DDM) changes what a column returns while the row stays visible; row-level security (RLS) removes whole rows while every column of visible rows stays in clear. This guide compares threat models, enforcement, inference and bypass paths, service account and BI access, and combining them. For masking techniques in general see the Tokenisation and Data Masking Guide, and for masking production copies see Static vs Dynamic Data Masking.

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

Key takeaways

• Masking hides values, RLS hides rows. DDM answers "which fields may this user read in clear"; RLS answers "which records may this user see at all".
• Neither stops a user who can write SQL from inferring data. Microsoft documents both a WHERE-clause inference against masked columns and an error-based inference against filtered rows, and Google documents query timing as a side channel for row policies.
• Who is exempt differs sharply. In SQL Server, sysadmin and db_owner always see unmasked values, but RLS policies apply to dbo and table owners unless the predicate allows them. In PostgreSQL, table owners, superusers and BYPASSRLS roles bypass RLS unless FORCE ROW LEVEL SECURITY is set.
• Service accounts and BI tools are where both controls quietly disappear. Shared logins, pooled connections and BI service principals turn per-user rules into per-application rules.
• Combine them deliberately. Row policies first, then masking, then column-level grants for columns nobody in the role should query at all, with auditing behind all three.

The short answer

Use row-level security when the boundary is the record: a tenant in a shared multi-tenant schema, a sales region, a patient's own care team, an employee's own payslip. Use dynamic data masking when the user legitimately needs the record but not every value in it: a contact centre agent who needs the last four digits of a card number, an analyst who needs a birth year but not a birth date. Use column-level permissions (or object-level security in BI models) when a role should never query a column at all, because a masked column can still be filtered on and a hidden row is not a hidden column. Where users run their own queries, treat both as exposure reduction and keep the real boundary in grants or separate tables; Google's BigQuery documentation rates separate tables as the strongest isolation.

Side-by-side comparison

What each hides, and what each leaves visible

Dynamic data masking

DDM rewrites column output for users without an unmask right. In SQL Server and Azure SQL the built-in functions are default(), email(), random(), partial() and, from SQL Server 2022, datetime() for masking part of a date. Snowflake and Databricks express it as a policy or SQL function returning either the original or a masked value. What masking leaves visible matters most: the row exists, its other columns are readable, the masked column can still be filtered and joined on, and row counts are exact. Masking reduces casual exposure; it does not stop a determined query writer.

Row-level security

RLS attaches a predicate to a table so that every query behaves as if a WHERE clause had been added. SQL Server binds an inline table-valued function to a table through CREATE SECURITY POLICY; PostgreSQL uses CREATE POLICY with USING and WITH CHECK expressions; Snowflake uses a schema-level row access policy that runs with the policy owner's privileges; BigQuery uses row-level access policies with a grantee list and filter expression; Databricks Unity Catalog uses row filter functions; Oracle Virtual Private Database (VPD) uses a policy function that generates a dynamic WHERE clause, configured with the DBMS_RLS package. What RLS leaves visible is every column of every permitted row.

Column-level controls

Column grants, BigQuery column-level security and Power BI object-level security remove a column from what a role may query. Microsoft states that Power BI RLS cannot hide columns and that object-level security does that job; the same logic holds in the engine. For the wider database control set see the Database Security Guide.

Threat model for each option

DDM addresses over-exposure of identifiers to support, operations and reporting users who need the record, and accidental extracts: in SQL Server, SELECT INTO and INSERT INTO run by a user without UNMASK copy masked values, and exports by such users contain masked data. DDM does not address inference by query (Microsoft's documentation concludes DDM should not be used alone to fully secure sensitive data), privileged users (SQL Server principals with CONTROL SERVER or database-level CONTROL, such as sysadmin and db_owner, always see unmasked data) or writes to the masked column.

RLS addresses cross-tenant and cross-region reads for every client,; AWS describes it as a database-managed WHERE clause, so developers need not add a tenant filter to every query. With SQL Server block predicates or PostgreSQL WITH CHECK it also rejects writes that would put a row outside the user's boundary. RLS does not address side channels, bypass roles (PostgreSQL superusers and BYPASSRLS roles always bypass it, and table owners do unless the table is set to FORCE ROW LEVEL SECURITY) or column exposure within permitted rows. The table below lists the documented paths.

Inference and bypass risks in practice

Two platform details surprise teams. BigQuery removes row-level policies on CREATE OR REPLACE TABLE and WRITE_TRUNCATE (TRUNCATE TABLE keeps them), so a rebuild pipeline can strip protection every run. Databricks returns no data from protected tables on runtimes older than 12.2 LTS, and recommends ANSI mode because silent casts to NULL can make filters and masks return wrong results.

Platform terms at a glance

Availability: Snowflake documents row access policies as requiring Enterprise Edition or higher. SQL Server granular UNMASK and the datetime() function require SQL Server 2022 or later. Databricks row filters and column masks need Databricks Runtime 12.2 LTS or above, with further rules for dedicated access mode. Check each vendor's current documentation for edition and version limits.

Service account and BI tool access

Both controls are only as precise as the identity the database sees, and most production traffic arrives as an application login, pooled connection, ETL job or BI service principal rather than a named person.

• Shared application logins: SQL Server documents the SESSION_CONTEXT pattern: the application sets the end user's ID with sp_set_session_context using @read_only = 1, a filter predicate checks it and a block predicate stops inserts for other IDs, with DENY UPDATE on the ID column. PostgreSQL's equivalent is a custom setting such as app.current_tenant read by current_setting() in the policy.
• Connection pools: AWS notes that server-side poolers such as pgBouncer may not preserve session variables, so tenant context can leak or be lost. Set context on every checkout and prove a missing context returns nothing.
• Owner logins: a PostgreSQL application connecting as table owner skips RLS by default; use a non-owner role without BYPASSRLS and set FORCE ROW LEVEL SECURITY.
• ETL and service jobs: BigQuery's TRUE filter serves service account jobs and table copies that need all data; those identities sit outside the row boundary and need the tightest credential handling. See the Service Account Security Guide.
• Power BI: RLS applies only to workspace Viewers (including those with Build permission); Admins, Members and Contributors are not filtered. Service principals cannot be added to RLS roles, so embedded apps must pass an EffectiveIdentity in the embed token for per-user RLS. In Import mode the source's own security is ignored and model RLS is the only enforcement.

Where identity fits

Every exempt principal in this guide, from db_owner to a BYPASSRLS role to a BigQuery TRUE-filter grantee, is a privileged identity, and many are non-human. Inventory them alongside other service accounts, keep their credentials in a vault, and review them on the same cycle as database administrators. See the Non-Human Identity (NHI) Guide and the Least Privilege Techniques Guide. An AI agent querying a warehouse as one service identity is seen by the policies as the agent, not the person asking.

Decision criteria

Small organisations with one engine and few query writers: RLS for tenant or customer boundaries and DDM on a short list of identifiers, enforced in the database. Large organisations with a warehouse and several BI tools: tag-driven policies (for example Databricks ABAC policies on governed tags) owned by central data governance, so a newly tagged column is protected everywhere. Classification is the prerequisite; see the Data Classification Guide.

Worked scenarios

Scenario 1, a B2B SaaS reporting feature. A product team adds customer-facing reports over a shared PostgreSQL database. They enable RLS on each reporting table with a policy comparing tenant_id to current_setting('app.current_tenant'), set FORCE ROW LEVEL SECURITY, and connect the reporting service as a role that does not own the tables. Load testing through the pooler shows the tenant setting carrying over between requests, so they set it at the start of every transaction and add a test asserting zero rows when no tenant is set. Masking is not needed: tenants may see all their own columns.

Scenario 2, a retailer's contact centre. Agents look up any customer by order number, so RLS would block the work. The team masks the card number (partial, last four), email and date of birth, grants UNMASK on email only to escalation staff, and removes ad hoc query access so range-predicate inference is out of reach.

Scenario 3, an analytics warehouse. Regional analysts must see only their region and never see national insurance numbers. A row access policy keyed on a region mapping table limits rows; the identifier column is denied to analyst roles rather than masked, because analysts write SQL. The BI service identity gets its own role with the same row policy.

Implementation approach

1. Classify and scope: list tables, the row boundary and sensitive columns; decide per column: mask, deny or encrypt.
2. Map identities: for every path in (application, pool, ETL, BI, notebooks, agents) record which identity the database sees.
3. Design policies centrally: keep predicate functions and policies in a dedicated schema, owned by a policy manager role that does not need SELECT on the data, as Microsoft recommends. Keep expressions simple and deterministic.
4. Test in a non-production copy: run each role's typical queries, the inference cases in the table above, and the "context missing" case. For Power BI use Test as role, but validate DirectQuery with SSO by signing in as a real Viewer, since Test as role does not work there.
5. Pilot with one consumer: compare row counts and masked outputs against expectations and watch performance (BigQuery notes row filters do not prune partitions or clusters).
6. Roll out by data domain: extend to remaining tables, then add write checks (block predicates or WITH CHECK).
7. Roll back safely: SQL Server policies can be switched with STATE = OFF and masks dropped per column; PostgreSQL policies can be dropped or RLS disabled by the owner. Version rollback scripts with the policies; disabling a row policy exposes rows.

Detection and measurement

• Policy change events: alert on CREATE, ALTER or DROP of security policies, row access policies, masks and unmask grants. BigQuery audit logs omit the filter expression and grantee list, so keep definitions in version control.
• Exempt principal activity: log reads, exports and clones by owners, administrators, BYPASSRLS roles and TRUE-filter grantees.
• Inference patterns: repeated narrow predicates on masked columns, bursts of errors from one principal, unusually high query counts against a protected table.
• Coverage drift: compare catalogue views (for example sys.masked_columns) with the classification inventory to find unprotected sensitive columns.

Metrics:

1. Percentage of classified sensitive columns with a mask, deny or encryption control.
2. Percentage of multi-tenant tables with RLS enabled and forced, with write checks.
3. Number of exempt principals per database, and the share reviewed in the last quarter.
4. Number of application and BI paths that carry the end user's identity versus a shared service identity.
5. Policy changes made outside the change process (target zero).

Common failure patterns

• Treating masking as access control for analysts. Users with SQL can infer masked values; deny the column instead.
• Losing tenant context in the pool. A pooled connection reuses the previous request's tenant or none at all.
• Rebuilding tables without policies. CREATE OR REPLACE in BigQuery, or a pipeline that drops and recreates objects, leaves a protected table unprotected.
• BI workspace roles that bypass the model. Power BI Members and Contributors are not subject to RLS, so granting edit roles widely removes the filter.
• Temporal and history tables without policies. SQL Server does not copy policies to temporal history tables, so the history holds every row unfiltered unless predicates are added there too.

Framework mapping

Practitioner checklist

• Every sensitive column has a decision recorded: mask, deny or encrypt, with the reason.
• Multi-tenant tables have RLS enabled, forced where the platform requires it, and write checks.
• Applications and BI refreshes never connect as table owners or bypass roles.
• Tenant or user context is set on every pooled connection and a missing context returns no rows.
• Users who can write ad hoc SQL are not relying on masking for sensitive identifiers.
• Exempt principals (owners, administrators, unmask holders, TRUE-filter grantees) are inventoried and reviewed quarterly.
• Policy managers are separate from data readers, and policy changes alert.
• Pipelines that rebuild tables reapply row and masking policies, with a test that fails if they are missing.
• BI workspace roles are reviewed so edit roles are not used as a way round model RLS.

Standards and references

• Microsoft Learn: Row-level security
• Microsoft Learn: Dynamic data masking
• PostgreSQL: Row Security Policies
• Google Cloud: Introduction to BigQuery row-level security
• Databricks: Row filters and column masks
• Oracle: Using Oracle Virtual Private Database to Control Data Access
• Snowflake: Understanding row access policies
• Microsoft Learn: Row-level security with Power BI

Related NHI Mgmt Group resources: Static vs Dynamic Data Masking · Database Security Guide · Multi-Tenant SaaS Architecture Security Guide · ABAC vs PBAC · Database Activity Monitoring vs DSPM