MySQL Bugs: #13989: Cannot create user with GRANT

Bug #13989	Cannot create user with GRANT
Submitted:	13 Oct 2005 1:27	Modified:	19 Oct 2005 15:02
Reporter:	Christian Call	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-nt	OS:	Windows (Windows XP Pro SP2)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The statement

GRANT ALL ON <table> to public

Does not work.  The server returns an error message 

ERROR 1133 (42000): Can't find any matching row in the user table

Actually, I believe that what should happen is that the user "public" should be created, with no password, and should be granted all privileges to <table>.

How to repeat:
Create a database.
Create a table in it.
Issue the GRANT command shown above, using the name of the new table instead of "<table>".

Hi!

I was unable to reporduce this bug:

mysql> grant all on user to public;
Query OK, 0 rows affected (0.03 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.13-rc-log |
+---------------+
1 row in set (0.02 sec)

mysql> exit
Bye

C:\my>

The following batch file will cause the failure I reported:

create database dabble;
use dabble;
create table tabby ( name varchar(80) );
grant all on tabby to unclesam;
grant all on tabby to unclesam identified by '';

Here is what happens when I run it:

C:\DOCUME~1\CHRIS~1.CAL\MYDOCU~1\MySQL>mysql -h localhost -u root -p < broken.sql
Enter password: *
ERROR 1133 (42000) at line 4: Can't find any matching row in the user table

Oh... I meant to attach the version info as well.  Here it is:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.02 sec)

Actually using your original script and the my.ini created by the installer
I was able to repeat the behavior reported:

C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>mysql -hlocalhost -uroot -p < broken.sql
Enter password: ****
ERROR 1133 (42000) at line 4: Can't find any matching row in the user table

However that happens because the below line of my.ini:

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then changing the script (how showed at the bottom) or removing that
restriction in the sql-mode in the my.ini file you should able for to grant
the privileges:

C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>type broken.sql
create database dabble;
use dabble;
create table tabby ( name varchar(80) );
create user unclesam identified by "";
grant all on tabby to unclesam;

C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>mysql -hlocalhost -uroot -p < broken.sql
Enter password: ****

C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>mysql -uunclesam
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.13-rc-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for "unclesam"@"%";
+------------------------------------------------------------+
| Grants for unclesam@%                                      |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'unclesam'@'%'                       |
| GRANT ALL PRIVILEGES ON `dabble`.`tabby` TO 'unclesam'@'%' |
+------------------------------------------------------------+
2 rows in set (0.02 sec)

mysql>

The correct line of my.ini is, I pasted a commented one:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I think the explanation is not clear.

The point is:

remove the option NO_AUTO_CREATE_USER from sql-mode.