Where the review stands after six sessions
The policy set over saltbox.analytics.customers now has five functional roles from session 2, a row access policy on region from session 3, masking with one exception role from session 4, a pseudonymised modelling view from session 5 and rotated service credentials from session 6. The written access design from session 1 sits in version control next to the policy definitions. Every control so far has been a thing you build once. An access review is a thing that has to keep happening, and the only way that works is if a machine does the comparing and a human does the deciding.
★ What you walk out with tonight. Three queries, each an anti-join between two tables you already have, that produce the three lists a reviewer actually needs: privileges the design never granted, privileges nobody used, and reads that should never have happened. A precise definition of standing privilege that matches the number the policy bench prints in session 8. And a scheduled job that writes the lists, checks the exception register, and refuses to publish quietly when the leak list is not empty.
LiveWhy the meeting finds nothing3 min▶
An access review has one job: find the grants that should not exist and take them back. The meeting version fails at it for a structural reason, not a lazy one. The reviewer is shown an inventory, and an inventory carries no signal on its own. A row saying fr_contractor_apac holds SELECT on mrr only means something next to a second document that says the contractor should not, and nobody brings that second document to the meeting because it lives in somebody's head.
Written down, the design is a table. The grants are a table. Query history is a table. Three tables give three pairwise comparisons, and each comparison is a query whose output is short, specific and actionable. The reviewer's job shrinks from "read 200 rows and feel uneasy" to "here are four rows, decide about each one". That is a review a person can actually do, and it is the same work the bench does when it prints a standing-privilege count.
Saltbox ran quarterly reviews for two years and signed every one. When the design grid was first joined to the live grants, the contractor role had held revenue in clear for nineteen months. It had been on every printed list. Nobody had ever been asked whether it should be there, because the list never said what should be there.
Self-studyThe three tables, and what each one knows3 min▶
- Grants - what every role can do right now. Dumped from the warehouse into a snapshot table with a date, so it can be diffed against last month. Knows nothing about whether the grant is wanted or used.
- Design - what every role should be able to do, as the grid of role against column with the values clear, masked and none. Lives in version control. Knows nothing about what is actually deployed.
- Usage - what every role actually read, from query history. Knows what happened, attributed to a user and their active roles. Knows nothing about whether it was allowed to happen.
- The exception register is the fourth table, and the smallest: grants that contradict the design on purpose, each with an owner, a reason and an expiry. It exists so that a justified deviation stops appearing as a finding without the design being edited to hide it.
Each table alone is an inventory. Each pair is a finding. That is the whole idea, and everything below is the mechanics of the pairs.
The three joins
10 min liveThree tables, three edges, three lists. Each edge is an anti-join, which is the SQL name for "rows in this table that have no partner in that one", and the absence of a partner is exactly what a finding is.
LiveGrants against design: over-grants and under-grants3 min▶
Dump the current grants to a table, one row per role, object, column and privilege. Join it to the design grid on role and column. Two lists fall out, and both matter.
- Over-grants - a role holds a column in clear that the design does not mark clear for that role. This is the classic finding, the one a leak grows out of, and the one every access review is nominally looking for.
- Under-grants - the design marks a column clear for a role and the role does not hold it. Nobody reports these, because nobody thinks of a missing grant as a security problem. It is an over-block waiting to be discovered by a support agent at 5pm, and the fix that agent finds will be a shared login or a colleague's export.
Both lists come from the same two tables and the same join, run in opposite directions. Producing only the first one means reporting one of the three counters and staying blind to the second.
LiveGrants against usage: the unused-privilege list3 min▶
Join the grants dump to query history, by role and column, over a window. A grant with no query behind it in 90 days is an unused privilege: something a role can read that nobody in that role has needed for a quarter. It might be legitimate and seasonal. It is far more often the residue of a project that ended, a person who moved teams, or a Friday-afternoon unblock that nobody took back.
The 90-day window is a convention rather than a law. Shorter windows flag quarterly reporting as unused. Longer windows let a leaver's grant sit for half a year. Ninety days lines up with a quarterly review cadence and catches most of what matters, and whatever window is chosen should be written down next to the query so the next person does not argue about it from scratch.
Unused is not the same as over-granted. A finance role that holds mrr in clear and has not queried it since the year-end close is unused but correctly granted. A contractor role holding mrr is over-granted whether or not it ever ran a query. The two lists overlap and neither replaces the other.
LiveUsage against design: leaks that already happened2 min▶
The third edge is the uncomfortable one. Join query history to the design and keep every read of a column by a role the design marks none. Each row is not a leak that might happen; it is a leak that did, with a timestamp and a user name attached. This is the list an incident review will ask for, and it is far better to have produced it yourself last night than to reconstruct it under pressure.
It is also the list that makes the case for the other two. An over-grant that has never been exercised is a risk. An over-grant that appears in this list is an event, and the difference in how people respond to those two words is the difference between a review that is tolerated and a review that gets its fixes deployed.
LiveStanding privilege, defined the way the bench counts it2 min▶
A standing privilege is a column a role holds in clear that the design does not mark clear for that role. That is the over-grant list from the first join, counted. It is deliberately independent of usage: a privilege is standing whether or not anybody has exercised it, because a compromised account does not care about the query history of its previous owner.
The policy bench over saltbox.customers counts exactly this, across five roles and ten columns. Fifty role-column cells, twenty of them marked clear in the design, so a single shared role holding every column in clear carries 30 standing privileges. Masking national_id for everybody takes it to 25 and masking email and phone outside support takes it to 17, because a masked column is no longer held in clear. Splitting the shared role five ways takes it to 1: the data scientist still holding a real customer_id where the design asked for a pseudonym. The finished design scores 0. Grant ACCOUNTADMIN on top of the finished design and the count goes straight back to 30, with every policy still defined and deployed, because a role above the policy layer holds every column in clear regardless of what the layer says.
The over-grant query at Saltbox and the bench's standing-privilege readout are the same definition applied to the same design. That is not a coincidence to be pleased about; it is the reason the review number and the scoreboard number can be put in the same monthly report without a footnote explaining why they differ.
Self-studyWhy an anti-join, and the two ways to write one3 min▶
Every list above is "rows in A with no matching row in B". The two idiomatic spellings are a LEFT JOIN with a null test on the right-hand key, and NOT EXISTS with a correlated subquery. Both produce the same rows. LEFT JOIN with IS NULL reads more naturally to people who write reporting SQL and is what the build-along uses. NOT EXISTS is safer when B can hold duplicate keys, because a LEFT JOIN against duplicates multiplies the rows of A before the null test removes them, which can make the counts wrong without making the list wrong.
Whichever spelling is used, the join keys have to be normalised first: the same column name in the same case, the same fully qualified object name, and role names as the warehouse stores them rather than as the design author typed them. Almost every false finding in a first review run is a case mismatch or a missing schema prefix, so the snapshot step should do the normalising once rather than every query doing it differently.
Evidence a reviewer can act on
7 min liveThree correct lists are still worthless if they land in the wrong shape. The shape that works is small, dated, diffed against last time, and delivered where the reviewer already looks.
LiveThe diff is the review packet, not the inventory2 min▶
A reviewer shown the full over-grant list every month sees the same forty rows they saw last month and signs, because forty stable rows carry no information about what changed. Shown the diff, they see three: two grants that appeared since the last run and one that was resolved. Three rows get read. Forty get signed.
So the job keeps yesterday's lists, computes new and resolved rows against them, and publishes the diff as the headline with the full list attached beneath it. The leader track's session 5 makes the same argument from the auditor's side: a 200-row list produces a signature and no information, and a signature is not evidence that anybody looked.
LiveThe exception register, and the job that fails on expiry2 min▶
Some over-grants are deliberate. A finance analyst covering for a colleague on leave holds a support column for six weeks. The design should not be edited to accommodate that, because the design is the statement of what the job needs, and the job did not change. Instead the grant goes into an exception register: a table with the role, the column, an owner, a reason, an approval date and an expiry.
| Column | What it is for |
|---|---|
| role_name, column_name | The exact over-grant row being excused, so the anti-join can exclude it by key |
| owner | A named person, not a team, who answers for it when the expiry arrives |
| reason | One sentence a reviewer can disagree with. "Business need" is not a reason |
| approved_on, expires_on | Expiry is mandatory. An exception with no end date is a design change wearing a disguise |
The register does two jobs in the pipeline. An unexpired exception removes its row from the over-grant list, so the reviewer sees only the unexplained findings. An expired exception fails the job, so the grant either gets removed or gets a new expiry with a new approval, and cannot quietly become permanent.
LiveThe honest limit: query history names a user and a role set, not a grant3 min▶
Query history attributes a read to a user and the roles that were active in the session. It does not say which grant permitted the read, because the warehouse aggregated privileges across the active roles and never had to decide. With a single primary role per session, the join by role and column is exact. Where secondary roles are common, one query can carry three roles and the read has to be credited to every role in the set that could have permitted it.
- For the unused-privilege list that credit makes the join conservative: a column read under a role set counts as used for every role in the set that holds it, so a privilege is only reported unused if no session that could have exercised it did. The list under-counts rather than falsely accusing, which is the right direction to be wrong in.
- For the usage-against-design list the same ambiguity cuts the other way: a read that was legitimate under one active role and forbidden under another is not a leak, so the join has to test whether any role in the set had the column marked clear or masked before flagging the row.
- Where the warehouse offers column-level access history as a separate view from query history, use it; it removes the guesswork about which columns a statement touched. The view names differ between warehouses and change between versions, so the snapshot step should be the only place that knows the vendor path.
Saltbox's first unused-privilege run flagged every column the finance role held, because the finance analysts ran everything with a secondary reporting role active and the join only looked at the primary. The fix was a one-line change to expand the role set before joining. The lesson was to check the list against one person you trust before sending it to a reviewer who does not know the roles.
Self-studyNightly, not quarterly, and published where people already look2 min▶
A quarterly review lets an over-grant live for ninety days before anybody sees it. A nightly job finds it the morning after it was made, when the person who made it still remembers why. The cost of running the three joins nightly is a few seconds of compute; the cost of running them quarterly is a quarter of exposure per finding.
Publishing means writing the three lists and their diffs to a review schema that the reviewer's existing tooling can read, and posting the diff summary into whichever channel the platform team already watches. A report that requires opening a new dashboard is a report that gets opened before the audit and at no other time. The job's failure, when it fails, should page the review owner the same way a broken pipeline would, because that is what it is.
Build the review job
★ 20 min · everyone buildsEverything below runs against a review schema and standard SQL. The only vendor-specific part is where the grants and the query history come from: the system views that expose them are called something different in every warehouse, and column-level attribution may live in a separate access-history view. Load whatever yours exposes into the snapshot tables, and keep that loading step as the single place that knows the vendor path.
Dump the grants into a dated snapshot. One row per role, object, column and privilege. Table-level SELECT expands to one row per column; a column with a masking policy that applies to the role is recorded with in_clear = false, because a masked column is not held in clear and should not count as a standing privilege.
Load the access design as a table. The grid from session 1 lives as a CSV next to the policies, one row per role and column with the value clear, masked or none. Loading it from the repo rather than retyping it is what keeps the pipeline honest: a change to the design is a pull request, and the next nightly run scores against it.
Write the over-grant query as an anti-join. Every row this returns is a standing privilege by the bench's definition: a column the role holds in clear that the design does not mark clear. Unexpired exceptions are excluded by key. Run the under-grant query alongside it, in the opposite direction.
Write the unused-privilege query. Grants joined to 90 days of query history by role and column, keeping the grants with no read behind them. The query_history table here is a snapshot you loaded, with the role set already expanded to one row per active role, so the join credits a read to every role that could have permitted it. Date arithmetic is spelled differently across warehouses; dateadd is one common form.
Write the usage-against-design query. Reads of a column by a role whose design row is none, or missing. Each row is an event with a first-seen timestamp and a count, which is the shape an incident review wants. Where a session carried several roles, the read is only a leak if none of them had the column marked clear or masked, which the not exists handles by testing the whole set.
Write the exception-expiry check. The register is small and the check is one query. Any row it returns is a grant that was excused for a period that has ended, and the job treats it exactly like a leak: it stops.
Wire the five into one scheduled job. Order matters: snapshot, then the three lists, then the diff against yesterday, then the gate, then publish. The gate is one count, and the scheduler treats any non-zero value as a failed run that pages the review owner. Nothing publishes on a failed run, so a reviewer never reads a packet that was produced while a leak was live.
Schedule the whole sequence nightly with whatever runs your other pipelines, so it inherits their alerting. The publish step writes the three lists and the diff to the review schema and posts the diff summary where the platform team already reads. The failure path should look exactly like any other broken pipeline, because that is what an unexplained leak is.
Test the job against a known bad state before trusting it. Grant a column to a role the design marks none, run the job, and confirm the over-grant list grows by one and the gate stays green. Then run a query under that role and confirm the gate fails. A review job that has never been seen to fail is indistinguishable from one that cannot, and the second kind is the more common of the two.
Before session 8
45 min- Run the over-grant query against your own table and design, and count the rows. That number is your standing-privilege count, and it should be the first thing you compare against the bench next session.
- Run the unused-privilege query with a 90-day window, then with 30 and 180. Write down which grants move between the lists and why, and pick a window with a written reason.
- Find every role in your estate that holds a column the design marks none and has a read in the last 90 days. If the list is not empty, that is tomorrow morning's first conversation, not a homework note.
- Create the exception register and move every "temporary" grant you know about into it with an owner and an expiry. Any grant nobody will own goes on the over-grant list, which is where it belonged.
- Break the job on purpose: grant something wrong, run it, watch it fail, revert. Keep the failure output as the first entry in the review evidence folder.
Three questions
Role fr_finance holds mrr in clear, the design marks it clear, and no finance query has touched it in 90 days. Role fr_contractor_apac holds mrr in clear, the design marks it none, and the contractor queries it weekly. Which is a standing privilege?
Standing privilege is defined against the design, not against usage. The finance grant is unused but correct; the contractor grant is a standing privilege whether or not it is exercised, and its weekly reads also put it on the leaks-observed list. Two different lists, two different findings.
The finished policy set scores 0 standing privileges on the bench. ACCOUNTADMIN is granted to unblock somebody and every policy stays defined and deployed. What does the over-grant query return the next morning?
The ACCOUNTADMIN row of the bench is identical to the opening state: 109 leaks and 30 standing privileges. The policies are all still there and none of them apply, and a snapshot that expands the top role's grants will show every column in clear.
A finance analyst covering for a colleague needs a support column for six weeks. Where does that decision go?
The design states what the job needs, and the job did not change. The exception register holds the deviation with an end date, and the expiry check turns a forgotten temporary grant into a failed run instead of a permanent one.