Column security and dynamic masking

A column has two audiences on any real table. Most roles need to know the column exists, need it to stay in the row for joins and counts, and have no business seeing the value. One or two roles need the value to do their job. A grant cannot express that split: it hands the whole column to a role or it does not.

Masking expresses it exactly. The policy is a function of the value and the caller. For most callers it returns a transformed value; for the named exception it returns the real one. The column never leaves the table, nobody's query breaks, and the data scientist's churn model still has a row with an email column in it, just not one that identifies a person.

• Dynamic masking policy - a schema-level object holding an expression over a column value. Attached to a column, it transforms the value at query time depending on who is asking. The stored data is untouched.
• Exception role - a role the policy names as receiving the real value. Written into the policy body, so removing the exception is a policy change with a diff, not a grant somebody forgets.
• Full mask - a fixed string or NULL in place of the value. The column exists; it says nothing.
• Partial mask - part of the value survives: the last four digits, the email domain, the year of a date. Enough to recognise, not enough to identify or contact.
• Hash or token - a deterministic replacement that keeps equal inputs equal, so the column still joins and groups. Session 5 takes this shape further.
• Classification tag - a label on a column that says what class of data it holds. A policy bound to the tag applies to every column carrying it.

A dynamic masking policy is a schema-level object that takes a column value and returns a value of the same type. Attached to a column, it runs at query time for every row that reaches it, and the caller's roles are available inside the expression. The data on disk never changes; only what the query returns does. Two callers running the same SELECT get two different answers, and both are correct.

The exception role belongs inside the policy body, as a branch of the expression: if the session holds the named role, return the value, otherwise return the mask. The alternative, masking for everyone and then handing the exception role a separate unmasked view or a grant on a copy, is how exceptions multiply. Each one is a new object with its own grants, none of them show up when you read the policy, and the access review in session 7 finds them one at a time. An exception in the policy body is one line in one place, and removing it is a diff somebody reviews.

Pick the weakest shape that still does the job the design describes, and write down why. A partial mask that leaves the last four digits of a phone number is a design decision about how many digits an attacker needs, not a formatting choice. The email domain is safe to leave for a plan-mix analyst and not safe to leave for a table of two hundred customers where one of them works at a company of three.

The written design produces exactly two masking policies for this table, and they are different in the one way that matters.

• national_id, masked for everybody, no exception. Read the design grid: no job at Saltbox needs a national ID in clear, or masked, or at all. The policy therefore has no exception branch, and that absence is the control. The first time somebody asks for one, the answer is not a grant but a change to the design, argued in the open.
• email and phone, masked for everybody except fr_support_agent. Support answers tickets and has to reach the actual human. That is the one job with a stated reason, so it is the one name in the policy. The EU analyst, the data scientist, finance and the contractor all get the mask, and none of their work needs the value.

On the bench, with the single shared role still in place, masking national_id takes leaks from 109 to 94 and masking email and phone with the support exception takes them to 70. Over-blocks stay at 0 at both steps, because the support agent's row stays clean: the exception is inside the policy, so nothing was taken away from the one job that needed it. Standing privileges fall 30 to 25 to 17 over the same two steps.

Attaching a policy column by column works for one table with ten columns. It does not work for a warehouse with four thousand columns of which three hundred are contact details, because the policy is only as complete as the last person's memory of which columns exist. The cross-platform answer is to attach the policy to a classification tag instead, and to tag columns by what class of data they hold.

• The pattern: a tag such as contact_pii is applied to email, phone and every other column of that class. One masking policy is bound to the tag. Every tagged column is masked by that policy, and a newly tagged column is masked the moment the tag lands.
• The equivalents: BigQuery calls the mechanism policy tags and pairs it with column-level security. Snowflake and Databricks Unity Catalog both offer tag-based masking. The category is the same; the syntax differs and is worth reading from each platform's own reference rather than a summary.
• The dependency it creates: classification becomes the input that decides which column gets masked. Mis-tag a column and it is mis-masked, which is why the classification tiers from session 1 are kept to three or four and why free-text columns are tagged by what they can contain.

The evaluation order means filter rows by region first, then mask what remains. A masking policy never runs on a row the row policy rejected, so the cost of masking scales with the rows a role is allowed to see, and a role allowed to see nothing costs nothing. It also means a masking policy cannot be used to hide the value the row policy needs, because by the time masking runs, the row decision has already been made on the real value.

The one-policy-type rule closes the loop: the column the row policy keys on cannot itself be masked. For Saltbox that is region, and it is fine, because region is non-sensitive and every one of the five jobs needs it in clear. The rule bites when a team wants to filter on something sensitive, a national ID prefix or a full postcode, and also mask it. The answer is to derive a coarse, non-sensitive column for the filter and leave the sensitive one to masking.

The policy bench in session 8 evaluates 150 access requests, of which 41 need a value in clear, 3 need it masked and 106 have no business being answered. The two masking policies tonight are the first two rungs of its ladder, applied on top of the single shared analyst role.

Two things to read off that table. First, over-blocks stay at 0 through both masking steps, because the exception role for support is inside the contact policy and the support agent's row stays clean. Second, the over-blocks only appear when somebody strips every PII column from everyone instead of masking: 0 leaks, 8 over-blocks, all of them the support agent across name, email, phone and support_notes in each of the two regions they cover. Masking with a named exception and stripping the column both satisfy a leaks-only report. Only one of them lets the support desk close a ticket.