Privileges and Hardening

Article · 3:00

GRANT and REVOKE at the right level, and the settings that keep the server off the open internet.

Privileges apply at four levels: global (*.*), database (shop.*), table (shop.orders) and column. Grant at the narrowest level that works. An application that reads and writes its own schema needs SELECT, INSERT, UPDATE, DELETE on that schema - not ALL PRIVILEGES, and certainly not globally.

The dangerous ones are worth naming. ALL PRIVILEGES includes GRANT OPTION, which lets the account give its privileges to others. FILE allows reading and writing files on the server's filesystem. SUPER bypasses a great deal, including read-only mode. PROCESS lets an account see every other session's queries, which may include data in the statement text.

MySQL 8 adds roles: grant privileges to a role, grant the role to users, and change the role once instead of every account. Note that a granted role is not active until it is set as a default, which catches people out.

Hardening the server itself comes down to a few settings. bind-address decides which interfaces it listens on, and 127.0.0.1 means local only - a database with no reason to be reachable from the internet should not be. Require TLS per account with REQUIRE SSL, because a plaintext connection carries credentials and data across the network. And keep local_infile off unless you need it, since it lets a malicious server read files from a connecting client.

-- The narrowest grant that works. Not ALL, and not globally.
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.1.%';

-- Read-only reporting, one schema.
GRANT SELECT ON shop.* TO 'reports'@'%';

-- Column level, where a table holds something an account must not read.
GRANT SELECT (id, name, department) ON shop.employees TO 'reception'@'%';

-- Roles: change the role once instead of every account.
CREATE ROLE 'read_only', 'app_writer';
GRANT SELECT ON shop.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_writer';
GRANT 'app_writer' TO 'app'@'10.0.1.%';

-- A granted role is NOT active until it is set as a default.
SET DEFAULT ROLE ALL TO 'app'@'10.0.1.%';

SHOW GRANTS FOR 'app'@'10.0.1.%';
REVOKE DELETE ON shop.* FROM 'app'@'10.0.1.%';

-- Require an encrypted connection for this account.
ALTER USER 'app'@'10.0.1.%' REQUIRE SSL;

-- Server settings. bind-address 127.0.0.1 means local only, which is
-- right for any database with no reason to be on the network.
SELECT @@bind_address, @@require_secure_transport, @@local_infile,
       @@skip_name_resolve;

-- FLUSH PRIVILEGES is only needed after editing the grant tables by
-- hand. GRANT and REVOKE take effect immediately.