“由“密码"标识"；在MySQL中 [英] "IDENTIFIED BY 'password'" in MySQL

问题描述

我经常在许多MySQL教程中看到人们在创建用户和授予他特权时都使用命令IDENTIFIED BY 'password'.

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

例如:

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

我尝试在不使用IDENTIFIED BY的情况下使用GRANT，并且有效.
有人可以解释一下为什么要使用两次吗?可以使用其他密码来获得特定特权吗?

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?

推荐答案

GRANT用于向用户添加特权.令人困惑的是，它还具有创建用户和更改其密码的能力.此功能已弃用，不应使用.

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.

如果将GRANT与IDENTIFIED结合使用，则可以更改用户密码:

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

如果存在IDENTIFIED并且您具有全局授予特权(GRANT OPTION)，则指定的任何密码都将成为该帐户的新密码，即使该帐户存在并且已经具有密码.如果没有IDENTIFIED，则帐户密码保持不变.

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.

从MySQL 5.7.2开始，如果该帐户已经存在，则IDENTIFIED WITH被禁止，因为该帐户仅在创建新帐户时使用.

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.

此外，GRANT可能会创建用户(如果不存在):

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

如果GRANT语句中命名的帐户不存在，则采取的操作取决于NO_AUTO_CREATE_USER SQL模式:

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

• 如果未启用NO_AUTO_CREATE_USER，则GRANT将创建该帐户.除非您使用IDENTIFIED BY指定非空密码，否则这是非常不安全的.
• 如果启用了NO_AUTO_CREATE_USER，则GRANT将失败并且不会创建帐户，除非您使用IDENTIFIED BY指定非空密码或使用IDENTIFIED WITH命名身份验证插件.

从MySQL 5.7.6开始，不赞成使用GRANT定义帐户身份验证特征.而是使用CREATE USER或ALTER USER建立或更改身份验证特征.此GRANT功能将在将来的MySQL版本中删除.

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.

请参见 https://dev.mysql.com/doc/refman /5.7/en/grant.html

总而言之，使用CREATE创建用户，然后使用GRANT添加权限:

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

这篇关于“由“密码"标识"；在MySQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！