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

[13 Oct 2005 1:27] Christian Call
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>".
[13 Oct 2005 6:47] Jorge del Conde
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>
[18 Oct 2005 17:44] Christian Call
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
[18 Oct 2005 17:46] Christian Call
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)
[19 Oct 2005 14:55] MySQL Verification Team
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>
[19 Oct 2005 15:02] MySQL Verification Team
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"
[17 Nov 2005 11:15] Marcello Romani
I think the explanation is not clear.

The point is:

remove the option NO_AUTO_CREATE_USER from sql-mode.