MySQL Bugs: #105006: provide per-user login session variable settings

Bug #105006	provide per-user login session variable settings
Submitted:	22 Sep 2021 11:46	Modified:	4 Jan 2023 13:06
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	custom login session settings, flexibility, User_attributes

Description:
Specific problem:

* We have users which stay connected but idle for too long, wasting connection resources (memory), important if the number of connections is high
* We have applications that don't use primary keys yet we generally want to ensure sql_require_primary_key = 1, so we'd like a way to disable this check for this specific user
* other session settings might be conveniently configured for specific users on login
* I would like to be able to migrate to using sql_mode = STRICT_ALL_TABLES for an app that fails to handle DML changes correctly (invalid values or size issues) and have control of that user by changing login attributes without having to set values at the global level
* I do not want the user to have to handle this in code partly because not all systems have access to the code to modify it to handle such changes
* I would like to be able to handle such changes at the server level "centrally", thinking in terms of an asynchronous or grep replication "cluster" of MySQL servers, managed only from the master or primary.

Currently MySQL has no way to configure such attributes or handle such tasks.

How to repeat:
read the docs and see that this is not possible at the moment.

Suggested fix:
I see there is a mysql.user.User_attributes column which currently allows json input and potentially could allow more user customisation without requiring changes to the MySQL Internal tables.

I would like to be able to customise the experience so we can adjust user login session settings as needed.

I would prefer configuration to be made within the MySQL server, and not external to it so that changes can be replicated via SQL commands if done at that level, perhaps an ALTER USER command to provide the option to set custom user_attributes (login_session_variables) would be cleaner as it would avoid direct manipulation of the underlying database tables, but this requires a syntax change in the server, unless a generic syntax could be used for this to provide more flexibility.

The end result would be I'd like to be able to do the equivalent of making an existing setting such as:

User_attributes: {"additional_password": "$A$00XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"}

to be modified to:

-- app that does DDL and doesn't understand about primary keys
User_attributes: {"additional_password": "$A$00XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", "login_session_variables": {"wait_timeout": 3600, "sql_require_primary_key": "OFF"}} 

or 

-- app that is idle for too long and taking up more resources than desired
User_attributes: {"additional_password": "$A$00XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", "login_session_variables": {"wait_timeout": 60}}

rather than using /etc/my.cnf configured settings of:

sql_require_primary_key = 1
wait_timeout = 600

The main thing is:
* keep this configuration in the database so that it can be distributed via replication
* make it tolerant to changes that the server does not expect so external plugins could potentially manipulate and add extra user information without impacting normal server behaviour
* in this specific case on logging in apply the "login_session_variables" to the session to the user concerned.

No, I don't see this as being the same, the implementation described in the worklog feels different to me.

The intent of this FR is that:
* settings can be configured per user, perhaps in The User_attributes column, additional JSON fields
* as part of the login process (thus a one off action for the session) if configured these user session settings would override the settings populated from the global variables.
* after this changes would happen at the session level as before

This allows the administrator to optionally custom-configure the session settings which is currently not possible.
This avoids the user having to have special connect scripts to setup their environment but still allows this to happen thus overriding the administrator configured settings.

We can go into a huge amount of extra complexity about whether we allow some  settings to be user-modified or not but that's out of scope for the feature request I'm making.

The intent is a SIMPLE way to achieve this, with what should be minimal code-side changes, thus allowing it to be implemented quickly.  It would make management of larger environments easier and more flexible.

oh and to emphasise a point, https://dev.mysql.com/worklog/task/?id=683 suggests things can be configured in a my.cnf file on the server.

That will not work via replication so does not scale if you have a large cluster of servers.

I do not want to have to maintain the configuration of X individual servers with Y users. This can be done as I suggest by replication from a single server and distributed via replication to all servers in the cluster / chain pretty much instantaneously so would be a far superior solution I think.

I indeed read that WL683 too fast..  Let that be irrelevant for this FR.
Thanks for the clarification.

Another potential use-case would being able to set a new parameter I have requested in bug#105089: range_optimizer_fail_on_memory_exceeded = 1.

I could set this on for users where falling back to a table scan would be considered unacceptable but leave it off for more batch oriented users.

Also could be used for "max_execution_time".

Related: https://bugs.mysql.com/bug.php?id=42035

time_zone would also be a good value to use and be able to configure here.