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 replica or group member. 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, using a CHANGE REPLICATION SOURCE TO statement. 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 can increase the security of a replication channel where privilege checks are applied by adding one or both of these options to the CHANGE REPLICATION SOURCE TO statement when you specify the PRIVILEGE_CHECKS_USER account for the channel:

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 8.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 15.4.1.3, “SET sql_log_bin Statement”).

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 19.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 REPLICATION SOURCE TO statement. If replication is running, issue STOP REPLICA before the CHANGE REPLICATION SOURCE TO statement, and START REPLICA after it. The use of row-based binary logging is strongly recommended when PRIVILEGE_CHECKS_USER is set; you can use the statement to set REQUIRE_ROW_FORMAT to enforce this.

When you restart the replication channel, checks on dynamic privileges are applied from that point on. However, static global privileges are not active in the applier's context until you reload the grant tables, because these privileges are not changed for a connected client. To activate static privileges, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

For example, to start privilege checks on the channel channel_1 on a running replica, issue the following statements:

mysql> STOP REPLICA FOR CHANNEL 'channel_1';
mysql> CHANGE REPLICATION SOURCE TO
     >    PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com',
     >    REQUIRE_ROW_FORMAT = 1 FOR CHANNEL 'channel_1';
mysql> FLUSH PRIVILEGES;
mysql> START REPLICA FOR CHANNEL 'channel_1';

If you do not specify a channel and no other channels exist, the statement is applied to the default channel. 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.

If you are using the Rewriter plugin, you should grant the PRIVILEGE_CHECKS_USER user account the SKIP_QUERY_REWRITE privilege. This prevents statements issued by this user from being rewritten. See Section 7.6.4, “The Rewriter Query Rewrite Plugin”, for more information.

When REQUIRE_ROW_FORMAT is set for a replication channel, the replication applier does not create or drop temporary tables, and so does not set the pseudo_thread_id session system variable. It does not execute LOAD DATA INFILE instructions, and so does not attempt file operations to access or delete the temporary files associated with data loads (logged as a Format_description_log_event). It does not execute INTVAR, RAND, and USER_VAR events, which are used to reproduce the client's connection state for statement-based replication. (An exception is USER_VAR events that are associated with DDL queries, which are executed.) It does not execute any statements that are logged within DML transactions. If the replication applier detects any of these types of event while attempting to queue or apply a transaction, the event is not applied, and replication stops with an error.

You can set REQUIRE_ROW_FORMAT for a replication channel whether or not you set a PRIVILEGE_CHECKS_USER account. The restrictions implemented when you set this option increase the security of the replication channel even without privilege checks. You can also specify the --require-row-format option when you use mysqlbinlog, to enforce row-based replication events in mysqlbinlog output.

Security Context.  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. Here, instead of granting the INSERT privilege for the db1.cust table directly to a user account as in the earlier example, this privilege is granted to the role priv_repl_role along with the REPLICATION_APPLIER privilege. The role is then used to grant the privilege set to two user accounts, both of which can now be used as PRIVILEGE_CHECKS_USER accounts:

mysql> SET sql_log_bin = 0;
mysql> CREATE USER 'priv_repa'@'%.example.com'
                  IDENTIFIED BY 'password'
                  REQUIRE SSL;
mysql> CREATE USER 'priv_repb'@'%.example.com'
                  IDENTIFIED BY 'password'
                  REQUIRE SSL;
mysql> CREATE ROLE 'priv_repl_role';
mysql> GRANT REPLICATION_APPLIER TO 'priv_repl_role';
mysql> GRANT INSERT ON db1.cust TO 'priv_repl_role';
mysql> GRANT 'priv_repl_role' TO
                  'priv_repa'@'%.example.com',
                  'priv_repb'@'%.example.com';
mysql> SET DEFAULT ROLE 'priv_repl_role' TO
                  'priv_repa'@'%.example.com',
                  'priv_repb'@'%.example.com';
mysql> SET sql_log_bin = 1;

Be aware that when the replication applier thread creates the security context, it checks the privileges for the PRIVILEGE_CHECKS_USER account, but does not carry out password validation, and does not carry out checks relating to account management, such as checking whether the account is locked. The security context that is created remains unchanged for the lifetime of the replication applier thread.