MySQL Bugs: #42034: SET sql_mode should be a privileged action

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

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

Thank you for a reasonable feature request.

Certainly a good idea.

No more privileges, please!  SUPER is fine, I think!

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.

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.

Bug #42124 was marked as a duplicate of this one.

some secondary motivation to disallow users setting sql mode - they could cause outages:
bug #45807 (crash)
bug #46968 (backup disruption)

another related: bug #17998

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.

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.