Module 7 — AI for Permission Audit & Anomaly Detection

Applied Generative AI SQL Server database security audit using Claude as an audit aperture tool — validated against Microsoft Learn, tested empirically.

---

📁 Repository Contents

File	Description
README.md	This file — project overview and walkthrough
permissions_baseline.csv	Full permission export from the HospitalDB simulation environment
remediation_script.sql	Validated GRANT/REVOKE T-SQL for all true findings
Module7_Permission_Audit_Waqar_Javed.docx	Complete assignment deliverable (audit transcript, findings, reflection)

---

🎯 Project Overview

This project demonstrates using AI (Claude) to audit SQL Server database permissions for:

• Over-privileged grants — permissions that exceed a principal's role function
• Separation-of-duties violations — role compositions that create dangerous effective permissions
• Redundant grants — object-level permissions already covered by schema or role
• RBAC bypasses — direct grants that route around the intended role hierarchy

The core discipline: AI widens the audit aperture. It does not close the audit. Every finding was cross-checked against Microsoft Learn and tested empirically before any remediation was accepted.

---

🚀 How to Reproduce This Audit

Step 1 — Export permissions from your SQL Server environment

Run these three queries in SSMS against your database:

-- Database-scoped permissions
SELECT
    dp.name            AS principal_name,
    dp.type_desc       AS principal_type,
    perm.permission_name,
    perm.state_desc    AS permission_state,
    COALESCE(obj.name,'') AS object_name,
    COALESCE(obj.type_desc,'') AS object_type,
    grantor.name       AS grantor
FROM sys.database_permissions perm
JOIN sys.database_principals  dp      ON perm.grantee_principal_id = dp.principal_id
JOIN sys.database_principals  grantor ON perm.grantor_principal_id = grantor.principal_id
LEFT JOIN sys.objects         obj     ON perm.major_id = obj.object_id
ORDER BY dp.name, perm.permission_name;

-- Server-scoped permissions
SELECT
    sp.name            AS principal_name,
    sp.type_desc       AS principal_type,
    perm.permission_name,
    perm.state_desc    AS permission_state
FROM sys.server_permissions perm
JOIN sys.server_principals   sp ON perm.grantee_principal_id = sp.principal_id
ORDER BY sp.name, perm.permission_name;

-- Role memberships
SELECT
    member.name   AS member_name,
    role.name     AS role_name
FROM sys.database_role_members drm
JOIN sys.database_principals   role   ON drm.role_principal_id   = role.principal_id
JOIN sys.database_principals   member ON drm.member_principal_id = member.principal_id;

Export the results to permissions_baseline.csv (right-click results grid in SSMS → Save Results As).

---

Step 2 — Run the AI audit

Paste your CSV into Claude (or ChatGPT) with this prompt:

Below is the full permission export from my SQL Server environment.
Please audit it for:
1. Over-privileged grants (permissions that exceed role function)
2. Separation-of-duties violations
3. Redundant grants already covered by a role
4. Grants that bypass intended RBAC

For each finding provide:
(a) finding label
(b) affected principal
(c) specific permission
(d) risk rationale
(e) recommended GRANT/REVOKE remediation

Save the full AI response — this becomes your audit transcript.

---

Step 3 — Classify every finding

For each AI finding, look up the exact permission in Microsoft Learn and classify it:

Classification	Meaning
✅ TRUE FINDING	Real risk confirmed by MS Learn permission semantics
❌ FALSE POSITIVE	Intentional design or not actually a risk in context
⚠️ AI CONFUSED ABOUT HIERARCHY	AI misunderstood DENY precedence, role composition, or schema scope

This step is mandatory — do not skip it. See the findings table in Module7_Permission_Audit_Waqar_Javed.docx for the full classification with MS Learn citations.

---

Step 4 — Test the remediation script

Ask the AI to generate the fix script, then review it manually before running anything:

-- Example: AI-generated remediation (always validate before running)
REVOKE ALTER ANY USER FROM AppServiceAccount;
REVOKE EXECUTE ON DATABASE::HospitalDB FROM AppServiceAccount;
ALTER ROLE BillingDept DROP MEMBER jr_nurse_chen;
-- ... see remediation_script.sql for full validated script

Run each statement in a dev/test environment first. Verify the bad access is blocked using EXEC AS USER impersonation tests.

---

Step 5 — Find the breakage case

Look for at least one AI-proposed remediation that would break legitimate access. The classic failure mode:

AI proposed: REVOKE SELECT ON dbo.PatientRecords FROM BackupOperator to "clean up" a DENY entry it called redundant.

Why it's wrong: DENY and REVOKE have completely different future-state behaviors.

• REVOKE removes the record — a later role grant will take effect silently.
• DENY persists and wins over any future GRANT from any source.

Running the AI's REVOKE would have silently removed a defense-in-depth control.

MS Learn reference: DENY (Transact-SQL) — "DENY overrides a GRANT from any source."

---

⚠️ Common AI Failure Modes (SQL Server Permissions)

Watch for these in any AI-assisted SQL Server audit:

1. DENY vs REVOKE confusion — AI treats DENY as redundant when no GRANT exists; proposes REVOKE which removes future protection
2. Role composition blindness — AI may miss that membership in two individually-reasonable roles creates a dangerous combined effective permission set
3. Schema vs object scope — Schema-level SELECT propagates to all contained objects; AI sometimes flags the object grant as the problem when the schema grant is the root cause
4. Server vs database DENY precedence — AI sometimes believes a server-level GRANT overrides a database-level DENY (it does not)
5. Invented permission types — AI may suggest syntax like GRANT EXECUTE ON DATABASE which is not valid in all SQL Server versions

---

📊 Audit Results Summary

Classification	Count	%
✅ True Finding	8	80%
❌ False Positive	0	0%
⚠️ AI Confused About Hierarchy	2	20%
Total	10	100%

Key findings included:

• AppServiceAccount had ALTER ANY USER and database-wide EXECUTE — far beyond app DML needs
• ETLProcess had ALTER ANY SCHEMA and TRUNCATE on ePHI tables
• DevOpsLogin held both CONTROL SERVER and sysadmin — dual SA-equivalent grants
• jr_nurse_chen was member of both NursingStaff and BillingDept — clinical + financial SoD breach
• billing_mgr_kim could INSERT into AuditLog via role composition — could cover own transactions

---

📖 Key References

• Permissions (Database Engine) — Microsoft Learn
• sys.database_permissions — Microsoft Learn
• DENY (Transact-SQL) — Microsoft Learn
• REVOKE (Transact-SQL) — Microsoft Learn
• Server-level roles — Microsoft Learn
• HIPAA Minimum Necessary Standard — HHS

---

👤 Author

Waqar Javed · Applied Generative AI
GitHub: @iamwaqarjaved