MySQL Users and Privileges

Understand MySQL Account Identity

A MySQL account is written as 'user'@'host'. Both parts identify the account. The host is where the client connects from, not where a human lives.

SELECT USER() AS client_identity,
       CURRENT_USER() AS matched_account,
       CURRENT_ROLE() AS active_roles;

Always write the host explicitly. If omitted, MySQL uses %; relying on broad or deprecated matching behavior creates surprises. Use network controls as well as account host restrictions.

Create a Secure User Deliberately

CREATE DATABASE IF NOT EXISTS school_app
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'school_reader'@'10.20.30.%'
  IDENTIFIED BY RANDOM PASSWORD
  REQUIRE SSL
  COMMENT 'Read-only school reporting service';

IDENTIFIED BY RANDOM PASSWORD lets MySQL generate a secret and return it to the authorized administrator. Transfer it once into the approved secret manager; do not paste it into source control, tickets, chat or shell history. A new user has no privileges, so creation and authorization remain separate.

GRANT SELECT ON school_app.*
TO 'school_reader'@'10.20.30.%';

SHOW GRANTS FOR 'school_reader'@'10.20.30.%';

REQUIRE SSL requires encryption but does not by itself make the client verify the server identity. Configure the client with VERIFY_IDENTITY and a trusted CA where supported.

Grant at the Smallest Useful Scope

Prefer the smallest stable scope. An attendance writer may need SELECT,INSERT,UPDATE on attendance tables, not DROP, FILE, user management or access to every database. Avoid ALL PRIVILEGES as a shortcut.

Database names containing _ or % need careful quoting/escaping in grants because wildcard interpretation has version-sensitive behavior. Use simple schema names and review SHOW GRANTS.

Build and Assign Reusable Roles

CREATE ROLE IF NOT EXISTS
  'school_read_role'@'%',
  'school_write_role'@'%';

GRANT SELECT ON school_app.*
TO 'school_read_role'@'%';

GRANT SELECT,INSERT,UPDATE ON school_app.attendance
TO 'school_write_role'@'%';

CREATE USER 'attendance_app'@'10.20.30.%'
  IDENTIFIED BY RANDOM PASSWORD REQUIRE SSL;

GRANT 'school_write_role'@'%'
TO 'attendance_app'@'10.20.30.%';

SET DEFAULT ROLE 'school_write_role'@'%'
TO 'attendance_app'@'10.20.30.%';

A role is a named privilege collection. Granting it and making it default are distinct operations. In an existing session use SET ROLE DEFAULT;, then check CURRENT_ROLE(). Keep role definitions in reviewed migrations and name roles by capability, not employee.

Inspect Grants and Test Effective Access

SHOW GRANTS FOR 'attendance_app'@'10.20.30.%';
SHOW GRANTS FOR 'school_write_role'@'%';
SHOW CREATE USER 'attendance_app'@'10.20.30.%';

SELECT USER(),CURRENT_USER(),CURRENT_ROLE();

SHOW GRANTS reveals direct privileges and role assignments. Inspect the role separately to see its privileges. SHOW CREATE USER shows account properties; access to authentication hashes is restricted.

1. Connect from the same host/network as the application.
2. Verify TLS identity and CURRENT_USER().
3. Activate the intended default role.
4. Run one expected read/write operation.
5. Run one expected denied operation, such as DROP or another schema.
6. Log the review date, owner and approved privilege diff.

Do not infer access only from a username. Host matching, active roles, definers, views and stored routines can change effective behavior.

Manage the Complete Account Lifecycle

-- Temporarily stop login
ALTER USER 'attendance_app'@'10.20.30.%' ACCOUNT LOCK;

-- Unlock after approved remediation
ALTER USER 'attendance_app'@'10.20.30.%' ACCOUNT UNLOCK;

-- Rotate through an approved secret workflow
ALTER USER 'attendance_app'@'10.20.30.%'
  IDENTIFIED BY RANDOM PASSWORD;

-- Remove when no longer needed
DROP USER 'attendance_app'@'10.20.30.%';

Use joiner–mover–leaver controls: owner, purpose, ticket, created date, expiry/review date and rotation procedure. Lock first when investigating; drop only after checking jobs, connection pools, replicas and stored objects whose DEFINER names the account. Orphan definers can break views, routines, triggers or events.

Never manage accounts by directly inserting/updating/deleting rows in mysql.user. Use CREATE USER, ALTER USER, GRANT, REVOKE and DROP USER.

Add TLS, Locking and Resource Controls

ALTER USER 'report_user'@'10.20.30.%'
  REQUIRE SSL
  WITH MAX_USER_CONNECTIONS 5
       MAX_QUERIES_PER_HOUR 5000
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 2;

Resource limits reduce accidental overload; they are not a substitute for query optimization or connection-pool limits. Failed-login tracking can temporarily lock password-authenticated accounts. Test compatibility with the deployed authentication method and recovery procedure.

• Use REQUIRE X509 when managed client certificates are part of the design.
• Enable server-side require_secure_transport where appropriate.
• Clients should prefer certificate and hostname verification.
• Apply password expiry only when policy/risk requires it; forced periodic changes can create weak behavior.
• Protect break-glass accounts, monitor their use and test recovery.

Account Security Review Checklist

1. Every account has an owner and documented purpose.
2. Host is explicit and as narrow as operations allow.
3. Applications never use root or a human administrator.
4. Secrets live in a vault and rotate without code changes.
5. TLS is required and server identity is verified.
6. Privileges/roles are minimal; no unjustified FILE or GRANT OPTION.
7. Default roles are intentional and tested.
8. Direct and role-derived grants are reviewed regularly.
9. Inactive accounts are locked/removed safely.
10. Backups preserve the account/role recovery plan securely.

Continue with GRANT and REVOKE, database security and backup recovery.

Official References

• MySQL 8.4: CREATE USER
• MySQL 8.4: Using Roles
• MySQL 8.4: Account Locking

Account defaults, host identity, role activation and locking syntax were verified against the official MySQL 8.4 manual.