10

I have a MySQL database and I am not able to grant permissions to users on newly created databases when logging in as root. 

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> grant usage on *.* to 'test'@'%' identified by 'test';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
mysql> show grants for root;
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*hash_is_here' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select user();
+-----------------+
| user()          |
+-----------------+
| root@172.17.0.4 |
+-----------------+
1 row in set (0.01 sec)

So as you can see, I can log in as root, root has all privileges on .. Any ideas how could this happen? I haven't upgraded MySQL while keeping the data (I know that this could be caused by upgrading from 5 to 5.5). Thanks.

sz4kerto
  • 201
  • 1
  • 3
  • 5
  • i don't think the problem is this, but anyway, your grant command is wrong, "grant usage on *.* to 'test'@'%' identified by 'test';" like that you give all privilegies to test on all databases – c4f4t0r Mar 13 '14 at 14:00

2 Answers2

5

You have all privileges for root@% but not the "grant option". In my MySQL installation, where my root account can apply grant, this is the output of "show grants" :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*some_hash' WITH GRANT OPTION

In MySQL, giving "all privileges" does not include grant. It must be given explicitly.

Chaminda Bandara
  • 577
  • 6
  • 17
Benoit
  • 396
  • 2
  • 10
  • 2
    Thanks, I figured it out just could not answer my own question. Root@% does not have grant, just root@localhost. https://github.com/orchardup/docker-mysql/commit/0a8dd7d85e745b26aa9793e53b92550ad968ca7d#commitcomment-5664813 – sz4kerto Mar 13 '14 at 14:03
  • GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD ‘tset123123’ WITH GRANT OPTION; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY PASSWORD ‘tset123123’ WITH GRANT OPTION' at line 1 – Coffee inTime Sep 30 '22 at 13:55
0

Even though you're not on 5.5, maybe you still need to run mysql_upgrade from the /mysql/bin directory?

See the first answer here:

Chaminda Bandara
  • 577
  • 6
  • 17
trpt4him
  • 253
  • 1
  • 7