Enabling Privilege Checks for MySQL Replication Threads

Background

Prior to MySQL 8.0.18, replication threads on a slave executed transactions without any privilege checks, effectively trusting the master completely. Starting with MySQL 8.0.18, MySQL added privilege checking for the applier thread, allowing per‑channel user permissions, which is especially useful for multi‑source replication scenarios.

MySQL before 8.0.18

Replication on the slave ran without verifying privileges, meaning the slave fully trusted the master. This could be a security risk when the master‑slave boundary is not fully trusted.

MySQL 8.0.18 and later

The new feature enables privilege checks for the applier thread, allowing administrators to enforce data‑access constraints on the replication stream and to control which user’s privileges are used for applying changes.

Three steps to enable privilege checks for the applier thread

Create a user on the replica (you may reuse an existing user).

Grant the required privileges to that user.

Use the newly introduced PRIVILEGE_CHECKS_USER option in CHANGE MASTER TO to associate the user with the applier thread.

Creating a user on the replica

-- On the slave
CREATE USER 'rpl_applier_user'@'localhost';

If the replica is the only node in a privileged‑check topology and sql_log_bin is enabled, disable binary logging before creating the user:

-- On the slave
SET @@session.sql_log_bin = 0;
CREATE USER 'rpl_applier_user'@'localhost';
SET @@session.sql_log_bin = 1;

When many replicas need the same user, you can create the user on the master and let the statement replicate:

-- On the master
CREATE USER 'rpl_applier_user'@'localhost';
SET @@session.sql_log_bin = 0;
DROP USER 'rpl_applier_user'@'localhost';
SET @@session.sql_log_bin = 1;

Granting permissions

Besides database/table/column privileges, the applier thread requires the following dynamic privileges: REPLICATION_APPLIER – allows the user to be used with CHANGE MASTER TO ... PRIVILEGE_CHECKS_USER. SESSION_VARIABLES_ADMIN – needed when session variables are set in the binary log. FILE – required only if statement‑based replication executes LOAD DATA on the master.

A typical grant sequence is:

-- On the slave
grant REPLICATION_APPLIER, SESSION_VARIABLES_ADMIN ON *.* TO 'rpl_applier_user'@'localhost';
grant CREATE, INSERT, DELETE, UPDATE ON db1.* TO 'rpl_applier_user'@'localhost';

Using roles (optional)

You can create a role, grant the required privileges to the role, and then assign the role to the user:

-- On the slave
CREATE ROLE 'rpl_applier_role';
GRANT REPLICATION_APPLIER, SESSION_VARIABLES_ADMIN ON *.* TO 'rpl_applier_role';
GRANT 'rpl_applier_role' TO 'rpl_applier_user'@'localhost';
SET DEFAULT ROLE 'rpl_applier_role' TO 'rpl_applier_user'@'localhost';

Additional database/table/column privileges can be added to the role as needed. Note that privilege changes only take effect after the applier thread is restarted.

Associating the user with the applier thread

After the user and its privileges are set, run:

-- On the slave
CHANGE MASTER TO PRIVILEGE_CHECKS_USER = 'rpl_applier_user'@'localhost';

If the SQL thread is running, stop it first, apply the change, then start it again:

-- On the slave
STOP SLAVE SQL_THREAD;
CHANGE MASTER TO PRIVILEGE_CHECKS_USER = 'rpl_applier_user'@'localhost';
START SLAVE SQL_THREAD;

Observability

The Performance Schema now includes the replication_applier_configuration table, which shows the PRIVILEGE_CHECKS_USER setting per channel:

-- On the slave
SELECT Channel_name, Privilege_checks_user FROM performance_schema.replication_applier_configuration;

Example output:

+------------------------+--------------------------------+
| Channel_name           | Privilege_checks_user          |
+------------------------+--------------------------------+
| rpl_privileged_channel | 'rpl_applier_user'@'localhost' |
+------------------------+--------------------------------+

Important notes

Running a privileged‑check applier does not make it a filtering mechanism; if a privilege check fails, replication for that channel stops and an error is logged.

Granting SESSION_VARIABLES_ADMIN may be a security concern if the source is not fully trusted.

Column‑level privileges are only enforced when binlog_row_format=MINIMAL; with FULL the binary log does not contain column‑change details.

Enabling privilege checks adds some overhead, potentially reducing throughput slightly.

In short

Configure a minimal set of privileges for the replication applier on the replica, associate the user with the PRIVILEGE_CHECKS_USER option, and restart the SQL thread to enforce security boundaries during replication.