The AI For Series

Segregation of Duties Analysis with AI: A User Access Review from an ERP Extract

By Chandan Gupta, CA, CIA · 2026-11-14

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

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

  1. 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.
  2. Re-perform one conflict by hand. Filter user_roles.xlsx for one flagged user, look up both roles in role_permissions.xlsx, and confirm the activity pair matches the rule in sod_rules.xlsx.
  3. 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.
  4. 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.
  5. 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

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