WL#12966: Replication with privilege checks

Affects: Server-8.0 — Status: Complete

Description
Requirements
High Level Architecture
Low Level Design

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."