4

I'm using the latest version of mysql. When running the below command, its asking me to use the ALTER USER command instead of GRANT. I don't see the syntax to use the ALTER USER command to update privileges anywhere. https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' IDENTIFIED by ‘1234’;

Any pointers would be much appreciated.

Indy
  • 814
  • 1
  • 11
  • 23

1 Answers1

7

The documentation of GRANT mentions:

Note

If an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode. It is also possible to use GRANT to specify nonprivilege account characteristics such as whether it uses secure connections and limits on access to server resources.

However, use of GRANT to create accounts or define nonprivilege characteristics is deprecated as of MySQL 5.7.6. Instead, perform these tasks using CREATE USER or ALTER USER.

The IDENTIFIED by ‘1234’ part of your query is used to set/change the password of the user. The password is not a privilege, it should be changed using ALTER USER.

Use:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'

to grant the desired privileges to the user and:

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234'

to change its password, if needed.

Remark

It is worth mentioning that the ALTER USER statement was introduced in MySQL 5.6. For older versions, the GRANT statement is the only way to change the user's password.

Community
  • 1
  • 1
axiac
  • 68,258
  • 9
  • 99
  • 134
  • 2
    Nice answer. I deleted my incorrect answer. Still want to mention though that as far as I know the root-user should not be used. – vrijdenker Oct 17 '17 at 10:27
  • GRANT SELECT ON *.* TO 'db_user'@'%'; to avoid issue https://stackoverflow.com/questions/20036547/mysql-grant-read-only-options/20061002 – Sachin G. Jun 04 '20 at 11:14