Database DevOps · Masking

Dynamic data masking: when hiding values at query time is enough, and when a copy must be changed

Dynamic data masking hides sensitive values in query results, according to who is asking, while the data stored in the database stays exactly as it was. It is useful in a live system, and often misused for test copies, where static masking is the right tool. This guide explains the difference, how SQL Server and PostgreSQL handle it, and what it means for personal data in Saudi Arabia.

Published November 10, 2026 8 min read

Plan masking for your databases Static vs dynamic

Orientation

At a glance

The whole page in one table, before the detail.

What it is: Masking applied when data is read, by user or role; the stored values do not change
Where it fits: Live and reporting databases where some users need the row but not every column
Where it does not: Development and test copies handed to wider teams; those need static masking
Native options: SQL Server and Azure SQL have a built-in feature; PostgreSQL builds it from views, privileges or an extension
Saudi angle: Personal data under the PDPL, in production and in every copy of it
Where Redgate fits: Test Data Manager: static masking and subsetting of non-production copies

The problem in plain words

The usual situation: support staff, analysts or contractors can read national ID numbers, mobile numbers and salaries they never need for their work.
What dynamic masking changes: those people see XXXX or a partial value instead; the database still holds the real one.
What it does not change: the real values remain on the server, in its backups and in any copy restored from them.
The decision: hide at read time in production, change the data in copies. Most estates need both.

What dynamic data masking is, and what it leaves unchanged

Dynamic data masking is a rule attached to a column. When a user without permission to see the real value runs a query, the database returns a masked form: a fixed string, the last four digits of a card, the first letter of an email address. A user with that permission sees the true value from the same table. Nothing is rewritten on disk. That is the strength of the method and its limit: it is data obfuscation in the result set, not a change to the data.

So it answers one question well: which people, using the live system, should see which columns. It does not answer what happens to the data when it is copied, exported or restored somewhere else. For that, see our guide to data masking for test environments.

Static vs dynamic data masking: the difference that decides the choice

The two methods share a name and solve different problems.

Static and dynamic data masking compared by what changes, where each fits and its limits
	Dynamic masking	Static masking
When it acts	At query time, each time data is read	Once, while a copy is made
Stored data	Unchanged; real values stay in the database	Replaced permanently in the copy
Who it protects against	Users of the live system without the unmask permission	Everyone who uses the copy, including administrators
Typical place	Production, reporting and support databases	Development, test, UAT, training and vendor copies
Backups and exports	Hold the real values	Hold only masked values
Main risk	Privileged or creative users can still reach or infer real values	Broken relationships if the same person is masked differently in two places

The common mistake is to restore a production backup into test and rely on the masking rules that came with it. Anyone with administrative rights on the test server sees the real data, and so does anyone who restores the backup elsewhere. Static masking is the method for copies.

Dynamic data masking in SQL Server and PostgreSQL: column-level masking rules

Behaviour as each vendor's own documentation describes it. Features vary by version and edition, so confirm yours.

How SQL Server, Azure SQL and PostgreSQL provide column-level dynamic masking
Platform	What is built in	How real values are shown	Watch out for
SQL Server and Azure SQL	A Dynamic Data Masking feature: a mask function on a column, such as a full mask, an email mask, a random number or a partial string	Users granted the UNMASK permission, and highly privileged users, see real values	Microsoft describes it as a complement to other controls, not a replacement for them
PostgreSQL	No dynamic masking feature in the core database	Views that return masked expressions, column privileges and row-level security; or an extension added for masking	Every route to the base table must be closed, or users read around the view

On either platform, write the rule per column: which role sees the real value, what the masked form looks like, and whether applications that validate a national ID, Iqama or mobile number format still accept it. Masking the Arabic and English name fields alike matters as much here as in a test copy.

What dynamic masking does not protect against

Read-time masking narrows what people see. It is not a wall around the data.

Four limits to plan for

Inference. A user who can write their own queries may filter on a masked column, for example where salary is above a figure, and learn the real value without ever seeing it. Microsoft's documentation warns about this.
Privileged accounts. Administrators and owners usually see real values. Who holds those rights is part of the control.
Copies. Backups, exports, replicas and restored test databases carry the real data.
Other paths. Logs, traces and application caches can hold the same values unmasked; see keeping personal data out of logs and traces.

Masking sensitive data under the PDPL in Saudi Arabia

Personal data in the Kingdom falls under the Personal Data Protection Law, overseen by the Saudi Data and Artificial Intelligence Authority (SDAIA). The National Cybersecurity Authority (NCA) publishes the cybersecurity controls many organisations must meet. Our overview of the PDPL gives the general picture; which rules apply to you, and what they require, is for your compliance owner to confirm from the authorities' own texts.

In practice, the engineering questions organisations in Saudi Arabia ask are these. Which columns hold personal data, by your classification? Who needs to see each one in production, and who only needs the row? How many copies of production exist, where do they sit, and who can read them? The first two answers shape dynamic masking. The third usually reveals the bigger exposure: development, test and vendor environments holding full, unmasked copies of customer data.

Masking does not replace classification, access control or audit, and dynamic masking alone does not reduce the number of places real personal data lives. Static masking of non-production copies does. Recording who reads sensitive tables is a separate control; it is covered under database activity monitoring.

Which control fits: a decision table by situation

Start from who reads the data and where, not from the tool.

Choosing between dynamic masking, static masking and other controls by situation
Situation	Control that fits	Why
Call-centre staff need the customer record but only the last digits of the ID	Dynamic masking	Same live system, fewer visible digits, no copy involved
Analysts query a reporting database with salaries in it	Dynamic masking plus restricted ad hoc queries	Masking alone leaves inference open to users who write their own filters
Developers need a fresh copy of production every sprint	Static masking, with subsetting	The copy leaves the production controls; its values must change
A vendor tests an integration against your data	Static masking or generated data	An outside party should never hold real values
A few people must never read a column at all	Column privileges or encryption	Masking still lets the column be queried
Personal data appears in logs and traces	Redaction in the telemetry pipeline	Database masking never sees those values

Where Redgate Test Data Manager fits, and where it does not

Redgate is a technology partner whose tooling Interkey implements for database delivery. Its Test Data Manager works on the copy side of this guide: according to Redgate's documentation it classifies data to find personally identifiable information, masks it deterministically so the same person stays consistent across tables, builds smaller subsets that keep referential integrity, provisions clones with Redgate Clone, and runs from a command line built for CI/CD pipelines. That makes static masking a routine pipeline step instead of a script a DBA runs by hand.

Where it does not fit: Test Data Manager does not mask query results in a live production database. For that, use the database's own dynamic masking or views, as described above. It is documented for relational engines, and supported engines and features vary by version and edition, so we confirm yours during scoping. If you only need to hide a few columns from some users of one reporting database, the built-in feature is usually enough. We will say so.

How Interkey helps

Interkey's database DevOps practice scopes masking from who reads what: the column list with your data owners, dynamic masking rules and the roles that unmask in production, and masked, subsetted test copies built into your delivery pipeline with Redgate where it fits. Support comes from Riyadh, in Arabic and English. Redgate licensing is quoted at Redgate's terms alongside Interkey's work.

Relateddatabase DevOps

Questions buyers ask about dynamic data masking

What is the difference between static and dynamic data masking?

Dynamic masking hides values when data is read, by user or role, and leaves the stored data unchanged. Static masking replaces the values permanently in a copy before anyone uses it. Dynamic suits live systems; static suits development and test copies.

Is SQL Server dynamic data masking enough to protect personal data?

Not on its own. It limits what users without the unmask permission see, but privileged users still see real values, users who write their own queries can infer values, and backups hold the real data. Microsoft describes it as a complement to access control, encryption and auditing.

Can we use dynamic masking on our test databases instead of static masking?

It is not advisable. Test servers usually have more administrators and more copies, and anyone with rights on the server or the backup sees real values. Change the data in the copy with static masking before access is granted.

Does masking personal data help with the PDPL in Saudi Arabia?

It reduces who sees personal data and how many places hold it, which supports your obligations. Which obligations apply and what they require is for your compliance owner to confirm from SDAIA's texts. Masking does not replace classification, access control or audit.

Does Redgate Test Data Manager do dynamic masking?

No. It masks copies of databases for development and test, with classification, deterministic masking and subsetting. Read-time masking in a live database comes from the database itself, such as SQL Server's built-in feature or views in PostgreSQL.

How long does a masking project take, and how is it priced?

Both are estimated per project, from the number of databases and columns holding personal data, how many copies exist and how often they refresh. Interkey's work is quoted per scope; Redgate licensing is quoted at Redgate's terms.

Next step

Plan where each kind of masking belongs

Tell us the databases that hold personal data, who reads them, and how your test copies are made today. We reply with where read-time masking is enough, where copies need static masking, and a first scope.

First name Last name

Company Email Company

Phone number (optional) What are you working on? (optional)

Required field

By sending this form, you agree to our Privacy Notice.

Published by Interkey. Last updated 2026-11-10. Interkey is registered in Riyadh, Saudi Arabia under commercial registration 1010156897.