"IDENTIFIED BY 'password'" in MySQL

mysql privileges grant
user3461823 picture user3461823 · Jun 29, 2015 · Viewed 95.6k times · Source

I often see in many MySQL tutorials that people use command IDENTIFIED BY 'password' both during user creation and granting him privileges.

For example:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password';

I tried using GRANT without IDENTIFIED BY and it works.
Can somebody explain me why it is used twice? Could there be other password for specific privileges?

Answer

SystemParadox picture SystemParadox · Mar 24, 2017

GRANT is meant for adding privileges to users. Confusingly, it also has the ability to create users and change their passwords. This functionality is deprecated and should not be used.

If you use GRANT with IDENTIFIED you can change the user's password:

When IDENTIFIED is present and you have the global grant privilege (GRANT OPTION), any password specified becomes the new password for the account, even if the account exists and already has a password. Without IDENTIFIED, the account password remains unchanged.

As of MySQL 5.7.2, if the account already exists, IDENTIFIED WITH is prohibited because it is intended only for use when creating new accounts.

Also, GRANT may create the user if it does not exist:

If an account named in a GRANT statement does not exist, the action taken depends on the NO_AUTO_CREATE_USER SQL mode:

  • If NO_AUTO_CREATE_USER is not enabled, GRANT creates the account. This is very insecure unless you specify a nonempty password using IDENTIFIED BY.
  • If NO_AUTO_CREATE_USER is enabled, GRANT fails and does not create the account, unless you specify a nonempty password using IDENTIFIED BY or name an authentication plugin using IDENTIFIED WITH.

Use of GRANT to define account authentication characteristics is deprecated as of MySQL 5.7.6. Instead, establish or change authentication characteristics using CREATE USER or ALTER USER. This GRANT capability will be removed in a future MySQL release.

See https://dev.mysql.com/doc/refman/5.7/en/grant.html

In summary, use CREATE to create a user, and use GRANT to add privileges:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';

Related questions

Why is a "GRANT USAGE" created the first time I grant a user privileges?

I'm new to the admin side of DBMS and was setting up a new database tonight (using MySQL) when I noticed this. After granting a user a privilege for the first time, another grant is created that looks like GRANT …

Read More
Remove privileges from MySQL database

Before you think this is a duplicate question, I believe I have a unique, even if it is somewhat dim-witted, case. A few days ago, I upgraded the version of MySQL on my Ubuntu 10.04 server to 5.3.3 (it's ahead of the …

Read More
Mysql: execute command denied to user ''@'localhost' for routine error

i got some problem during open my old website. My dataTable show: DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error. After that, I tried to debug my script and found …

Read More