CODEWITHSUNDEEP
mysqlprivilegessql-grant
user3461823
user3461823

Reputation: 1403

"IDENTIFIED BY 'password'" in MySQL

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?

Upvotes: 78

Views: 177734

Answers (5)

Zafar Malik
Zafar Malik

Reputation: 6844

To grant self-created user the permissions, the below line is enough for rights:

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

Note: Identify represents to your password what password you want to save for credential verification.

New Update:

In MySQL 8.0, you must create a user first before assigning permission, as the GRANT command will no longer create a new user.

  1. So till MySQL 5.7, you can create and assign permission in single command via GRANT like GRANT SELECT ON *.* TO 'USER'@'IP' IDENTIFIED BY 'PASS'; Note: Still better option is that first, we should create a user and then should assign permission to follow the standard process.

  2. But since MySQL 8.0, you must first create a user and then only can assign permission like CREATE USER 'USER'@'IP' IDENTIFIED BY 'PASS'; GRANT SELECT ON *.* TO 'USER'@'IP';

Upvotes: 15

Kevin Finnaran
Kevin Finnaran

Reputation: 19

Tried your idea, had to change it up a little to get to run without errors. Not sure how/why but it works.

CREATE DATABASE radius_db;

CREATE USER 'radius_user'@'localhost' IDENTIFIED BY 'Somestrongpassword_321';

GRANT ALL PRIVILEGES ON radius_db.* TO 'radius_user'@'localhost';

FLUSH PRIVILEGES;

quit;

Upvotes: 1

Muhammad Dyas Yaskur
Muhammad Dyas Yaskur

Reputation: 8098

from MySQL 5.7 doc says:

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

But I can't find that notice or similar message on MySQL 8.0 doc, and I tried:

grant all privileges on dbname . * to newuser@localhost;

it's returned:

ERROR: You are not allowed to create a user with GRANT

and with identified by :

grant all privileges on dbname . * to newuser@localhost  identified by 'passw0rd';

returns:

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 'passw0rd'' at line 1

MySQL 8.0 doc says:

Normally, a database administrator first uses CREATE USER to create an account and define its nonprivilege characteristics such as its password, whether it uses secure connections, and limits on access to server resources, then uses GRANT to define its privileges. ALTER USER may be used to change the nonprivilege characteristics of existing accounts

if you want to create user and grand the privilege use:

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

if want to update the password first use alter:

ALTER USER 'user'@'localhost' IDENTIFIED BY 'passw0rd';
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

Upvotes: 7

SystemParadox
SystemParadox

Reputation: 8647

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';

Upvotes: 99

scrowler
scrowler

Reputation: 24406

It's just an added security measure. You might have different passwords for the same user on different servers, for example in a shared host environment. If it's your own server and you and your colleagues are the only ones who use it then you don't need to identify the users you grant privileges to.

If you identify users then only the password you specify can be used with that user to perform those privileges.

Upvotes: 0

Related Questions