Users, Roles & Privileges

Creating users

A MySQL account is a pair: 'username'@'host'.

CREATE USER 'app'@'localhost' IDENTIFIED BY 'StrongPass!42';
CREATE USER 'reader'@'192.168.1.%' IDENTIFIED BY 'AnotherPass!7';
CREATE USER 'dev'@'%' IDENTIFIED BY 'Pass!9'
  PASSWORD EXPIRE INTERVAL 90 DAY
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

'app'@'localhost' and 'app'@'%' are two different accounts. Prefer specific hosts over %.

Granting privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO 'app'@'localhost';
GRANT SELECT ON school.students TO 'reader'@'192.168.1.%';
GRANT SELECT (id, name_en) ON school.students TO 'reader'@'192.168.1.%';  -- column level
GRANT EXECUTE ON PROCEDURE school.dept_stats TO 'app'@'localhost';
GRANT ALL PRIVILEGES ON school.* TO 'owner'@'localhost';

FLUSH PRIVILEGES;   -- only needed after editing the mysql.* tables directly

Privilege levels, from broad to narrow: global (*.*), database (db.*), table (db.table), column, and routine.

Revoking and inspecting

REVOKE INSERT, UPDATE ON school.* FROM 'app'@'localhost';
SHOW GRANTS FOR 'app'@'localhost';
SELECT user, host, account_locked, password_expired FROM mysql.user;

Roles (MySQL 8)

Roles are named bundles of privileges — far easier to manage than granting to each user.

CREATE ROLE 'app_read', 'app_write';

GRANT SELECT ON school.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON school.* TO 'app_write';

GRANT 'app_read' TO 'reporting'@'%';
GRANT 'app_read', 'app_write' TO 'app'@'localhost';

-- roles are inactive until set as default
SET DEFAULT ROLE ALL TO 'app'@'localhost';
SELECT CURRENT_ROLE();

Managing accounts

ALTER USER 'app'@'localhost' IDENTIFIED BY 'NewStrongPass!99';
ALTER USER 'app'@'localhost' ACCOUNT LOCK;
ALTER USER 'app'@'localhost' ACCOUNT UNLOCK;
RENAME USER 'old'@'localhost' TO 'new'@'localhost';
DROP USER IF EXISTS 'temp'@'%';

Principle of least privilege

Give each account only what it needs:

Account	Typical grants
Web application	SELECT, INSERT, UPDATE, DELETE on one database
Reporting / BI	SELECT only, ideally against a replica
Migration job	Adds CREATE, ALTER, DROP, INDEX, used only during deploys
Backup	SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT

Never let an application connect as root. An SQL injection in a SELECT-only account leaks data; the same injection as root can drop the database.