Chapter 11 · Users, Roles, Authentication, Authorization, and Least Privilege
Object-Level vs Administrative Privilege Design and Separation of Duties
Separate application, migration, backup, and operator duties into independently testable MySQL roles and accounts with explicit positive and negative authorization evidence.
Learning outcomes
The most damaging MySQL privilege designs are often not “wrong” at the individual statement level; they are wrong because one identity can perform mutually risky jobs. ServiceHub should not let the same application credential modify customer rows, alter schema, administer sessions, and perform privileged backup operations. This lesson turns least privilege into separation of duties.
Separate application DML, migration DDL, backup/read, and operator administrative capabilities into distinct roles and accounts.
Use positive and negative tests to prove each actor can perform required work and is denied unrelated work.
Distinguish object-level privileges from global/dynamic administrative privileges and explain their blast radius.
Design migration and backup roles without granting broad wildcard administration to the application.
Produce a privilege evidence table that can be reviewed and version-controlled as part of deployment.
Start from actors and actions, not privilege names
| Actor | Required actions | Should not be able to |
|---|---|---|
| ServiceHub application | Read sites/work orders; insert/update work orders | Change schema, create users, kill sessions, perform backup administration |
| Migration job | Create/alter/drop application objects and indexes during controlled deployment | Operate server connections or run as the application forever |
| Backup job | Read/lock/backup as required by the chosen backup method | Modify business rows or alter schema |
| Operator | Inspect/operate server sessions under runbook control | Read private business data merely because they can operate the server |
This matrix is a security requirement before it is SQL. The exact privileges for backup tooling can vary by method and version, so a production backup role must be derived from the tool’s current documentation. The lab models the boundary without pretending one privilege recipe fits every backup product.
Create separate accounts and roles
DROP USER IF EXISTS 'svc11_migrator'@'127.0.0.1', 'svc11_backup'@'127.0.0.1', 'svc11_operator'@'127.0.0.1';DROP ROLE IF EXISTS 'r_servicehub_migration', 'r_servicehub_backup', 'r_servicehub_ops';CREATE USER 'svc11_migrator'@'127.0.0.1' IDENTIFIED BY 'LabOnly-Migrate!2026';CREATE USER 'svc11_backup'@'127.0.0.1' IDENTIFIED BY 'LabOnly-Backup!2026';CREATE USER 'svc11_operator'@'127.0.0.1' IDENTIFIED BY 'LabOnly-Operate!2026';CREATE ROLE 'r_servicehub_migration','r_servicehub_backup','r_servicehub_ops';GRANT CREATE,ALTER,DROP,INDEX,REFERENCES,CREATE VIEW,SHOW VIEW,TRIGGERON servicehub_security_lab.* TO 'r_servicehub_migration';GRANT SELECT,SHOW VIEW,TRIGGER,EVENTON servicehub_security_lab.* TO 'r_servicehub_backup';GRANT BACKUP_ADMIN ON *.* TO 'r_servicehub_backup';GRANT PROCESS ON *.* TO 'r_servicehub_ops';GRANT CONNECTION_ADMIN ON *.* TO 'r_servicehub_ops';GRANT 'r_servicehub_migration' TO 'svc11_migrator'@'127.0.0.1';GRANT 'r_servicehub_backup' TO 'svc11_backup'@'127.0.0.1';GRANT 'r_servicehub_ops' TO 'svc11_operator'@'127.0.0.1';SET DEFAULT ROLE 'r_servicehub_migration' TO 'svc11_migrator'@'127.0.0.1';SET DEFAULT ROLE 'r_servicehub_backup' TO 'svc11_backup'@'127.0.0.1';SET DEFAULT ROLE 'r_servicehub_ops' TO 'svc11_operator'@'127.0.0.1';BACKUP_ADMIN is a global dynamic privilege used by backup-related operations, but actual backup tools can require additional privileges. Derive a production role from the exact current tool and backup mode; do not cargo-cult this lab role.
Prove the migration boundary
-- Connect as svc11_migrator over TCP.SELECT CURRENT_USER(),CURRENT_ROLE();CREATE INDEX ix_wo_status ON servicehub_security_lab.work_orders(status);ALTER TABLE servicehub_security_lab.work_orders ADD COLUMN migration_tag VARCHAR(20) NULL;-- Expected denial: migration role has no UPDATE privilege on business rows.UPDATE servicehub_security_lab.work_ordersSET private_note='migration should not edit data'WHERE work_order_id=1001;ALTER TABLE servicehub_security_lab.work_orders DROP COLUMN migration_tag;DROP INDEX ix_wo_status ON servicehub_security_lab.work_orders;Some real migrations need controlled data backfills. If so, grant the specific DML needed for the migration window or use a separate backfill role rather than permanently turning the migration identity into the application identity.
Prove backup and operator boundaries
-- Connect as svc11_backup.SELECT CURRENT_USER(),CURRENT_ROLE();SELECT COUNT(*) FROM servicehub_security_lab.work_orders;SHOW CREATE TABLE servicehub_security_lab.work_orders;-- Expected denial:UPDATE servicehub_security_lab.work_ordersSET status='CLOSED'WHERE work_order_id=1001;-- Connect as svc11_operator.SELECT CURRENT_USER(),CURRENT_ROLE();SHOW PROCESSLIST;-- Expected denial: no business-data SELECT was granted.SELECT private_noteFROM servicehub_security_lab.work_ordersWHERE work_order_id=1001;This is separation of duties in executable form: an operator may have powerful server-scope capability without inheriting business-data access. Whether that split is sufficient for your threat model depends on infrastructure access as well—an OS administrator or cloud administrator may have paths outside MySQL privileges. Document those separately.
Wrong design: one “ops_app” super-account
A common shortcut is to give one credential application DML, DDL, backup privileges, session administration, and grant powers. That credential then has the blast radius of every team that uses it. Rotation becomes harder, audit attribution becomes weaker, and a compromise crosses multiple control planes.
The repair is not “use four passwords” by itself. The repair is four distinct identities with distinct role sets, ownership, rotation, deployment paths, and negative tests.
Audit the separation
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 GRANTEE,PRIVILEGE_TYPE,IS_GRANTABLEFROM INFORMATION_SCHEMA.USER_PRIVILEGESWHERE GRANTEE LIKE '%svc11_%'ORDER BY GRANTEE,PRIVILEGE_TYPE;SHOW GRANTS is the authoritative account-oriented audit view; INFORMATION_SCHEMA.USER_PRIVILEGES is useful for global privileges but does not replace table/database/routine grant inspection. Audit at all scopes that your design uses.
Production judgment
| Risk | Design response |
|---|---|
| Application credential leaked | No DDL, no account admin, no backup admin, no operator role |
| Migration automation compromised | No long-lived application identity; limit network source and deployment window where possible |
| Backup credential leaked | No business writes; secure backup destination and encryption separately |
| Operator credential abused | Operational privilege without automatic access to private application rows |
| Role definition drifts | Version-control intended grants and continuously compare to SHOW GRANTS evidence |
Hands-on separation test
- Create the three specialized accounts and roles.
- Open one fresh session per account.
- For each actor, run one required operation and one forbidden operation.
- Record exact denial messages and do not “repair” them if the denial matches the policy.
- Run
SHOW GRANTS ... USINGfor each account and attach the output to your lab notes. - Confirm the application account has no administrative dynamic privilege.
Knowledge check
- Why is a denied statement sometimes a successful test?
- Should the application account receive migration DDL because deployments sometimes need it?
- Does
BACKUP_ADMINalone define every production backup role? - Why can an operator have PROCESS/CONNECTION_ADMIN without SELECT on business tables?
- What makes separation of duties auditable?
Reveal answers
- Because the policy includes forbidden actions; an expected denial proves that part of the least-privilege boundary is working.
- No. Deployment/migration should use a distinct identity or controlled temporary privilege path.
- No. Exact requirements depend on the backup tool/mode/version and must come from its current documentation.
- Operational server control and application-data access are separate responsibilities and can be granted independently.
- Distinct accounts/roles, explicit grants, fresh-session tests, ownership/rotation records, and retained positive/negative evidence.
Summary and bridge to Lesson 4
Least privilege is now more than “few grants”: it is a job model whose roles cannot silently substitute for each other. Lesson 4 examines the most subtle privilege boundary in this chapter—stored objects that can execute in a definer context, plus the plugin-dependent PROXY mechanism.