Until now Orbit has used one account with all privileges on its database. In production that is a risk: a SQL injection bug in the API could drop tables, and a leaked password would expose everything. This lesson sets up accounts the way they should be: each one with exactly the rights it needs.
Accounts are user plus host
A MySQL account is identified by a user name and the host it connects from:
CREATE USER 'orbit_app'@'10.0.%' IDENTIFIED BY 'a-long-random-secret';
CREATE USER 'orbit_report'@'localhost' IDENTIFIED BY 'another-long-secret';
| Host part | Matches |
|---|---|
'localhost' | Connections through the local socket (or 127.0.0.1 when resolved as localhost) |
'10.0.%' | Any address starting with 10.0. |
'10.0.0.0/255.255.0.0' | The same range written as a netmask |
'%' | Anywhere, the most permissive choice |
'orbit'@'localhost' and 'orbit'@'%' are two different accounts with separate passwords and privileges. When several accounts match a connection, MySQL picks the most specific host. Restrict hosts as tightly as your network allows.
List accounts:
SELECT user, host, plugin, account_locked FROM mysql.user ORDER BY user, host;
Authentication plugins
How a password is checked depends on the account’s authentication plugin:
- caching_sha2_password is the default since MySQL 8.0: SHA-256 based, with a server-side cache for fast reconnects. It requires TLS or RSA key exchange for the first login, which every current driver handles.
- mysql_native_password is the old SHA-1 based method. It is disabled by default since MySQL 8.4; accounts that use it cannot log in unless the server starts with
mysql-native-password=ON. It is removed in the 9.x series. Migrate old accounts withALTER USER ... IDENTIFIED WITH caching_sha2_password BY '...'. - auth_socket logs in local operating-system users without a password (lesson 2).
- LDAP, Kerberos, FIDO/WebAuthn and other plugins exist, several of them in the Enterprise edition.
If an old client fails with “Authentication plugin ‘caching_sha2_password’ cannot be loaded”, update the client library rather than downgrading the account.
Passwords and account policies
Let MySQL generate strong passwords, and set limits on each account:
CREATE USER 'orbit_migrate'@'10.0.%'
IDENTIFIED BY RANDOM PASSWORD
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1
PASSWORD EXPIRE INTERVAL 180 DAY;
+---------------+--------+----------------------+-------------+
| user | host | generated password | auth_factor |
+---------------+--------+----------------------+-------------+
| orbit_migrate | 10.0.% | qJ7%n_Fz;8Ku>2pW1vAb | 1 |
+---------------+--------+----------------------+-------------+
1 row in set (0.00 sec)
The generated password is shown once; store it in your secret manager immediately. (Yours will be different.)
| Clause | Effect |
|---|---|
IDENTIFIED BY RANDOM PASSWORD | MySQL generates and returns a strong password (8.0.18+) |
FAILED_LOGIN_ATTEMPTS n PASSWORD_LOCK_TIME d | Lock the account for d days after n wrong passwords (8.0.19+) |
PASSWORD EXPIRE INTERVAL n DAY | Force a change every n days |
ACCOUNT LOCK / ACCOUNT UNLOCK | Disable an account without dropping it |
PASSWORD REQUIRE CURRENT | Changing the password needs the old one |
The validate_password component (installed by mysql_secure_installation) rejects weak passwords server-wide; see its settings with SHOW VARIABLES LIKE 'validate_password%';.
Rotating passwords without downtime
Dual passwords let an account accept the old and the new password during a rotation:
ALTER USER 'orbit_app'@'10.0.%' IDENTIFIED BY 'new-secret' RETAIN CURRENT PASSWORD;
-- deploy the new secret to every app instance, then:
ALTER USER 'orbit_app'@'10.0.%' DISCARD OLD PASSWORD;
Other account statements: ALTER USER CURRENT_USER() IDENTIFIED BY '...' changes your own password, RENAME USER 'a'@'h' TO 'b'@'h' renames, and DROP USER IF EXISTS 'a'@'h' removes an account.
Privileges and GRANT
A new account can only log in; it has no other rights (USAGE). Privileges are granted at a level:
| Level | Syntax | Example |
|---|---|---|
| Global | ON *.* | Administration such as PROCESS, REPLICATION CLIENT |
| Database | ON orbit.* | Everything in Orbit’s database, including future tables |
| Table | ON orbit.notes | One table |
| Column | SELECT (id, title) ON orbit.notes | Some columns only |
| Routine | EXECUTE ON PROCEDURE orbit.create_note | One stored procedure |
The privileges you will use most:
| Privilege | Allows |
|---|---|
SELECT, INSERT, UPDATE, DELETE | Reading and changing rows |
EXECUTE | Calling procedures and functions |
CREATE, ALTER, DROP, INDEX, REFERENCES | Changing the schema |
CREATE VIEW, SHOW VIEW, TRIGGER, EVENT, CREATE ROUTINE, ALTER ROUTINE | Managing views, triggers, events and routines |
LOCK TABLES | Explicit table locks (used by some backup tools) |
PROCESS | Seeing every connection in SHOW PROCESSLIST |
RELOAD, REPLICATION CLIENT, REPLICATION SLAVE | Flushing, replication status, acting as a replica |
ALL PRIVILEGES | Everything at that level except GRANT OPTION |
MySQL 8 also splits the old all-powerful SUPER into dynamic privileges such as SYSTEM_VARIABLES_ADMIN, CONNECTION_ADMIN, BACKUP_ADMIN and REPLICATION_SLAVE_ADMIN, so administrators can receive only the part they need.
GRANT SELECT, INSERT, UPDATE, DELETE ON orbit.* TO 'orbit_app'@'10.0.%';
REVOKE DELETE ON orbit.* FROM 'orbit_app'@'10.0.%';
Grants take effect immediately for new statements; there is no need for FLUSH PRIVILEGES after GRANT, REVOKE or CREATE USER.
Roles: least privilege made manageable
Granting lists of privileges to each account gets messy. A role is a named bundle of privileges you grant to accounts. Orbit needs three:
CREATE ROLE orbit_read, orbit_write, orbit_ddl;
GRANT SELECT ON orbit.* TO orbit_read;
GRANT INSERT, UPDATE, DELETE, EXECUTE ON orbit.* TO orbit_write;
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES, CREATE VIEW, SHOW VIEW,
TRIGGER, EVENT, CREATE ROUTINE, ALTER ROUTINE ON orbit.* TO orbit_ddl;
GRANT orbit_read, orbit_write TO 'orbit_app'@'10.0.%';
GRANT orbit_read TO 'orbit_report'@'localhost';
GRANT orbit_read, orbit_write, orbit_ddl TO 'orbit_migrate'@'10.0.%';
SET DEFAULT ROLE ALL TO 'orbit_app'@'10.0.%', 'orbit_report'@'localhost', 'orbit_migrate'@'10.0.%';
The API account can read and write rows but cannot change the schema, so even a SQL injection bug cannot drop a table. Migrations run with a separate account that the API never sees.
Roles must be active in a session to apply. SET DEFAULT ROLE activates them at login; alternatively set activate_all_roles_on_login = ON server-wide. Inside a session, SELECT CURRENT_ROLE(); shows the active roles and SET ROLE orbit_read; narrows them.
Checking what an account can do
SHOW GRANTS FOR 'orbit_app'@'10.0.%';
+------------------------------------------------------------------+
| Grants for orbit_app@10.0.% |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `orbit_app`@`10.0.%` |
| GRANT `orbit_read`@`%`,`orbit_write`@`%` TO `orbit_app`@`10.0.%` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)
Roles have a host part too, % by default. To see the privileges the roles carry, expand them:
SHOW GRANTS FOR 'orbit_app'@'10.0.%' USING orbit_read, orbit_write;
The column-level and restricted-object views of the same information live in information_schema.USER_PRIVILEGES, SCHEMA_PRIVILEGES and TABLE_PRIVILEGES.
Encrypting connections
MySQL 8 creates TLS certificates at initialisation and clients prefer TLS by default. Make it mandatory:
SET PERSIST require_secure_transport = ON; -- every connection must use TLS
ALTER USER 'orbit_app'@'10.0.%' REQUIRE SSL; -- or per account
Check a session with \s in the client (look for the SSL: Cipher in use line) or SHOW STATUS LIKE 'Ssl_cipher';. In production, install certificates from your own CA and have clients verify them (--ssl-mode=VERIFY_IDENTITY), so they cannot be fooled by a man-in-the-middle.
Network exposure
- Bind MySQL to private addresses only:
bind-address = 10.0.0.5inmy.cnf, or127.0.0.1when the app runs on the same machine. - Never expose port 3306 to the internet. Use a firewall or security group that only admits application servers, and an SSH tunnel or VPN for administrators.
- Run the server as the unprivileged
mysqloperating-system user (packages do this) and keep the data directory readable only by it.
A security checklist
| Check | How |
|---|---|
| No anonymous accounts | SELECT user, host FROM mysql.user WHERE user = ''; returns nothing |
| No remote root | root only @'localhost' |
| No legacy auth | SELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password'; returns nothing |
| Least privilege | Each app has its own account and roles, no ALL ON *.* |
| TLS enforced | require_secure_transport = ON |
| Private network only | bind-address, firewall |
| Secrets managed | Passwords in a secret manager, rotated with dual passwords |
| Patched | Latest patch release of your LTS line |
| Injection-safe code | Applications use parameterised queries, never string concatenation |
That last item matters most: privileges limit the damage of SQL injection, but parameterised queries (? placeholders in every driver) prevent it.
Quick reference
| Task | SQL |
|---|---|
| Create an account | CREATE USER 'u'@'host' IDENTIFIED BY '...'; |
| Generated password | CREATE USER 'u'@'h' IDENTIFIED BY RANDOM PASSWORD; |
| Change a password | ALTER USER 'u'@'h' IDENTIFIED BY '...'; |
| Lock / unlock | ALTER USER 'u'@'h' ACCOUNT LOCK; / ACCOUNT UNLOCK |
| Grant / revoke | GRANT SELECT ON db.* TO 'u'@'h'; / REVOKE ... FROM ...; |
| Create and assign a role | CREATE ROLE r; GRANT ... TO r; GRANT r TO 'u'@'h'; SET DEFAULT ROLE ALL TO 'u'@'h'; |
| Inspect | SHOW GRANTS FOR 'u'@'h' USING r; |
| Require TLS | SET PERSIST require_secure_transport = ON; |
| Remove an account | DROP USER 'u'@'h'; |
Exercise
You create ‘orbit’@’localhost’ and later ‘orbit’@’%’ with a different password. How many accounts exist?
Show answer
Answer: C. An account is the pair of user name and host. Different hosts mean different accounts, and MySQL picks the most specific match at login.
Exercise
After upgrading to MySQL 8.4, an old application account can no longer log in. Its plugin is mysql_native_password. What is the recommended fix?
Show answer
Answer: B. mysql_native_password is disabled by default in 8.4 and gone in 9.x. Moving the account to caching_sha2_password is the lasting fix; re-enabling the old plugin only buys time.
Exercise
Why give the API account only SELECT, INSERT, UPDATE, DELETE and EXECUTE on orbit.*?
Show answer
Answer: D. Least privilege limits the damage of a breach. Schema changes run under a separate migration account the API never uses.
Key takeaways
- Accounts are
'user'@'host'; keep hosts as narrow as your network allows. - Use
caching_sha2_password;mysql_native_passwordis off in 8.4 and removed in 9.x. - Generate passwords, lock accounts after failures and rotate with dual passwords.
- Bundle privileges into roles and give each application only what it needs.
- Enforce TLS, keep port 3306 private and use parameterised queries everywhere.
FAQ
Do I need FLUSH PRIVILEGES?
Not after CREATE USER, GRANT, REVOKE, ALTER USER or DROP USER, which update the in-memory privilege tables themselves. It is only needed if someone edited the mysql system tables directly, which you should never do.
How do I reset a forgotten root password?
Stop the server, start it once with --skip-grant-tables --skip-networking, connect without a password, run FLUSH PRIVILEGES; then ALTER USER 'root'@'localhost' IDENTIFIED BY '...';, and restart normally. On Debian and Ubuntu, sudo mysql via auth_socket often makes this unnecessary.
Can I restrict an account to some rows of a table?
MySQL has no row-level security. Create a view with a WHERE clause (and WITH CHECK OPTION for writes), grant access to the view instead of the table, and define it with SQL SECURITY DEFINER.
How do I see who connected and what they changed?
The general query log records every statement but is too heavy for production. For auditing, use the Enterprise Audit plugin, Percona’s or MariaDB’s audit plugins, or log changes in the application. Triggers, as in lesson 16, can audit specific tables.