Users and Privileges

Stop using root for apps#

Your application needs to read and write its own data. It doesn't need to drop databases or create users. If it runs as root and has an injection hole, the attacker owns everything. Give each application its own account with the smallest set of privileges that works.

Creating users#

CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'a-long-random-secret';
CREATE USER 'shop_report'@'%' IDENTIFIED BY 'another-secret'
  PASSWORD EXPIRE INTERVAL 90 DAY;

The host part restricts where the user can connect from. '%' means any host, so use it only if you also control access with a firewall.

Granting privileges#

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.0.%';
GRANT SELECT ON shop.* TO 'shop_report'@'%';
GRANT EXECUTE ON PROCEDURE shop.sp_place_order TO 'shop_app'@'10.0.0.%';

SHOW GRANTS FOR 'shop_app'@'10.0.0.%';

Privileges apply at levels:

*.*              all databases (global)
shop.*           one database
shop.orders      one table
shop.orders(id)  specific columns

Notice the app account has no CREATE, DROP, or ALTER. Schema changes go through a separate migration account.

Revoking and removing#

REVOKE DELETE ON shop.* FROM 'shop_app'@'10.0.0.%';
DROP USER 'shop_report'@'%';

Existing connections keep the privileges they had at connect time for global-level changes in some cases, so a revoke on a busy server may need a reconnect to be fully effective.

Roles (MySQL 8)#

CREATE ROLE 'app_rw', 'app_ro';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_rw';
GRANT SELECT ON shop.* TO 'app_ro';
GRANT 'app_ro' TO 'analyst1'@'%';
SET DEFAULT ROLE 'app_ro' TO 'analyst1'@'%';

Roles let you manage groups of privileges and assign them to many users.

What the check looks like#

connect as user@host ─▶ authenticate
each statement ─▶ check global ▶ db ▶ table ▶ column privileges
                  any level allows it? ▶ proceed, else error 1142

Other hygiene#

• Never grant ALL PRIVILEGES or GRANT OPTION to an app.
• Remove anonymous accounts and test databases on fresh installs.
• Require TLS for remote connections: CREATE USER ... REQUIRE SSL.
• Rotate passwords, and keep them out of source control.

Common mistakes#

• Using '%' hosts and no firewall.
• One shared account for every service, so you can't tell who did what.
• Granting on *.* out of laziness.
• Forgetting that 'u'@'localhost' and 'u'@'%' differ.

Practice#

Create three accounts: a migration user with ALL on shop.*, an app user with data-only privileges, and a read-only report user. Connect as each and verify that the report user can't INSERT and the app user can't DROP TABLE. Record each error message.