Segregation of Duties Analysis with AI: A User Access Review from an ERP Extract
A user access review looks simple on paper: take the user list, expand each account to its roles and each
role to what it allows, test the result against your segregation of duties (SoD) rules, and match the
accounts to HR to find leavers. In practice the work goes wrong at the joins. Employee IDs are text in one
file and numbers in another. Some have leading zeros. Open-ended role assignments carry an end date of
9999-12-31 stored as text. A named account with no employee ID drops out of the HR match without a word.
Each of these quietly removes people from the review, and the output still looks complete.
An AI tool can write and run this review on the whole access listing, with the user data staying on your laptop. Here is how, and how to check the result.
The rule: let the AI write the script, not read the data
Claude Code is Anthropic's AI tool that works on files on your own computer. It needs a paid Claude plan, and your organization should approve it for this kind of data first.
The model runs on Anthropic's servers, so whatever Claude Code reads, it sends. User lists are personal data, often covered by data protection laws such as GDPR, so do not let it open the files. Ask it instead to read the column headers, write a Python script, run the script on your laptop and print only counts. The script reads every row locally; only the headers, the script and the counts go to the model. The detail is saved to a workbook you open yourself.
That limits what reaches the model, not what an approved script can do. Work in a folder holding only the copies you need, use Manual mode, and read each permission prompt.
What you need
users.xlsx: accounts with user type, lock status and last logonuser_roles.xlsx: role assignments with valid-from and valid-to datesrole_permissions.xlsx: what each role allows, as activitiessod_rules.xlsx: conflicting activity pairs, with a rule ID and risk ratingmitigating_controls.xlsx: approved mitigations, with end dateshr_roster.xlsx: employees with status and termination date
Step 1: Learn the structure, not the people
This folder holds a user access extract from our ERP as at September 30, 2026, plus an HR roster, for an internal audit of user access and segregation of duties. Treat every file as company data: do not open the .xlsx files directly and do not print any data rows. Write and run a short Python script that shows, for each file, the sheet names, column names, data types, row count, blank values per column, and for ID columns the lengths and formats of the values (without printing the values). Tell me how the files link, and anything that could make a join or a date comparison go wrong.
This is the most valuable prompt in the review. In the book's tested run on practice data, structure alone
found that the employee ID had a different type in each file with some zero-padded values, that one named
(dialog) account had no employee ID, that a mitigating control pointed to a user not in the list, and that
the valid-to column mixed real dates with 9999-12-31 text. Ask about joins and dates before you ask about
conflicts.
Step 2: Test every rule for every account
Create a script access_review.py that writes access_review_output.xlsx. Never print names or data rows. Use an as-at date of 2026-09-30, kept as a setting. Keep only role assignments valid on that date (treat 9999-12-31 or a blank end date as open-ended). Expand users to roles to activities. For each rule in sod_rules.xlsx, list every user who holds both activities, with: User_ID, User_Type, Lock_Status, Rule_ID, Risk_Rating, roles giving each activity, Within_One_Role (Yes/No), Mitigation (Valid, Expired with the date, or None) and Status (Open conflict, Mitigated or Locked account). Also write Roles_With_Internal_Conflicts: any role that on its own grants both sides of a rule. Print only counts: assignments used and ignored, and conflicts by Status and by Rule_ID.
Read where it explains its choices. The order in which statuses are applied (valid mitigation, then locked account, otherwise open) is a judgment: agree it or change it, and record which. A role that conflicts on its own deserves its own line: anyone given that role has the conflict, so the fix is in the role design, not in individual users.
Step 3: Leavers, odd accounts and mitigations
Add to access_review.py: Leavers_Active: match users.xlsx to hr_roster.xlsx on employee ID after normalizing both sides (text, trimmed, leading zeros removed). For named accounts with no employee ID, try a match on name, allowing for "Last, First" order, and record how each account was matched. List accounts whose employee was terminated on or before the as-at date and whose account is not locked, with termination date, last logon and Logon_After_Termination (Yes/No). Do not treat other HR statuses, such as On Leave, as leavers. Unmatched_Accounts, NonPersonal_Accounts (generic and service accounts with their activities) and Mitigation_Housekeeping (expired mitigations, or ones referring to a user who does not exist). Then a Summary sheet and a Parameters sheet (files and row counts, as-at date, matching rules, versions, date run, blank Reviewed by). Show me only the Summary.
The name match matters. In the book's practice data, one of two leavers with working accounts could only be found by name, because the account had no employee ID. A join on ID alone would have reported one leaver and looked clean. Keep a sheet that records how every account was matched; a reviewer will ask for it.
How to check the result
- Control totals. Tie the user count to the system's own user count on the extract date, and the role assignment count to the extract. Assignments used plus assignments ignored should equal the total read.
- Re-perform one conflict by hand. Filter
user_roles.xlsxfor one flagged user, look up both roles inrole_permissions.xlsx, and confirm the activity pair matches the rule insod_rules.xlsx. - Re-perform one leaver. Find the person in the HR roster, then the account in the user list. If the match was by name, confirm with HR that it is the same person. A name match is a lead, not proof.
- Test what should not appear. Find a role assignment that ended before the as-at date and confirm no conflict is reported for it. If one is, the dates were mishandled. A review that only checks what was found never catches what was wrongly left in.
- Re-run and compare.
Run access_review.py again so that it writes access_review_rerun.xlsx instead (change nothing else). Then write and run compare_runs.py, which checks that every sheet except Parameters has identical rows in both files. Print only the comparison result.
An identical re-run shows consistency, not correctness. The hand checks show correctness.
File the script, the prompts, both workbooks and your re-performed items with a short note: as-at date, extract source, matching rules, counts and reviewer. Next quarter the same script runs on the new extract.
Draft observations, without overstating them
Create Observations_Draft.xlsx from access_review_output.xlsx, one row per issue type. Columns: Issue, Accounts Affected (user IDs), Condition (counts and facts from the data only), Criteria ("To confirm: access management policy" unless the data states the rule), Cause (blank), Effect (worded with "may"), Recommendation (draft), Proposed Priority (High/Medium/Low with one line of reasoning, as a proposal), and Status ("Flag only: not confirmed with the system owner"). Do not use the words fraud or misuse.
Then apply your judgment to the proposed priorities. A service account that posts journals through an interface may be expected; the question is whether anyone can log on with it interactively.
Limits
- A conflict in the data is not yet a finding. Confirm the extract came from production and was complete, that roles really grant what the role file says, and whether the access was used.
- Last logon is not the same as activity. Logons after termination need the security log; ask IT.
- The result is only as good as your SoD rule set. The script tests the rules you give it.
- Work from read-only extracts. Do not let any tool lock users or change roles in a live system.
- Use IDs instead of names where the test does not need names, and follow your privacy team's advice.
From the book
This guide is a short version of one method in AI for Internal Auditors. Chapter 18 of AI for Internal Auditors walks through it end to end on practice files with an answer key.
More free guides
- Free guide · AI for Internal Auditors
Duplicate Payment Testing with AI: Full AP Population
Test every AP invoice for duplicate payments and three-way match exceptions with AI, without uploading the ledger. Prompts, checks and limits.
Read the guide → - Free guide · AI for Internal Auditors
Journal Entry Testing with AI: Completeness and Risk Scoring
Roll the trial balance forward, score every manual journal against criteria you can defend, and keep GL data on your laptop. Prompts and checks.
Read the guide →