In this blog, I’ll discuss the use case for replication. We want to improve our ability to replicate your data and limit replication to row-based events securely, wherein we do not have control over the source(s).
The replica doesn’t have checking capabilities when processing replicated transactions as of MySQL 8.0.18. It does this to carry out all instructions from its upstream. The replica must impose data access limitations on the replicated stream because changes may get past the security barrier separating the source and replica in some configurations. In that situation, implementing the upstream changes in a more constrained security context is beneficial to organizations needing privilege-controlled aggregate data from multiple separate databases.
In MySQL 8.0.18, a new feature PRIVILEGE_CHECKS_USER is introduced in replication channels. When a PRIVILEGE CHECKS USER account is used, a replication channel is more protected from unauthorized or unintentional use of privileged or undesirable actions.
By default, the replication threads use the privileges of the MySQL user account configured for replication. This can pose a security risk if the user account has privileges not necessary for replication, such as the SUPER privilege or the ability to execute arbitrary SQL statements. You can reduce the risk of unauthorized access or malicious attacks by setting PRIVILEGE_CHECKS_USER to a separate user account with limited privileges. The replication threads will use the limited privileges of the PRIVILEGE_CHECKS_USER account, which can help prevent the execution of unauthorized SQL statements or the manipulation of sensitive data.
PRIVILEGE CHECKS USER is helpful in multi-source replication to carefully gather data from various sources.
Configure user on replica
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> STOP REPLICA; mysql> SET SQL_LOG_BIN=0; mysql> CREATE USER repl; mysql> GRANT REPLICATION_APPLIER, SESSION_VARIABLES_ADMIN, CREATE, INSERT, DELETE, UPDATE ON *.* TO repl; mysql> SET SQL_LOG_BIN=1; mysql> CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER = repl FOR CHANNEL 'channel1'; mysql> START REPLICA; 1 row in set (0.00 sec) |
Limitation
- The SESSION_VARIABLES_ADMIN privilege is a subset of the SYSTEM_VARIABLES_ADMIN and SUPER privileges. SESSION VARIABLES ADMIN might be a security risk if the replication provider is unreliable.
- Minimal performance issues may be noticed.
- Messages are recorded in the error file if the channel terminates due to failed privilege checks.
Another feature, REQUIRE ROW FORMAT, was added in MySQL 8.0.19, limiting replication to row-based replication events. The replication channel will only take row-based replication events if REQUIRE ROW FORMAT is set. If REQUIRE ROW FORMAT is enabled, row-based binary logging (binlog format=ROW) is required on the source server.
Configure REQUIRE_ROW_FORMAT
1 2 3 4 5 6 7 | mysql> STOP REPLICA; mysql> CHANGE REPLICATION SOURCE TO REQUIRE_ROW_FORMAT = 1; mysql> START REPLICA; 1 row in set (0.00 sec) |
All transactions received and implemented after this new feature has been enabled in a replication channel are checked, and the following are not permitted:
(Upon encountering any of these events, replication shall fail and stop.)
- DML that logged for statement-based replication
- Creation or deletion of temp tables: The reason for disallowing the replication of temporary tables is that they may contain sensitive data that is not intended to be persisted beyond their lifetime. For example, a temporary table may contain sensitive customer data used to calculate a tax or fee, but this data may be deleted as soon as the calculation is complete. If this temporary data is replicated to the replica, it may be vulnerable to exposure or manipulation.
- LOAD DATA events: The reason for disallowing the replication of LOAD DATA instructions is that the data loaded from the file may be in plaintext and not encrypted. When the data is loaded into the database using SQL statements, it can be encrypted before insertion to protect it from unauthorized access or disclosure. However, when the data is loaded using LOAD DATA, it is not automatically encrypted and may be vulnerable to tampering or interception.
Summary
Using PRIVILEGE_CHECKS_USER and require_row_format can further increase the security of a MySQL replication environment.
As mentioned earlier, PRIVILEGE_CHECKS_USER can limit the privileges required by the replication threads. You can reduce the risk of unauthorized access by setting PRIVILEGE_CHECKS_USER to a separate user account with limited privileges.
require_row_format, on the other hand, can be used to ensure that tables in the InnoDB storage engine use the ROW format, which is the most secure option for replication. When require_row_format is set to ON, MySQL will require that all tables that use the InnoDB storage engine use the ROW format. This can help ensure that replication events are processed correctly and can help prevent data inconsistencies.
By combining PRIVILEGE_CHECKS_USER and require_row_format, you can create a more secure replication environment. The replication threads will use the limited privileges of the PRIVILEGE_CHECKS_USER account, while the tables in the InnoDB storage engine will use the secure ROW format. This can help prevent unauthorized access to sensitive data or operations and reduce the risk of data inconsistencies during replication.
It’s important to note that using PRIVILEGE_CHECKS_USER and require_row_format should be done carefully, as changing these variables can have implications for the performance and functionality of the replication environment. Testing any changes in a development or test environment before implementing them in a production environment is recommended.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
