Bug #65333 option --safe-user-create and sql_mode NO_AUTO_CREATE_USER should be dependent
Submitted: 16 May 2012 13:03 Modified: 17 May 2012 12:18
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.x OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[16 May 2012 13:03] Peter Laursen
option --safe-user-create and GLOABL sql_mode NO_AUTO_CREATE_USER should be interdependent. They are overlapping and do almost the same according to http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_safe-user-create and http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_auto_create_user - namely:

1) prevent implicit creation of user by executing a GRANT statement
2) prevent creation of a user with an empty password

How to repeat:
1) --safe-user-create used as option
2) SHOW GLOBAL VARIABLES LIKE 'sql_mode'; -- returns empty set

Suggested fix:
In this case I think --safe-user-create should initialize global sql_mode to contain NO_AUTO_CREATE_USER. Further --safe-user-create should prevent setting setting a SESSION sql_mode not containing NO_AUTO_CREATE_USER.

I also note from docs: 
--safe-user-create: If this option is enabled, a user cannot create new MySQL users by using the GRANT statement unless the user has the INSERT privilege for the mysql.user table or any column in the table. "unless the user has the INSERT privilege .. " is an incompability between how the option works and how the sql_mode works.

So we have an old implementation (the option) and a newer (an sql_mode) and I think it should be coordinated how they work together.
[16 May 2012 20:19] Peter Laursen
.. and besides --safe-user-create should be exposed as a variable (unless NO_AUTO_CREATE_USER sql_mode is made a synonym).
[16 May 2012 20:22] Peter Laursen

How to repeat:
1) --safe-user-create used as option
2) SHOW GLOBAL VARIABLES LIKE 'sql_mode'; -- returns empty *string* (not empty *set*)