Rechercher dans le manuel MySQL
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, using a CHANGE MASTER
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 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.
When you specify a PRIVILEGE_CHECKS_USER
account for a replication channel, you can also use the
CHANGE MASTER TO
statement to set
the REQUIRE_ROW_FORMAT
option (available from
MySQL 8.0.19) to make the channel accept only row-based
replication events. When REQUIRE_ROW_FORMAT
is
set, you must use row-based binary logging
(binlog_format=ROW
) on the
master. In MySQL 8.0.18, REQUIRE_ROW_FORMAT
is
not available, but the use of row-based binary logging for secured
replication channels is still strongly recommended. With
statement-based binary logging, some administrator-level
privileges are required to execute transactions successfully.
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:
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 Statement”).
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:
You assign the PRIVILEGE_CHECKS_USER
account
for a replication channel using a CHANGE
MASTER TO
statement. The use of row-based binary logging
is strongly recommended when
PRIVILEGE_CHECKS_USER
is set, and from MySQL
8.0.19 you can use the statement to set
REQUIRE_ROW_FORMAT
to enforce this. If
replication is running, issue STOP
SLAVE
before the CHANGE MASTER
TO
statement, and START
SLAVE
after it. 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';
- PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com',
- REQUIRE_ROW_FORMAT = 1,
- FOR CHANNEL 'channel_1';
When you restart the replication channel, the privilege checks are
applied from that point on. 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.
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, which grants the
REPLICATION_APPLIER
privilege
together with the
SESSION_VARIABLES_ADMIN
privilege:
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.
Limitation.
In MySQL 8.0.18 only, if the slave mysqld is
restarted immediately after issuing a RESET
SLAVE
statement (due to a server crash or deliberate
restart), the PRIVILEGE_CHECKS_USER
account
setting, which is held in the
mysql.slave_relay_log_info
table, is lost and
must be respecified. When you use privilege checks in that
release, always verify that they are in place after a restart,
and respecify them if required. From MySQL 8.0.19, the
PRIVILEGE_CHECKS_USER
account setting is
preserved in this situation, so it is retrieved from the table
and reapplied to the channel.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-replication-privilege-checks.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.