CODEWITHSUNDEEP
mysql
Doc
Doc

Reputation: 51

Can't grant all privileges to root in MySQL 5.7. Syntax error

This one is really baffling. I hope I'm not doing something stupid, but it just doesn't make sense. I installed a fresh MySQL 5.7 on Ubuntu, logged in locally to mysql as root and ran the following:

CREATE USER 'root'@'%' IDENTIFIED BY 'password';

Success!

Next, I ran this:

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

No bueno. This resulted in the following error:

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 '*.* TO 'root'@'%' WITH GRANT OPTION' at line 1

WTF? I've been running that command on MySQL servers for 2 years. Why didn't it work? As any good little developer would do, I decided to RTM. Here's the example given in the 5.7 manual (http://dev.mysql.com/doc/refman/5.7/en/adding-users.html):

mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
->     WITH GRANT OPTION;

Am I taking crazy pills? Except for the username, that's exactly what I did. This should just work, right??? There's a baffling lack of other people on the Internet reporting this problem, so I assume I must just be glossing over something obvious, but I'm 2 hours deep into this and just plain can't figure it out. Thanks in advance.

Upvotes: 5

Views: 14224

Answers (2)

Alp Altunel
Alp Altunel

Reputation: 3443

on mysql syntax has been changed

GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

Upvotes: 2

XCloud
XCloud

Reputation: 71

After kicking my own brain for several minutes, remembered that, by default in a new install, the user "root@%" doesn't exists and must be created separately.

This must work for you:

CREATE USER 'root'@'%' IDENTIFIED BY 'yourpassword';    
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'  WITH GRANT OPTION;
FLUSH PRIVILEGES;

Upvotes: 7

Related Questions