Setting session variables is available to everybody. But certain session variables can affect the work of the entire server. So allowing them set in an unsanctioned way can destabilize the work of the running server.

This worklog is about adding a new global dynamic privilege called SESSION_VARIABLES_ADMIN and having it checked for certain session variables.

• FR1: A new global dynamic privilege shall be added, called SESSION_VARIABLES_ADMIN
• FR2: root will have it granted by default, with grant option
• FR3: Checks if the current session has SESSION_VARIABLES_ADMIN will be added when setting a session value for the variables listed in the first group from HLS.
• FR4: The checks for SYSTEM_VARIABLES_ADMIN will be complemented with checks for SESSION_VARIABLES_ADMIN for the variables in the 3d group from HLS.
• FR5: For backward compatibility (and because it makes sense) SYSTEM_VARIABLE_ADMIN, in addition to its own rights, also grants the same rights as SESSION_VARIABLE_ADMIN: i.e. if you have SYSTEM_VARIABLE_ADMIN you can set the session variables protected by SESSION_VARIABLE ADMIN. But not the other way around.
• FR6: For backward compatibility SUPER grants SESSION_VARIABLE_ADMIN too.
• FR7: the mysql.session user will have SESSION_VARIABLES_ADMIN granted to it (in addition to SUPER and SYSTEM_VARIABLES_ADMIN that it currently has).

Rationale

The following vairables need to enforce SESSION_VARIABLES_ADMIN:

• auto_increment_increment
• auto_increment_offset
• binlog_direct_non_transactional_updates
• bulk_insert_buffer_size
• character_set_database
• character-set-filesystem
• collation_database
• pseudo_slave_mode
• pseudo_thread_id
• transaction_write_set_extraction
• rbr_exec_mode

The following variables will not be protected:

• default_storage_engine
• default_tmp_storage_engine
• max_allowed_packet
• rand_seed1
• rand_seed2

These variables should transition from checking SYSTEM_VARIABLES_ADMIN to SESSION_VARIABLES_ADMIN:

• histogram_generation_max_mem_size
• sql_log_off
• debug_sync
• original_commit_timestamp
• The not documented gtid_next
• The disabled and not documented gtid_next_list
• default_collation_for_utf8mb4
• explicit_defaults_for_timestamp
• sql_log_bin
• explicit_defaults_for_timestamp The variable is mis-documented as not requiring SYSTEM_VARIABLES_ADMIN for SET SESSION. But in reality it does require it. Since the variable is deprecated we'll keep the current behavior.
• binlog_format
• binlog_row_image
• binlog_row_value_options
• binlog_rows_query_log_events

Implementation details

Implemented as a set of direct checks on certain variables, using the on_check() hook.

This is also the suggested way for plugin and component variables wishing to follow: add a check for the privileges via the dynamic privileges component service.

Considered adding a flag to the variable definition, but ruled it out due to having to break the ABI compatibility.