Privileges, Roles, DEFAULT ROLE, Dynamic Privileges, and Delegated Administration

Learning outcomes

Direct grants work for one account, but they become unmanageable when ServiceHub grows to many services and operators. Roles let you name privilege sets; dynamic privileges let MySQL split administrative powers that historically tended to accumulate in one superuser. The important nuance is that a role may be granted yet inactive in a particular session.

Privilege scope is part of the design

A static privilege is built into the server privilege model. A dynamic privilege is registered at runtime, commonly by the server or a component, and is granted at global scope. Dynamic privileges such as CONNECTION_ADMIN, BACKUP_ADMIN, ROLE_ADMIN, or SET_ANY_DEFINER are not “safer because they are dynamic”; they are safer only when you grant the smallest one that matches the administrative job.

Create roles and keep them narrow

DROP ROLE IF EXISTS 'r_servicehub_reader', 'r_servicehub_writer';CREATE ROLE 'r_servicehub_reader', 'r_servicehub_writer';GRANT SELECTON servicehub_security_lab.sitesTO 'r_servicehub_reader';GRANT SELECTON servicehub_security_lab.work_ordersTO 'r_servicehub_reader';GRANT INSERT,UPDATEON servicehub_security_lab.work_ordersTO 'r_servicehub_writer';SHOW GRANTS FOR 'r_servicehub_reader';SHOW GRANTS FOR 'r_servicehub_writer';GRANT 'r_servicehub_reader','r_servicehub_writer'TO 'svc11_app'@'127.0.0.1';-- Chapter 01 used a direct grant for the initial lab. Remove it now so-- Chapter 11 tests role-based access rather than inherited direct access.REVOKE SELECT,INSERT,UPDATEON servicehub_security_lab.work_ordersFROM 'svc11_app'@'127.0.0.1';SHOW GRANTS FOR 'svc11_app'@'127.0.0.1';

The final SHOW GRANTS proves that the roles are assigned; it does not by itself prove that their privileges are active in a new session. MySQL separates role assignment from role activation.

Wrong assumption: granted role means active role

SELECT USER(),CURRENT_USER(),CURRENT_ROLE();SELECT COUNT(*) FROM servicehub_security_lab.sites;-- If no default role is configured, activate a granted role explicitly:SET ROLE 'r_servicehub_reader';SELECT CURRENT_ROLE();SELECT COUNT(*) FROM servicehub_security_lab.sites;-- Return to the account defaults:SET ROLE DEFAULT;SELECT CURRENT_ROLE();

If the account's default role is still NONE, the first query against sites can be denied even though an administrator previously granted the reader role. This is not a broken grant—it is an inactive role. Repair the account's login state deliberately:

SET DEFAULT ROLE 'r_servicehub_reader','r_servicehub_writer'TO 'svc11_app'@'127.0.0.1';SHOW GRANTS FOR 'svc11_app'@'127.0.0.1'USING 'r_servicehub_reader','r_servicehub_writer';SELECT @@global.activate_all_roles_on_login;

By default, activate_all_roles_on_login is OFF, so default roles matter. Avoid turning on automatic activation globally simply to fix one account; that changes login privilege behavior for every account with granted roles.

Dynamic privileges: split administrative capability

SELECT GRANTEE,PRIVILEGE_TYPE,IS_GRANTABLEFROM INFORMATION_SCHEMA.USER_PRIVILEGESWHERE PRIVILEGE_TYPE IN ('CONNECTION_ADMIN','BACKUP_ADMIN','ROLE_ADMIN')ORDER BY GRANTEE,PRIVILEGE_TYPE;DROP ROLE IF EXISTS 'r_servicehub_operator';CREATE ROLE 'r_servicehub_operator';GRANT CONNECTION_ADMIN ON *.* TO 'r_servicehub_operator';SHOW GRANTS FOR 'r_servicehub_operator';

CONNECTION_ADMIN is global and powerful; the lab grants it only to a role so the difference between an application role and an operator role is visible. You do not need to invoke disruptive operations to learn the model. The evidence is the role definition and a negative test proving the application account cannot perform operator actions.

Delegated administration with WITH ADMIN OPTION

DROP USER IF EXISTS 'svc11_teamlead'@'127.0.0.1';CREATE USER 'svc11_teamlead'@'127.0.0.1'  IDENTIFIED BY 'LabOnly-TeamLead!2026';GRANT 'r_servicehub_reader'TO 'svc11_teamlead'@'127.0.0.1'WITH ADMIN OPTION;SET DEFAULT ROLE 'r_servicehub_reader'TO 'svc11_teamlead'@'127.0.0.1';SHOW GRANTS FOR 'svc11_teamlead'@'127.0.0.1';

WITH ADMIN OPTION on a role means the grantee can grant or revoke that role to/from other accounts while the role is active; it does not grant arbitrary CREATE USER or unrelated privilege-management authority. That makes it a useful delegated boundary for a team lead who should manage membership in one approved role but not design new powers.

Production judgment

Hands-on role lab

1. Create reader and writer roles and grant them to the application account.
2. Connect fresh before setting defaults and record CURRENT_ROLE().
3. Activate the reader role manually and prove the read succeeds.
4. Set reader+writer as default roles, reconnect, and verify both positive reads/writes and a negative DROP.
5. Create the team-lead account with admin option only on the reader role.
6. Record the operator role's dynamic privilege separately from application roles.

Summary and bridge to Lesson 3

Roles make privilege sets composable, but least privilege still depends on scope and job separation. Lesson 3 turns that into a separation-of-duties design: application, migration, backup, and operator responsibilities will be deliberately prevented from collapsing into one account.

Authoritative references

• MySQL 8.4 — Privileges Provided by MySQL
• MySQL 8.4 — CREATE ROLE
• MySQL 8.4 — GRANT
• MySQL 8.4 — SET DEFAULT ROLE
• MySQL 8.4 — SHOW GRANTS