Design and Audit a Least-Privilege Access Model for Applications and Operators

Learning outcomes

A least-privilege design is incomplete until it can be audited and operated. This final lesson converts everything from the chapter into an access matrix, creates deterministic positive and negative tests, and defines rotation/revocation/offboarding procedures. The output is a small security runbook you could hand to an application owner, DBA, or reviewer.

Build the access matrix before touching GRANT

The matrix is a contract. SQL grants are merely its implementation. If a future privilege cannot be traced to an actor/action requirement, it needs review rather than an automatic GRANT.

Audit account properties and direct grants

SELECT User,Host,plugin,account_locked,password_expiredFROM mysql.userWHERE User LIKE 'svc11_%'ORDER BY User,Host;SELECT User,Host,Default_role_user,Default_role_hostFROM mysql.default_rolesWHERE User LIKE 'svc11_%'ORDER BY User,Host,Default_role_user;SHOW GRANTS FOR 'svc11_app'@'127.0.0.1';SHOW GRANTS FOR 'svc11_migrator'@'127.0.0.1';SHOW GRANTS FOR 'svc11_backup'@'127.0.0.1';SHOW GRANTS FOR 'svc11_operator'@'127.0.0.1';SHOW GRANTS FOR 'svc11_logic_owner'@'127.0.0.1';

Account metadata tells you lifecycle/authentication state. SHOW GRANTS tells you direct roles/privileges. Neither alone proves what a fresh application session can do; active roles and stored-object context can change effective capability.

Expand roles and audit stored privilege boundaries

SHOW GRANTS FOR 'svc11_app'@'127.0.0.1'  USING 'r_servicehub_reader','r_servicehub_writer';SHOW GRANTS FOR 'svc11_migrator'@'127.0.0.1'  USING 'r_servicehub_migration';SHOW GRANTS FOR 'svc11_backup'@'127.0.0.1'  USING 'r_servicehub_backup';SHOW GRANTS FOR 'svc11_operator'@'127.0.0.1'  USING 'r_servicehub_ops';SELECT ROUTINE_NAME,ROUTINE_TYPE,DEFINER,SECURITY_TYPEFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_SCHEMA='servicehub_security_lab';SELECT TABLE_NAME,DEFINER,SECURITY_TYPEFROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA='servicehub_security_lab';

For each definer object, ask two questions: does the definer still exist, and does it hold only the underlying privileges that the object truly needs? A clean application grant audit can still hide an unsafe definer boundary if you stop at SHOW GRANTS FOR svc11_app.

Run a deterministic authorization test suite

-- Fresh svc11_app connection:SELECT CURRENT_USER(),CURRENT_ROLE();SELECT work_order_id,status,summaryFROM servicehub_security_lab.work_ordersORDER BY work_order_id;-- Required operation through the reviewed API:CALL servicehub_security_lab.close_work_order(1002);-- Expected denials:DROP TABLE servicehub_security_lab.work_orders;CREATE USER 'should_not_work'@'127.0.0.1';SHOW PROCESSLIST;

-- Fresh svc11_migrator connection: DDL should work, private business UPDATE should be denied.-- Fresh svc11_backup connection: SELECT should work, UPDATE should be denied.-- Fresh svc11_operator connection: SHOW PROCESSLIST should work,-- but SELECT private_note FROM servicehub_security_lab.work_orders should be denied.-- Record for every connection:SELECT USER(),CURRENT_USER(),CURRENT_ROLE(),CONNECTION_ID();SHOW SESSION STATUS LIKE 'Ssl_cipher';SHOW GRANTS;

Do not normalize away denials. Capture the exact error code/message as test evidence. A future deployment that unexpectedly turns a denial into success is a security regression.

Wrong audit: “SHOW GRANTS looks small, therefore we are secure”

Rotation, revocation, and offboarding runbook

Planned credential rotation

Coordinate credential update with the application secret store and connection pools. Change the password using ALTER USER, validate a new connection, then drain old connections according to your application behavior. Chapter 12 will cover secret storage and TLS in depth.

-- Planned rotation:ALTER USER 'svc11_app'@'127.0.0.1'  IDENTIFIED BY 'LabOnly-FinalRotate!2026';-- Emergency stop without deleting evidence/grants:ALTER USER 'svc11_app'@'127.0.0.1' ACCOUNT LOCK;-- Restore after investigation if policy allows:ALTER USER 'svc11_app'@'127.0.0.1' ACCOUNT UNLOCK;-- Remove a role assignment when no longer needed:REVOKE 'r_servicehub_writer' FROM 'svc11_app'@'127.0.0.1';SET DEFAULT ROLE 'r_servicehub_reader' TO 'svc11_app'@'127.0.0.1';

Offboarding sequence

1. Identify owner and dependent applications/jobs.
2. Lock the account to stop new authentication while preserving metadata.
3. Observe for failed dependency attempts during an agreed window.
4. Reassign or recreate stored objects whose definer points to the account.
5. Revoke role memberships and grants no longer required.
6. Drop the account after dependencies and ownership are clean.
7. Retain the change ticket/audit record outside the database according to policy.

Final cleanup for the disposable Chapter 11 lab

-- Run as the local lab administrator after closing all svc11_* sessions.DROP PROCEDURE IF EXISTS servicehub_security_lab.close_work_order;DROP DATABASE IF EXISTS servicehub_security_lab;DROP USER IF EXISTS  'svc11_app'@'127.0.0.1',  'svc11_teamlead'@'127.0.0.1',  'svc11_migrator'@'127.0.0.1',  'svc11_backup'@'127.0.0.1',  'svc11_operator'@'127.0.0.1',  'svc11_logic_owner'@'127.0.0.1';DROP ROLE IF EXISTS  'r_servicehub_reader',  'r_servicehub_writer',  'r_servicehub_operator',  'r_servicehub_migration',  'r_servicehub_backup',  'r_servicehub_ops';

The cleanup uses exact lab names. Never run wildcard-style account removal on a shared instance.

Production acceptance checklist

• Every account has a named owner, purpose, host scope, authentication method, and rotation policy.
• Application privileges are object-scoped or routine-scoped; no unexplained *.* grant exists.
• Roles are activated deliberately and verified in fresh sessions.
• Administrative dynamic privileges belong to operator/deployment roles, not application identities.
• Positive tests prove required operations; negative tests prove forbidden operations.
• Every stored-object definer exists, is narrowly privileged, and has documented ownership.
• Offboarding covers both login identities and stored-object ownership.
• TLS/secrets/audit controls are recorded as Chapter 12 follow-ups rather than assumed to be solved by grants.

Chapter summary and bridge to Chapter 12

Chapter 11 built MySQL access control from first principles. Accounts are user+host identities; authentication is distinct from authorization; caching_sha2_password is the normal 8.4 default under the standard policy; roles must be activated; dynamic privileges split administrative powers; separation of duties prevents one credential from accumulating every job; and definer/proxy mechanisms can cross privilege boundaries only when their trust model is explicit.

Chapter 12 now protects those identities and sessions in transit and at rest: TLS verification, secrets storage/rotation, encryption/key-management boundaries, audit/logging choices, and a broader hardening checklist.

Authoritative references

• MySQL 8.4 — Access Control and Account Management
• MySQL 8.4 — SHOW GRANTS
• MySQL 8.4 — Account Management Statements
• MySQL 8.4 — Stored Object Access Control
• MySQL 8.4 — Using Encrypted Connections