Introduction
One of the most common findings in a compliance audit is simple: the wrong people still have access to systems they shouldn't. Terminated employees. Sales reps with admin rights. Accounts that haven't been touched in months.
This is exactly what a GRC Analyst is hired to catch and in this project, I built a SQL-based User Access Review (UAR) to simulate a real audit workflow.
The Scenario
Imagine you're a GRC Analyst at a mid-sized company. The IAM (Identity and Access Management) policy says:
Terminated employees must have database access revoked within 24 hours
Only IT and DevOps staff can hold Admin privileges (Principle of Least Privilege)
Any account inactive for 90+ days is considered stale and must be disabled
Your job: write SQL audit queries to find every policy violation.
Step 1 — Building the Mock Database
I created a simple user_access table with intentional policy violations baked in:
sqlCREATE TABLE user_access (
user_id INT,
name VARCHAR(50),
department VARCHAR(50),
role VARCHAR(20),
status VARCHAR(10),
last_login DATE,
termination_date DATE
);
INSERT INTO user_access VALUES (101, 'Alice Smith', 'IT', 'Admin', 'Active', '2026-02-01', NULL);
INSERT INTO user_access VALUES (102, 'Bob Jones', 'Sales', 'Admin', 'Active', '2026-01-15', NULL);
INSERT INTO user_access VALUES (103, 'Charlie Brown', 'HR', 'User', 'Terminated', '2025-12-01', '2025-12-05');
INSERT INTO user_access VALUES (104, 'David Wilson', 'Marketing', 'User', 'Active', '2025-08-20', NULL);
Three violations are hiding in plain sight — can you spot them?
Step 2 — The Audit Queries
Audit 1: Zombie Accounts — Terminated employees still with active access
sqlSELECT name, department, termination_date
FROM user_access
WHERE status = 'Terminated' AND termination_date IS NOT NULL;
Finding: Charlie Brown (HR) was terminated on Dec 5, 2025 — access never revoked. High Risk.
Audit 2: Privilege Escalation Check — Non-IT/DevOps users with Admin rights
sqlSELECT name, department, role
FROM user_access
WHERE role = 'Admin' AND department NOT IN ('IT', 'DevOps');
Finding: Bob Jones (Sales) has Admin privileges. Violates Principle of Least Privilege. Medium Risk.
Audit 3: Stale Account Detection — No login in 90+ days
sqlSELECT name, department, last_login,
JULIANDAY('now') - JULIANDAY(last_login) AS days_inactive
FROM user_access
WHERE status = 'Active'
AND JULIANDAY('now') - JULIANDAY(last_login) > 90;
Finding: David Wilson (Marketing) hasn't logged in since August 2025 — over 180 days inactive. Medium Risk.
Step 3 — The GRC Executive Summary
This is what separates a security project from a GRC project. The code is just the tool. The report is the deliverable.
Audit Finding Report — Q1 2026
Finding 1 (High Risk): One terminated employee (Charlie Brown, HR) retains active database access 90+ days post-termination. Immediate remediation required.
Finding 2 (Medium Risk): One active employee (Bob Jones, Sales) holds Admin privileges without business justification. Violates Principle of Least Privilege.
Finding 3 (Medium Risk): One active account (David Wilson, Marketing) has been inactive for 180+ days. Account should be disabled pending review.
Recommendation: Implement an automated trigger between the HR system and the database to disable accounts within 24 hours of termination. Conduct quarterly access reviews for all Admin-level users.
What I Learned
This project taught me how GRC work actually flows in practice — you don't just find vulnerabilities, you document them, assign risk levels, and recommend remediations that a business can act on. The SQL is almost secondary. The report is the real output.
If you're breaking into GRC, I'd strongly recommend building something like this. It's concrete, demonstrable, and directly mirrors what compliance teams do every day.
Links
🔗 GitHub: github.com/SankethSubhas/sql-user-access-review
🌐 Portfolio: sankethsubhas.pages.dev
This is part of my open-source cybersecurity portfolio — 8 projects built to demonstrate real GRC and SOC skills.
Top comments (0)