Database security: privilege, TLS, binds, and no FILE

Database security is who can connect, what they can run, and whether the data is readable if the disk walks out.

A FILE privilege left on, a user that is also a DBA for convenience, and a backup sitting next to the app in the same bucket are the usual misses. The database version is one line. The grants are the rest.

The usual mistake is encrypting the volume and leaving root@% in the privilege table.

This page is seven controls that still hold on MySQL and cousins, and the privileges you should grep before the next dump.

MySQL 8.4.0 shipped on 30 April 2024 as the LTS line. 8.4 FILE privilege text on 22 August 2026. It still says a user with FILE can read any world-readable file the server can read, and can create files in any directory the server can write, including the data directory.Those are adjacent controls. The locks are a private socket, a tight grant, TLS you verify, a real bind, and no FILE.

This page is defense. It does not walk a payload. The process user and the host firewall sit on the Ubuntu hardening guide. The rest of the request surface sits on the secure coding checklist. Identifiers stay clerk, migrator, shop, 10.0.4.%, and skuByCode.

The engine is not a public socket

Lock one is placement. The database process answers the app subnet. It does not answer the internet. Bind mysqld to a private address. Put a security group or nftables rule in front that allows 3306 only from the app CIDR. Do not publish 3306 on a cloud load balancer. Do not give a laptop a public grant “so staging is easier.”

# mysqld.cnf on a host you own
[mysqld]
bind-address = 10.0.4.10
require_secure_transport = ON
local_infile = OFF

The 8.4 “Making MySQL Secure Against Attackers” chapter says never run the server as Unix root, and it says do not put the datadir on a world-writable path. A container that maps 3306:3306 to 0.0.0.0 has undone lock one. If you need a jump host, it is an SSH tunnel to 10.0.4.10, not a public listener.

Separating the app process from mysqld is useful. It is not magic. A stolen app credential still reaches the private socket. Locks two through five are why that credential is small.

The app user is not a DBA

Lock two is the grant. Do not connect the site as root. Do not grant ALL PRIVILEGES. clerk needs SELECT, INSERT, UPDATE, and DELETE on shop.*. It does not need CREATE USER, GRANT OPTION, SUPER, or FILE. The 8.4 privileges chapter is the first-party list. Read it once per role.

CREATE USER 'clerk'@'10.0.4.%'
 IDENTIFIED WITH caching_sha2_password BY RANDOM PASSWORD
 REQUIRE SSL;

GRANT SELECT, INSERT, UPDATE, DELETE
 ON shop.* TO 'clerk'@'10.0.4.%';

REQUIRE SSL is the account-level half of lock four. The host pattern is the app subnet, not %. A leftover 'clerk'@'%' from a tutorial is a grant you will find with SHOW GRANTS later on this page.

Dual control is a process. It is not a MySQL privilege. Put dual control on the break-glass user. Do not invent it as a reason to give clerk SUPER.

FILE reads the host

Lock three is the grant you refuse. FILE enables LOAD DATA, SELECT... INTO OUTFILE, and LOAD_FILE(). Oracle’s current LTS manual says the account can read any world-readable host file the server can read, including files in other database directories, and can create new files where the server can write. That is a host walk sitting in a SQL privilege. The app never needs it.

REVOKE IF EXISTS FILE ON *.* FROM 'clerk'@'10.0.4.%';
SHOW GRANTS FOR 'clerk'@'10.0.4.%';

Expect no FILE in the output. REVOKE IF EXISTS (MySQL 8.0.16+) does not error when the grant was never held, which is the normal case for a fresh clerk. secure_file_priv can pin remaining FILE use to one directory. That is a belt for migrator, not a reason to give clerk the grant. Keep local_infile=OFF so a client cannot be talked into sending a local file. The 8.4 page “Security Considerations for LOAD DATA LOCAL” is the citation. The MySQL 8.4 lock page walks stacked statements and that flag in more depth. This page only needs them off.

TLS is a setting, not a hope

Lock four is the wire. 8.4 encrypted connections chapter. The server flag refuses plaintext. The client must still verify the server certificate. A pool that sets ssl-mode=REQUIRED encrypts and will still accept any cert. VERIFY_CA or VERIFY_IDENTITY is the check.

const mysql = require("mysql2/promise");

async function openShopPool() {
 return mysql.createPool({
 host: "10.0.4.10",
 user: "clerk",
 password: process.env.MYSQL_CLERK_PASSWORD,
 database: "shop",
 ssl: {
 ca: process.env.MYSQL_CA_PEM,
 rejectUnauthorized: true,
 },
 multipleStatements: false,
 });
}

openShopPool is the named gate. rejectUnauthorized: true plus a CA you pin is VERIFY_CA in the Node mysql2 client. Set MYSQL_CA_PEM to the PEM you minted for this environment. Do not ship ssl: { rejectUnauthorized: false } “until we have a cert.” That is plaintext with extra steps. JDBC’s equivalent is sslMode=VERIFY_CA on the URL. PHP PDO’s equivalent is PDO::MYSQL_ATTR_SSL_CA and PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT.

Port 3306 is this lock. A browser padlock does not encrypt the hop from app to engine when they sit on two hosts.

Bind the value. Do not template

Lock five is the application. A tight account does not fix a string-built query. CWE-89 is untrusted text reaching the SQL parser as grammar. A real prepare sends the statement and the values on two channels. Escaping a quote into a string is still one channel.

async function skuByCode(pool, code) {
 const [rows] = await pool.execute(
 "SELECT id, sku, price_cents FROM shop.item WHERE sku = ?",
 [code]
 );
 return rows[0] ?? null;
}

skuByCode is the only lookup a catalog route should call. The sku never enters the statement text. multipleStatements: false in openShopPool is the extra refuse for a stacked second statement. Identifiers still cannot be bound. A sort token maps to a column you wrote.

const SORTS = {
 price: "price_cents",
 name: "title",
};

async function listItems(pool, sortToken) {
 const column = SORTS[sortToken];
 if (!column) {
 const err = new Error("unknown sort token");
 err.status = 400;
 throw err;
 }
 const [rows] = await pool.execute(
 `SELECT id, sku FROM shop.item ORDER BY ${column} ASC`
 );
 return rows;
}

An ORM literal, raw, or query() hatch turns this lock back into a string. Grep those names. The injection page lists the hatches by library. This page only needs skuByCode and listItems to stay clean.

internet
 |
 app :443
 |
 10.0.4.0/24 only ------ 3306 / TLS VERIFY_CA
 |
 mysqld 10.0.4.10
 |
 clerk SELECT..DELETE on shop.*
 FILE revoked
 migrator DDL only, jump host
 |
 skuByCode(pool, code)
 ? placeholder, two channels

Split migrate from serve

Lock six is a second account. Schema changes are not a request path. migrator holds CREATE, ALTER, and INDEX. If a load job still needs FILE, grant it only for that job, pin secure_file_priv, then revoke it. The GRANT below does not include FILE. It logs in from a jump host or from CI, not from the app pool. clerk cannot ALTER a table and cannot mint a user.

CREATE USER 'migrator'@'10.0.5.%'
 IDENTIFIED WITH caching_sha2_password BY RANDOM PASSWORD
 REQUIRE SSL;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX
 ON shop.* TO 'migrator'@'10.0.5.%';

10.0.5.% is the jump or CI subnet, not the app subnet. A stolen app secret then cannot rewrite a trigger. Flyway, Liquibase, or a SQL file in CI uses migrator. The running Node process uses openShopPool. Mixing them is how a support engineer pastes DDL through the same DSN the site uses.

Read-only reporting is a third account if you have one. GRANT SELECT on the views you minted. Do not hand a BI tool clerk. Do not hand it FILE.

Log the account that ran it

Lock seven is evidence. Start smaller. Enable the 8.4 error log and the slow query log on a host you own. Set log_slow_extra=ON so the account name and the client host land on the line. Audit plugin extras exist. The built-in logs plus SHOW GRANTS are enough to prove who connected.

[mysqld]
slow_query_log = ON
long_query_time = 1
log_slow_extra = ON
log_error_verbosity = 2

A line that shows clerk from 10.0.4.20 running skuByCode‘s statement is the healthy shape. A line that shows root from a public address is an incident. Rotate the clerk password after you close the listener. Do not keep a shared admin password in the app env “for emergencies.” Emergencies use migrator from the jump host.

Masking a card number in a UI is a presentation rule. It does not encrypt the column and it does not revoke FILE. If you store PAN, that is a different page: tokenization and a vault. This page will not pretend a last-four mask is an eighth lock.

Prove the seven locks

You are proving your own engine and your own pool. You are not attacking a foreign database.

1. From a shell you own, run SHOW GRANTS FOR 'clerk'@'10.0.4.%';. Expect SELECT through DELETE on shop.*. Expect no FILE, no SUPER, no *.*.
2. The SHOW GRANTS line is the proof. clerk has no grant on mysql.*, so do not query FILE_PRIV as clerk.
3. Point a throwaway client at 10.0.4.10 with SSL disabled. Expect a refuse when plaintext is banned.
4. Call skuByCode with a sku that contains a quote. The row comes back or comes back empty. The statement text in the slow log still shows ?.
5. Grep the app for query(, .literal, multipleStatements: true, and rejectUnauthorized: false.

# from the app subnet, prove TLS is required
mysql --host=10.0.4.10 --user=clerk --ssl-mode=DISABLED
# Expect: Connections using insecure transport are prohibited

# prove the grant
mysql --host=10.0.4.10 --user=clerk --ssl-mode=VERIFY_CA \
 --ssl-ca="$MYSQL_CA_PEM" -e "SHOW GRANTS;"

A unit test for the bind, using a pool you create in the test:

async function testSkuByCodeRejectsTemplate(pool) {
 await pool.execute(
 "INSERT INTO shop.item (sku, title, price_cents) VALUES (?, ?, ?)",
 ["SKU-1", "seed", 100]
 );
 const row = await skuByCode(pool, "SKU-1' OR '1'='1");
 if (row !== null && row.sku !== "SKU-1' OR '1'='1") {
 throw new Error("driver templated the sku");
 }
}

That test does not teach a payload. It seeds one row first so an empty table cannot pass. It asserts that a funny string is still a sku, not grammar.