0

I am aware that this question has been asked already on stackoverflow but I don't see any answer for the actual question posted. Hence posting again.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'abcd' WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY PASSWORD 'abcd' WITH GRANT OPTION;

Above lines throw

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 'abcd' WITH GRANT OP' at line 1

I am trying to upgrade mysql from 5.7 to latest 8.0.21

Thanks!

dj308
  • 47
  • 3
  • 6
  • old post - https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0 – dj308 Jul 24 '20 at 17:11

1 Answers1

2

For GRANT ALL privileges to root user use the following syntax:

GRANT ALL PRIVILEGES ON database_name.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

The above grant mysql command defines that:

  • GRANT the PRIVILEGES of type ALL
  • These privileges are for a particular database named dbname and it applies to all tables of that database indicated by the .* that follows the dbname.
  • These privileges are assigned to username when that username is connected through locally, as specified by @'localhost'.
  • You can change to specify any valid host, replace 'localhost' with '%'.
Ankit Jindal
  • 3,672
  • 3
  • 25
  • 37
  • 1
    Are you sure this will work for MySql 8 ? Also I want to grant PRIVILEGES to all databases. Can you please explain why the statements I have don't work ? Note - Privileges assigned through GRANT choice don't want FLUSH PRIVILEGES to take effect. – dj308 Jul 24 '20 at 17:37