Lesson 18 intermediate

Users, Privileges and Security

Secure Orbit's database: accounts as user plus host, caching_sha2_password and the retired mysql_native_password, password policies, expiry, lockout and dual passwords for rotation, GRANT and REVOKE at global, database, table, column and routine level, roles for least privilege, SHOW GRANTS, TLS-only connections, network exposure and a security checklist.

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 partMatches
'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 with ALTER 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.)

ClauseEffect
IDENTIFIED BY RANDOM PASSWORDMySQL generates and returns a strong password (8.0.18+)
FAILED_LOGIN_ATTEMPTS n PASSWORD_LOCK_TIME dLock the account for d days after n wrong passwords (8.0.19+)
PASSWORD EXPIRE INTERVAL n DAYForce a change every n days
ACCOUNT LOCK / ACCOUNT UNLOCKDisable an account without dropping it
PASSWORD REQUIRE CURRENTChanging 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:

LevelSyntaxExample
GlobalON *.*Administration such as PROCESS, REPLICATION CLIENT
DatabaseON orbit.*Everything in Orbit’s database, including future tables
TableON orbit.notesOne table
ColumnSELECT (id, title) ON orbit.notesSome columns only
RoutineEXECUTE ON PROCEDURE orbit.create_noteOne stored procedure

The privileges you will use most:

PrivilegeAllows
SELECT, INSERT, UPDATE, DELETEReading and changing rows
EXECUTECalling procedures and functions
CREATE, ALTER, DROP, INDEX, REFERENCESChanging the schema
CREATE VIEW, SHOW VIEW, TRIGGER, EVENT, CREATE ROUTINE, ALTER ROUTINEManaging views, triggers, events and routines
LOCK TABLESExplicit table locks (used by some backup tools)
PROCESSSeeing every connection in SHOW PROCESSLIST
RELOAD, REPLICATION CLIENT, REPLICATION SLAVEFlushing, replication status, acting as a replica
ALL PRIVILEGESEverything 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.5 in my.cnf, or 127.0.0.1 when 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 mysql operating-system user (packages do this) and keep the data directory readable only by it.

A security checklist

CheckHow
No anonymous accountsSELECT user, host FROM mysql.user WHERE user = ''; returns nothing
No remote rootroot only @'localhost'
No legacy authSELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password'; returns nothing
Least privilegeEach app has its own account and roles, no ALL ON *.*
TLS enforcedrequire_secure_transport = ON
Private network onlybind-address, firewall
Secrets managedPasswords in a secret manager, rotated with dual passwords
PatchedLatest patch release of your LTS line
Injection-safe codeApplications 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

TaskSQL
Create an accountCREATE USER 'u'@'host' IDENTIFIED BY '...';
Generated passwordCREATE USER 'u'@'h' IDENTIFIED BY RANDOM PASSWORD;
Change a passwordALTER USER 'u'@'h' IDENTIFIED BY '...';
Lock / unlockALTER USER 'u'@'h' ACCOUNT LOCK; / ACCOUNT UNLOCK
Grant / revokeGRANT SELECT ON db.* TO 'u'@'h'; / REVOKE ... FROM ...;
Create and assign a roleCREATE ROLE r; GRANT ... TO r; GRANT r TO 'u'@'h'; SET DEFAULT ROLE ALL TO 'u'@'h';
InspectSHOW GRANTS FOR 'u'@'h' USING r;
Require TLSSET PERSIST require_secure_transport = ON;
Remove an accountDROP USER 'u'@'h';

Exercise

You create ‘orbit’@’localhost’ and later ‘orbit’@’%’ with a different password. How many accounts exist?

  1. One, the second statement changed the password
  2. One, because the user name is the same
  3. Two separate accounts with their own passwords and privileges
  4. None, the second statement fails
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?

  1. Downgrade to MySQL 8.0
  2. Switch the account to caching_sha2_password and update the client library if needed
  3. Grant the account SUPER
  4. Set require_secure_transport = OFF
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.*?

  1. Those privileges make queries faster
  2. MySQL requires it for connection pooling
  3. DDL privileges cannot be granted at database level
  4. A compromised API or SQL injection bug then cannot change or drop the schema
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_password is 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.

↑ ↓ navigate Enter open Esc close