Case Study 2: Sixty-Two Identities, Twenty-Three Users

"The quarterly access review was a spreadsheet with forty-seven rows and a column of tick boxes. It was completed on time, every quarter, for two years, by a manager who could not have told you what any of the rows did."

Executive Summary

Kestrel passed a quarterly access review for eight consecutive quarters. The review was a spreadsheet of 47 roles and a manager's signature, and it had never once resulted in a grant being removed.

An audit prompted by an unrelated question — "who can see customer email addresses?" — found:

identities with SELECT on gold                              62
identities that had queried gold in 90 days                 23
roles granted to exactly one user                           19
grants added during an incident and never reviewed          14
identities nobody could attribute to a person or system      4

Thirty-nine identities had access they did not use. Four could not be attributed to anyone at all, and two belonged to people who had left the company — one nine months earlier.

The fix was to stop reviewing against a list and start reviewing against usage, which turns an attestation into a decision. Twenty-eight grants were removed in the first pass. Nothing broke.

Skills applied: grant sprawl (§30.5); reviewing against usage rather than intent (§30.12); Chapter 25 §25.12's query-log method, applied to permissions.

Background

The review, as it existed. Every quarter a spreadsheet was generated listing 47 roles with their members, and a manager ticked each row to attest that the access was appropriate.

It was completed on time, every quarter, for two years.

Three reasons it produced nothing, and each is worth naming because the review looked entirely reasonable:

The unit was wrong. A role name — analytics_readonly, etl_service, migration_2025_03 — tells a manager nothing about what it can see or whether anyone uses it. Attesting to a name is attesting to a name.

The default was "approve." Removing a grant risks breaking something; approving one risks nothing visible. The asymmetry means a review with no evidence resolves to approval every time, and this is not a failure of diligence — it is the correct response to having no information.

Nothing measured the outcome. Eight quarters, zero removals, and nobody noticed that zero removals across two years is itself a finding.

⚠️ Failure Mode — an attestation is not a review

The distinction is precise and it is the whole case study.

An attestation asks: do you approve this? The reviewer has a list and a signature, and the only information they bring is their own memory.

A review asks: here is evidence; what should change? The reviewer has facts they did not have before.

Kestrel's spreadsheet was an attestation wearing a review's name, and it satisfied a compliance requirement perfectly while providing no assurance whatsoever — which is Chapter 23 Case Study 2's "measure whether a control is OPERATING, not whether it EXISTS," arriving again in a fourth department.

The test that distinguishes them: could the reviewer's answer have been "no"? If the reviewer has no basis on which to decline any row, the exercise cannot produce a removal, and an exercise that cannot produce its intended outcome is not performing its function regardless of how reliably it completes.

And the metric that would have caught it in quarter two: removals per review. Zero, eight quarters running, is either a perfectly-provisioned system or a review that does nothing — and nobody was looking at the number that distinguishes them.

The Problem

The trigger was a question, not an incident. Legal asked, in the course of unrelated work, "who can see customer email addresses?"

Nobody could answer. The role list was available; what each role could actually reach was not, because grants had accumulated across four Terraform applies, two migrations, and an unknown number of console changes (Chapter 28 §28.10).

The query that answered it took twenty minutes to write and returned 62 identities.

-- Everything that can reach a column classified `confidential`.
SELECT DISTINCT m.grantee_name AS identity
  FROM snowflake.account_usage.grants_to_roles g
  JOIN snowflake.account_usage.grants_to_users m
    ON m.role = g.grantee_name
 WHERE g.granted_on IN ('TABLE', 'VIEW')
   AND g.deleted_on IS NULL
   AND g.name IN (SELECT object_name
                    FROM governance.classified_objects
                   WHERE tier = 'confidential');

Then the second query, which is the one that mattered:

-- Of those, which have actually run a query recently?
SELECT user_name, MAX(start_time) AS last_query
  FROM snowflake.account_usage.query_history
 WHERE start_time > dateadd(day, -90, current_timestamp())
 GROUP BY 1;

Twenty-three.

The Analysis

Step 1: characterize the 39.

39 identities with access and no usage in 90 days
├── 14  people who had changed roles internally           <- reorganizations
├──  9  service accounts for decommissioned integrations
├──  6  contractors whose engagements had ended
├──  4  UNATTRIBUTABLE - no owner, no documentation       <-
├──  4  seasonal or quarterly users, legitimately idle    <- keep
└──  2  people who had left the company                   <-

The last two rows matter in opposite directions.

Two departed employees retained access for nine and four months. Offboarding revoked their SSO, which removed interactive access — but both had personal access tokens created for scripting, and those were not tied to the SSO session. Neither had been used, which is the only reason this is a case study about process rather than about a breach.

Four legitimately idle users — a quarterly reporting analyst, an external auditor, and two seasonal merchandisers. A 90-day review window would have removed all four, which is Chapter 25 §25.12's lesson arriving in permissions: the window must be longer than your slowest legitimate consumer.

🔎 Read the Plan — the four unattributable identities are the most important finding

Nine service accounts for decommissioned integrations are untidy and were straightforward to remove. The four nobody could attribute were different, and the team's handling of them is the part worth copying.

What was known: a name (svc_reporting_2, dataload, etl_user, bi_connector_old), a creation date, and in two cases a last-query timestamp from over a year earlier.

What was not known: who created them, what created them, what they were for, or whether anything would break.

The wrong response is to delete them, which is tempting and risks an outage in something nobody remembers. The wrong response is also to leave them, which is what had happened for years.

What Kestrel did, and it is a good pattern for any unattributable resource:

1. Revoke the grants but keep the identity. Reversible in seconds.
2. Alert on any authentication attempt by them. "If something is using this, it will tell us."
3. Wait one full business cycle — a quarter, chosen to cover monthly and quarterly jobs.
4. Then delete, with the wait recorded.

Two of the four produced an authentication attempt. One was a vendor's reporting connector that ran quarterly — legitimate, undocumented, and it would have been an incident if the identity had simply been deleted. The other was a scheduled export nobody had known existed, feeding a spreadsheet a team still used.

The other two were silent for the full quarter and were deleted.

The generalizable move: for anything you cannot attribute, remove the capability and keep the observability. It converts "we do not know" into "we will find out within a cycle," at almost no risk — and it applies equally to a table, a DAG, a dashboard, or a firewall rule.

Step 2: why did the grants accumulate? The audit traced each of the 14 incident grants:

"the analyst can't see the new table"           4
"the migration script needs write"              3
"the vendor's tool needs read on gold"          2
"give the BI account SELECT ANY, narrow later"  1
other                                           4

"Narrow later" appears once explicitly and is the shape of all fourteen. Every one was a correct response to an urgent problem, and narrowing has a cost (something might break) and no visible benefit — so it never happened. The alerting ratchet from Chapter 25 Case Study 2, arriving in permissions.

Step 3: the nineteen single-member roles. Each had been created because the existing roles did not quite fit, which is a reasonable local decision that produces, in aggregate, a role model that describes individuals rather than job functions — at which point it provides no abstraction and all of the overhead.

The Decision

Five changes.

One: review against usage. The spreadsheet was replaced by a generated report:

ACCESS REVIEW - 2026-Q3
  identities with access to `confidential` data          62
  ...that queried it in the last 400 days                27
  ...that did NOT                                        35   <- decide these

  For each: name, owner, last query, grant age, grant reason,
            and the classified objects the grant reaches.
  Default action: REVOKE. Tick to keep, with a reason.

The default is now revoke, and the reviewer must justify keeping rather than removing. That single inversion is most of the fix, and it works because it puts the burden of evidence on the side that carries the risk.

Two: a 400-day window, not 90 — longer than the slowest legitimate consumer.

Three: incident grants expire. Any grant tagged incident gets seven days and a calendar entry. This removed 14 of the 47 roles within a quarter.

Four: personal access tokens are tied to employment. Offboarding revokes tokens, not just SSO.

Five: the metric. Removals per review, published. Zero is now a finding rather than a non-event.

📐 Design Decision — inverting the default, and what it costs

The change from "tick to approve" to "tick to keep" was contested, and the objection was fair: it moves work onto reviewers and it will occasionally remove access somebody needed.

Both are true. The first review took a manager about ninety minutes instead of ten. Two grants were removed that had to be restored, both within a day, both because a quarterly process ran shortly afterwards.

The argument that won has two parts:

The asymmetry was already there, pointing the wrong way. Under the old default, the cost of a wrong decision was invisible (an unnecessary grant persists forever) and the cost of a right one was visible (something might break). Inverting the default does not create an asymmetry; it points the existing one at the outcome you want.

Restoring access is cheap and reversible; the alternative is not. A grant removed in error is restored in minutes. A grant retained in error is retained for years, and its cost appears in a different quarter as an unanswerable question from legal.

What made it acceptable in practice was making restoration trivially fast — a documented, pre-authorized path (Chapter 26 §26.8) where any of four people can restore a previously-held grant without a ticket. Without that, the inversion would have been resented and routed around, and the objection would have been correct.

The general principle: before inverting a default, make recovery from the new default's mistakes cheaper than recovery from the old one's. Otherwise you have moved the pain rather than reduced it.

What Happened

Twenty-eight grants removed and two restorations. The team's assessment is that two restorations out of twenty-eight removals is close to the right error rate — zero would suggest the review was still too cautious.

The question that started it now has an answer, generated rather than assembled:

Who can see customer email addresses?
  34 identities, of which 27 have queried in 400 days.
  All 34 attributed to a person or a documented system.
  Report: platform/governance/access_review.py --classification confidential

Two further findings:

The reviewer had never seen what the roles could reach. The old spreadsheet listed role names; the new report lists the classified objects each role can access. Two of the 47 turned out to reach data the manager did not know was in scope, and neither was inappropriate — but the manager had been attesting to something they could not see.

And the review found a grant Terraform did not know about. A console-created grant on gold.dim_customer, made during an incident eleven months earlier and never reflected in code — exactly Chapter 28 §28.10's drift, discovered from the other direction, and the trigger for adding grants to the nightly drift check.

Lessons

1. An attestation is not a review. The test: could the reviewer's answer have been "no"? If they have no basis to decline any row, the exercise cannot produce a removal.

2. Zero removals across eight quarters is a finding. Nobody was looking at the number that distinguishes a well-provisioned system from a review that does nothing.

3. Review against usage, not against a list. 62 granted, 23 used — and the 39-identity gap is the only actionable number in the exercise.

4. The window must be longer than your slowest legitimate consumer. A 90-day window would have removed a quarterly analyst, an auditor, and two seasonal users. 400 days.

5. Invert the default to revoke — but 📐 first make restoration cheap and pre-authorized, or the inversion is resented and routed around, and the objection to it is correct.

6. Two restorations out of twenty-eight removals is about the right error rate. Zero would mean the review is still too cautious.

7. 🔎 For anything you cannot attribute: remove the capability, keep the observability. Revoke, alert on authentication, wait a full business cycle, then delete. Two of Kestrel's four unattributable identities announced themselves, and one would have been an incident.

8. Offboarding that revokes SSO does not revoke personal access tokens. Two departed employees retained working credentials for nine and four months.

9. "Narrow it later" never happens, because narrowing has a cost and no visible benefit. Expire incident grants automatically — it removed 14 of 47 roles.

10. Nineteen single-member roles means the role model describes individuals, not job functions — no abstraction and all of the overhead.

11. The reviewer had been attesting to role names, not to what those roles could reach.

12. The review found drift Terraform did not know about, which is Chapter 28 §28.10 arriving from the permissions side.

Questions for Discussion

1. The old review completed on time for eight quarters and satisfied its compliance requirement perfectly. Was it worthless, or was it doing something this case study does not credit?

2. "Could the reviewer's answer have been no?" is offered as the test. Apply it to three other recurring reviews in your organization.

3. Inverting the default moved reviewer time from 10 minutes to 90. At what point does that cost exceed the benefit, and how would you know?

4. Two of four unattributable identities announced themselves within a quarter. What would you have done about the two that stayed silent — and how confident are you?

5. The 400-day window was chosen to cover a quarterly analyst. What is the longest legitimate idle period in your systems, and does anything currently accommodate it?

6. Departed employees retained access via tokens not tied to SSO. What other credentials in your systems survive an offboarding?

7. This is now the fourth department in which "measure whether a control is operating, not whether it exists" has appeared. Is there a single audit that would find all four at once?