Show an auditor who approved each person's data access, and why

Step 1. Put the five lists in five CSV files

Microsoft Entra entitlement management keeps a request for each access package assignment. The request carries the requester’s justification (The requestor’s supplied justification, per the accessPackageAssignmentRequest documentation (read 2026-11-04)). The approval is a separate record whose stages carry the reviewer, the result (NotReviewed, Approved or Denied), the time and the reviewer’s justification (approvalStage (read 2026-11-04)). The assignment carries a schedule whose expiration holds an end date or says there is none (expirationPattern (read 2026-11-04)). Stages are listed per request by List approval stages (read 2026-11-04), and a single request is read with Get accessPackageAssignmentRequest (read 2026-11-04).

The script takes five files. Nested Graph properties are flattened by joining the names with an underscore, so requestor.objectId becomes requestor_objectId and assignment.target.principalName becomes assignment_target_principalName.

With --demo the script downloads Microsoft’s published Graph metadata at a pinned commit (microsoftgraph/msgraph-metadata at commit c4b98b7b), stops if the file’s size or SHA-256 differs from the pinned one, and stops if any of the 22 columns it takes from Graph is not a property in it. Run 1 below prints that check.

Step 2. Let the input checks run first

The script runs eight checks on the files before it looks at a single grant: the date, that every file and column is there, that every date and time is readable, that every grant names a grantee and a role, that every request id appears once, that every request state, request type, result, expiration type and assignment state is one Microsoft documents, that every role in the role map holds a grant, and that every role maps to one access package. If any fails, the run says INCOMPLETE and computes nothing, because a report built on files it could not read would look clean for the wrong reason. Run 3 shows this.

Step 3. Match each grant to its request, its approvals and its end date

For each grant the script finds the delivered request that assigned that person the access package the role maps to, whether the person asked or an admin assigned it, preferring the one completed just before the grant. Then it counts that request’s approvals by a named reviewer, how many of them are by the requester, how many have no reason, and when the last one was recorded. This is the matching query, copied from the script:

WITH cand AS (
  SELECT g.grant_no, r.id AS request_id, r.oid, r.created, r.justification AS req_reason,
         row_number() OVER (PARTITION BY g.grant_no ORDER BY coalesce(r.completed > g.granted_at, TRUE),
                            abs(epoch(r.completed) - epoch(g.granted_at)), r.id) AS rk
  FROM g JOIN pkg p ON p.role = g.role JOIN req r ON r.principal = g.grantee AND r.package = p.package)
SELECT c.grant_no, c.request_id, c.oid, c.created, c.req_reason,
       (SELECT count(*) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS n_appr,
       (SELECT count(*) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer = c.oid) AS n_self,
       (SELECT count(*) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL
          AND a.justification IS NULL) AS n_blank,
       (SELECT max(a.at) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS last_appr,
       (SELECT string_agg(DISTINCT coalesce(a.reviewer_name, a.reviewer), '; ') FROM apr a
          WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS approvers,
       (SELECT string_agg(coalesce(a.justification, '(none)'), ' | ') FROM apr a
          WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS approver_reasons
FROM cand c WHERE c.rk = 1

Each finding is one line of a second query. Here are the first lines of it and the last one (the full set is in the download):

SELECT grant_no, 'UNMAPPED_ROLE' AS code, 'role ' || role_shown || ' is not in role_packages' AS detail
  FROM m WHERE NOT mapped
UNION ALL SELECT grant_no, 'NO_REQUEST', 'no delivered request for this person and package' FROM m
  WHERE mapped AND NOT has_req
UNION ALL SELECT grant_no, 'NO_APPROVAL', 'request ' || request_id || ' has no approval by a named reviewer'
  FROM m WHERE has_req AND n_appr = 0
UNION ALL SELECT grant_no, 'SELF_APPROVAL', 'request ' || request_id || ' was approved by the person who asked'
  FROM m WHERE has_req AND n_self > 0
  ...
UNION ALL SELECT grant_no, 'PAST_END_DATE', 'ended ' || strftime(ends_at, '%Y-%m-%d') || ', grant still present'
  FROM m WHERE mapped AND ends_at < {asof}

An approval with a blank reviewer counts as no approval: an approval that does not say who gave it cannot answer the auditor’s question. A grant whose role is not in the role map gets only UNMAPPED_ROLE, because without a package the script has no request to look for. Reason fields count as blank after trimming spaces.

Run 3: the input checks stop a bad export

--plant-input plants eight faults in the generated files, aimed at seven of the eight input checks: the header of approvals.csv renamed (justification to reason), a grant date written 03/04/26, a grant with no grantee, one request id twice, a request state Graph does not define (approved), an expiration type Graph does not define (forever), a role in the map that no grant holds, and one role listed twice. One check (every state, type and result is one Microsoft documents) catches two of the faults, so seven of the eight input checks fail. The one that passes is the date in the settings.

Command: run 3

python3 guides/prove-who-approved-each-data-access/who_approved_each_grant.py --demo --plant-input --out loop-out/data/wa

Output from our run 3

Demo: checking the generated records' column names against Microsoft Graph's metadata
  Graph metadata at the pinned commit: 8200681 bytes, SHA-256 ff4f707d562dd1aa...
  Checked 22 columns against Graph's own property names; not found: 0
Generated records written to loop-out/data/wa/generated-planted-input/ (seed 124; not a company's data).

Run at 2026-11-04 10:48:04 UTC. Exports taken 2026-11-04 (UTC).

Input checks
  [ok] AS_OF is a real date written YYYY-MM-DD
  [FAIL] every file is there with every column the script reads: approvals.csv has no column justification
      read 30 rows from grants.csv
      read 8 rows from role_packages.csv
      read 31 rows from requests.csv
      read 0 rows from approvals.csv
      read 30 rows from assignments.csv
  [FAIL] every date and time is written as YYYY-MM-DD[T]HH:MM:SS (UTC): not readable: grants.csv.created_on: 1
  [FAIL] every grant names a grantee and a role: 1 grant row(s) with no grantee or no role
  [FAIL] every request id is there once: 1 request row(s) repeat an id or have none
  [FAIL] every state, type and result is one Microsoft documents: unknown values: requests.csv.state: 1, assignments.csv.schedule_expiration_type: 1
  [FAIL] every role in role_packages holds at least one grant: 1 role(s) match no grant: a typo or a stale row
  [FAIL] every role maps to one access package: 1 role(s) listed more than once

INCOMPLETE: 7 input check(s) failed, so no findings are computed. Fix the input.

How to read the results

• A finding is a question, not a verdict. It says a record is missing or out of order. The access may still be right; the record that shows it is not there.
• A matched grant is a complete chain, not a good decision. The script does not know whether the approver was the right owner or the reason was a good one. A person reads the reasons in grant_evidence.csv.
• Check the counts add up. The summary line gives grants read, grants matched and grants with a finding; they must add up to the rows of grants.csv, and so must the rows of grant_evidence.csv.
• Keep the file with the date of the export. The evidence is as of AS_OF and of the exports you took.

Method

• Records: generated with Python’s random.Random(124). Thirty grants, six roles, twenty people who hold access, one admin who made six of the requests, five approvers, all invented, all example.com addresses. Each assignment ends 180 days after its request was delivered. Nothing in them is a measurement of any company or platform.
• Column names: taken from microsoftgraph/msgraph-metadata at commit c4b98b7b, the Graph v1.0 metadata file (8,200,681 bytes, SHA-256 ff4f707d562dd1aa1dfaa591f4e17f4e6f50d517dc65b0278d216fb4fc4e547b). The script stops if the downloaded file differs. The Snowflake-style grant columns come from GRANTS_TO_USERS (read 2026-11-04).
• Matching rule: a request counts if its state is delivered, its requestType is userAdd, adminAdd or onBehalfAdd (Microsoft’s v1.0 page marks onBehalfAdd not supported; it is counted if it appears), and the person it assigns (assignment.target.principalName) and its access package equal the grant’s grantee and the package the role maps to. Names are compared without regard to case. If several match, the one completed closest before the grant is used; if none was completed before it, the closest after.
• Approval rule: a stage counts if its reviewResult is Approved and its reviewer is named. A reviewer id of all zeros, which Microsoft documents for a stage nobody reviewed (approvalStage (read 2026-11-04)), counts as not named. Ids are compared without regard to case.
• Removal rule: a grant is reported REMOVED_IN_ENTRA if the person’s most recent assignment for the package has a state other than delivered, or a delivered request of type userRemove, adminRemove, systemRemove or approverRemove names that assignment’s id. Microsoft documents that for a removal the request’s assignment holds the id of the assignment it removes (accessPackageAssignmentRequest (read 2026-11-04)).
• Checks and what trips each, in the order of the findings: NO_REQUEST (grant 3), NO_APPROVAL (7), SELF_APPROVAL (11), NO_REASON (15 and 16), GRANT_BEFORE_APPROVAL (19), NO_END_DATE (22, and 3), REMOVED_IN_ENTRA (9), PAST_END_DATE (25), UNMAPPED_ROLE (28). Input checks: file and column present (renamed header), date readable (03/04/26), grantee and role named (blank grantee), request ids once (repeated id), values Microsoft documents (approved, forever), role map holds a grant (a stale role), one package per role (a role twice). The date setting check is tripped by an AS_OF that is not a date; no run plants that.
• Not a check: if the grants the script numbered differ from the grants it read, it stops with an internal error. No input of ours trips it, and it is not a check on your data.
• Mapping tables written out: the known request states, request types, results, expiration types and assignment states, and the request types the script counts as creating or ending an assignment, are lists at the top of the script (values copied from Microsoft’s documentation (read 2026-11-04)); edit them if you disagree.
• Spreadsheet safety: a text cell in grant_evidence.csv that starts with =, +, -, @, a tab or a carriage return gets a ' in front, so a spreadsheet shows it as text and does not run it as a formula. Reasons are typed by requesters, so this matters.

Limits

• This is not a client result and not a measurement. The records are generated to show the check. They say nothing about how often real grants lack a request.
• It checks that a record exists, not that the decision was right. A named approver may not own the data, and a reason may be one word. A person has to read them.
• It only knows the approvals you export. Access given outside Entra, or approved by email or in a ticket, shows up as NO_REQUEST even if someone said yes. To count an approval given by email, add it to your requests and approvals files and re-run, or accept the finding.
• It matches by sign-in name and role. If your platform names people differently from Entra’s principalName, map them in the export first. A grant held through a group or a role nested in another role needs the export to list the person and the role they end up holding.
• Request history may not reach back far enough. A grant older than the history you export has no request in the files and is reported as NO_REQUEST.
• An assignment that carries a duration and no end date is reported as NO_END_DATE, because the script reads only schedule_expiration_endDateTime. Export the end date, or work it out from the start and the duration, before you run it.
• It does not change anything. It does not remove a grant, ask for a reason or end access.
• Evidence, not a conclusion. What it prints is one input to a review. It does not say any review or framework is satisfied.

Tested on

When this is not enough

• The record is not there to find. If requests and approvals happened in email and tickets, the script lists the gaps and cannot fill them. Someone has to decide, now, who approves and why, and write it down.
• Request history is gone. The script can only see the requests you exported. Export them on a schedule, so the history is there when the review starts.
• You need a person to judge the reasons. A complete chain can still hold a weak reason or the wrong approver. That takes the data owner.
• Finding is not fixing. The next step is requests that always carry an owner’s yes, a reason and an end date, so the gaps do not come back.