Privileges, Roles, Default Roles, Grant Design, and Delegated Administration

Learning outcomes

ServiceHub now has separate identities, but identity alone does not protect data. An application that only reads and updates ticket status should not be able to create users; an analyst should not update internal notes; a migration job may need DDL that the runtime application never receives. MariaDB authorization is built from privileges granted at scopes such as global, database, table, column and routine, plus roles that bundle privileges into named operational capabilities.

1. Create a role-based lab with explicit denied operations

DROP DATABASE IF EXISTS servicehub_rbac_lab;CREATE DATABASE servicehub_rbac_lab;USE servicehub_rbac_lab;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY,  customer_name VARCHAR(120) NOT NULL,  severity VARCHAR(16) NOT NULL,  internal_note VARCHAR(255),  status VARCHAR(16) NOT NULL) ENGINE=InnoDB;INSERT INTO tickets VALUES(3001,'Northwind Clinic','high','security callback','open'),(3002,'Caspian Foods','medium','billing context','assigned');DROP USER IF EXISTS 'svc_dispatch'@'localhost';DROP USER IF EXISTS 'analyst_ro'@'localhost';DROP USER IF EXISTS 'release_bot'@'localhost';CREATE USER 'svc_dispatch'@'localhost' IDENTIFIED BY 'LabOnly-Dispatch-42!';CREATE USER 'analyst_ro'@'localhost' IDENTIFIED BY 'LabOnly-Analyst-42!';CREATE USER 'release_bot'@'localhost' IDENTIFIED BY 'LabOnly-Release-42!';DROP ROLE IF EXISTS r_dispatch,r_readonly,r_migration,r_security_delegate;CREATE ROLE r_dispatch;CREATE ROLE r_readonly;CREATE ROLE r_migration;CREATE ROLE r_security_delegate;

Roles start with no useful privileges. That is valuable: a role should express a reviewed capability, not “all access because it is convenient.”

2. Grant the smallest scope that represents the job

GRANT SELECT(ticket_id,customer_name,severity,status),      UPDATE(status)ON servicehub_rbac_lab.tickets TO r_dispatch;GRANT SELECT(ticket_id,customer_name,severity,status)ON servicehub_rbac_lab.tickets TO r_readonly;GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,INDEXON servicehub_rbac_lab.* TO r_migration;GRANT r_dispatch TO 'svc_dispatch'@'localhost';GRANT r_readonly TO 'analyst_ro'@'localhost';GRANT r_migration TO 'release_bot'@'localhost';SET DEFAULT ROLE r_dispatch FOR 'svc_dispatch'@'localhost';SET DEFAULT ROLE r_readonly FOR 'analyst_ro'@'localhost';SET DEFAULT ROLE r_migration FOR 'release_bot'@'localhost';

The application can update only status; it cannot read internal_note. The analyst cannot modify rows. The release bot can make schema/data changes inside this database but does not receive global user-management or FILE privileges.

3. Roles are granted and activated separately

Granting a role makes it available to an account. Its privileges are used when the role becomes current. A default role is activated automatically at connection time. In a session, SET ROLE replaces the active role rather than accumulating multiple active roles.

SHOW GRANTS FOR 'svc_dispatch'@'localhost';SHOW GRANTS FOR r_dispatch;SELECT * FROM information_schema.APPLICABLE_ROLESWHERE GRANTEE LIKE '%svc_dispatch%';-- Run in a session connected as svc_dispatch:SELECT CURRENT_ROLE();SHOW GRANTS;SET ROLE NONE;SELECT CURRENT_ROLE();SET ROLE r_dispatch;SELECT CURRENT_ROLE();

Do not infer effective runtime privilege only from the role’s definition. Test a connection using the exact account and confirm its default/current role.

4. Deliberate failures prove least privilege

A security test suite needs denied operations. If every test succeeds, you have tested functionality rather than authorization.

SELECT ticket_id,customer_name,severity,statusFROM servicehub_rbac_lab.tickets;UPDATE servicehub_rbac_lab.ticketsSET status='assigned'WHERE ticket_id=3001;-- Expected denial: internal_note was not granted for SELECT.SELECT ticket_id,internal_noteFROM servicehub_rbac_lab.tickets;-- Expected denial: only status was granted for UPDATE.UPDATE servicehub_rbac_lab.ticketsSET internal_note='should fail'WHERE ticket_id=3001;-- Expected denial: application role has no DDL authority.ALTER TABLE servicehub_rbac_lab.tickets ADD COLUMN unsafe_col INT;

Record the exact denial and verify the row/table remained unchanged. Denials are acceptance evidence: they prove the capability boundary is narrower than an administrator session.

SELECT ticket_id,status,internal_noteFROM servicehub_rbac_lab.ticketsWHERE ticket_id=3001;SHOW COLUMNS FROM servicehub_rbac_lab.tickets;

5. Delegated administration: role admin is not global grant power

WITH GRANT OPTION can let an account grant privileges it holds and becomes dangerous at broad scopes. For role delegation, WITH ADMIN OPTION is the narrower concept: it allows the grantee to grant that role onward, without automatically handing over arbitrary global privileges.

CREATE USER IF NOT EXISTS 'team_lead'@'localhost'  IDENTIFIED BY 'LabOnly-TeamLead-42!';GRANT r_readonly TO 'team_lead'@'localhost' WITH ADMIN OPTION;SHOW GRANTS FOR 'team_lead'@'localhost';

That does not mean the team lead can grant FILE, CREATE USER or any unrelated capability. Keep delegation explicit and reviewable. Avoid name collisions between users and roles because MariaDB can prefer the role interpretation in ambiguous grant syntax.

6. Privilege discovery must be version-aware

MariaDB has evolved from broad administrative capabilities toward more fine-grained privileges. Do not freeze a privilege list into your architecture. Ask the target server what it supports.

SHOW PRIVILEGES;SHOW GRANTS FOR 'release_bot'@'localhost';SHOW GRANTS FOR r_migration;

Privileges such as SHOW CREATE ROUTINE are version-sensitive. A migration framework should declare required capabilities and fail closed when the target server does not support or grant them.

7. Production verification and cleanup

1. Capture SHOW GRANTS for every account and role.
2. Connect as each account and verify CURRENT_ROLE().
3. Run at least one allowed and one denied operation per role.
4. Review every use of global scope *.*, WITH GRANT OPTION and WITH ADMIN OPTION.
5. Keep account-to-role mappings under change control.

DROP USER IF EXISTS 'svc_dispatch'@'localhost';DROP USER IF EXISTS 'analyst_ro'@'localhost';DROP USER IF EXISTS 'release_bot'@'localhost';DROP USER IF EXISTS 'team_lead'@'localhost';DROP ROLE IF EXISTS r_dispatch,r_readonly,r_migration,r_security_delegate;DROP DATABASE IF EXISTS servicehub_rbac_lab;

Lesson 3 applies these authorization ideas to stored objects, where the caller and the stored object’s definer may be different security identities.