Shulou(Shulou.com)05/31 Report--

This article mainly explains "does mysql have a user table?" the content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "does mysql have a user table"?

Mysql has a user table. The user table is a permission table in MySQL, which is used to record the account information allowed to connect to the server; 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 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, which is used to record account information that is allowed to connect to the server. It is important to note that all permissions enabled in the user table are global and apply to all databases.

The fields in the user table can be divided into four categories, namely, user column, permission column, security column and resource control column. The meaning of these fields is mainly described below.

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 version 5. 7 no longer uses Password as the password field, but instead changes it to authentication_string.

The users of MySQL version 5.7 are listed in Table 1.

Table 1:user table whether the user column field name field type is empty default value description Hostchar (60) NO no hostname Userchar (32) NO useless user name authentication_stringtextYES no password

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

Permission column

The fields of the permissions column determine the permissions of the user and are used to describe the operations allowed on data and databases at the global scope.

Permissions are roughly divided into two categories, namely, advanced administrative permissions and general permissions:

Advanced administrative permissions mainly manage the database, such as turning off service permissions, super permissions, loading users, etc.

Ordinary permissions mainly operate the database, such as query permissions, modify permissions and so on.

The permission column of the user table includes fields ending in priv, such as Select_priv and Insert_ priv. The data type of these field values is ENUM. Only Y and NInsert_ priv Y indicate that the user has the corresponding permissions, and N indicates that the user does not have the corresponding permissions. From a security perspective, the default values for these fields are N.

Whether the permission column field name type of the table 2:user is an empty default indicates whether Select_privenum ('naughty journal Y') NON can query the data Insert_privenum (' nasty journal Y') NON through the SELECT command whether the data can be inserted by the INSERT command Update_privenum ('nasty journal Y') NON can modify the existing data Delete_privenum (' N') through the UPDATE command Can'Y') NON delete existing data through the DELETE command whether Create_privenum ('naughty grammar Y') NON can create new databases and tables Drop_privenum (' nasty heroines Y') NON can delete existing databases and tables Reload_privenum ('naughty heroines Y') NON can execute specific commands to refresh and reload various internal caches used by MySQL Including logs, permissions, hosts, queries, and table Shutdown_privenum ('nasty parenting Y') NON whether the MySQL server can be shut down. When this permission is provided to any user outside the root account You should be very careful about whether Process_privenum ('nasty penalty Y') NON can use the SHOW PROCESSLIST command to check whether other users' processes File_privenum ('nasty dagger Y') NON can execute SELECT INTO OUTFILE and LOAD DATA INFILE command Grant_privenum (' nasty dagger Y') NON can re-grant its own permissions to other users References_privenum ('nasty pencil Y') NON can create foreign key constraints Index_privenum (' N') Whether the index can be added or deleted by'Y') NON to check whether Alter_privenum ('nasty grammar Y') NON can rename and modify the table structure Show_db_privenum (' nasty journal Y') NON can view the names of all databases on the server It includes whether the database Super_privenum ('nasty recently authorized Y') NON, which the user has sufficient access rights, can perform some powerful management functions, such as deleting the user process through the KILL command Use the SET GLOBAL command to modify the global MySQL variable and execute various commands about replication and logging. (Super permission) whether Create_tmp_table_privenum ('Nickel Magazine Y') NON can create temporary tables Lock_tables_privenum (' Noble Magazine Y') NON can use the LOCK TABLES command to block access to the table / modify whether Execute_privenum ('Nickelin') NON can execute the stored procedure Repl_slave_privenum ('N') Whether'Y') NON can read the binary log file Repl_client_privenum ('nasty database Y') NON can determine where the slave server and master server are replicated, Create_view_privenum (' nasty journal Y') NON can create a view Show_view_privenum ('nasty journal Y') NON can view the view Create_routine_privenum (' N' Can stored procedures and functions be changed or discarded by'Y') NON? can stored procedures and functions be modified or deleted by NON? can stored functions be modified or deleted by Create_user_privenum? can NON execute CREATE USER commands? This command is used to create a new MySQL account, Event_privenum ('naughty recording Y') NON can create, modify and delete events Trigger_privenum (' nasty recording Y') NON can create and delete triggers Create_tablespace_privenum ('nasty last minute Y') NON can create tablespaces

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

Safety column

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

Table 3:user Table Security column field name field type is empty default value description ssl_typeenum ('', 'ANY','X509','SPECIFIED') NO

Support for ssl standard encryption security field ssl_cipherblobNO

Support for ssl standard encryption security field x509_issuerblobNO

Support x509 standard field x509_subjectblobNO

Support x509 standard field pluginchar (64) NOmysql_native_password to introduce plugins for password authentication when users connect. Plugin creates external / proxy user password_expiredenum ('naughty department Y') NON password is expired (N has not expired, y has expired) password_last_changedtimestampYES

Record the time when the password was last modified password_lifetimesmallint (5) unsignedYES

Set the validity period of the password (in days) whether the NON user is locked (Y is locked, N is not locked). Account_lockedenum ('nasty minutes Y') NON user is locked.

Note: even if password_expired is "Y", the user can log in to MySQL with a password, but nothing is allowed.

In general, standard distributions do not support ssl, and readers can use the SHOW VARIABLES LIKE "have_openssl" statement to see if it has ssl functionality. If the value of have_openssl is DISABLED, then ssl encryption is not supported.

Resource control column

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

Table 4:user table resource control column field name type is empty default indicates that max_questionsint (11) unsignedNO0 specifies the number of operations allowed to execute queries per hour max_updatesint (11) unsignedNO0 specifies the number of operations allowed to perform updates per hour max_connectionsint (11) unsignedNO0 specifies the number of connection operations allowed per hour max_user_connectionsint (11) unsignedNO0 specifies the number of connections allowed to be established simultaneously

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 one hour, the user will be locked out and the corresponding operation can not be performed here until the next hour. You can update the values of these fields using the GRANT statement.

Thank you for your reading, the above is the content of "does mysql have a user table?" after the study of this article, I believe you have a deeper understanding of the problem that mysql has a user table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.