Row policy and column mask tests

Last updated: 6 Oct 20265 min read

tutorial

A row policy limits which records an identity can read; a column mask changes the value it sees, so both need tests at the consumer query boundary.

State the intended visibility

A regional support analyst may see only customers assigned to their region, with account token masked. A finance service may read all regions but only the aggregate billing amount. Write this as an identity-by-field matrix, including null and unknown region cases. An unassigned customer should not become visible merely because the policy comparison returns an unexpected null value.

Test through the same query path

A unit test of a masking expression cannot prove the BI connection, view ownership and role mapping apply it. Execute representative SELECTs as each real role against the published view. Verify allowed row IDs, denied row IDs and exact masked values. Then try a join, export and materialized copy if those paths are available to the role.

Guard policy inputs

If an administrator can edit the analyst-to-region mapping, that mapping is part of the security boundary. Version it, review changes and test a user moved between regions. Default-deny when membership is absent or ambiguous. A policy that reads a mutable lookup can produce different historical query results; retain audit evidence of the mapping used.

Account for derived exposure

Masking a direct account token is insufficient if a combination of postal code, event time and rare merchant makes the person identifiable. Review derived columns and small cohorts under the same classification rules. The classification contract decides which joins and exports are allowed, while the row and mask policies enforce the resulting boundary.

Recheck after schema changes

Adding a new sensitive column can bypass an explicit mask list if the view uses wildcard selection. Require an allowlist of exposed fields and run policy tests as part of the release gate. Confirm that an authorized role still sees the values it needs; over-masking can break operations and create unsafe workarounds.

Implementation

customers = [
    {"id": "cust-47", "region": "north", "token": "tok-4731"},
    {"id": "cust-83", "region": "west", "token": "tok-8394"},
]

def support_view(rows, assigned_region):
    return [{"id": row["id"], "region": row["region"], "token": "[masked]"}
            for row in rows if assigned_region is not None
            and row["region"] == assigned_region]

assert support_view(customers, "north") == [
    {"id": "cust-47", "region": "north", "token": "[masked]"}]
assert support_view(customers, None) == []

Performance and operating cost

A full in-memory scan is O(N) time and O(R) output for N rows and R authorized rows. A database may push row filters into indexed access, but complex policy functions can prevent pruning or add per-row work. Benchmark the authorized query plan without weakening the policy, and test the actual database role rather than only this reference function.

Common Mistakes

Do not test masking only through an administrator connection.
Do not make unknown region membership mean all regions.
Do not expose newly added columns through wildcard views.