background

Before MySQL 8.0.18, the slave server performed replication transactions without checking permissions, so that the master server could get all the content. In fact, this means that the slave fully trusts the host. However, there may be settings where changes cross the security boundary between the primary and secondary servers. Therefore, the slave server may need to enforce data access constraints on the replication flow. In this case, data changes from the host are performed in a more rigorous and secure manner.

Starting with MySQL 8.0.18, permission checking for slave application threads has been introduced to allow setting and checking of user permissions on each channel. This feature is particularly useful in multi-source replication scenarios, where data needs to be aggregated from multiple primary servers, but you want to maintain control over the applied data. In other words, to organize several independent databases, you need to aggregate the data in a controlled way.

For those who are already considering how permissions can be used as column level replication filtering, you cannot run a slave application thread with restricted permissions as a filtering mechanism. In case of permission conflict, stop the slave application thread and stop replication. In this case, the appropriate error message is logged to the error log.

Three steps to turn on permission checking for a slave application thread:

1. Create users on the slave node, and you can use existing users. 2. Set the required permissions for the target user. 3. Use the newly introduced option for CHANGE MASTER TO, named privilege? Checks? User, to associate the expected user with the thread permission of the slave application.

Create user on slave node

Create a client connection to the slave server and use the CREATE USER statement to create a new user:

// On the slave
mysql> CREATE USER 'rpl_applier_user'@'localhost';

If the target slave is the only node in our permission checking topology and SQL log bin is enabled, please do not forget to disable binary logging before creating users:

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

On the other hand, if we have a large number of slaves and want to enable permission checking for all slaves, it may be a good way to create users on the host and let the statements be copied:

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

Set permissions for users

In addition to database / table / column level permissions we may need or want to give users, we also need global level permissions (or dynamic permissions) for application threads to function properly:

• Replication? Applier: dynamic permission, which explicitly allows the target user to be used as the thread permission of the slave application. This permission is required to be granted by replication? Slave? Admin (able to execute CHANGE MASTER TO...) But a user without GRANT permission cannot set the permission slave application session with any given user.
• Session? Variables? Admin: session variables that need to be set explicitly in the binary log.
• FILE: This is only true if statement based replication is used and LOAD DATA is executed on the primary database.

A reasonable set of authorization syntax is:

// On the slave
mysql> GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN ON *.* TO 'rpl_applier_user'@'localhost';
mysql> GRANT CREATE,INSERT,DELETE,UPDATE ON db1.* TO 'rpl_applier_user'@'localhost';

Similarly, if the target slave is the only node in our permission checking topology and SQL log bin is enabled, please do not forget to disable binary logging before creating users:

// On the slave
mysql> SET @@session.sql_log_bin = 0;
mysql> GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN ON *.* TO 'rpl_applier_user'@'localhost';
mysql> GRANT CREATE,INSERT,DELETE,UPDATE ON db1.* TO 'rpl_applier_user'@'localhost';
mysql> SET @@session.sql_log_bin = 1;

Similarly, if we want to pass permissions throughout the topology, we only need to run commands on the primary server:

// On the master
mysql> GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN ON *.* TO 'rpl_applier_user'@'localhost';
mysql> GRANT CREATE,INSERT,DELETE,UPDATE ON db1.* TO 'rpl_applier_user'@'localhost';
mysql> SET @@session.sql_log_bin = 0;
mysql> DROP USER 'rpl_applier_user'@'localhost';
mysql> SET @@session.sql_log_bin = 1;

Roles can also be used to grant the required permissions to the target user. Run CHANGE MASTER TO... Explicit role settings are not allowed at statement time, but we can use the default role to circumvent this setting.

Create and set up roles:

mysql> CREATE ROLE 'rpl_applier_role';
mysql> GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN ON *.* TO 'rpl_applier_role';
mysql> GRANT 'rpl_applier_role' TO 'rpl_applier_user'@'localhost';

For each user we want to use as a user before and after the thread permission of the slave application, assign the role as the default role:

mysql> SET DEFAULT ROLE 'rpl_applier_role' TO 'rpl_applier_user'@'localhost';

If necessary, we can also add database / table / column level permissions to roles.

**Note: * * permission changes performed when the slave application thread is running (using roles or granting permissions directly) will not take effect until the thread is restarted.

Associate the user with the permission of the slave application before and after

After setting up the user, we can use CHANGE MASTER TO... Associate the user with the slave application permissions before and after:

// On the slave
mysql> CHANGE MASTER TO PRIVILEGE_CHECKS_USER = 'rpl_applier_user'@'localhost';

If the slave application thread is running, we need to stop it to change the option value:

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

Observability

The Performance Schema table related to the state of the slave application has been enhanced to show the state of the new CHANGE MASTER TO... Statement option privilege "checks" user:

// On the slave
mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO PRIVILEGE_CHECKS_USER = 'rpl_applier_user'@'localhost' FOR CHANNEL 'rpl_privileged_channel';
mysql> START SLAVE SQL_THREAD;
mysql> SELECT Channel_name, Privilege_checks_user FROM performance_schema.replication_applier_configuration;
+------------------------+--------------------------------+
| Channel_name           | Privilege_checks_user          |
+------------------------+--------------------------------+
| rpl_privileged_channel | 'rpl_applier_user'@'localhost' |
+------------------------+--------------------------------+
1 row in set (0.00 sec)

Matters needing attention

We need to consider some rules before or when using a slave application with permission checking:

• Running a slave application thread with privilege check does not mean that it is used as a filtering mechanism. When the privilege check fails, the replication of the given channel will be stopped, in which case the appropriate message will be recorded in the error log.
• If the replication source is not fully trusted, granting session ABCD variables ABCD admin can be a security issue.
• Although we can grant column level permissions, we will disable row based replication to check such permissions when binlog [row] format = full, because binary log events will not contain information about which columns were actually changed. Therefore, column level permissions are checked only when binlog? Row? Format = minimal.
• Since replication applications must perform more work, some minimal throughput degradation may be observed.

In short

On the slave server, set the minimum permission sequence for the previously configured replication channel.

mysql> STOP SLAVE;
mysql> CREATE USER u;
mysql> GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN,CREATE,INSERT,DELETE,UPDATE ON *.* TO u;
mysql> CHANGE MASTER TO PRIVILEGE_CHECKS_USER = u;
mysql> START SLAVE;