Exercise: A Database Security Audit

Build the mental model

A security audit is not the same activity as learning about security topics one at a time. Earlier lessons introduced authentication, least privilege, network access, TLS, secrets management, and SQL injection as separate ideas.

A real audit means looking at one running system and asking, for every layer, whether that idea has actually been applied, because a system can get every individual concept partly right and still be dangerously exposed as a whole.

This exercise gives you a small, realistic, and intentionally flawed setup — all six of the characteristics below are present in it.

A single shared admin account is used for every database operation, including read-only reporting.
The connection string and password are hardcoded in a config file that has been committed to source control.
The database server is reachable directly from the public internet, with no firewall rule restricting connections.
The connection never negotiates TLS, so traffic travels unencrypted.
Backups run nightly but have never been test-restored.
Some queries build SQL by concatenating user input directly into the query string.

Framework-neutral failures

These are framework-neutral failures of authentication, least privilege, network exposure, encryption, and operational readiness — not deep product-specific configuration issues.

Work through the description the way a real auditor would: layer by layer, asking what could go wrong and why it matters, before checking your reasoning against the worked answer key that follows.

text

FLAWED DATABASE SETUP (AUDIT TARGET)
------------------------------------
FLAWED DATABASE SETUP (AUDIT TARGET)
-----
[ Web App ]
     |
     | connects with ONE shared "admin" account
     | (same account for writes AND read-only reports)
     v
[ Config file -- committed to git repo ]
     | connection string + password in PLAIN TEXT
     v
[ Database Server ]
     | NO TLS -- traffic sent unencrypted
     | reachable from 0.0.0.0/0 -- NO FIREWALL RULE
     v
[ Public Internet ] <-- anyone can attempt to connect

[ Nightly Backup Job ]
     | backups are created on schedule
     | NEVER test-restored -- unknown if usable
     v
[ ??? ] -- recovery path is unverified

Connect it to a real scenario

Here is how you would actually walk through auditing the setup described in the exercise, the way a careful engineer would before touching anything in production.

Identity

A single shared admin account for every operation is a least-privilege failure. The normal read/write path, the read-only reporting path, and any human operators should each use separate, narrowly scoped credentials.

Secrets handling

A password committed to source control is compromised the moment it's committed. Move the credential into an environment variable or secrets manager and rotate the exposed password immediately.

Network exposure

A database reachable from the entire public internet has no real perimeter. Add a firewall or security-group rule that only allows connections from the application's own servers.

Encryption in transit

Without TLS, credentials and data cross the network in a readable form. Enabling TLS closes that specific gap.

Backups

A backup nobody has restored is a hope, not a plan. Schedule a real test restore on a regular basis and confirm the recovered data is actually usable.

A real audit checks whether framework-neutral security concepts — least privilege, secrets handling, network access, encryption in transit, backup verification — have actually been applied layer by layer, not just whether each concept is understood in isolation.
A committed secret is compromised the moment it's committed; deleting the file later doesn't undo the exposure, only rotating the credential does.
An untested backup is not a verified recovery plan — the only way to know a backup works is to actually restore it.

Try the working example

javascript

function auditDatabaseSetup(setup) {
  const issues = [];

  if (setup.usesSharedAdminAccount) {
    issues.push(
      "Shared admin/superuser account used for all access -- violates " +
      "least privilege; app writes, read-only reporting, and human " +
      "operators should each use separate, narrowly scoped credentials."
    );
  }
  if (setup.credentialsHardcoded) {
    issues.push(
      "Credentials hardcoded in a committed config file -- move to " +
      "environment variables or a secrets manager, and rotate the " +
      "exposed credentials immediately."
    );
  }
  if (setup.publiclyExposed) {
    issues.push(
      "Database reachable directly from the public internet -- " +
      "restrict network access with a firewall/security-group rule " +
      "that allows only the app servers."
    );
  }
  if (!setup.usesTLS) {
    issues.push(
      "Connections are not encrypted with TLS -- credentials and " +
      "data can be read by anyone observing the network traffic."
    );
  }
  if (!setup.backupsRestoreTested) {
    issues.push(
      "Backups exist but have never been test-restored -- an " +
      "unverified backup is not a confirmed recovery path."
    );
  }
  if (!setup.usesParameterizedQueries) {
    issues.push(
      "User input is concatenated directly into queries -- " +
      "vulnerable to SQL injection; use parameterized queries " +
      "or prepared statements instead."
    );
  }

  return issues;
}

const flawedSetup = {
  usesSharedAdminAccount: true,
  credentialsHardcoded: true,
  publiclyExposed: true,
  usesTLS: false,
  backupsRestoreTested: false,
  usesParameterizedQueries: false,
};

const hardenedSetup = {
  usesSharedAdminAccount: false,
  credentialsHardcoded: false,
  publiclyExposed: false,
  usesTLS: true,
  backupsRestoreTested: true,
  usesParameterizedQueries: true,
};

console.log("Flawed setup issues found:", auditDatabaseSetup(flawedSetup).length);
console.log("Hardened setup issues found:", auditDatabaseSetup(hardenedSetup).length);

You should see

Running against the flawed setup logs `Flawed setup issues found: 6` and returns all six issue strings (shared admin account, hardcoded credentials, public exposure, no TLS, untested backups, non-parameterized queries). Running against the hardened setup logs `Hardened setup issues found: 0` and returns an empty array.

5-minute try-it

A small team's production database is set up like this: the application connects using one shared admin/superuser account for every operation, including its read-only reporting dashboard. The connection string, including the password, is hardcoded in a config file that lives in the same git repository as the application code. The database server accepts connections from any IP address on the internet, with no firewall or security-group rule restricting who can reach it. The connection does not use TLS. Nightly backups are configured and have been running for months, but no one has ever tried restoring one. Finally, a few older parts of the codebase build queries by directly concatenating form input into the SQL string. Working through this description on your own, list every security problem you can find, and for each one, state specifically what you would change to fix it. Try to finish your own list before reading the worked answer key in the practical section above.

One important caution

Treating this as a single problem instead of six separate, independently exploitable failures, fixing only the most obvious one (like enabling TLS) while leaving the shared admin account and public exposure untouched.

Proposing a fix that doesn't match the actual risk, such as just changing the admin password without also splitting it into separate least-privilege accounts.

OWASP Database Security Cheat Sheet — How Databases Work