Quarterly access reviews die of tedium: hundreds of grants, nested roles, and a spreadsheet nobody finishes reading. This playbook dumps the real grant state, has AI cluster it into effective-permission narratives with anomalies flagged — orphaned users, PUBLIC grants, role loops — and keeps every revocation verified by a human before it runs.
Tier: Core · Workflow area: Security & Compliance · ~35 minutes
The failure mode of access reviews is not missing tooling — every engine can dump its grants. It is that raw grant dumps are unreadable at scale, so reviews get rubber-stamped. AI fixes the readability problem: it turns a thousand grant rows into per-principal narratives ("this role can effectively write to billing via this chain") and a ranked anomaly list. What it must never do is execute the cleanup. Every flagged item gets human verification against the raw dump, and every revocation goes through change control with an owner sign-off — because AI-flagged is a hypothesis, and revoking a grant an integration secretly depends on is a self-inflicted outage.
REVOKE statements unreviewed is how you learn which forgotten service account ran payroll.Dump the complete, real grant state — metadata, not memory.
-- PostgreSQL: role attributes and memberships
SELECT r.rolname, r.rolsuper, r.rolcreaterole, r.rolcanlogin,
r.rolvaliduntil,
ARRAY(SELECT b.rolname FROM pg_auth_members m
JOIN pg_roles b ON m.roleid = b.oid
WHERE m.member = r.oid) AS member_of
FROM pg_roles r ORDER BY r.rolname;
-- Object grants
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee NOT IN ('postgres')
ORDER BY grantee, table_schema, table_name;
-- Default privileges (the ones reviews always miss)
SELECT * FROM pg_default_acl;
Add the last-login evidence you have (log analysis, pg_stat_activity history from monitoring) — dormancy is the strongest single review signal.
Ask for narratives and anomalies, with claims tied to rows.
Below: role list with memberships, object grants, default ACLs and
last-seen data for PostgreSQL 16. Produce:
1. EFFECTIVE-PERMISSION NARRATIVES — for each login-capable role:
what it can effectively read and write, resolving role chains,
as 2-3 plain sentences. Note which access comes via inheritance
and name the chain.
2. ANOMALY LIST, ranked by risk, checking at least: superuser or
CREATEROLE beyond the break-glass set; grants to PUBLIC; login
roles unseen >90 days; role-membership loops or chains >3 deep;
write grants on financial-looking schemas to non-service roles;
service accounts with password expiry unset; default ACLs that
auto-grant future tables.
3. QUESTIONS — access patterns you cannot classify from the data.
For every anomaly, cite the exact rows (role + grant) it derives
from. Do not propose REVOKE statements yet.
[paste dumps]
Verify every anomaly against the raw dump — the model misreads chains. Nested role resolution is precisely where LLMs slip. For each flagged item, confirm from the dump rows, and for membership chains re-derive one by hand:
-- Does reporting_ro really reach billing writes? Check the chain:
WITH RECURSIVE chain AS (
SELECT oid, rolname FROM pg_roles WHERE rolname = 'reporting_ro'
UNION ALL
SELECT r.oid, r.rolname FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.roleid
JOIN chain c ON c.oid = m.member
) SELECT * FROM chain;
Mark each anomaly confirmed / false positive / needs owner input. Expect false positives; their presence is the review working, not failing.
Route confirmed items to owners with a decision deadline. For each confirmed anomaly, the data owner (not the DBA) rules: keep with justification, or revoke. AI drafts the owner-facing summaries well — one paragraph per item in business language: "The intern_2023 account can modify vendor bank details via the ap_admin chain and has not logged in for 214 days. Keep or revoke by Aug 20." Unanswered by deadline defaults to revoke-after-monitoring (step 5), which is what makes the review finish.
Revoke in two stages: observe, then execute. For each approved revocation, first watch for usage, then act through change control:
-- Stage 1: alert-on-use for the doomed grant (log-based watch), or
-- for login roles: NOLOGIN as the reversible first step
ALTER ROLE intern_2023 NOLOGIN;
-- Stage 2 (after 2 quiet weeks), the actual revocation
REVOKE ap_admin FROM intern_2023;
DROP ROLE intern_2023; -- only after ownership reassignment checks
NOLOGIN first is the discipline that converts "we think it's unused" into "it was disabled for two weeks and nothing broke" — an evidence-grade claim, and instantly reversible.
File the review pack. The artifact auditors accept: the dated raw dumps, the anomaly list with per-item verdicts and verifier initials, owner decisions with timestamps, and the executed change tickets. AI drafts the cover narrative; the evidence underneath is all queries and sign-offs.
sys.server_principals, sys.database_principals, sys.database_permissions and role memberships; add checks for db_owner sprawl, orphaned users (sp_change_users_login @Action='Report' era leftovers) and GUEST access; DENY semantics deserve explicit narrative treatment since they invert the usual resolution.DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS plus profile checks; the anomaly list gains ANY-privilege grants (SELECT ANY TABLE) and default-password accounts from DBA_USERS_WITH_DEFPWD.table_privileges miss where standing access actually hides.A grant dump is a map of your attack surface with real usernames on it — the exact document an attacker wants. Handle review packs at your highest data classification, strip principal names before any external-model processing where policy requires, and never leave dumps in chat histories or ticket attachments with broad read access.
AI turns the unreadable grant dump into narratives and a ranked worklist — that is the acceleration. The review itself stays human: verify each flag against raw rows, put decisions on data owners, and disable-before-revoke so every cleanup claim rests on two quiet weeks of evidence.