Creating MySQL Users and Granting Privileges Correctly

2017-03-03 · mysql, security, users

Almost every MySQL server I inherit has the same problem: one or two accounts with ALL PRIVILEGES ON *.*, shared by every application, often with '%' as the host. It works until someone leaks a config file or a developer runs DROP DATABASE on production by accident. Doing accounts properly takes five minutes, and I've never regretted it. Examples are for MySQL 5.6 and 5.7; the syntax also works on MariaDB 10.x.

An account is user plus host

In MySQL an account is always 'user'@'host'. 'app'@'localhost' and 'app'@'10.0.0.%' are two different accounts with separate passwords and privileges. When a client connects, the server picks the most specific matching row from mysql.user, so a '%' account can be shadowed by a 'localhost' one.

Have a look at what already exists:

SELECT user, host, authentication_string FROM mysql.user ORDER BY user, host;

On 5.6 the column is still password instead of authentication_string.

Use CREATE USER, not GRANT, to make accounts

Creating accounts implicitly through GRANT ... IDENTIFIED BY still works in 5.7 but is deprecated and is disabled when NO_AUTO_CREATE_USER is in sql_mode (which it is by default in 5.7). Use CREATE USER:

CREATE USER 'shopapp'@'10.0.1.%' IDENTIFIED BY 'Xk9#pLm2!vQr';
CREATE USER 'shopapp'@'localhost' IDENTIFIED BY 'Xk9#pLm2!vQr';

Restrict the host as tightly as you can. I stick to a subnet wildcard like 10.0.1.% because it's a fair compromise: I don't have to touch grants every time an app server is added. Avoid '%' unless a firewall already restricts the source.

Grant the minimum

For a typical web application that reads and writes its own schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shopapp'@'10.0.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shopapp'@'localhost';

If the application runs schema migrations itself (Rails, Laravel, Flyway and so on), it additionally needs DDL rights during deployment. I prefer a separate deployment account for that:

CREATE USER 'shopdeploy'@'10.0.1.%' IDENTIFIED BY 'another-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES,
      CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE, TRIGGER
ON shop.* TO 'shopdeploy'@'10.0.1.%';

A read-only reporting account is even simpler:

CREATE USER 'report'@'10.0.2.50' IDENTIFIED BY 'yet-another-password';
GRANT SELECT ON shop.* TO 'report'@'10.0.2.50';

A backup account for mysqldump needs a bit more than SELECT:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, RELOAD, PROCESS, REPLICATION CLIENT
ON *.* TO 'backup'@'localhost';

RELOAD is required for --flush-logs, PROCESS for dumping tablespaces in 5.7, and REPLICATION CLIENT for --master-data.

FLUSH PRIVILEGES is not needed

Persistent myth. CREATE USER, GRANT, REVOKE and ALTER USER update the in-memory grant tables immediately. FLUSH PRIVILEGES is only needed when you modified the mysql.* tables directly with INSERT or UPDATE, which you should not do anyway. Running it does no harm, but it is not what makes your grant work.

Limits and password rules

On 5.7 you can force a password change interval and connection limits per account:

ALTER USER 'report'@'10.0.2.50' PASSWORD EXPIRE INTERVAL 180 DAY;
ALTER USER 'report'@'10.0.2.50' WITH MAX_USER_CONNECTIONS 5;

Service accounts that nobody logs into interactively should get PASSWORD EXPIRE NEVER, otherwise the application will fail on a random morning when the default default_password_lifetime kicks in (it was 360 days in 5.7.4–5.7.10 and is 0, meaning never, from 5.7.11).

Check what you actually gave

Look at the effective grants first:

SHOW GRANTS FOR 'shopapp'@'10.0.1.%';

Then test from an actual application host, not from the database server. I once "verified" a grant from localhost and it told me nothing about the remote account:

mysql -h db01.internal -u shopapp -p shop -e "SELECT COUNT(*) FROM orders;"
mysql -h db01.internal -u shopapp -p shop -e "DROP TABLE orders;"
# ERROR 1142 (42000): DROP command denied to user 'shopapp'@'10.0.1.23' for table 'orders'

The second command failing is exactly what you want.

When it goes wrong

ERROR 1045 (28000): Access denied for user 'shopapp'@'10.0.1.23' (using password: YES) – Either the password is wrong or no account matches that host. Run SELECT user,host FROM mysql.user WHERE user='shopapp' and compare the host pattern with the client IP shown in the error.
ERROR 1130 (HY000): Host '10.0.1.23' is not allowed to connect to this MySQL server – No account exists for that user with a matching host at all. The host in the error is what the server sees; if it shows a hostname instead of an IP, skip-name-resolve is off and reverse DNS is involved.
ERROR 1044 (42000): Access denied for user 'shopapp'@'10.0.1.%' to database 'shop_test' – Grants were given on shop.* but the app connects to a different database. Grant on the right schema or use shop% patterns carefully (underscores and percent signs are wildcards in GRANT ... ON database names unless escaped).
Grants seem to be ignored – Check for a more specific account, such as 'shopapp'@'localhost' with different privileges, that matches before the wildcard one.

One account per application and purpose, narrowest host, schema.* never *.*. That's the whole rule.