Accounts, Host Matching, Authentication Plugins, Password Policy, and Account Lifecycle

Learning outcomes

A ServiceHub deployment is about to expose MySQL to a second application host. The team has a dangerous assumption: “the username identifies the user.” MySQL does not work that way. The account identity used for authentication is a user plus host match, and after that identity is accepted, authorization begins as a separate stage.

Authentication is stage 1; privileges are stage 2

MySQL access control is easiest to reason about as two gates. Stage 1 decides whether a connection may become a session: the server finds the most appropriate account row for the supplied user name and client host, verifies the authentication method and credentials, and rejects locked accounts. Stage 2 checks each statement against the privileges of the authenticated identity and active roles.

That distinction explains two errors learners often mix together. “Access denied for user …” during connection is primarily an identity/authentication problem. “SELECT command denied …” after a successful login is an authorization problem. Fixing the first with GRANT ALL is conceptually wrong because privileges cannot repair a failed stage-1 identity match.

Build the lab and inspect security defaults

-- Run as a local MySQL administrator in a disposable development instance.DROP DATABASE IF EXISTS servicehub_security_lab;CREATE DATABASE servicehub_security_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE servicehub_security_lab.sites (  site_id BIGINT UNSIGNED PRIMARY KEY,  site_name VARCHAR(80) NOT NULL) ENGINE=InnoDB;CREATE TABLE servicehub_security_lab.work_orders (  work_order_id BIGINT UNSIGNED PRIMARY KEY,  site_id BIGINT UNSIGNED NOT NULL,  status ENUM('OPEN','IN_PROGRESS','CLOSED') NOT NULL DEFAULT 'OPEN',  summary VARCHAR(200) NOT NULL,  private_note VARCHAR(300) NULL,  CONSTRAINT fk_wo_site FOREIGN KEY (site_id)    REFERENCES servicehub_security_lab.sites(site_id)) ENGINE=InnoDB;INSERT INTO servicehub_security_lab.sites VALUES  (1,'North Plant'),(2,'Harbor Workshop');INSERT INTO servicehub_security_lab.work_orders  (work_order_id,site_id,status,summary,private_note) VALUES  (1001,1,'OPEN','Inspect pump seal','Vendor quote pending'),  (1002,1,'IN_PROGRESS','Replace motor bearing','Night shift approved'),  (1003,2,'CLOSED','Crane brake inspection','Internal audit complete');

SELECT VERSION() AS server_version,       @@hostname AS server_host,       @@port AS tcp_port,       @@authentication_policy AS authentication_policy;SELECT component_urnFROM mysql.componentORDER BY component_urn;SHOW VARIABLES LIKE 'validate_password.%';SHOW SESSION STATUS LIKE 'Ssl_cipher';

On a normal 8.4 server whose first authentication-policy element is *, accounts created with IDENTIFIED BY use caching_sha2_password. Do not make the lesson depend on the password-validation component being present. If SHOW VARIABLES LIKE 'validate_password.%' returns no rows, the component is not loaded; if it returns variables, those values describe the active policy.

Create a deliberately narrow account and prove the host part

DROP USER IF EXISTS 'svc11_app'@'127.0.0.1';CREATE USER 'svc11_app'@'127.0.0.1'  IDENTIFIED BY 'LabOnly-ChangeMe!2026';SHOW CREATE USER 'svc11_app'@'127.0.0.1';SHOW GRANTS FOR 'svc11_app'@'127.0.0.1';SELECT User,Host,plugin,account_locked,password_expiredFROM mysql.userWHERE User='svc11_app';

A new account has no application privilege merely because it can authenticate. SHOW GRANTS should initially show only the account's baseline usage state. Connect explicitly over TCP so the lab is not confused by local socket/named-pipe behavior:

mysql --protocol=TCP -h 127.0.0.1 -P 3306 -u svc11_app -p-- Enter the disposable lab password when prompted; do not put it on the command line.SELECT USER(), CURRENT_USER(), CONNECTION_ID();SHOW SESSION STATUS LIKE 'Ssl_cipher';SELECT COUNT(*) FROM servicehub_security_lab.work_orders;

The connection should succeed, while the final SELECT should fail because no SELECT privilege has been granted. This is the cleanest demonstration that authentication and authorization are separate.

Wrong repair: widen the account or grant everything

GRANT SELECT,INSERT,UPDATEON servicehub_security_lab.work_ordersTO 'svc11_app'@'127.0.0.1';SHOW GRANTS FOR 'svc11_app'@'127.0.0.1';

Reconnect in a fresh session and repeat the SELECT. It should now succeed. A destructive statement such as DROP TABLE servicehub_security_lab.work_orders should still fail. That negative test is as important as the positive test: least privilege means proving both required capability and forbidden capability.

Account lifecycle: rotate, expire, lock, and remove

ALTER USER 'svc11_app'@'127.0.0.1'  IDENTIFIED BY 'LabOnly-Rotated!2026';ALTER USER 'svc11_app'@'127.0.0.1'  PASSWORD EXPIRE INTERVAL 90 DAY;ALTER USER 'svc11_app'@'127.0.0.1' ACCOUNT LOCK;SHOW CREATE USER 'svc11_app'@'127.0.0.1';ALTER USER 'svc11_app'@'127.0.0.1' ACCOUNT UNLOCK;SHOW CREATE USER 'svc11_app'@'127.0.0.1';

Password rotation clears the caching_sha2_password cache entry for the account. A first subsequent authentication may therefore need a secure transport or RSA-based password exchange depending on client/server configuration. In production, test connector compatibility before rotating thousands of accounts.

Locking is a deliberate offboarding or incident-control tool: it prevents successful connection without silently rewriting grants. Dropping is final cleanup. Prefer a lock-and-observe window for many real offboarding workflows before deletion, especially when you need evidence that no dependency still uses the identity.

Production judgment and monitoring

Hands-on acceptance test

1. Bootstrap the schema.
2. Create svc11_app@127.0.0.1 with no privileges.
3. Prove login succeeds and table access fails.
4. Grant only SELECT, INSERT, UPDATE on the one table and prove the read succeeds.
5. Prove DROP TABLE is still denied.
6. Rotate the password, lock the account, verify login denial, unlock it, and verify login again.
7. Record USER(), CURRENT_USER(), CURRENT_ROLE(), and Ssl_cipher in your lab notes.

Summary and bridge to Lesson 2

You now have a precise account lifecycle: identify the host-scoped account, authenticate with the current plugin, observe transport and effective identity, grant only the application capability, and manage rotation/lock/removal as explicit operations. Lesson 2 adds roles and dynamic privileges so those permissions can be composed and delegated without collapsing back into global administrator accounts.

Authoritative references

• MySQL 8.4 — Access Control and Account Management
• MySQL 8.4 — Specifying Account Names
• MySQL 8.4 — CREATE USER
• MySQL 8.4 — caching_sha2_password
• MySQL 8.4 — Password Validation Component