Up until MySQL 8.0.18, the slave executes replicated transactions without checking privileges. It does so to be able to apply everything that its upstream server (the master) tells it to. In practice this means that the slave fully trusts its master. However, there may be setups in which changes cross a security boundary between master and slave, and therefore the slave may need to enforce data access constraints for the replicated stream. In that case it is helpful to apply the changes coming from upstream in a more restricted security context.

From 8.0.18 on-wards, privilege checks for slave applier threads are introduced, allowing for user privileges to be set and checked on a per-channel basis. This feature is specially useful in multi-source replication scenarios, where one needs to aggregate data from different multiple sources – fan-in use-case – but wants to keep control of the data being applied. In other words, organizations with several independent databases that need to aggregate data in a controlled way, privilege-wise.

For those of you already thinking on how privileges may be used as column level replication filtering, running the slave applier threads with restricted privileges can’t be used as a filtering mechanism. Slave applier threads are made to stop, in case of privilege breach, and replication is stopped, all together, for the channel. Appropriate error messages are logged to the error log in such cases.

Three steps are needed to start running slave applier threads with privilege checks:

1. Create a user on the slave nodes – optional, may use an existing one.
2. Setup required privileges for the intended user.
3. Use newly introduced option for CHANGE MASTER TO …, named PRIVILEGE_CHECKS_USER, to associate the intended user account with the slave applier threads privilege context.

Create a user on the slave nodes

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

On the slave

If the intended slave is the only node in the topology for which we want to enable privilege checking and it has sql_log_bin enabled, let’s not forget to disable binary logging before creating the user:

On the slave

On the other hand, if we have a large amount of slave nodes and wish to enable privilege checking in all of them, creating the user in the master and let the statement to be replicated may be a good way to get it done:

On the master

Setup privileges for the user

Other than the database/table/column level privileges we may need or want to attribute the user with, there are global level privileges – or dynamic privileges – needed for the applier thread to be able to function properly:

• REPLICATION_APPLIER: dynamic privilege that explicitly allows for the intended user to be used as the slave applier thread privilege context user. This privilege is needed so that users granted with REPLICATION_SLAVE_ADMIN (able to execute CHANGE MASTER TO …) but without GRANT privileges are unable to setup a privileged slave applier session using any given user.
• SESSION_VARIABLES_ADMIN: needed to set session variables that are explicitly set in the binary log.
• FILE: if and only if using statement-based replication and executing LOAD DATA on the master.

A reasonable set of grant statements would be:

On the slave

Again, if the intended slave is the only node in the topology for which we want to enable privilege checking and it has sql_log_bin enabled, let’s not forget to disable binary logging before granting the privileges:

On the slave

Again, if we want to disseminate the privileges throughout our topology, just run the commands on the master:

On the master

Roles may also be used to grant the intended user the privileges we need it to have. No explicit role setting is allowed while running the CHANGE MASTER TO … statement but we may use default roles to circumvent that.

Create and setup the role:

For each user we wish to use as slave applier threads privilege context user, assign the role as a default role:

We may also add database/table/column level privileges to the role, if we want.

Note: changes to the privileges performed while the slave applier thread is running, either while using roles or direct grants, will only be in effect after the thread is restarted.

Associate the user with the slave applier privilege context

Once the user is all set, we may use CHANGE MASTER TO … to associate the user with the slave applier privilege context:

On the slave

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

On the slave

Observability

The Performance Schema tables related to the slave applier status were enhanced to display the status of the new CHANGE MASTER TO … statement option, PRIVILEGE_CHECKS_USER:

On the slave

Caveats

Some stipulations we need to consider while or before using slave applier with privilege checks:

• Running slave applier threads with privilege checks is not meant to be used as a filtering mechanism, replication for a given channel stops when privileges checks fail, in which cases appropriate messages are logged to the error log.
• Granting SESSION_VARIABLES_ADMIN may be a security issue if the source of the replication is not fully trusted.
• Although we may grant column level privileges, checking for such privileges while using row-based replication and binlog_row_format=FULL will be disabled since the binary log events will hold no information on which columns are actually changed. Therefore, column level privileges will only be checked when the binlog_row_format=MINIMAL.
• As the replication applier has to do more work, some minimal throughput degradation may be observed.

In a nutshell

The minimal sequence of commands, on the slave, for setting up a privileged slave applier channel – for a previously configured replication channel:

Hope this feature can help you replicate from sources outside a security boundary for which you need to enforce a set of data access constraints. If you want to comment or share your experience, please, don’t be a stranger and leave us your opinion.