Masking Sensitive Output
The result of a script "query" may contain columns that must not appear in a report, an
export, or an agent's context: email, ssn, phone, or a *_enc column. A mask {} block redacts those
columns before the result leaves Atlas.
Test datasets use the same mask blocks to mask the rows they load.
Examples
Common masked reads as code. Each tab is a complete script, and each Example execution block shows an end-to-end
run on SQLite.
- Masked export
- Default and override
- Shared mask library
This exports customer rows with the identity columns hashed, the card's last four kept, and the phone digits replaced:
# Reusable, named mask: hash identity columns with a fixed salt so tokens stay
# joinable across reports (define once, apply with `use`).
mask "identity" {
columns = ["email", "ssn"]
method = HASH
salt = "prod-secret"
}
script "query" "customer_export" {
query "rows" {
sql = "SELECT id, name, email, ssn, card, phone FROM customers ORDER BY id"
format = CSV
# Apply the reusable identity mask...
mask { use = [mask.identity] }
# ...plus per-column masks: keep the card's last 4, star the phone digits.
mask {
columns = ["card"]
method = PARTIAL
keep_right = 4
}
mask {
columns = ["phone"]
method = REPLACE
match = "[0-9]"
with = "#"
}
}
}
Example execution
The example is self-contained on SQLite. Set up a database with sqlite3 export.db < setup.sql:
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, ssn TEXT, card TEXT, phone TEXT);
INSERT INTO customers VALUES
(1, 'Ada Lovelace', 'ada@example.com', '123-45-6789', '4111111111111111', '555-0100'),
(2, 'Bob Stone', 'bob@example.com', '987-65-4321', '4222222222222222', '555-0199');
Run the script:
atlas script query --url "sqlite://export.db" --file "file://export.hcl" --run customer_export --quiet
1,Ada Lovelace,71f2179ddbb6c48f28aca29b80efe46b136ce9568dd75041a98ad482be6ca2fc,f63009387803b2964829857acedf2a92374d7fe00c942020180b129a4f3c646e,************1111,###-####
2,Bob Stone,30afa44bf93ac9486cf6d5c225bd57bd2ac5a4c7175ae1b31fadc3859daeb300,239d31c6bcb443ca87fa5a32702bdf112421223126cbe3c24a77ecb8889c0f4e,************2222,###-####
The reusable identity mask turns email and ssn into keyed HMAC-SHA256 tokens. Because the salt is fixed, the
same value hashes identically everywhere, so masked columns stay joinable. The inline PARTIAL keeps the card's last
four, and the inline REPLACE stars every phone digit while leaving the dash. id and name match no mask, so they
pass through. Masks apply in declaration order, and the first match wins.
A mask {} at the script level applies to every query in the script. This sets a REDACT default on email, and
lets one query override it with a narrower mask:
script "query" "support_report" {
# Script-level default: applied to every query below.
mask {
columns = ["email"]
method = REDACT
}
query "all_customers" {
sql = "SELECT id, email, phone FROM customers ORDER BY id"
format = CSV
}
# This query's own mask wins for the columns it matches.
query "one_customer" {
sql = "SELECT id, email FROM customers WHERE id = 1"
format = CSV
mask {
columns = ["email"]
method = PARTIAL
keep_left = 3
}
}
}
Example execution
The example is self-contained on SQLite. Set up a database with sqlite3 support.db < setup.sql:
CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, phone TEXT);
INSERT INTO customers VALUES
(1, 'ada@example.com', '555-0100'),
(2, 'bob@example.com', '555-0199');
Run the script:
atlas script query --url "sqlite://support.db" --file "file://support.hcl" --run support_report --quiet
The first section inherits the script default and prints ***; the second keeps the first three characters of the
address. phone matches no mask, so it passes through:
1,***,555-0100
2,***,555-0199
1,ada************
A set of files parsed together forms one document, so masks can live in a library file that every script references
with use:
mask "pii" {
columns = ["email", "ssn"]
method = REDACT
}
mask "contact" {
columns = ["phone*"]
method = PARTIAL
keep_right = 4
}
script "query" "customers" {
query "rows" {
sql = "SELECT id, email, ssn, phone FROM customers ORDER BY id"
format = CSV
mask { use = [mask.pii, mask.contact] }
}
}
Example execution
The example is self-contained on SQLite. Set up a database with sqlite3 library.db < setup.sql:
CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, ssn TEXT, phone TEXT);
INSERT INTO customers VALUES
(1, 'ada@example.com', '123-45-6789', '555-0100'),
(2, 'bob@example.com', '987-65-4321', '555-0199');
Point --file at the directory so both files parse together:
atlas script query --url "sqlite://library.db" --file "file://scripts" --run customers --quiet
1,***,***,****0100
2,***,***,****0199
Because the definitions live in one file, changing a mask updates every script that uses it.
Selecting Columns
columns is a list of output-column names. An entry with a glob metacharacter (*, ?, [...]) is a glob (the same
wildcard style as schema include/exclude); an entry without one behaves as an exact name.
Every entry is matched with the same path-style matcher, so a backslash escapes the next character and a * never
crosses a /. A column is masked when its name equals an exact entry or matches a glob. For example,
columns = ["*email*", "phone*"] matches email, contact_email, and phone, and leaves name untouched.
A mask that matches no output column is a no-op, not an error: Atlas never parses the SELECT, so the result columns
are unknown until the query runs and there is nothing to validate against. Masking applies regardless of the column's
SQL type: a masked value serializes as text (a masked int renders as text, and a BLOB is masked over its 0x...
hex form).
NULL normally passes through unmasked: there is nothing to leak, and masking it to a token would hide the fact that
the value is absent. This relies on the driver reporting the column's type, so on SQLite a NULL is masked anyway
when the column is a BLOB, is declared without a type, or is an expression rather than a plain column reference
(SELECT NULL AS lit). Alias and type such columns, or expect the mask token in place of the empty field.
Methods
Four methods are supported. Each method accepts only its own parameters: a parameter that does not apply to the
chosen method is a parse-time error, not silently ignored, for example
mask: `salt` is not valid for method REDACT.
REDACTreplaces the whole value with a fixed token (***by default; override withtoken).PARTIALkeeps characters at the start and end and replaces the rest withchar(*by default).keepsets a symmetric window on both ends;keep_left/keep_rightset them asymmetrically. A value with no characters left to mask between the kept ends is fully masked, so nothing leaks.HASHreplaces the value with a hex digest. Withsaltthe digest is a keyed HMAC-SHA256; withoutsaltit is a bare SHA256, which is recoverable for low-entropy values such as an SSN or a phone number, so supply a secretsaltfor sensitive columns. The digest is deterministic, so equal inputs mask to equal tokens: masked columns stay joinable, groupable, and correlatable across queries, runs, files, and even database engines.REPLACEapplies a regular-expression substitution (matchis the pattern,withthe replacement). Unlikecolumns, which selects which columns to mask,matchoperates on the value, so values can be redacted by pattern.
For example, each tab below masks one column of the row
(1, 'ada@example.com', '123-45-6789', '4111111111111111', '555-0100'):
- REDACT
- PARTIAL
- HASH
- REPLACE
script "query" "u" {
query "u" {
sql = "SELECT id, email, ssn FROM users WHERE id = 1"
format = CSV
mask {
columns = ["email"]
method = REDACT
}
}
}
1,***,123-45-6789
script "query" "u" {
query "u" {
sql = "SELECT id, card FROM users WHERE id = 1"
format = CSV
mask {
columns = ["card"]
method = PARTIAL
keep_right = 4
}
}
}
1,************1111
script "query" "u" {
query "u" {
sql = "SELECT id, email FROM users WHERE id = 1"
format = CSV
mask {
columns = ["email"]
method = HASH
salt = "prod-secret"
}
}
}
1,71f2179ddbb6c48f28aca29b80efe46b136ce9568dd75041a98ad482be6ca2fc
script "query" "u" {
query "u" {
sql = "SELECT id, phone FROM users WHERE id = 1"
format = CSV
mask {
columns = ["phone"]
method = REPLACE
match = "[0-9]"
with = "#"
}
}
}
1,###-####
Masking Entities
entities masks the entities found in a value, such as email addresses and phone numbers, and keeps the rest
of the value. It takes a list of entity names, or ALL for every entity:
| Entity | Matches |
|---|---|
CREDIT_CARD | Card numbers of 13 to 19 digits, grouped or not, that start with a card issuer's prefix and pass the Luhn checksum. |
EMAIL | Email addresses. |
IBAN | International bank account numbers of their country's length that pass the mod-97 checksum. |
IP | IPv4 and IPv6 addresses. |
MAC | MAC addresses: 00:1A:2B:3C:4D:5E, 00-1A-2B-3C-4D-5E, or 001a.2b3c.4d5e. |
PHONE | International numbers of 8 to 15 digits starting with +, and North American numbers written with separators, such as (415) 555-0132, 415-555-0132, or 415.555.0132, whose area code and exchange start with 2 to 9. |
SSN | US Social Security numbers, 123-45-6789 or 123 45 6789, except numbers never issued: area 000, 666, or 900 to 999, group 00, or serial 0000. |
URL | URLs starting with http://, https://, ftp://, or www.. |
The method applies to each entity on its own:
REDACT, the default, replaces each entity with its name in angle brackets, such as<EMAIL>, or withtokenwhen set.PARTIALandHASHmask the characters of each entity, and keep the text around it.REPLACEdoes not takeentities.
Entities that overlap are masked as one, under the name of the one that starts first.
This script masks the email addresses and phone numbers written in a free-text column:
script "query" "notes" {
query "rows" {
sql = "SELECT id, body FROM notes ORDER BY id"
format = CSV
mask {
columns = ["body"]
entities = [EMAIL, PHONE]
}
}
}
Example execution
The example is self-contained on SQLite. Set up a database with sqlite3 notes.db < setup.sql:
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes VALUES
(1, 'Call +1 (415) 555-0132 or mail ada@example.com'),
(2, 'Nothing to hide');
Run the script:
atlas script query --url "sqlite://notes.db" --file "file://notes.hcl" --run notes --quiet
1,Call <PHONE> or mail <EMAIL>
2,Nothing to hide
Each entity is replaced by its name, and a value holding none passes through unchanged.
Entities are recognized by their format and checksum, not by meaning: names and street addresses are not entities, and an entity written in an unusual format stays unmasked. Mask a column holding only sensitive values with a whole-value mask instead.
Precedence and Scopes
When more than one mask matches a column, the first one in declaration order wins. A query's own masks are consulted
first (with any use references expanded in place), then the script-level defaults. So a narrow query mask overrides a
broad default, and within one use list the leftmost entry takes the column.
A mask {} on a query masks that query. A mask {} at the script "query" level is a default applied to every
query in the script, and a query's own mask overrides the default for the columns it matches, as the
default and override example shows.
Reusable Masks
Declare a mask once at the top level, give it a name, and apply it with use, instead of copying the same
columns, method, and salt into every query. A top-level mask "<name>" is a definition only; it is never
applied on its own, only through use = [mask.<name>, ...].
Inline columns/method and use are mutually exclusive: a mask {} block is either inline or a reference, and
mixing them reports one error per conflicting attribute (use, columns attributes/blocks are mutually exclusive).
Only columns and method are reported this way. Any other parameter set alongside use, such as salt, token,
or keep_right, is dropped without an error, so keep a use block to use alone and change the definition when you
need different parameters. For example:
mask "pii" {
columns = ["email", "ssn"]
method = REDACT
}
script "query" "customers" {
query "rows" {
sql = "SELECT id, email, ssn FROM customers ORDER BY id"
format = CSV
mask { use = [mask.pii] }
}
}
Because the mask is defined in one place, a HASH mask's salt is defined in one place too: the same input masks to
the same token in every query that uses it. Pin the salt (sourced from a variable, KMS, or a secret manager) to keep
tokens comparable.
Unlike a columns typo, which can only no-op, a use that names a mask that does not exist is a parse-time error,
since names are known up front: This object does not have an attribute named "nope".
When no top-level mask is in scope at all, the error names the whole object instead, There is no variable named "mask",
even for a name that is spelled correctly. That is the signal that the defining file was not passed to --file.
A top-level mask can group other masks with use, instead of selecting columns itself. A group expands in
place, in order, so the first mask of the group that matches a column takes it. Here, customer groups the
other two, and a query applies all of them with mask { use = [mask.customer] }:
mask "pii" {
columns = ["email", "ssn"]
method = REDACT
}
mask "phones" {
columns = ["phone*"]
method = PARTIAL
keep_right = 4
}
mask "customer" {
use = [mask.pii, mask.phones]
}
A group holds use alone: setting any other attribute alongside it is an error, such as
use, token attributes/blocks are mutually exclusive. A mask that uses itself, directly or through another
group, is an error even when nothing applies it: mask "customer" uses itself.
A named mask's parameters are validated only when something uses it: an unused definition with invalid
parameters parses without complaint, and the error surfaces on the first use.
A set of files parsed together forms one document, so a mask "<name>" declared in one file is usable via use from
a sibling. A file may hold only top-level mask definitions that scripts in other files reference, as the
shared mask library example shows. Point --file at the directory (or list each file) so both parse
together.
How it Works
Masking is a post-processing pass over the rows Atlas has already read. Each run goes through three steps:
- The query executes as written. As everywhere in Data Scripts, the SELECT is never rewritten.
- Atlas reads the real values into memory and applies the masks to the result. Columns are selected by output name, matched case-sensitively, so you alias in the SELECT and mask the alias.
- The masked result is serialized per
formatand printed.
Masking therefore protects what leaves Atlas, not the data at rest. The raw value is still read from the database, so a mask is not a substitute for restricting the query itself.