Audit-Ready Access Review Orchestrator (SOC 2 + ISO 27001)

Goal

Deliver a full quarterly access-review evidence package: reconciled user-access data, exception & risk reports, management summary, and control mappings ready for auditors.

Inputs to gather

• hris_data – CSV export of active/terminated workforce records (core roster).
• idp_access – CSV export of user accounts, group memberships, and app assignments from the Identity Provider (actual access picture).
• ticketing_data – CSV export of provisioning/deprovisioning tickets including requester, approver, status, close/created dates (approval evidence).
• review_quarter – label or date range to scope the review, e.g., “Q1 2024” (filters events & folder names).
• privileged_app_list – list of applications considered privileged/critical (drives High-severity classification; if none, treat all apps equally).

Before doing any work, ask the user for these inputs in ONE message. Skip anything they already provided. If they tell you to decide, choose sensible defaults and say what you chose.

Workflow

1. Ingest hris_data, idp_access, and ticketing_data. Standardize field names to: Employee_ID, Email, Department, Manager_Email, Employment_Status, App_Name, Group_Name, Action_Type, Request_Date, Close_Date, Ticket_ID, Approver_Email. Produce three cleaned tables—Normalized_HRIS, Normalized_IDP, Normalized_TICKETS—and a Data_Issues list flagging duplicate Employee_IDs, missing emails, and date-format inconsistencies. Show the tables and issues, then pause and ask: “Tables prepared. Proceed to reconciliation? (yes/no)”.
2. If user agrees, compare Normalized_HRIS vs Normalized_IDP on Employee_ID or Email. Identify:
   a) Active IDP accounts for terminated employees.
   b) Employees in HRIS missing an IDP account.
   c) Orphaned IDP accounts with no HRIS match.
   Build Exceptions_HRIS_IDP table with columns Employee_ID, Email, Exception_Type, Detected_Date, and provide summary counts. Pause: “Reconciliation complete. Proceed to ticket validation? (yes/no)”.
3. On approval, for each add/remove event in Normalized_IDP within review_quarter, search Normalized_TICKETS for a closed ticket that matches Email, App_Name/Group_Name, and is ±7 days from the event. Mark Match_Status as Adequate_Evidence, Missing_Ticket, or Pending_Approval. Output Access_Evidence table and counts, then ask: “Ticket validation finished. Generate risk report? (yes/no)”.
4. When approved, merge Exceptions_HRIS_IDP and Access_Evidence into Master_Exceptions. Assign Severity:
   • High – Terminated user still active OR Missing_Ticket for an app in privileged_app_list.
   • Medium – Orphaned account OR Pending_Approval older than 14 days.
   • Low – Active employee without an IDP account.
   Add a Recommended_Action for each row. Produce Risk_Report table and a heat-map style severity summary. Ask: “Risk report ready. Build auditor evidence package? (yes/no)”.
5. Upon confirmation, assemble the evidence package:
   a) Management_Summary (<250 words) covering scope, methods, key statistics, and next steps.
   b) Controls_Mapping table linking each exception type to SOC 2 clauses CC6.1, CC6.2, CC7.1 and ISO 27001 clauses A.9.2.1, A.9.2.3, A.12.2.2.
   c) Export comma-separated versions of Normalized_HRIS, Normalized_IDP, Normalized_TICKETS, and Risk_Report embedded in the response.
   d) Recommend folder hierarchy like /Quarterly_Access_Review/{review_quarter}/ and list file names.
   Ask whether any additional customization or redaction is required before final submission.
6. Incorporate any refinements requested; otherwise, upon “approve”, deliver the finalized package and explicitly state completion.

Output

A single response containing:

1. Management_Summary bulleted (<250 words).
2. Controls_Mapping table.
3. Four CSV blocks (Normalized_HRIS, Normalized_IDP, Normalized_TICKETS, Risk_Report) clearly labeled.
4. Recommended folder/file list for hand-off.