Securing Binlog Access with MySQL 8.0 Dynamic Privileges #

The failure this page prevents is quiet until it is catastrophic: a binlog archiving daemon connects with an account that holds SUPER or ALL PRIVILEGES, and one day a bad config value, a typo in an admin script, or a compromised credential runs RESET MASTER (now RESET BINARY LOGS AND GTIDS) against a live primary. Every binary log is purged, gtid_executed is truncated, and every downstream replica halts with error 1236 — “Cannot replicate because the master purged required binary logs.” Your recoverable window is gone and the only fix is a full rebuild. The opposite mistake is just as common — an account provisioned too narrowly throws ERROR 1227 (42000) — “Access denied; you need (at least one of) the SUPER, BINLOG_ADMIN privilege(s)” mid-run and stalls the pipeline. This page resolves that exact tension by replacing the coarse SUPER grant with MySQL 8.0’s named dynamic privileges, provisioning a read-only extractor identity and a replay identity that each hold precisely what they need and nothing that can destroy the archive.

Visual Overview #

Context & Prerequisites #

Dynamic privileges are named, individually grantable capabilities that MySQL 8.0 carved out of the monolithic SUPER privilege, so an account can be given the one power it needs — purge logs, or read replication status, or apply events — without inheriting the dozens of unrelated administrative capabilities SUPER bundles. This page implements the concrete grant sequence that the runtime authorization gates in Security & Access Frameworks later assert on every connection; that framework enforces and audits these grants, while this page creates them. Because a point-in-time recovery (PITR) pipeline reasons about continuity in Global Transaction Identifiers rather than file offsets, this only holds when the estate shares one identifier space extracted gap-free by the GTID Tracking & Enforcement pipeline, over logs written in the deterministic ROW shape described in ROW vs STATEMENT vs MIXED Formats — both concepts are grounded in MySQL Binary Log Architecture & GTID Fundamentals. Require MySQL 8.0.22+ so SHOW BINARY LOG STATUS and the stable dynamic-privilege names are available, run the server with activate_all_roles_on_login = OFF so accounts activate only their explicit DEFAULT ROLE, and connect over TLS with a short-lived credential.

Step-by-Step Implementation #

The sequence below provisions two disjoint identities — a read-only binlog_extractor that streams and inspects logs, and a binlog_replayer that applies archived events during recovery — plus a separately held retention operator. Roles are used so the powerful grants live on a role object that can be granted, revoked, and audited independently of the login accounts.

1. Create the login accounts, TLS-required and network-scoped. Binding each account to a specific source host and forcing REQUIRE SSL guarantees the credential is useless off its subnet and never travels in cleartext — the first line of defence before any privilege is even granted.

-- MySQL 8.0.22+
CREATE USER 'binlog_extractor'@'10.20.0.0/255.255.0.0'
  IDENTIFIED BY RANDOM PASSWORD
  REQUIRE SSL
  PASSWORD EXPIRE INTERVAL 7 DAY;

CREATE USER 'binlog_replayer'@'10.20.0.0/255.255.0.0'
  IDENTIFIED BY RANDOM PASSWORD
  REQUIRE SSL
  PASSWORD EXPIRE INTERVAL 7 DAY;

2. Create the roles and grant only the dynamic privileges each function requires. The extractor gets read-only visibility into the log stream and server status; it can watch rotation boundaries and file offsets for PITR tracking but can never mutate, purge, or replay. The replayer gets exactly the applier privilege that mysqlbinlog | mysql replay needs. Neither role is ever granted SUPER, BINLOG_ADMIN, or GRANT OPTION.

-- MySQL 8.0.22+
CREATE ROLE 'binlog_reader_role', 'binlog_replay_role';

-- Read-only extractor: status + stream, nothing destructive.
GRANT REPLICATION CLIENT, REPLICATION SLAVE, BACKUP_ADMIN
  ON *.* TO 'binlog_reader_role';
GRANT SELECT ON mysql.gtid_executed TO 'binlog_reader_role';

-- Recovery operator: apply archived events only.
GRANT REPLICATION_APPLIER ON *.* TO 'binlog_replay_role';

REPLICATION CLIENT authorizes SHOW BINARY LOG STATUS and SHOW BINARY LOGS; REPLICATION SLAVE authorizes the COM_BINLOG_DUMP stream a python-mysql-replication reader consumes; SELECT ON mysql.gtid_executed lets the extractor diff the executed set so it can detect gaps before archival. REPLICATION_APPLIER is the privilege a channel’s PRIVILEGE_CHECKS_USER and BINLOG replay statements require — the core of recovery relevance.

3. Bind each role to its account and make it the default. SET DEFAULT ROLE means the account activates its privileges automatically on login without the powers being loose on the base user, and with activate_all_roles_on_login = OFF nothing broader can leak in.

-- MySQL 8.0.22+
GRANT 'binlog_reader_role' TO 'binlog_extractor'@'10.20.0.0/255.255.0.0';
GRANT 'binlog_replay_role' TO 'binlog_replayer'@'10.20.0.0/255.255.0.0';

SET DEFAULT ROLE 'binlog_reader_role'
  TO 'binlog_extractor'@'10.20.0.0/255.255.0.0';
SET DEFAULT ROLE 'binlog_replay_role'
  TO 'binlog_replayer'@'10.20.0.0/255.255.0.0';

4. Keep the destructive retention privilege on a separate, non-automation identity. BINLOG_ADMIN authorizes PURGE BINARY LOGS — a recovery-affecting operation that must never sit on the extractor. Grant it to a retention operator that runs the purge boundary logic, and never let it be the same principal that streams logs.

-- MySQL 8.0.22+
CREATE ROLE 'binlog_purge_role';
GRANT BINLOG_ADMIN ON *.* TO 'binlog_purge_role';
-- Grant this role only to the retention operator, never to binlog_extractor.

5. Verify the effective posture from Python before trusting the account. The daemon should read its own grants at startup and fail closed if a forbidden privilege is present or a required one is missing — the same drift check the framework gate performs. This pooled, TLS-enforced reader uses only mysql-connector-python, already standard in this codebase.

# Requires: Python 3.10+, mysql-connector-python 8.0+
from dataclasses import dataclass
import mysql.connector

REQUIRED = frozenset({"REPLICATION CLIENT", "BACKUP_ADMIN"})
FORBIDDEN = frozenset({"SUPER", "ALL PRIVILEGES", "GRANT OPTION", "BINLOG_ADMIN"})


@dataclass(slots=True)
class GrantAudit:
    active_role: str | None
    granted: frozenset[str]

    @property
    def safe(self) -> bool:
        return REQUIRED <= self.granted and not (FORBIDDEN & self.granted)


def audit_binlog_account(host: str, user: str, password: str) -> GrantAudit:
    cnx = mysql.connector.connect(
        host=host, user=user, password=password,
        ssl_disabled=False, ssl_verify_identity=True, connection_timeout=5,
    )
    try:
        cur = cnx.cursor()
        cur.execute("SELECT CURRENT_ROLE()")
        (active_role,) = cur.fetchone()
        cur.execute("SHOW GRANTS FOR CURRENT_USER()")
        granted: set[str] = set()
        for (line,) in cur.fetchall():
            if (head := line.upper().partition(" ON ")[0]).startswith("GRANT "):
                granted.update(p.strip() for p in head[6:].split(","))
        cur.close()
        return GrantAudit(active_role, frozenset(granted))
    finally:
        cnx.close()


audit = audit_binlog_account("primary.internal", "binlog_extractor", "...")
match audit:
    case GrantAudit(active_role=None) | GrantAudit(active_role="NONE"):
        raise SystemExit("halt: default role not activated")
    case a if not a.safe:
        raise SystemExit(f"halt: unsafe grant set {sorted(a.granted)}")
    case _:
        print("posture OK — extractor is least-privilege")

Grant Reference Table #

The minimal privilege set for each identity in a binlog archiving and PITR pipeline. Grant nothing that is not in this table; every extra grant widens the blast radius the framework gate is built to catch.

Verification Checklist #

• binlog_extractor and binlog_replayer created with REQUIRE SSL and a network-scoped user@host
• SHOW GRANTS FOR 'binlog_extractor'@...; shows NO SUPER, BINLOG_ADMIN, or GRANT OPTION
• CURRENT_ROLE() returns the intended DEFAULT ROLE for each account after login (not NONE)
• activate_all_roles_on_login is OFF so only the default role activates
• BINLOG_ADMIN is held solely by the retention operator, never by the extractor
• A test RESET BINARY LOGS AND GTIDS from the extractor fails with ERROR 1227 (proves it cannot destroy the archive)
• Extractor can run SHOW BINARY LOG STATUS and SELECT ... FROM mysql.gtid_executed successfully
• Passwords issued via IDENTIFIED BY RANDOM PASSWORD and rotated through the secret manager before PASSWORD EXPIRE
• The Python startup audit fails closed on any forbidden or missing privilege

Gotchas & Version-Specific Caveats #

RESET MASTER and SHOW MASTER STATUS were renamed. As of MySQL 8.4, RESET MASTER is RESET BINARY LOGS AND GTIDS and SHOW MASTER STATUS is SHOW BINARY LOG STATUS (the latter deprecated from 8.2.0). Automation or grant-testing scripts that hard-code the old statement names break silently after an 8.4 upgrade — pin the statement to the detected server version. The dynamic privileges themselves (BINLOG_ADMIN, REPLICATION_APPLIER) are unchanged.

SUPER is deprecated but still exists — do not rely on it lingering. MySQL 8.0 keeps SUPER for backward compatibility but marks it deprecated; the whole point of migrating is that a future release may remove it, and any pipeline still depending on SUPER for binlog access will break. Migrate to the named privileges now rather than at forced-upgrade time.

REPLICATION_APPLIER is not enough on its own for a checked channel. When a replication channel uses PRIVILEGE_CHECKS_USER, the applier account also needs the table-level privileges for the schemas it writes during replay. Granting REPLICATION_APPLIER alone yields ERROR 1227 or a table-access denial mid-replay — pair it with the specific INSERT/UPDATE/DELETE grants for the recovery target, never a blanket ALL PRIVILEGES.

A dropped or revoked role fails the identity gate, not the login. If the DEFAULT ROLE is later dropped, the account still connects but CURRENT_ROLE() returns NONE and every privileged statement is denied — surfacing as ERROR 3530 ("is not granted to the current user"). Treat a NONE active role as a hard stop and re-grant through automation; an out-of-band role change is often the first sign of drift.

Never grant on *.* what belongs on a table. Substituting SELECT ON *.* for the scoped SELECT ON mysql.gtid_executed turns a gap-detection reader into a full-database exfiltration path. With partial_revokes = ON you can grant broad and revoke specific schemas, but for this pipeline the table-scoped grant is simpler and tighter.

Frequently Asked Questions #

Related #

• Security & Access Frameworks — the runtime gate that enforces and audits these grants on every connection.
• GTID Tracking & Enforcement — the executed-set continuity the read-only extractor’s mysql.gtid_executed access feeds.
• Setting a Safe binlog_expire_logs_seconds for Compliance — locking BINLOG_ADMIN and the retention timer to a compliance role, not an application account.
• Compression & Encryption Workflows — protecting the logs at rest once a least-privilege extractor has pulled them off the server.

Back to Security & Access Frameworks.