MySQL Bugs: #77071: grant all privileges on single db to certain user from any host doesn't work

Bug #77071	grant all privileges on single db to certain user from any host doesn't work
Submitted:	18 May 2015 11:48	Modified:	23 Nov 2016 12:22
Reporter:	admins spirula	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.5.43	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
grant all privileges on single database to certain user connected from any host doesn't work using the following query 

grant all privileges on test_db.* to test_user@'' with grant option;

it gives access denied when trying to use test_db by test_user.  

How to repeat:
what i did : 

grant all privileges on test_db.* to test_user@'' with grant option;

what i wanted to happen: 

grant test_user from any host all privileges on test_db with grant option

what actually happened : 

when i connected to mysql server, and tried to use test_db

USE test_db; 

i got access denied for user test_user@'' on test_db . 

On the other hand when i tried to grant test_user all privileges using the following query : 

grant all privileges on test_db.* to test_user@'%' with grant option;

it worked fine !

I can reproduce this 

-- as root
CREATE DATABASE test_db;
SET sql_mode = '';
GRANT ALL PRIVILEGES ON test_db.* TO test_user@'' WITH GRANT OPTION;
SELECT USER,HOST FROM mysql.user;
/*
user       host       
---------  -----------
test_user             
root       localhost  */

-- as test_user
SHOW DATABASES;
/*
Database            
--------------------
information_schema
*/

.. but is this construction with a *named user* and an *anonymous host* supposed to work at all? I don't think so and I think the GRANT (or CREATE USER) statement should fail with an error. 

An 'anonymous host' ('') is not the same as 'any host' ('%'), This will work as expected:

GRANT ALL PRIVILEGES ON test_db.* TO test_user@'%' WITH GRANT OPTION;

-- Peter
-- not a MySQL/Oracle person

Hello!

Thank you for the report and feedback.
Sorry for the delayed response, somehow I lost track of this issue.
Just I tried with latest build and this is not repeatable with latest 5.5.53/5.6.34 builds and observed that created user can connect from local/remotely without any issue and perform any DDL/DML in test_db:

-- As root (also, ensured no anonymous account exists)

root@localhost [(none)]> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> GRANT ALL PRIVILEGES ON test_db.* TO test_user@'' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> SELECT USER,HOST FROM mysql.user;
+-----------+-----------+
| USER      | HOST      |
+-----------+-----------+
| test_user |           |
| root      | 127.0.0.1 |
| root      | ::1       |
| root      | hod03     |
| root      | localhost |
+-----------+-----------+
5 rows in set (0.00 sec)

## later, tried from remote WB client, from localhost and it allows to connect user test_user to test_db and create tables etc

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh