WL#12086: Cryptographically signed SET statements

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Extra authentication is needed to avoid privilege escalation via SET PERSIST
ONLY setting sensitive data like for example secure-file-priv.
This is to avoid the risk of somebody impersonating the DBA sql

Currently the MySQL server doesn't allow these to be set so people are forced
into authenticating to the host OS and editing a text file. 

But this is impractical for cases where there's no shell access.

Hence we need to replace this extra authentication the OS remote login provides
with something else. 

This worklog proposes that we replace the shell authentication with PKI
signatures on the SQL session.

We will add a new *read only* command line/config file option to the server:
Default value is empty, takes a X509 distinguished name. 

We will allow SET PERSIST [ONLY] setting the variables listed on this page
when the session it's issued on has:
* enough permissions to execute the SET PERSIST command
* a client SSL certificate whose signature checks out against the server's CA
list and it has a distinguished name matching the one supplied in
FR1: A new global system variable will be added called
"persist_only_admin_x509_subject". The variable will:
FR1.1: be read only (not settable via SET GLOBAL)
FR1.2: take its values in the system charset
FR1.3: be available as a command line parameter
FR1.4: not be settable via SET PERSIST ONLY.
FR1.5: be settable via the my.cnf config file
FR1.6: have empty ("") as a default value
FR2: persist_only_admin_x509_subject, if specified and not empty, will allow
executing SET PERSIST [ONLY] on the variables listed in the HLS, that are
currently not settable via SET PERSIST [ONLY] *AND* are settable via the server
command line/SET statements. 
FR3: if persist_only_admin_x509_subject is not specified or is empty, the
variables listed in the HLS will keep being unsettable via SET PERSIST [ONLY]
FR4: if persist_only_admin_x509_subject is not specified the default for it will
be such that it will ensure full backward compatibility
FR5: persisted_globals_load should not be settable via SET PERSIST_ONLY even
when persist_only_admin_x509_subject is specified and it matches the X.509
subject of the client's certificate for the current session. This is because
persisted_globals_load is checked before the persisted variables file is processed. 
The variables affected are listed on

Not all variables on that list are affected, since some are not specifiable at
the command line too.

Here's a list of all affected variables:

* audit_log_file
* audit_log_format
* auto_generate_certs
* basedir
* bind_address
* caching_sha2_password_auto_generate_rsa_keys
* caching_sha2_password_private_key_path
* caching_sha2_password_public_key_path
* character_sets_dir
* daemon_memcached_engine_lib_name
* daemon_memcached_engine_lib_path
* daemon_memcached_option
* datadir
* default_authentication_plugin
* ft_stopword_file
* init_file
* innodb_buffer_pool_load_at_startup
* innodb_data_file_path
* innodb_data_home_dir
* innodb_dedicated_server
* innodb_directories
* innodb_force_load_corrupted
* innodb_log_group_home_dir
* innodb_page_size
* innodb_read_only
* innodb_temp_data_file_path
* innodb_temp_tablespaces_dir
* innodb_undo_directory
* innodb_undo_tablespaces
* keyring_encrypted_file_data
* keyring_encrypted_file_password
* lc_messages_dir
* log_error
* mecab_rc_file
* named_pipe
* pid_file
* plugin_dir
* port
* relay_log
* relay_log_info_file
* secure_file_priv
* sha256_password_auto_generate_rsa_keys
* sha256_password_private_key_path
* sha256_password_public_key_path
* shared_memory
* shared_memory_base_name
* skip_networking
* slave_load_tmpdir
* socket
* ssl_ca
* ssl_capath
* ssl_cert
* ssl_crl
* ssl_crlpath
* ssl_key
* tmpdir
* version_tokens_session_number

In general this applies to all variables defined to have the sys_var::NOTPERSIST
flag on and to all plugin/component variables that have the PLUGIN_VAR_NOPERSIST
flag on and that also are settable at the command line.

Why we need the extra variable ? 

We already can check X509 SUBJECT for user accounts. Why not use that instead of
--persist_only_admin_x509_subject ?
Mostly because in this case if you happen to have CREATE USER you could start
setting read only persisted sensitive variables in seconds: you just create a
user account with a subject name of a certificate you have and voila !

--persist_only_admin_x509_subject is still not settable via SET PERSIST[_ONLY].
Hence it still does require the extra authentication that an OS session entails. 
We will amend the place where we check for the sys_var::NOTPERSIST flag with
checks if the:
1. session uses SSL
2. the connecting client have supplied a certificate validated against the
server's CAs
3. the Subject of that certificate matches the string specified.

Note that exactly the same way to generate the name will be used as in the
server status variables that show the subject. This will allow one to just check
the subject in the status var and copy it into the new system var. 
And the format is the same as CREATE USER ... REQUIRE SUBJECT ... too.

== log_bin_use_v1_row_events ==

This variable was the only non-persisted variable that wasn't read-only.
Before WL#12086 the non-persist flag was just ignored for non-read-only variables.
Now it's honored. 
Hence the behavior of this variable changes.