Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 46.8Mb
PDF (A4) - 46.9Mb
PDF (RPM) - 42.3Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.3Mb
Man Pages (TGZ) - 226.1Kb
Man Pages (Zip) - 331.6Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Replication Privilege Checks

17.3.3 Replication Privilege Checks

By default, MySQL replication (including Group Replication) does not carry out privilege checks when transactions that were already accepted by another server are applied on a replication slave or group member. From MySQL 8.0.18, you can create a user account with the appropriate privileges to apply the transactions that are normally replicated on a channel, and specify this as the PRIVILEGE_CHECKS_USER account for the replication applier. MySQL then checks each transaction against the user account's privileges to verify that you have authorized the operation for that channel. The account can also be safely used by an administrator to apply or reapply transactions from mysqlbinlog output, for example to recover from a replication error on the channel.

The use of a PRIVILEGE_CHECKS_USER account helps secure a replication channel against the unauthorized or accidental use of privileged or unwanted operations. The PRIVILEGE_CHECKS_USER account provides an additional layer of security in situations such as these:

  • You are replicating between a server instance on your organization's network, and a server instance on another network, such as an instance supplied by a cloud service provider.

  • You want to have multiple on-premise or off-site deployments administered as separate units, without giving one administrator account privileges on all the deployments.

  • You want to have an administrator account that enables an administrator to perform only operations that are directly relevant to the replication channel and the databases it replicates, rather than having wide privileges on the server instance.

You grant the REPLICATION_APPLIER privilege to enable a user account to appear as the PRIVILEGE_CHECKS_USER for a replication applier thread, and to execute the internal-use BINLOG statements used by mysqlbinlog. The user name and host name for the PRIVILEGE_CHECKS_USER account must follow the syntax described in Section 6.2.4, “Specifying Account Names”, and the user must not be an anonymous user (with a blank user name) or the CURRENT_USER. To create a new account, use CREATE USER. To grant this account the REPLICATION_APPLIER privilege, use the GRANT statement. For example, to create a user account priv_repl, which can be used manually by an administrator from any host in the example.com domain, and requires an encrypted connection, issue the following statements:

mysql> SET sql_log_bin = 0;
mysql> CREATE USER 'priv_repl'@'%.example.com' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT REPLICATION_APPLIER ON *.* TO 'priv_repl'@'%.example.com';
mysql> SET sql_log_bin = 1;

The SET sql_log_bin statements are used so that the account management statements are not added to the binary log and sent to the replication channels (see Section 13.4.1.3, “SET sql_log_bin Syntax”).

Important

The caching_sha2_password authentication plugin is the default for new users created from MySQL 8.0 (for details, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”). To connect to a server using a user account that authenticates with this plugin, you must either set up an encrypted connection as described in Section 17.3.1, “Setting Up Replication to Use Encrypted Connections”, or enable the unencrypted connection to support password exchange using an RSA key pair.

After setting up the user account, use the GRANT statement to grant additional privileges to enable the user account to make the database changes that you expect the applier thread to carry out, such as updating specific tables held on the server. These same privileges enable an administrator to use the account if they need to execute any of those transactions manually on the replication channel. If an unexpected operation is attempted for which you did not grant the appropriate privileges, the operation is disallowed and the replication applier thread stops with an error. Section 17.3.3.1, “Privileges For The Replication PRIVILEGE_CHECKS_USER Account” explains what additional privileges the account needs. For example, to grant the priv_repl user account the INSERT privilege to add rows to the cust table in db1, issue the following statement:

mysql> GRANT INSERT ON db1.cust TO 'priv_repl'@'%.example.com';

You assign the PRIVILEGE_CHECKS_USER account for a replication channel using a CHANGE MASTER TO statement. If replication is running, issue STOP SLAVE before the CHANGE MASTER TO statement, and START SLAVE after it. If you do not specify a channel and no other channels exist, the statement is applied to the default channel. For example, to start privilege checks on the channel channel_1 on a running replication slave, issue the following statements:

mysql> STOP SLAVE FOR CHANNEL 'channel_1';
mysql> CHANGE MASTER TO PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com' FOR CHANNEL 'channel_1'; 
mysql> START SLAVE FOR CHANNEL 'channel_1';

When you restart the replication channel, the privilege checks are applied from that point on. The user name and host name for the PRIVILEGE_CHECKS_USER account for a channel are shown in the Performance Schema replication_applier_configuration table, where they are properly escaped so they can be copied directly into SQL statements to execute individual transactions.

As well as securing asynchronous and semi-synchronous replication, you may choose to use a PRIVILEGE_CHECKS_USER account to secure the two replication applier threads used by Group Replication. The group_replication_applier thread on each group member is used for applying the group's transactions, and the group_replication_recovery thread on each group member is used for state transfer from the binary log as part of distributed recovery when the member joins or rejoins the group. To secure one of these threads, issue the CHANGE MASTER TO statement in the same way, specifying group_replication_applier or group_replication_recovery as the channel name.

By default, when a replication applier thread is started with a user account specified as the PRIVILEGE_CHECKS_USER, the security context is created using default roles, or with all roles if activate_all_roles_on_login is set to ON. You can use roles to supply a general privilege set to accounts that are used as PRIVILEGE_CHECKS_USER accounts, as in the following example, which grants the REPLICATION_APPLIER privilege together with the SESSION_VARIABLES_ADMIN privilege:

mysql> SET sql_log_bin = 0; 
mysql> CREATE USER 'priv_repl'@'%.example.com' IDENTIFIED BY 'password' REQUIRE SSL; 
mysql> CREATE ROLE 'priv_repl_role'; 
mysql> GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN TO 'priv_repl_role'; 
mysql> GRANT 'priv_repl_role' TO 'priv_repl'@'%.example.com'; 
mysql> SET DEFAULT ROLE 'priv_repl_role' TO 'priv_repl'@'%.example.com'; 
mysql> SET sql_log_bin = 1;

If a remote cloning operation is used for distributed recovery in Group Replication (see Section 18.4.3.1, “Cloning for Distributed Recovery”), the PRIVILEGE_CHECKS_USER account is not automatically used on the joining member after cloning. You must assign the user account manually to the appropriate channels on the joining member.