A brief discussion on MySQL user permission table

MySQL will automatically create a database named mysql when it is installed. The mysql database stores 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, which is 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 four categories, namely user column, permission column, security column and resource control column. The following mainly introduces the meaning of these fields.

User Column

The user column stores the information that the user needs 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 has changed to authentication_string.

The user list for MySQL 5.7 is shown in Table 1.

Table 1: User column of the user table

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

Permission column

The fields in the permission column determine the user's permissions and are used to describe the operations that are allowed on data and databases globally.

Permissions are roughly divided into two categories, namely advanced management permissions and ordinary permissions:

• Advanced management permissions mainly manage the database, such as permissions to shut down services, super permissions, and load users;
• Ordinary permissions mainly operate the database, such as query permissions, modification permissions, etc.

The permission columns of the user table include Select_priv, Insert_priv and other fields ending with priv. The data type of these field values ​​is ENUM, and the possible values ​​are only Y and N: Y means that the user has the corresponding permission, and N means that the user does not have the corresponding permission. From a security perspective, the default values ​​of these fields are all N.

Table 2: Permission columns of the user table

If you want to modify permissions, you can use the GRANT statement to grant some permissions to the user, or you can set permissions by updating the user table through the UPDATE statement.

Security Column

The security column is mainly used to determine whether the user can log in successfully. The security column in the user table is shown in Table 3:

Table 3: Security columns of the user table

Note: Even if password_expired is "Y", the user can log in to MySQL using the password, but is not allowed to do anything.

Usually the standard distribution does not support SSL. Readers can use the SHOW VARIABLES LIKE "have_openssl" statement to check whether it has SSL function. If the value of have_openssl is DISABLED, SSL encryption is not supported.

Resource Control Column

The fields in the resource control column are used to limit the resources used by users. The resource control column in the user table is shown in Table 4.

Table 4: Resource control columns of the user table

The default value for the above fields is 0, which means there is no limit. If the number of user queries or connections exceeds the resource control limit within an hour, the user will be locked out and can only perform the corresponding operation here until the next hour. You can use the GRANT statement to update the values ​​of these fields.

This is the end of this article about the MySQL user permission table. For more information about the MySQL user permission table, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!