SQL Lab — Database Security Audit (Hard)

🖥️

SQL Terminal

📚

Reference

📌 OBJECTIVES — NO HINTS

○Audit all user privileges (SHOW GRANTS)

○Revoke excessive privileges

○Enable binary log + audit plugin

○Encrypt sensitive columns (AES_ENCRYPT)

○Parameterise vulnerable stored procedure

○Create row-level access policy

○Pass all 7 audit checks

0 / 7 complete

SQL TERMINAL — DATABASE SECURITY AUDIT (HARD)

MySQL 8.0 — Lab environment loaded Database Security Audit (Hard) — Hard tier · No syntax hints provided ⚠ This lab tests independent problem-solving. Reference window available. ──────────────────────────────

mysql>

COMMAND REFERENCE

AVAILABLE COMMANDS

SELECT user, host, Select_priv, Super_priv FROM mysql.user;
SHOW GRANTS FOR 'app_user'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM 'app_user'@'%';
GRANT SELECT, INSERT ON health_db.appointments TO 'app_user'@'%';
SET GLOBAL general_log = ON;
UPDATE patients SET nhs_number = AES_ENCRYPT(nhs_number, UNHEX(SHA2('key',256)));
ALTER PROCEDURE get_patient COMMENT 'PARAMETERISED';

Type keywords from the command list. The terminal will recognise your intent even if the exact syntax differs.

MISSION BRIEFING

DB SECURITY AUDIT — MERIDIAN HEALTH NHS · HARD ⭐⭐⭐

SCENARIO

A penetration test found that Meridian Health's patient database has critical security misconfigurations: users have excessive privileges, audit logging is disabled, sensitive columns are unencrypted, and there are SQL injection vulnerable stored procedures. You are the DBA brought in to remediate every finding.

OBJECTIVES

Audit all user privileges (SHOW GRANTS)
Revoke excessive privileges
Enable binary log + audit plugin
Encrypt sensitive columns (AES_ENCRYPT)
Parameterise vulnerable stored procedure
Create row-level access policy
Pass all 7 audit checks

HARD MODE RULES

No step-by-step guidance. Use the Reference window for command syntax only. You must determine the approach, the correct columns and order, and verify your own results.

Display Mode