Security and Access Control
Users, roles, GRANT and REVOKE, and the least-privilege discipline that protects production data
GRANT hands privileges out, REVOKE takes them back, and roles bundle privileges so you manage them once and reuse them across many accounts.
- create login accounts with
CREATE USERand understand the'user'@'host'identity, - read the privilege hierarchy - global, database, table, column and routine scope,
- grant and revoke exactly the privileges an account needs with
GRANT/REVOKE, - build reusable
ROLEobjects in MySQL 8 and assign them as default roles, - apply least privilege so an application - or an SQL-injection payload - can only ever touch what it was designed to touch.
1. Authentication vs authorization
These two words are often blurred, but the engine keeps them strictly separate. Authentication answers "is this really that account?" - it is the password check at connect time. Authorization answers "may this account run this statement on this object?" - it is checked on every query, against the privileges stored in the server's grant tables.
In MySQL an account is not just a name - it is a name plus a host pattern: 'user'@'host'. The same username connecting from a different host can be a different account with different rights. This lets you say "the application may connect only from the app servers, never from a laptop".
-- A user that may connect only from one private subnet
CREATE USER 'app_web'@'10.0.%' IDENTIFIED BY 'S0me-Strong-Secret';
-- A user that may connect from anywhere (%). Convenient, but a wider attack surface.
CREATE USER 'reporting'@'%' IDENTIFIED BY 'An0ther-Secret';
-- Force a password change and expire it every 90 days
ALTER USER 'app_web'@'10.0.%' PASSWORD EXPIRE INTERVAL 90 DAY;Creating a user grants nothing except the ability to log in (and see information_schema). A brand-new account cannot read a single business row until you GRANT it something. That "deny by default" starting point is the foundation everything else builds on.