Where is the password for mysql placed?

The password of mysql is placed in the authentication_string field of the user permission table. The user table is the most important permission table in MySQL. It is used to record account information that is allowed to connect to the server. When modifying the user password, it actually modifies the value of the authentication_string field in the user table. The syntax is "SET PASSWORD FOR 'username'@' hostname'=PASSWORD ('new password')".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The password of mysql is placed in the authentication_string field of the user permission table.

User column of MySQL user permission table

MySQL will automatically create a database named mysql during installation, mysql database Stored in are user permission tables. After the user logs in, MySQL will grant corresponding permissions to each user based on the contents of these permission tables.

The user table is the most important permission table in MySQL, used to record account information allowed to connect to the server. It should be noted that all permissions enabled in the user table are global and apply to all databases.

#The fields in the user table can be roughly divided into 4 categories, namely user columns, permission columns, security columns and resource control columns.

The user column stores the information that users need to enter when connecting to the MySQL database.

It should be noted that MySQL 5.7 version no longer uses Password as the password field, but changed it to authentication_string.

MySQL user columns are shown in Table 1.

Table 1: User column of user table

Field name	Field type	Is it empty	Default value	Description
Host	char(60)	NO	None	Host Name
User	char(32)	NO	None	Username
authentication_string	text	YES	None	Password

When a user logs in, if these three fields match at the same time, the MySQL database system will allow him to log in. When creating a new user, the values ​​of these three fields are also set. When modifying the user password, it actually modifies the value of the authentication_string field of the user table. Therefore, these 3 fields determine whether the user can log in.

Use the SET statement to modify the password of an ordinary user

In MySQL, only the root user can change the password by updating the MySQL database. After logging in to the MySQL server as the root user, you can use the SET statement to modify the ordinary user password. The syntax format is as follows:

SET PASSWORD FOR 'username'@'hostname' = PASSWORD ('newpwd');

Among them, the username parameter is the user name of an ordinary user, the hostname parameter is the host name of an ordinary user, and newpwd is the new password to be changed.

Note: The new password must be encrypted using the PASSWORD() function. If it is not encrypted using PASSWORD(), it will be executed successfully, but the user will not be able to log in.

If an ordinary user changes the password, you can omit the FOR clause to change your own password. The syntax format is as follows:

SET PASSWORD = PASSWORD('newpwd');

Example 1

First create a testuser user without a password. The SQL statement and running results are as follows:

mysql> CREATE USER 'testuser'@'localhost';
Query OK, 0 rows affected (0.14 sec)

root user After logging in to the MySQL server, use the SET statement to change the password of the testuser user to "newpwd". The SQL statement and running results are as follows:

mysql> SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD("newpwd");
Query OK, 0 rows affected, 1 warning (0.01 sec)

It can be seen from the running results that the SET statement is executed successfully, and the password of the testuser user is Successfully set to "newpwd".

The following verifies whether the password of the testuser user has been modified successfully. Exit the MySQL server, log in as the testuser user, and enter the password "newpwd". The SQL statement and running results are as follows:

C:\Users\leovo>mysql -utestuser -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.29-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

It can be seen from the running results that the testuser user logged in successfully and changed the password successfully.

Example 2

Use the testuser user to log in to the MySQL server, and then use the SET statement to change the password to "newpwd1". The SQL statement and running results are as follows:

mysql> SET PASSWORD = PASSWORD('newpwd1');
Query OK, 0 rows affected, 1 warning (0.00 sec)

It can be seen from the running results that the password was changed successfully.

[Related recommendations: mysql video tutorial]

The above is the detailed content of Where is the password for mysql placed?. For more information, please follow other related articles on the PHP Chinese website!