Bug #42124 sql_mode should be integrated into privileges system
Submitted: 15 Jan 2009 6:29 Modified: 15 Jan 2009 8:17
Reporter: Shlomi Noach (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:4.1, 5.0, 5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: SQL_MODE

[15 Jan 2009 6:29] Shlomi Noach
Description:
Some modes in sql_mode dictate the nature of data integrity: ALLOW_INVALID_DATES, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO etc.

Since every user can "SET sql_mode = 'whatever she likes'", different users can write incompatible values into, say, a DATE column: while one may not be allowed to set ZERO, others may.

I suggest adding the ZERO_IN_DATE, for example, into the privileges system. So that we can use GRANT as follows:

GRANT SELECT, UPDATE, ZERO_IN_DATE ON world.* TO 'someuser'@'somehost';

This could be implemented in global, schema, table and column levels.

How to repeat:
NA

Suggested fix:
Integrate parts of sql_mode into privileges system.
[15 Jan 2009 6:59] Valeriy Kravchuk
I think this is a duplicate of bug #42034.
[15 Jan 2009 8:17] Shlomi Noach
While the issue arises from a similar place, the suggested solution is different.
While Roland Bouman suggests:

GRANT SET SQL_MODE ... TO ...
which allows for a user to change her session sql_mode, I suggest:

GRANT ZERO_IN_DATE ON ... TO ...
Which allows for users to set zero in date for given schemas, tables, columns. It has nothing to do with changing the sql_mode itself.

And so the feature request which I suggest is different from the feature request in [http://bugs.mysql.com/bug.php?id=42034].