Bug #42034 SET sql_mode should be a privileged action
Submitted: 11 Jan 2009 22:02 Modified: 13 Jan 2009 22:08
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1,5.0,4.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: SQL_MODE
Triage: Triaged: D5 (Feature request)

[11 Jan 2009 22:02] Roland Bouman
Description:
The ability for an arbitrary user to change the sql_mode is a serious problem. For example, changing the sql_mode when using partitioned tables can lead to data loss and corruption (as per http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html)

Making the set sql_mode command a privileged action that can by default be executed only by root and SUPER users would alleviate this problem. It would be even better if an explicit privilege would be created for this, so that it is still possible to grant the ability to change the sql_mode to a particular user.

How to repeat:
NA

Suggested fix:
Do not allow arbitrary users to set the sql_mode by default. Instead, allow this only to root and SUPER users and users having the SET SQL_MODE privilege
[12 Jan 2009 7:04] Valeriy Kravchuk
Thank you for a reasonable feature request.
[12 Jan 2009 9:53] Michal Jarosz
Certainly a good idea.
[13 Jan 2009 21:55] Peter Laursen
No more privileges, please!  SUPER is fine, I think!
[13 Jan 2009 22:08] Roland Bouman
Peter: IMHO having it available only for SUPER and root only makes sense in case this feature: http://bugs.mysql.com/bug.php?id=42035 is implemented. Otherwise, you'd need to grant SUPER to random applications that expect an empty SQL_MODE, which is the last thing I'd want.

That said, if both suggestions 42035 and 42034 are implemented together, I think you are right and I would like to see it available only for SUPER and root in that case.
[15 Jan 2009 6:34] Shlomi Noach
One of the suggestions above is to GRANT SET SQL_MODE to users.
I further suggest that particular modes can be granted. Thus:

GRANT SET SQL_MODE(ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO) ON *.* TO 'someuser'@'somehost';

Will only allow the user to set/unset the two: ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO properties, but that user would not be able to unset STRICT_ALL_TABLES, for example.

GRANT SET SQL_MODE ON *.* TO 'someuser'@'somehost';

Will grant change of all sql_mode properties.
[15 Jan 2009 7:03] Valeriy Kravchuk
Bug #42124 was marked as a duplicate of this one.
[1 Oct 2009 8:52] Shane Bester
some secondary motivation to disallow users setting sql mode - they could cause outages:
bug #45807 (crash)
bug #46968 (backup disruption)
[1 Oct 2009 9:18] Shane Bester
another related: bug #17998
[2 Oct 2009 9:11] Sergei Golubchik
I don't like this idea (using SUPER, that is).

First it's a badly incompatible change in behavior.

Second we'll have all hosting providers screaming at once - in their setup there are many (thousands?) users, one db per user, and users are independent - every one can mess with his own database any way he likes. Granting SUPER to all of them is absolutely unacceptable, if you remember what SUPER users can do.

a better solution would be to introduce "min_sql_mode" and "max_sql_mode" global variables. a session sql_mode will always have all bits set in the min_sql_mode and will never have bits unset in max_sql_mode. Bits that are unset in min_ and set in max_ can be freely chosen for the session sql_mode.

A perfect solution would introduce min. and max. versions for all variables.

These two approaches are perfectly backward compatible.

A simple - but limited and incompatible - solution would simply use global.sql_mode as min.sql_mode, not allowing to remove any mode from session.sql_mode that is set in global.sql_mode.
[2 Oct 2009 12:47] Simon Mudd
For those who don't like sql_mode being limited to SUPER, make it a configurable default and not enabled. This keeps old users happy.

For those people who DO NOT want the users to change their SQL_MODE, enable this option.  Again the other option is to make this a new privilege, but to maintain backwards compatibility you still need a way to enforce the option, disabling the enforcement by default.

These backwards compatibility "hooks" should be signalled as such and the default should probably be stricter after say 5.4, but still the users allowed to adjust the config for a more lax behaviour if they really need it.