Mysql has a user table. The user table is a permission table in MySQL, used to record account information that is allowed to connect to the server; the fields in the user table can be roughly divided into four categories, namely user columns, permission columns, security columns and resource control columns.
The operating environment of this tutorial: windows7 system, mysql5.7 version, Dell G3 computer.
mysql has a user table.
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 following mainly introduces the meaning of these fields.
User column
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.
The user list for MySQL version 5.7 is shown in Table 1.
| 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, the MySQL database system will only allow him or her to log in 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 actually modify the value of the authentication_string field of 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 permissions on data and databases that are allowed globally. operate.
Permissions are roughly divided into two categories, namely advanced management permissions and ordinary permissions:
Advanced management permissions mainly manage the database, such as the permission to close services, Super permissions and loading users, etc.;
Normal 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. The only possible values are Y and N: Y represents the user There are corresponding permissions, N means that the user does not have the corresponding permissions. For security reasons, the default value for these fields is N.
| Field name | Field type | Whether it is empty | Default value | Description |
|---|---|---|---|---|
| enum('N','Y') | NO | N | Whether it is possible to query data through the SELECT command | |
| enum('N','Y') | NO | N | Whether data can be inserted through the INSERT command | |
| enum('N','Y' ) | NO | N | Can existing data be modified through the UPDATE command? | |
| enum ('N','Y') | NO | N | Can you delete existing data through the DELETE command? | |
| enum('N','Y') | NO | N | Can create new databases and tables | |
| enum('N','Y') | NO | N | Can I delete an existing database? and table | |
| enum('N','Y') | NO | N | Is it possible to execute specific commands that refresh and reload the various internal caches used by MySQL, including logs, permissions, hosts, queries, and tables | |
| enum( 'N','Y') | NO | N | Whether it is possible to shut down the MySQL server. Extreme caution should be exercised when providing this privilege to any user other than the root account | |
| enum('N','Y') | NO | N | Can I check the processes of other users through the SHOW PROCESSLIST command? | |
| enum(' N','Y') | NO | N | Whether it is possible to execute the SELECT INTO OUTFILE and LOAD DATA INFILE commands | |
| enum('N','Y') | NO | N | Can I grant my permissions to other users? | |
| enum('N','Y') | NO | N | Can create external Key constraints | |
| enum('N','Y') | NO | N | Whether it is possible to perform addition and deletion checks on the index | |
| enum('N','Y') | NO | N | Can the table structure be renamed and modified | |
| enum('N','Y') | NO | N | Is it possible to view the names of all databases on the server, including databases to which the user has sufficient access rights | |
| enum('N','Y') | NO | N | Whether it is possible to perform some powerful management functions, such as deleting user processes through the KILL command; use The SET GLOBAL command modifies global MySQL variables and executes various commands regarding replication and logging. (Super permission) | |
| enum('N','Y') | NO | N | Is it possible to create a temporary table | |
| enum('N','Y') | NO | N | Is it possible to use the LOCK TABLES command to prevent access/modification of tables | |
| enum('N','Y') | NO | N | Can the stored procedure be executed? | |
| enum('N',' Y') | NO | N | Is it possible to read the binary log file used to maintain a replicated database environment | |
| enum('N','Y') | NO | N | Is it possible to determine the location of the replication slave server and master server | |
| enum('N','Y') | NO | N | Can a view be created? | |
| enum('N','Y') | NO | N | Yes Can view view | |
| enum('N','Y') | NO | N | Whether stored procedures and functions can be changed or discarded | |
| enum('N','Y') | NO | N | Whether stored functions and functions can be modified or deleted | |
| enum('N','Y') | NO | N | Whether it is possible to execute the CREATE USER command, which is used to create a new MySQL account | |
| enum('N','Y') | NO | #N | Whether events can be created, modified and deleted | |
| enum('N','Y') | NO | N | Whether triggers can be created and deleted | |
| enum('N','Y') | NO | N | Can the table be created? space |
| Field name | Field type | Is it empty | Default value | Description |
|---|---|---|---|---|
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | Support ssl standard encryption security field | |
| ssl_cipher | blob | NO | Support ssl standard encryption security field | |
| x509_issuer | blob | NO | Support x509 standard fields | |
| x509_subject | blob | NO | Support x509 standard fields | |
| plugin | char(64) | NO | mysql_native_password | Introduce plugins To perform password verification when users connect, the plugin creates external/proxy users |
| password_expired | enum('N','Y') | NO | N | Whether the password has expired (N has not expired, y has expired) |
| password_last_changed | timestamp | YES | Record the time when the password was last modified | |
| password_lifetime | smallint(5) unsigned | YES | Set the validity time of the password in days | |
| account_locked | enum('N','Y ') | NO | N | Whether the user is locked (Y locked, N unlocked) |
Note: Even if password_expired is "Y", the user can still use the password to log in to MySQL, but no operations are allowed.
Usually standard distributions do not support ssl. Readers can use the SHOW VARIABLES LIKE "have_openssl" statement to check whether it has ssl functionality. If the value of have_openssl is DISABLED, the ssl encryption feature 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 columns in the user table are as shown in Table 4 shown.
| Field name | Field type | Is it empty | Default value | Description |
|---|---|---|---|---|
| max_questions | int(11) unsigned | NO | 0 | Specifies the number of query operations allowed per hour |
| max_updates | int(11) unsigned | #NO | 0 | Specifies the number of update operations allowed per hour |
| max_connections | int(11) unsigned | NO | 0 | Specifies the number of connection operations allowed per hour |
| max_user_connections | int(11) unsigned | NO | 0 | Specifies the number of connections allowed to be established simultaneously |
The default value of the above fields is 0, indicating no limit . If the number of user queries or connections exceeds the resource control limit within an hour, the user will be locked and cannot perform corresponding operations here until the next hour. The values of these fields can be updated using the GRANT statement.
[Related recommendations: mysql video tutorial]
The above is the detailed content of Does mysql have a user table?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
