Spec-Zone .ru
спецификации, руководства, описания, API
|
Required credentials for clients that connect to the MySQL server can include a password. This section describes how to assign passwords for MySQL accounts. In MySQL 5.7, it is also possible for clients to authenticate using plugins. For information, see Section 6.3.7, "Pluggable Authentication".
To assign a password when you create a new account with CREATE USER
, include an IDENTIFIED BY
clause:
mysql>CREATE USER 'jeffrey'@'localhost'
->IDENTIFIED BY 'mypass';
To assign or change a password for an existing account, one way is to issue a SET PASSWORD
statement:
mysql>SET PASSWORD FOR
->'jeffrey'@'localhost' = PASSWORD('mypass');
MySQL stores passwords in the user
table in the mysql
database. Only users such as root
that have update access to the mysql
database can change the password for other users. If you are not connected
as an anonymous user, you can change your own password by omitting the FOR
clause:
mysql> SET PASSWORD =
PASSWORD('mypass');
The old_passwords
system variable value determines the hashing method used by PASSWORD()
. If you
specify the password using that function and SET
PASSWORD
rejects the password as not being in the correct format, it may be necessary to set old_passwords
to change the hashing method. For descriptions of the permitted
values, see Section 5.1.4, "Server System Variables".
In MySQL 5.7, enabling the read_only
system variable prevents the use of the SET PASSWORD
statement by any user not having the SUPER
privilege.
You can also use a GRANT USAGE
statement at the global level (ON *.*
)
to assign a password to an account without affecting the account's current privileges:
mysql>GRANT USAGE ON *.* TO 'jeffrey'@'localhost'
->IDENTIFIED BY 'mypass';
To assign a password from the command line, use the mysqladmin command:
shell> mysqladmin -u user_name
-h host_name
password "newpwd
"
The account for which this command sets the password is the one with a user
table
row that matches user_name
in the User
column and the client host from which you connect in the Host
column.
During authentication when a client connects to the server, MySQL treats the password in the user
table as an encrypted hash value (the value that PASSWORD()
would return for the password). When assigning a password to an
account, it is important to store an encrypted value, not the plaintext password. Use the following guidelines:
When you assign a password using CREATE USER
, GRANT
with an IDENTIFIED BY
clause, or the mysqladmin password command, they encrypt the
password for you. Specify the literal plaintext password:
mysql>CREATE USER 'jeffrey'@'localhost'
->IDENTIFIED BY 'mypass';
For CREATE
USER
or GRANT
, you can avoid sending the plaintext password if you know the hash
value that PASSWORD()
would return for the password. Specify the hash value
preceded by the keyword PASSWORD
:
mysql>CREATE USER 'jeffrey'@'localhost'
->IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
When you assign an account a nonempty password using SET PASSWORD
, you must use the PASSWORD()
function to encrypt the password, otherwise the password is
stored as plaintext. Suppose that you assign a password like this:
mysql>SET PASSWORD FOR
->'jeffrey'@'localhost' = 'mypass';
The result is that the literal value 'mypass'
is stored as the password
in the user
table, not the encrypted value. When jeffrey
attempts to connect to the server using this password, the value is encrypted and compared to the
value stored in the user
table. However, the stored value is the
literal string 'mypass'
, so the comparison fails and the server rejects
the connection with an Access denied
error.
PASSWORD()
encryption differs from Unix password encryption. See Section
6.3.1, "User Names and Passwords".
It is preferable to assign passwords using SET
PASSWORD
, GRANT
, or mysqladmin, but it is also possible to modify the user
table directly. In this case, you must also use FLUSH PRIVILEGES
to cause the server to reread the grant tables. Otherwise, the
change remains unnoticed by the server until you restart it.
To establish a password for a new account, provide a value for the Password
column:
mysql>INSERT INTO mysql.user (Host,User,Password)
->VALUES('localhost','jeffrey',PASSWORD('mypass'));
mysql>FLUSH PRIVILEGES;
To change the password for an existing account, use UPDATE
to set the Password
column value:
mysql>UPDATE mysql.user SET Password = PASSWORD('bagel')
->WHERE Host = 'localhost' AND User = 'francis';
mysql>FLUSH PRIVILEGES;