WL#12966: Replication with privilege checks
Affects: Server-8.0 — Status: Complete
EXECUTIVE SUMMARY ================= Make it possible to enable privilege checks on replication channels, so that it is possible to replicate securely from untrusted masters. USER STORIES ============ U1. As a cloud service provider: - I need ways to restrict the replication applier threads for a channel to a limited set of privileges, - so that I can enable the following use cases without giving my customers control over the cloud: U1.1. Migrate from on-premise or other cloud providers U1.2. Have their database on-premise or on another cloud provider, but use my service for analytics or backup. U2. As an operator in an organization with multiple on-premise deployments: - I need to restrict the replication applier threads for a channel to a limited set of privileges, - so that I can provide slaves for HA, scale-out, and analytics, without giving full privileges on my slaves to the DBAs of the deployments. U3. As an operator of a topology containing multi-source slaves: - I need to restrict the replication applier threads for channels to different parts of the database, - so that I am sure that different channels do not replicate conflicting changes. U4. As an operator of a GR cluster: - I need to restrict the GR applier channels so that operations which are disallowed for all users are disallowed on the channel, - so that even if one node is compromised, it does not allow the attacker to perform the disallowed operations on the rest of the cluster. U5. As a cloud server provider or operator in any of the scenarios described in U1-U3: - I need ways to allow a user to execute the output of mysqlbinlog without giving that user arbitrary privileges, - so that I can use mysqlbinlog to recover from mistakes or bugs, without exposing my deployment to security threats.
FUNCTIONAL REQUIREMENTS ======================= F1. It shall be possible to restrict the replication applier by specifying the security context in which it can execute. Specifically, it shall be possible to configure a user account through CHANGE MASTER TO. F2. It shall remain possible to configure replication to run without privilege checks. This shall be the default, when no user is explicitly configured. F3. A new privilege, REPLICATION_APPLIER shall allow the holder of the privilege to appear as the user for a replication channel. F4. REPLICATION_APPLIER shall give sufficient privileges to execute the output from mysqlbinlog, provided that the user has the right to execute the database updates contained in that output. I.e.: F4.1. REPLICATION_APPLIER shall give privileges to exeucte BINLOG statements, provided the user has the right to execute the event contained in the statement. F4.2. The need to use SESSION_ADMIN or other privileges for options printed by mysqlbinlog should be removed, either by making it unnecessary for mysqlbinlog to print those, or by removing the need to hold the privilege while setting the options. (See list of options in HLS section 6.) F5. It shall be possible to observe the new configuration through performance_schema tables. F6. New configuration shall be persisted in replication repositories. F7. No password validation shall be performed when starting applier threads for a replication channel having an associated user account. NON-FUNCTIONAL REQUIREMENTS =========================== NF1.When enabling privilege checks in the applier, it shall reduce performance by less than 1% for all workloads.
INTERFACE SPECIFICATION ======================= 1. NEW SYNTAX FOR CHANGE MASTER TO ---------------------------------- This section describes new syntax in detail and purpose superficially. Precise semantics, defaults, corner cases, and errors are described in subsequent sections. There is one new SQL statement clause: 1.1. CHANGE MASTER TO PRIVILEGE_CHECKS_USER = [user | NULL] The effect is: if PRIVILEGE_CHECKS_USER != NULL, the channel, once started, will execute with the security context of that account. NULL is the default value and means that the channel executes without privilege checks. See details in later sections. PRIVILEGE_CHECKS_USER may be either NULL, or an account name using the syntax described at https://dev.mysql.com/doc/refman/8.0/en/account-names.html. As an exception, we disallow anonymous users (where the username part has length 0, for instance ''@host.com). CURRENT_USER is not allowed here. Additional information: user names are utf8, case-sensitive, using utf8_binary collation, and maximum length 32 characters. Host names are ascii, converted to lowercase (hence, case-insensitive and non-case-preserving), using ascii_general_ci collation, and maximum length 255 characters/bytes. This syntax shall be allowed on both async/semisync and group replication channels. 2. NEW PRIVILEGE ---------------- 2.1. The following privilege must be added to the server: REPLICATION_APPLIER This privilege shall be a dynamic privilege: see https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#static-dynamic-privileges The holder of this privilege may appear as the PRIVILEGE_CHECKS_USER for a replication channel. See section 8 for details. 3. UPGRADES, DEFAULTS, AND RESET SLAVE -------------------------------------- 3.1. When the server is upgraded from a version that does not have this worklog, to a version that has it, all existing replication channels shall have PRIVILEGE_CHECKS_USER = NULL. 3.2. When a new replication channel is created using CHANGE MASTER, and no PRIVILEGE_CHECKS_USER clause is specified, then the channel shall be configured with PRIVILEGE_CHECKS_USER = NULL. 3.3. When RESET SLAVE is used, it shall not change PRIVILEGE_CHECKS_USER in memory and persisted values. 3.4. When RESET SLAVE ALL is used in such a way that all replication channels are removed and a new default channel is created, then the new default channel shall have PRIVILEGE_CHECKS_USER = NULL. 4. PRIVILEGE CHECKS FOR APPLIER THREADS --------------------------------------- Replication threads having PRIVILEGE_CHECKS_USER != NULL shall run with a security context, defined as follows: 4.0. When a replication applier thread starts, it shall create the security context of PRIVILEGE_CHECKS_USER with default roles (or with all roles, if activate_all_roles_on_login=1). No password validation shall be performed. If the account is locked, the thread shall fail to start. The security context shall remain unchanged for the life time of the thread. When the replication thread subsequently executes replicated transactions, it shall require the following privileges, and stop the replication threads with an error if the privilege check fails (the error code is case-specific): 4.1. Applying a Query_log_event requires privileges for executing the statement contained in the event. If the privilege check fails, the error message shall be as if a client executed the statement with insufficient privileges. 4.2. Applying a Write_rows_log_event requires INSERT privileges on the table. If the privilege check fails, the error message shall be as if a client executed INSERT with insufficient privileges. 4.3. Applying an Update_rows_log_event requires UPDATE privileges on the table. If the privilege check fails, the error message shall be as if a client executed UPDATE with insufficient privileges. 4.4. Applying a Delete_rows_log_event requires DELETE privileges on the table. If the privilege check fails, the error message shall be as if a client executed DELETE with insufficient privileges. 4.5. Applying any kind of LOAD DATA INFILE events requires FILE privileges as well as INSERT privileges on the table. If the FILE privilege check fails, the error message shall be: ER_FILE_PRIVILEGE_FOR_REPLICATION_CHECKS "The user specified for PRIVILEGE_CHECKS_USER for channel '%.192s' needs FILE privileges to execute a LOAD DATA INFILE statement replicated in statement format. LOAD DATA INFILE is unsafe for statement-based replication, so consider using binlog_format=ROW on master and slave_allow_load_data_events=0 on this server. If the replicated events are trusted, recover from the failure by temporarily granting FILE to the user specified for PRIVILEGE_CHECKS_USER." If the INSERT privilege check fails, the error message shall be as if a client executed INSERT with insufficient privileges. 4.6. When a replication applier thread starts, and @@global.init_slave is set, the server shall check privileges while executing the statements of @@global.init_slave. If the privilege check fails, the error message shall be as if a client executed the statement with insufficient privileges. 4.7. Note: Format_description_log_event are processed without privilege checks. The only operation that this statement performs, is to attempt to delete old temporary files created by LOAD DATA events from @@global.slave_load_tmpdir. It does so using (effectively) a wildcard pattern that will not match other files created by the server. Such files will not be created if FILE privilege is not granted, due to 4.5. Therefore, we consider it safe to look for such files and delete them if they exist. 4.8. Note: Table_map_log_events are processed without privilege checks. The reason is that the event itself does not make any database change, and at the time of processing the event it is not known whether the following update will require INSERT, UPDATE, or DELETE privileges. The privilege check will instead be done when applying the row event. 4.9. Note: Replication applier threads update the system tables mysql.gtid_executed, mysql.slave_relay_log_info, and mysql.slave_worker_info, and replication receiver threads update the mysql.slave_master_info table. There are currently no privilege checks when replication threads access the tables to update replication metadata, i.e., there is no code path that inspects the security context. And even after this worklog, it should always be allowed for replication threads to modify these tables in order to update replication metadata. Therefore, we consider it as implicit in the REPLICATION_APPLIER privilege that these tables can be modified while updating replication metadata. (Accessing the tables through SQL or row events operating directly on the tables still requires privileges, just as any other table.) 4.10. Note: We may consider verifying that the user has privileges to execute 'USE db' for the database that is included in each Query_log_event. In case the user specified for PRIVILEGE_CHECKS_USER does not have privileges to execute 'USE db', the statement contained in Query_log_event may fail. If the error for the failing statement differs between the cases where the database exists or does not exist, then it is probably better to generate an error already on USE; otherwise an attacker might use the inbound replication channel to find out if a database exists or not, despite not having privileges on that database. 5. PRIVILEGE CHECKS WHEN EXECUTING BINLOG STATEMENTS ---------------------------------------------------- Executing the output from mysqlbinlog is logically equivalent to applying events in an applier thread in a replication channel. Users that replicate, may typically also have to apply the output from mysqlbinlog, in order to debug or recover from errors or bugs. Currently, executing the output from mysqlbinlog requires strong privileges: a user with sufficient privileges to apply the output of mysqlbinlog, can effectively do almost anything on the server. In particular, it is possible to do direct inserts into system tables in the mysql database. Specifically, by updating the mysql.user table a user can give herself arbitrary privileges. To allow users to prevent that, we introduce ways to enable more fine-grained privilege checks for sessions applying the output from mysqlbinlog: 5.1. REPLICATION_APPLIER shall give the right to execute BINLOG statements, under the condition that the user has privileges to execute the event according to 4.2-4.4. * Example: Consider the following statement: BINLOG 'base64 encoding of a row event representing an' 'INSERT INTO db.t VALUES (1) statement'; This statement is allowed if one or more of the following is true: 1. The user executing it has SUPER privileges, or 2. The user executing it has BINLOG_ADMIN privileges, or 3. The user executing it has *both* REPLICATION_APPLIER privileges *and* INSERT privileges on db.t. The two first cases are existing functionality. This worklog adds the third case. See also 12.5 6. PRIVILEGE CHECKS FOR SYSTEM VARIABLES SET BY REPLICATION ----------------------------------------------------------- The replication threads set a number of session variables while executing. mysqlbinlog also prints SET statements for these options. Some of the options require SESSION_ADMIN privileges. Therefore, if this behavior is to be secured, users will need to grant SESSION_ADMIN to the user specified for PRIVILEGE_CHECKS_USER. However, on cloud environments, SESSION_ADMIN may be too strong. Therefore, we analyze all the options to assess their impact on security, and propose a number of changes below. The following list of variables was extracted by searching for SET statements printed by mysqlbinlog, in log_event.cc and mysqlbinlog.cc. In the left column is the session variable name. The right column can be either: - a dash ('-'), meaning that no privileges are currently required in order to set the variable, or - a privilege, meaning that the session option is settable by users having either that privilege, or SYSTEM_VARIABLES_ADMIN, or SUPER. From log_event.cc: timestamp - pseudo_thread_id SESSION_ADMIN foreign_key_checks - sql_auto_is_null - unique_checks - autocommit - sql_mode - auto_increment_increment SESSION_ADMIN auto_increment_offset SESSION_ADMIN character_set_client - collation_connection - collation_server - time_zone - lc_time_names - collation_database SESSION_ADMIN explicit_defaults_for_timestamp SESSION_ADMIN default_collation_for_utf8mb4 SESSION_ADMIN sql_require_primary_key SESSION_ADMIN default_table_encryption TABLE_ENCRYPTION_ADMIN last_insert_id - insert_id - rand_seed1 - rand_seed2 - gtid_next SESSION_ADMIN original_commit_timestamp SESSION_ADMIN original_server_version SESSION_ADMIN immediate_server_version SESSION_ADMIN From mysqlbinlog.cc: pseudo_slave_mode SESSION_ADMIN sql_log_bin SESSION_ADMIN completion_type - character_set_results - rbr_exec_mode SESSION_ADMIN SET NAMES - Analysis of those options that require privileges, grouped by the kind of action they require: - The following options should be fixed in the current worklog, by removing the need to hold SESSION_ADMIN privilege, because they are harmless: 6.1. auto_increment_increment, auto_increment_offset, collation_database, explicit_defaults_for_timestamp, default_collation_for_utf8mb4, rbr_exec_mode: - ACTION: Remove the need for privileges when setting these options. - The following options should be fixed in the current worklog, by allowing users that hold REPLICATION_APPLIER to set the options, and later followed up by worklogs to improve security. The options have as primary purpose to preserve replication state through multiple hops in the replication topology, and are set by every replicated transaction. These represent various degrees of security vulnerabilities, even when setting them on untrusted replication channels, and have traditionally always been protected by a privilege (SUPER in 5.6/5.7, SESSION_ADMIN in 8.0). They should be protected on normal user sessions, because that provides sufficient protection on user sessions in case replication channels are trusted and run without privilege checks. Removing the need for privileges would be a security regression for most existing deployments. Therefore, two problems must be fixed: (1) privilege-protected replication channels should be able to set these options without holding SESSION_ADMIN; (2) inbound replication needs to be protected from the security vulnerabilities. We address (1) by making REPLICATION_APPLIER enable access to these variables. This also clarifies and formalizes the notion that the variables are specific to replication. We will have to address (2) by new features, specific to each option. 6.2. gtid_next: - ACTION: Allow users to set this while holding REPLICATION_APPLIER privilege. 6.3. original_commit_timestamp: - ACTION: Allow users to set this while holding REPLICATION_APPLIER privilege. 6.4. original_server_version, immediate_server_version: - ACTION: Allow users to set this while holding REPLICATION_APPLIER privilege. 6.5. pseudo_slave_mode: - ACTION: Allow users to set this while holding REPLICATION_APPLIER privilege. - The following options should not be changed in this worklog. The options need to be addressed in follow-up work, in order to remove the need to hold SESSION_ADMIN and/or address security issues: 6.6. pseudo_thread_id: - ACTION: Retain the requirement to hold SESSION_ADMIN in order to set this option. Make replication threads check privileges when setting the option. 6.7. sql_require_primary_key: - ACTION: Retain the requirement to hold SESSION_ADMIN in order to set this option. Make replication threads check privileges when setting the option. 6.8. default_table_encryption: - ACTION: Retain the requirement to hold TABLE_ENCRYPTION_ADMIN in order to set this option. - This option that should not change at all, is not set by replication threads, and only required when using a special option with mysqlbinlog: 6.9. sql_log_bin: - ACTION: No change Below, the list of session variables for which privileges were dropped when setting a value: BEFORE: Users must have `SESSION_VARIABLES_ADMIN` privilege in order to do `SET SESSION` values for the following variables: - auto_increment_increment - auto_increment_offset - collation_database - explicit_defaults_for_timestamp - default_collation_for_utf8mb4 - rbr_exec_mode. AFTER: Users can now do `SET SESSION` values without ANY privilege check for the following variables: - auto_increment_increment - auto_increment_offset - collation_database - explicit_defaults_for_timestamp - default_collation_for_utf8mb4 - rbr_exec_mode. 7. PERSISTENT CONFIGURATION --------------------------- 7.1. When @@global.relay_log_info_repository=FILE, the file shall contain two extra lines: 7.1.1. the username part of PRIVILEGE_CHECKS_USER, with newlines escaped, or empty line if PRIVILEGE_CHECKS_USER == NULL; 7.1.2. the hostname part of PRIVILEGE_CHECKS_USER, with newlines escaped, or empty line if PRIVILEGE_CHECKS_USER == NULL; 7.2. When @@global.relay_log_info_repository=TABLE, the mysql.slave_relay_log_info table shall contain three extra columns: 7.2.1. PRIVILEGE_CHECKS_USERNAME CHAR(32) COLLATE utf8_bin DEFAULT NULL COMMENT 'Username part of PRIVILEGE_CHECKS_USER' Newlines shall be escaped. If PRIVILEGE_CHECKS_USER == NULL, this shall be NULL. This column has the same definition as the User column of the mysql.user table (except nullability and default). 7.2.2. PRIVILEGE_CHECKS_HOSTNAME CHAR(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'Hostname part of PRIVILEGE_CHECKS_USER.' Newlines shall be escaped. If PRIVILEGE_CHECKS_USER == NULL, this shall be NULL. This column has the same definition as the Host column of the mysql.user table (except nullability and default). 8. OBSERVABILITY ---------------- 8.1. The performance_schema.replication_applier_configuration table shall have the following new column: PRIVILEGE_CHECKS_USER TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'User name for the security context of the applier.' This shall be a generated string on the form `user`@`host`. Both the user and host shall always be present, and always be quoted. The user and host name shall be properly escaped, so that it is possible to copy-paste from this column directly to an SQL statement, even in the presence of special characters. Remark. The column definitions in mysql.slave_relay_log_info and performance_schema.replication_applier_configuration differ significantly. The reason is that the user-visible performance_schema table needs a human-readable format whereas the internal slave_relay_log_info table needs a machine-parsable format. There is no existing way to parse the textual form (other than when it appears as a clause in an SQL statement). 8.2. There is an existing information-level message ER_RPL_SLAVE_SQL_THREAD_STARTING, which is currently printed to the error log when an applier thread starts, and is phrased as follows: "Slave SQL thread%s initialized, starting replication in log '%s' at position %s, relay log '%s' position: %s" This message shall remain for replication channels having PRIVILEGE_CHECKS_USER = NULL. For channels having PRIVILEGE_CHECKS_USER != NULL, the following new message shall be used: ER_RPL_SLAVE_SQL_THREAD_STARTING_WITH_PRIVILEGE_CHECKS "Slave SQL thread%s initialized, starting replication in log '%s' at position %s, relay log '%s' position: %s, user: '%.64s'@'%.255s', roles: %.512s" The 'roles' part shall be DEFAULT if activate_all_roles_on_login=0, and ALL if activate_all_roles_on_login=1. We reserve 64 characters for the user, in order to accommodate space for escape characters. 9. INVALID CONFIGURATION ------------------------------------- Configuration validity shall be checked in the following cases: - When the configuratios is loaded during server start. In this case, a warning shall be printed to the server log and the server shall resume starting. - When executing a CHANGE MASTER statement. In this case, an error shall be reported to the client and the statement shall have no effect. - When executing START SLAVE, or START GROUP REPLICATION statement, or when replication threads start during server start (in case skip-slave-start is not used). In this case, an error shall be reported to SHOW SLAVE STATUS / performance_schema.replication_applier_status_by_worker and the applier threads for the channel shall stop. Configuration can be invalid in the following ways: 9.1. The format should be valid, i.e.: - PRIVILEGE_CHECKS_USERNAME should have length at most 32. - PRIVILEGE_CHECKS_USERNAME should be valid utf8. - PRIVILEGE_CHECKS_HOSTNAME should have length at most 255. - PRIVILEGE_CHECKS_HOSTNAME should be lowercase ascii. - If PRIVILEGE_CHECKS_USERNAME is NULL, then PRIVILEGE_CHECKS_HOSTNAME must be NULL. Otherwise, use the following messages: The warning when server starts and the error shown by starting threads have the same text: ER_CLIENT_PRIVILEGE_CHECKS_USER_CORRUPT ER_LOG_WARN_PRIVILEGE_CHECKS_USER_CORRUPT "Invalid configuration for PRIVILEGE_CHECKS_USER was found in the replication configuration repository for channel %.192s. Use CHANGE MASTER TO PRIVILEGE_CHECKS_USER to correct the configuration." In this situation, performance_schema.replication_applier_configuration shall display the string "
" for PRIVILEGE_CHECKS_USER. (This can be distinguished from a user with the name , since valid usernames are quoted in this column, so that would display as e.g. ` `@`localhost`.) Note: this can only happen when the repository has been corrupted. So it will be detected the first time when the server starts, which generates a warning, and the second time if an applier thread is subsequently started. It cannot happen in a CHANGE MASTER statement, because if a PRIVILEGE_CHECKS_USER clause is included in the statement, the parser performs these validations, and if a PRIVILEGE_CHECKS_USER clause is not included, the format will not be validated. 9.2. The user specified for PRIVILEGE_CHECKS_USER should not be an anonymous user (i.e., the username part should have length greater than zero). Otherwise, use the following messages: Warning when server starts: Use ER_LOG_WARN_PRIVILEGE_CHECKS_USER_CORRUPT from 9.1. Error when starting replication threads: Use ER_CLIENT_PRIVILEGE_CHECKS_USER_CORRUPT from 9.1. Error for CHANGE MASTER: ER_CLIENT_PRIVILEGE_CHECKS_USER_CANNOT_BE_ANONYMOUS "PRIVILEGE_CHECKS_USER for replication channel '%.192s' was set to ``@`%.255s`, but anonymous users are disallowed for PRIVILEGE_CHECKS_USER." 9.3. The user specified for PRIVILEGE_CHECKS_USER should be a valid user. Otherwise, use the following messages: Warning when server starts: ER_WARN_LOG_PRIVILEGE_CHECKS_USER_DOES_NOT_EXIST "PRIVILEGE_CHECKS_USER for replication channel '%.192s' was set to `%.64s`@`%.255s`, but this is not an existing user. Correct this before starting replication threads." Error for CHANGE MASTER and when starting replication threads: ER_CLIENT_PRIVILEGE_CHECKS_USER_DOES_NOT_EXIST "PRIVILEGE_CHECKS_USER for replication channel '%.192s' was set to `%.64s`@`%.255s`, but this is not an existing user." 9.4. The user specified for PRIVILEGE_CHECKS_USER should have the REPLICATION_APPLIER privilege when using the default roles (or all roles, if activate_all_roles_on_login=1). Otherwise, use the following messages: Warning when server starts: ER_WARN_LOG_PRIVILEGE_CHECKS_USER_NEEDS_REPLICATION_APPLIER_PRIVILEGE "PRIVILEGE_CHECKS_USER for replication channel '%.192s' was set to `%.64s`@`%.255s`, but this user does not have REPLICATION_APPLIER privilege. Correct this before starting the replication threads." Error for CHANGE MASTER and when starting replication threads: ER_CLIENT_PRIVILEGE_CHECKS_USER_NEEDS_REPLICATION_APPLIER_PRIVILEGE "PRIVILEGE_CHECKS_USER for replication channel '%.192s' was set to `%.64s`@`%.255s`, but this user does not have REPLICATION_APPLIER privilege."
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.