Database Security Configuration Checker (MySQL)

A Computer-Assisted Audit Technique (CAAT) for Automated ITGC Compliance

An enterprise-grade, modular Computer-Assisted Audit Technique (CAAT) built by an AI/ML and Data Analytics student to automate database logical access security assessments and compliance exception reporting for IT General Controls (ITGC).

---

The Data Analytics & IT Audit Perspective

In financial and technology audits (e.g., SOX 404, SOC 1/2), legacy audit methodology relies heavily on manual sampling (e.g., pulling screenshots for 25 random accounts). This approach is inefficient, labor-intensive, and leaves critical blind spots.

This project bridges Data Engineering and IT Audit by framing database entitlement verification as an automated ETL and outlier-detection pipeline:

1. Data Ingestion: The engine connects directly to the MySQL system catalog and extracts 100% of records from the administrative metadata table (mysql.user).
2. Feature Filtering & Outlier Extraction: Using specialized audit check algorithms, the engine scans the dataset for anomalies and compliance outliers that deviate from the Principle of Least Privilege (PoLP) and CIS MySQL Benchmarks.
3. Automated Audit Working Papers: The pipeline structures all identified outliers into an RFC 4180-compliant CSV exception report (audit_findings.csv) and generates an immutable execution log (AUDIT_EVIDENCE.md).

---

Modular Architecture & Directory Layout

To avoid monolithic script anti-patterns, the solution is organized into a scalable, single-responsibility package structure:

Database_Security_Checker/
├── setup_test_db.sql               # Provisions 4 synthetic vulnerable accounts
├── main.py                         # Master CLI orchestrator
├── README.md                       # Comprehensive documentation
├── audit_findings.csv              # Generated CSV exception report (Working Paper)
├── AUDIT_EVIDENCE.md               # Runtime-generated proof of execution
└── audit_engine/                   # Core audit framework package
    ├── __init__.py                 # Framework exports
    ├── config.py                   # DB connection dictionary & system account baselines
    ├── connection.py               # Connection pooling & fail-fast timeout management
    ├── reporter.py                 # CSV exporter & automated AUDIT_EVIDENCE.md generator
    └── checks/                     # Modular baseline check algorithms
        ├── __init__.py             # Security check aggregator
        ├── privileges.py           # ITGC AC-1: Excessive Global Privileges (SUPER / GRANT)
        ├── network.py              # ITGC AC-2: Wildcard '%' Network Exposure
        ├── authentication.py       # ITGC AC-3: Empty / Missing Passwords
        └── lifecycle.py            # ITGC AC-4: Anonymous Default Accounts

---

ITGC Control Objectives & Data Rules

Control Objective	Dataset Attribute Inspected	Data Rule / Outlier Condition	Risk Severity
AC-1: Least Privilege / SoD	Super_priv, Grant_priv	(Super_priv = 'Y' OR Grant_priv = 'Y') on non-system accounts	High
AC-2: Network Boundary	host	host = '%' allowing arbitrary remote inbound connections	Medium
AC-3: Authentication Standards	authentication_string	authentication_string = '' permitting passwordless logins	Critical
AC-4: Account Lifecycle Hygiene	user	user = '' anonymous accounts destroying audit attribution	High

---

Proof of Execution & Results

Important Note for Recruiters & Audit Reviewers:
This code was successfully executed and verified locally against a simulated test environment (setup_test_db.sql) containing 4 synthetic vulnerable accounts. The anonymous account violates two controls (no password and anonymous account), so the tool reports 5 findings. You do not need to configure a local MySQL instance to verify the tool's effectiveness.

Please inspect the following repository files as verifiable evidence that the data extraction and exception reporting pipeline operates successfully:

1. audit_findings.csv: The exact CSV working paper produced by the pipeline, mapping all detected outliers to standardized severity levels and audit observations:

   Severity,User,Host,Finding Description
   High,test_admin,localhost,Global administrative privileges detected.
   Medium,test_remote,%,Account exposed to wildcard '%' IP addresses.
   Critical,ANONYMOUS,localhost,Account has no password.
   Critical,test_nopass,localhost,Account has no password.
   High,ANONYMOUS,localhost,Anonymous default account detected.

2. AUDIT_EVIDENCE.md: The timestamped execution log dynamically generated at runtime. It formally records the execution timestamp, the 4 ITGC objectives tested, anomaly statistics, and auditor attestation.

---

How to Run the Project Locally

1. Prerequisites & Dependencies

• Python 3.8+
• MySQL Server 8.0+
• Install the official MySQL connector:

  pip install -r requirements.txt

2. Provision the Synthetic Test Database

Inject the 4 test accounts into your local testing database:

mysql -u root -p < setup_test_db.sql

3. Configure Connection Credentials

Pass your database credentials via environment variables (recommended) or update audit_engine/config.py:

# PowerShell (Windows)
$env:MYSQL_HOST="localhost"
$env:MYSQL_USER="root"
$env:MYSQL_PASSWORD="YourPasswordHere"

# Bash (Linux / macOS)
export MYSQL_HOST="localhost"
export MYSQL_USER="root"
export MYSQL_PASSWORD="YourPasswordHere"

4. Execute the Audit Engine

Run the primary orchestrator:

python main.py

Terminal Output:

Starting Database Security Data Extraction...

   Audit complete! Extracted 5 anomalies.
[+] Audit CSV Working Papers exported to   : audit_findings.csv
[+] Proof-of-Work Evidence generated at     : AUDIT_EVIDENCE.md

---

Environment Teardown & Reset

To cleanly purge the synthetic test users after verification, open setup_test_db.sql, uncomment the teardown block at the bottom, and execute:

DROP USER IF EXISTS 'test_admin'@'localhost';
DROP USER IF EXISTS 'test_remote'@'%';
DROP USER IF EXISTS 'test_nopass'@'localhost';
DROP USER IF EXISTS ''@'localhost';
FLUSH PRIVILEGES;

---

Future Enhancements

1. More Security Checks: Add checks for password expiry, dangerous privileges (such as FILE and DROP), and leftover default databases.
2. Fix Recommendations: Add a column to the report that tells the user how to fix each finding.
3. Compare Past Runs: Save each audit and show which issues are new and which have been fixed.