MySQL User & Permission Management: From Grant Statements to Production-Grade Security Architecture

Why MySQL permission management is hard

Typical incidents show that granting ALL PRIVILEGES or using a wildcard host (e.g. '%') quickly leads to credential leakage, lateral movement, and a “revocation vacuum” where existing pooled connections keep old rights.

MySQL authentication and authorization fundamentals

Connection flow

Client initiates connection
  -> match user@host
  -> run authentication plugin
  -> check lock/expire/resource limits
  -> load session‑visible privileges
  -> enter SQL execution phase

User@host is the first real boundary

MySQL identifies accounts by the user@host pair. Different hosts can have different privileges, and using % widens the attack surface dramatically.

CREATE USER 'app'@'%' IDENTIFIED BY 'xxx';
GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO 'app'@'%';

Risks of the above pattern:

If the credential leaks, any reachable host can log in.

It becomes hard to know which pod or node is using the credential.

Reusing the account causes privilege boundaries to drift.

Permission granularity

Global: *.* – only for a few infrastructure accounts.

Database: db.* – common for small‑to‑medium services.

Table: db.table – micro‑service isolation, read/write split.

Column: db.table.column – data masking, restricted queries.

Routine: stored procedures / functions.

Role: role -> user – batch grant, responsibility reuse.

Production practice prefers table‑level grants for services, adds column‑level constraints for sensitive data, and uses roles for groups of similar accounts.

Beyond GRANT

Authentication plugin (e.g. caching_sha2_password).

Password expiration, failed‑login limits, lock time.

SSL/TLS requirement.

Resource limits such as MAX_USER_CONNECTIONS, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR.

MySQL 8 roles and dynamic permission changes

CREATE ROLE IF NOT EXISTS 'role_order_ro';
CREATE ROLE IF NOT EXISTS 'role_order_rw';
CREATE ROLE IF NOT EXISTS 'role_payment_ro';
CREATE ROLE IF NOT EXISTS 'role_payment_settle';
CREATE ROLE IF NOT EXISTS 'role_audit_append';
CREATE ROLE IF NOT EXISTS 'role_dba_readonly';

GRANT SELECT ON `order_db`.`orders` TO 'role_order_ro';
GRANT SELECT, INSERT, UPDATE ON `order_db`.`orders` TO 'role_order_rw';
GRANT SELECT ON `payment_db`.`payment_records` TO 'role_payment_ro';
GRANT SELECT, INSERT, UPDATE ON `payment_db`.`payment_records` TO 'role_payment_settle';
GRANT INSERT ON `audit_db`.`permission_audit_log` TO 'role_audit_append';

GRANT 'role_order_rw' TO 'svc_order_api'@'10.20.%';
SET DEFAULT ROLE 'role_order_rw' TO 'svc_order_api'@'10.20.%';
GRANT 'role_payment_settle' TO 'svc_payment_worker'@'10.21.%';
SET DEFAULT ROLE 'role_payment_settle' TO 'svc_payment_worker'@'10.21.%';
GRANT 'role_dba_readonly' TO 'dba_oncall_zhangsan'@'172.16.8.%';
SET DEFAULT ROLE 'role_dba_readonly' TO 'dba_oncall_zhangsan'@'172.16.8.%';

Benefits: a single role edit updates all attached users, baseline vs. diff becomes easy to audit.

Production design principles

Minimal privilege is a default policy

For an order service, the minimal set is: SELECT – query orders. INSERT – create orders. UPDATE – change order status.

Never grant DROP, ALTER, CREATE, INDEX, CREATE USER, or GRANT OPTION to application accounts.

Separate service, human, and platform accounts

Service accounts: prefix svc_, minimal rights, auto‑rotation, limited source.

Human accounts: prefix hum_ or dba_, strong authentication, short‑term approval, full audit.

Platform accounts: prefix ops_ or sys_, used for backup, monitoring, governance controller.

Hard rules:

Human users must never hold long‑term service credentials.

Service accounts must never perform troubleshooting, DDL, or audit export tasks.

Align permissions with business boundaries, not database instances

In micro‑services a single MySQL instance may host many logical databases. The recommended flow:

Split roles by business domain (e.g. role_order_ro, role_order_rw).

Further split by read/write responsibilities.

Finally grant at the table level.

Every permission should have a lifecycle

Temporary troubleshooting accounts have an expiration time.

Developer read‑only accounts expire after a ticket window.

Service credentials rotate daily or weekly.

Unused roles are periodically cleaned.

Long‑inactive accounts are automatically frozen.

Changes must be auditable, rollback‑able, and traceable

Pre‑change expected state.

Approval workflow.

Post‑change audit log.

Fast rollback on failure.

Ability to answer “who changed what, when”.

Permission‑as‑code workflow

Store the desired state in a declarative format (YAML or JSON) and let a platform reconcile it to actual SQL.

apiVersion: db.platform/v1
kind: MySQLAccountPolicy
metadata:
  name: order-service-prod
spec:
  cluster: mysql-prod-order-01
  user:
    name: svc_order_api
    host: 10.20.%
    authPlugin: caching_sha2_password
    tlsRequired: true
    maxUserConnections: 30
    passwordRotationDays: 30
  roles:
    - role_order_rw
  owners:
    system: order-service
    team: trade-platform
  approval:
    required: true
    ticket: SEC-24819

The platform performs three steps:

Read the current state from mysql.user and SHOW GRANTS.

Diff the current state against the desired state.

Execute only the minimal set of CREATE USER, ALTER USER, GRANT, REVOKE statements inside a transaction.

Python reconciler (core idea)

from __future__ import annotations
from dataclasses import dataclass, field
from typing import Dict, List, Set, Tuple
import json, pymysql

@dataclass(frozen=True)
class AccountSpec:
    username: str
    host: str
    auth_plugin: str
    require_ssl: bool
    max_user_connections: int
    roles: Tuple[str, ...]
    profile: Dict[str, str] = field(default_factory=dict)

@dataclass
class ChangePlan:
    create_users: List[AccountSpec] = field(default_factory=list)
    alter_users: List[AccountSpec] = field(default_factory=list)
    grant_roles: List[Tuple[str, str, str]] = field(default_factory=list)
    revoke_roles: List[Tuple[str, str, str]] = field(default_factory=list)
    drop_users: List[Tuple[str, str]] = field(default_factory=list)
    def is_empty(self) -> bool:
        return not any([self.create_users, self.alter_users, self.grant_roles, self.revoke_roles, self.drop_users])

class PermissionReconciler:
    def __init__(self, conn_args: Dict[str, str], vault_client, audit_client):
        self.conn_args = conn_args
        self.vault_client = vault_client
        self.audit_client = audit_client

    def reconcile(self, desired: List[AccountSpec]) -> ChangePlan:
        current = self._load_current_state()
        plan = self._build_plan(current, desired)
        if plan.is_empty():
            return plan
        self._precheck(plan)
        self._execute(plan)
        self._emit_audit(plan)
        return plan

    # _load_current_state, _load_roles, _build_plan, _precheck, _execute, _emit_audit omitted for brevity

Key production features:

Read‑current‑state then compute diff – never replay the whole grant set.

Dangerous operations (e.g. dropping service users) require explicit approval.

All changes happen inside a transaction with commit/rollback.

Integration with Vault for password generation and with an audit client for immutable logs.

Kubernetes & cloud‑native considerations

Pod IPs change; rely on host‑based user@host only as a first layer.

Enforce network‑level restrictions with NetworkPolicy or security groups.

Use ServiceAccount, Namespace, and workload labels for runtime isolation.

Issue one credential per service – never share secrets across pods.

Permission changes must be tied to a deployment rollout so that old pods are terminated before the new credential takes effect.

Modify declaration → Platform reconciles → Update credentials → Trigger Deployment rollout → Rebuild connection pool → Verify old connections are gone

A typical CRD representation:

apiVersion: platform.example.com/v1alpha1
kind: MySQLAccess
metadata:
  name: order-service-access
  namespace: trade-prod
spec:
  clusterRef: mysql-prod-order-01
  account:
    username: svc_order_api
    host: 10.20.%
    authPlugin: caching_sha2_password
    requireSSL: true
    maxUserConnections: 30
  roles:
    - role_order_rw
  secretRef:
    name: order-db-credential
    key: password
  rollout:
    restartDeployments:
      - order-service
      - order-worker
status:
  phase: Ready
  observedGeneration: 7
  lastReconciledAt: "2026-07-07T10:30:00Z"

Credential lifecycle management

Common problems include passwords in config files, printed in logs, shared across services, and never rotated.

Recommended principles:

Never commit passwords to source control.

Never store passwords in plain‑text configuration files.

Each service gets an independent credential.

Credentials have a fixed rotation period (e.g. 30 days).

Rotation supports zero‑downtime switch.

After revocation, verify impact before final removal.

Generate new password → Write new version to secret store → ALTER USER password in MySQL → Deploy or refresh connection pool → Observe success rate → Clean up old credential

Python password rotator (simplified)

import secrets, string
from datetime import datetime

class PasswordRotator:
    def __init__(self, mysql_client, secret_store, deploy_client, metrics):
        self.mysql_client = mysql_client
        self.secret_store = secret_store
        self.deploy_client = deploy_client
        self.metrics = metrics

    def rotate(self, username: str, host: str, workload_names: list[str]):
        new_password = self._generate_password()
        self.secret_store.write_new_version(
            key=f"mysql/{username}",
            payload={"password": new_password, "rotatedAt": datetime.utcnow().isoformat()},
        )
        self.mysql_client.alter_user_password(username=username, host=host, password=new_password)
        for workload in workload_names:
            self.deploy_client.rollout_restart(workload)
        self._wait_until_new_connections_stable(username)
        self.metrics.emit("mysql.password.rotation.success", 1, {"user": username})

    def _wait_until_new_connections_stable(self, username: str):
        # In production watch connection success rate, auth failures, old pods, processlist, etc.
        return

    def _generate_password(self, length: int = 32) -> str:
        alphabet = string.ascii_letters + string.digits + "!@#$%^&*()-_=+"
        while True:
            pwd = "".join(secrets.choice(alphabet) for _ in range(length))
            if (any(c.islower() for c in pwd) and any(c.isupper() for c in pwd)
                and any(c.isdigit() for c in pwd) and any(c in "!@#$%^&*()-_=+" for c in pwd)):
                return pwd

High‑concurrency pitfalls (often ignored)

Connection pools keep old privileges after a REVOKE, creating a “revocation vacuum”.

Shared accounts amplify noise – you cannot pinpoint which service caused a surge.

Mixing DDL with high‑frequency write traffic lets an attacker or bug lock metadata and destabilise the system.

Monitoring and backup tools need minimal privilege; over‑privileged platform accounts become high‑value attack targets.

The permission service itself must be idempotent, retry‑safe, and emit audit events via a reliable outbox.

Real‑world case study: Order service permission governance

Initial state – a single user 'order_app'@'%' with GRANT ALL on order_db.*. Problems observed after six months: mixed read/write/job usage, test credentials leaked into production, connection explosion during load tests, and a third‑party component exposing env vars.

Governance goals

Split service accounts into read, write, and job categories.

Eliminate % host wildcards.

Remove all DDL privileges from application accounts.

Store credentials in Vault with a 30‑day rotation.

Gate permission changes through GitOps and an approval flow.

Audit must answer “who granted what, when”.

Post‑refactor model

Roles: role_order_ro, role_order_rw, role_order_job.

Accounts: svc_order_api_ro, svc_order_api_rw, svc_order_job.

Permissions aligned with responsibilities, K8s Secrets split per service, deployments tied to credential rotation, and per‑account connection metrics collected.

Outcomes

Security containment – a leaked credential can affect only a limited set of tables.

Stability boost – per‑account connection and SQL behaviour can be observed and tuned.

Controlled changes – revocation, rotation, and scaling are linked to deployment pipelines.

Compliance traceability – full audit trail of who granted/changed permissions.

Audit queries and runtime monitoring

SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv
FROM mysql.user
WHERE user NOT IN ('mysql.session','mysql.sys','mysql.infoschema')
ORDER BY user, host;

Run SHOW PROCESSLIST or query performance_schema.threads to see active users, hosts, databases, commands, and durations. Aggregate metrics per user for connection count, auth failures, slow SQL, DDL count, and sensitive‑table access.

Production checklist (selected items)

No anonymous or legacy test accounts.

No remote high‑privilege accounts.

All service accounts map one‑to‑one with a service.

No GRANT ALL or DDL rights on application accounts.

Roles used instead of direct grants.

Credentials stored in a secret manager; never in code.

Fixed rotation schedule with zero‑downtime rollout.

Network source restrictions and TLS enforced.

Per‑account connection observability.

Permission changes are declarative, approved, auditable, and rollback‑able.

Evolution roadmap

Stage 1 – Stop‑bleeding: Remove GRANT ALL, eliminate '%' hosts, split shared accounts, revoke DDL from apps, move credentials out of config.

Stage 2 – Converge: Introduce roles, enforce naming conventions, separate service/human/platform accounts, create minimal‑privilege templates, build an account inventory.

Stage 3 – Automate: Store desired state in Git/YAML, add diff calculation and auto‑apply, integrate approval, audit, and secret‑rotation pipelines.

Stage 4 – Platform‑ify: Define a CRD, build a controller that creates users, grants roles, syncs Secrets, and restarts Deployments; add drift detection and risk alerts.

Stage 5 – Operate Systematically: Periodic permission reviews, auto‑expire temporary grants, focus on dangerous permissions, conduct breach‑and‑credential‑leak drills, continuously refine audit baselines.

Conclusion

Production‑grade MySQL permission management is not just “write a GRANT ”. It must answer:

Who can access which data, from where, using which method, for how long, with full traceability?

Key characteristics of a mature solution:

Permissions aligned with business responsibilities.

Clear account‑source boundaries.

Rotatable, revocable credentials.

Approved, rollback‑able, auditable changes.

Runtime connection behaviour observable.

Continuous platform validation and drift repair.

When any of the following still exist – wildcard hosts, shared accounts, GRANT ALL, passwords in config files, permission revocation without rollout linkage, or missing audit – the practices described above are essential, not optional, for a secure and reliable MySQL deployment.