| Bug #42035 | session defaults on the account level | ||
|---|---|---|---|
| Submitted: | 11 Jan 2009 22:15 | Modified: | 1 Oct 2009 7:39 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[12 Jan 2009 7:57]
Valeriy Kravchuk
Thank you for a reasonable feature request.
[1 Oct 2009 7:35]
MySQL Verification Team
This is a hackish way of doing it: mysqld --init-connect="set session sql_mode=case current_user() when 'shane@%' then 'strict_all_tables' when 'roland@%' then 'strict_trans_tables' when 'bob@%' then '' else 'traditional' end;"
[1 Oct 2009 7:39]
Roland Bouman
Hi Shane, thanks! I have been thinking along these lines, but stopped pursuing it because it would become pretty unmaintainable. I envisioned a solution that would use a stored procedure so you can specify it all in database tables and manage it from there, but unfortunately you cannot persistently set the SQL_MODE from within a stored routine. Really, it would be so much nicer if this could be dealt with in a clean way. Anyway thanks for the suggestion. I'll link to it from the relevant blog post.
[4 Jan 2023 11:46]
Simon Mudd
Related: https://bugs.mysql.com/bug.php?id=105006
Description: Many applications require specific session level settings with regards to storage_engine, sql_mode, and autocommit. It would be nice if the application's user account could be defined along with the set of defaults so that these would be automatically applied when the account is used to establish a connection. Not only would this feature help new applications which require specific settings, it would also provide a clean an maintainable mechanism to run applications that expect MySQL to be completely default on servers that are configured to run with special options that are incompatible with these applications How to repeat: na Suggested fix: Add the ability to specify default session-level settings on the account level so that these can automatically be applied as soon as the user connects. Example syntax: CREATE USER user@host IDENTIFIED BY 'password' SESSION DEFAULTS SET SQL_MODE := 'TRADITIONAL,NO_ENGINE_SUBSTITUTION,IGNORE_SPACE,PIPES_AS_CONCAT,ANSI_QUOTES' , AUTOCOMMIT := OFF , STORAGE_ENGINE := INNODB ;