Where is the user table of mysql?

The user table is in the database named "mysql". MySQL will automatically create a database named "mysql" during installation. The mysql database stores user permission tables; the user table is the most important permission table in the mysql database and is used to record the accounts allowed to connect to the server. information. All permissions enabled in the user table are global and apply to all databases.

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

The user table is in the database named "mysql".

MySQL will automatically create a database named mysql during installation. 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, 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.

Fields in the user table

The fields in the user table can be roughly divided into There are 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.

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, 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 operations allowed on data and databases in the global scope.

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 Load users, etc.;
• Normal permissions mainly operate the database, such as query permissions, modify 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 means that the user has corresponding Permissions, N means that the user does not have corresponding permissions. For security reasons, the default value for these fields is N.

##Select_privenum('N','Y')NONWhether it is possible to query data through the SELECT commandInsert_privenum('N','Y')NONWhether data can be inserted through the INSERT commandUpdate_privenum('N','Y' )NONCan existing data be modified through the UPDATE command?Delete_privenum ('N','Y')NONCan you delete existing data through the DELETE command?## Create_privDrop_privReload_privShutdown_privProcess_privFile_priv Grant_privReferences_privIndex_privAlter_privShow_db_privSuper_privCreate_tmp_table_privLock_tables_privExecute_privRepl_slave_privRepl_client_priv Create_view_privShow_view_privCreate_routine_privAlter_routine_privCreate_user_privEvent_privTrigger_privCreate_tablespace_priv

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 with 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 2: Permission columns of user table

Field name	Field type	Is it empty	Default value	Description
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

##ssl_typeenum('','ANY','X509','SPECIFIED')NOSupport ssl standard encryption security fieldssl_cipherblobNOSupport ssl standard encryption security fieldx509_issuerblobNOSupport x509 standard fieldsx509_subjectblobNOSupport x509 Standard fieldspluginchar(64)NOmysql_native_passwordIntroducing plugins for user Password verification when connecting, plugin creates external/proxy userpassword_expiredenum('N','Y')NONWhether the password has expired (N has not expired, y has expired)password_last_changedtimestampYES Record the time when the password was last modifiedpassword_lifetimesmallint(5) unsignedYESSet the validity time of the password in daysaccount_lockedenum('N','Y')NONWhether the user is locked (Y locked, N unlocked)

Table 3: Security columns of user table

Field name	Field type	Whether it is empty	Default value	Description

Note: Even if password_expired If it is "Y", the user can also 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 of the resource control column are used to limit the resources used by users. The resource control columns in the user table are shown in Table 4.

Table 4: Resource control columns of the user tableField nameField typeYes Is emptyDefault valueDescriptionmax_questionsint(11) unsignedNO0Specifies the number of query operations allowed per hourmax_updatesint(11) unsigned NO0Specifies the number of update operations allowed per hourmax_connectionsint(11) unsignedNO0Specifies the number of connection operations allowed per hourmax_user_connectionsint(11) unsigned NO0Specifies the number of connections allowed to be established simultaneouslyThe default value of the above fields is 0. Indicates 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.

How to access the user table

1. Connect to the mysql database and view all default database information

2. Because the user table exists in the mysql database, you can switch the database through the use command.

After switching the database, you can view the database through the show command. For all data tables in , you can see the user data table at the end

You can view the structural information of the user table through the desc command, because The User table contains a lot of information, so there is also a lot of structural information

#When no other users are created by default, there is only super administrator information

[Related recommendations:

mysql video tutorial]

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