Running an Access Review From the Audit Log

An access review is usually described as a compliance chore and is actually the only mechanism that removes permissions once they have been granted. Done from a permission audit trail it takes an afternoon; done by exporting spreadsheets from three systems it takes a quarter and happens once. This page is part of the designing role-based access control systems guide.

What the Review Is Actually Asking

Strip away the framework language and a review answers four questions, each of which is a query against the grant table and the append-only permission log.

The four questions an access review answers Who holds elevated access, who was granted access since the last review, whose access has not been used, and which grants have no owner or reason recorded. Who is elevated admin and above right now What changed grants since the last review What is unused granted but never exercised What is unowned no reason, no approver recorded
The last two columns are where the removals come from. The first two are what an auditor asks to see.

The Queries

Each question is a small query if the model carries the fields discussed in the RBAC guide — a scope on every grant, an actor and reason on every change, and a usage timestamp.

-- 1. Who currently holds elevated roles, and who granted them?
select u.email, g.role, g.scope_id, g.granted_by, g.granted_at, g.reason
from grants g join users u on u.id = g.user_id
where g.role in ('admin', 'billing_admin', 'support_impersonate')
order by g.granted_at desc;

-- 3. Grants that have never been exercised since they were made.
select u.email, g.role, g.scope_id, g.granted_at
from grants g join users u on u.id = g.user_id
where g.last_used_at is null and g.granted_at < now() - interval '90 days';

The third query is the one that produces action. A permission granted three months ago and never used is either unnecessary or attached to a workflow that has moved, and both conclusions lead to removal. Recording last_used_at when a permission is exercised costs one cheap write and is what makes the review productive rather than descriptive.

Deciding What to Revoke

Reviews stall when every entry needs a judgement. Reduce the judgement to a default and a small number of exceptions.

Default to removal for anything unused beyond a threshold, anything with no recorded reason, and anything belonging to a person who has changed role since the grant. Ask the owning team to justify keeping it rather than asking them to approve removing it — the asymmetry matters, because silence should shrink access rather than preserve it.

Reserve individual judgement for elevated roles, where the question is not “is this used?” but “should this person still hold it?”. That list should be short enough to read aloud in a meeting; if it is not, the model has too many elevated roles and that is the finding.

Batch the removals and schedule them with notice. Revoking fifty permissions at once without warning generates a wave of interruptions; announcing “these are being removed on Friday unless you object” turns the same action into a self-service confirmation.

Closing the Loop

The review cycle from query to evidence Queries produce a candidate list, owners confirm or justify, removals are executed through the normal grant path so they are audited, and the resulting log entries are the evidence. 1 · query candidate list 2 · confirm owners justify 3 · revoke via the normal path 4 · evidence the log itself Revoking with ad-hoc SQL breaks step four — the removal never appears in the trail.
Every revocation must go through the same code path as a grant, or the review produces changes with no record that they happened.

The evidence an auditor wants is not a spreadsheet you assembled but the log entries themselves: who reviewed, what was decided, what was removed and when. If revocations flow through the normal grant path, that evidence produces itself.

Reviewing Joiners, Movers and Leavers

Most access problems are created by people changing roles rather than by anyone being granted something inappropriate. A review that only looks at current holders misses the mechanism that produced them.

Joiners are the easy case: a default role applied by the invitation flow, granted through the audited path, with anything beyond it requested explicitly. Check that the default has not quietly grown — a capability added to the default role is granted retroactively to everyone who ever joined, with no audit event to show for it.

Movers are where the accumulation happens. Someone moves from support to engineering and keeps the support tools, then moves to a management role and keeps both. Query for users whose grants span roles that no single job function needs, and treat that combination as the finding rather than any individual grant.

Leavers should be automatic, and the review is where you verify that they are. Check for grants belonging to deactivated accounts, and for service accounts whose named owner has left — the second is the more common gap, because deactivating a person rarely triggers any thought about the integrations they set up.

Where access accumulates across a person's tenure A joiner receives a default role, each move adds the new role without removing the old one, and a departure leaves service accounts and integrations behind unless they are explicitly reassigned. Joiner default role Move 1 adds, rarely removes Move 2 now holds three sets Leaver integrations remain The review's job is to notice the third box; the process's job is to stop it forming.
Nobody grants someone three overlapping job functions deliberately — it happens one reasonable step at a time.

Making the Next One Cheaper

The best outcome of a review is that the next one is smaller. Three changes do most of that work.

Add expiry to grants that are inherently temporary — incident access, contractor accounts, project-scoped permissions — so they disappear without anyone deciding. A large share of what a review removes should never have persisted in the first place.

Require a reason at grant time. It costs the requester a sentence and saves the reviewer an investigation, and the absence of a reason becomes a clean default-to-remove signal rather than a judgement call.

Fix the causes you find. If a review keeps removing the same role from the same team, the workflow that needs it should be changed rather than the grant repeatedly revoked and re-requested. Reviews that only remove access, without feeding anything back into the model, get slowly larger each cycle instead of smaller.

Presenting the Result

The review’s output is usually read by three audiences with different needs, and producing one document for all of them satisfies none.

Engineering wants the list of what changed and why, which is the log itself with a filter. Team leads want a per-team view: who holds what, which grants were removed, and which they need to justify. Compliance wants a summary — scope, date, participants, decisions and exceptions — with a pointer to the underlying records rather than the records themselves.

Generate all three from the same queries rather than assembling them by hand. A review that requires an afternoon of formatting will drift towards being skipped, and the formatting is exactly the part a script does well.

Keep the previous cycle’s output alongside the current one so the two can be compared. The most useful signal a review produces is not its own list but the difference from last time: a category that keeps reappearing is a process problem rather than an access problem, and it will not be fixed by removing exactly the same grants again next quarter.

Record the exceptions explicitly. Every review produces a handful of grants that are kept despite matching a removal rule, and those need a reason and a review date attached — otherwise the same exception is re-argued from scratch every cycle, and eventually granted permanently because nobody remembers why it was questioned.

Frequently Asked Questions

How often should a review run?

Quarterly for elevated roles and annually for ordinary ones is a common and workable cadence. What matters more than the interval is that it is cheap enough to actually happen — a review that takes a week will slip, while one that takes an afternoon because the queries are ready will run on schedule and stay useful.

What if the audit log does not go back far enough?

Then the first review establishes a baseline rather than reconstructing history: record the current state, note that grants before a certain date have no recorded reason, and default those to removal unless someone claims them. Set the retention to cover your longest obligation from that point, so the second review has the history the first one lacked.

Who should confirm each grant?

The owner of the capability rather than the holder’s manager. The person who understands what billing:refund allows is best placed to say who needs it, and asking a line manager to approve a list of permission names they cannot interpret produces approvals that mean nothing. That is also why every capability should carry an owner in the model.

Should service accounts be reviewed the same way?

Yes, and they are usually where the worst findings are, because nobody notices an integration that has held broad access for three years. Review them on the same cycle with the same defaults, and require an owning team for each — an unowned service account with production access is the single most common finding worth acting on.

How do I prove the review happened?

From the log: entries showing the reviewer, the date, the decisions and the resulting revocations, all produced by the normal grant path. That is stronger evidence than any document, because it cannot be assembled after the fact — which is precisely why revoking with ad-hoc database access undermines the whole exercise.