Shulou(Shulou.com)06/01 Report--

Preface

In MySQL 5.7.24, the relevant information about users and user permissions is stored in the user table in the mysql library. The user table can be roughly divided into user columns, permissions columns, security columns, and resource control columns.

1. User column

The user columns of the user table include Host, User, and password, which represent the hostname, user name, and password, respectively. Where user and Host are the joint hosts of the user table. When a connection is established between the user and the server, the user name, host name and password entered in the account information must match the corresponding fields in the user table. Only when all three values match, the connection is allowed. The values of these three fields are the account information that was saved when the account was created. When you change a user's password, you actually change the value of the password field in the user table.

2. Permission column

The fields of the permissions column determine the permissions of the user and describe the operations that are allowed on data and databases at the global scope. It includes general permissions such as query permissions and modify permissions, as well as advanced permissions such as shutting down the server, super permissions, and loading users. Normal permissions are used to manipulate the database; advanced permissions are used for database management. The corresponding permissions in the user table are for all user databases. The type of these field values is ENUM, and the only values you can take are Y and N, where Y means the user has the corresponding permissions; N means the user does not have the corresponding permissions. Looking at the structure of the user table, you can see that the values of these fields are N by default. If you want to modify permissions, you can use GRANT statements or UPDATE statements to change these fields in the user table to modify the corresponding permissions for the user.

3. Security column

The security column has only six fields, of which two are SSI-related, two are x509-related, and the other two are authorized plug-ins. SSI is used for encryption; the X509 standard can be used to identify the user: the Plugin field identifies the plug-in that can be used to authenticate the user, and if the field is blank, the server uses the built-in authorization authentication mechanism to authenticate the user. You can use the SHOW VARIABLES LIKE 'have_openssl' statement to query whether the server supports the SSI feature.

4. Resource control column

The fields of the resource control column are used to limit the resources used by users, and contain four fields, which are:

1) the number of query operations allowed by Max_questions- users per hour.

2) Max_ updates-the number of update operations allowed by the user per hour.

3) the number of connection operations allowed by Max_connections- users per hour.

4) the number of connections allowed by Max_user_connections- users at the same time.

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 use the Grant statement to update the values of these fields.

Note: if the newly created user cannot log in to the database, you can try to refresh the permissions without permission errors. The command is as follows:

Mysql > flush privileges

When using grant to authorize a user, you can use the following instructions to see which permissions can be granted to the user:

Mysql > SHOW PRIVILEGES; 1, create user 1, use create user statement to create new user 1) method 1:mysql > create user 'tom'@'localhost' identified by' 123.com'

The above statement creates a tom user that only allows local login and the password is 123.com.

However, in the above way, the password is written in clear text (although it is stored in ciphertext), but it is not safe. The above instructions will generate the information of the user tom in the mysq.user table, as follows:

Mysql > select * from user where User='tom'\ G * * 1. Row * * Host: localhost User: tom Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: NCreate_ user_priv: N Event_priv: N Trigger_priv: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 Plugin: mysql_native_password authentication_string: * AC241830FFDDC8943AB31CBD47D758E79F7953EA password_expired: n password_last_changed: 2019-12-27 11:45:25 password_lifetime: NULL account_locked: N1 row in set (2019 sec) 2) method 2:mysql > select password ('123.com') +-- + | password ('123.com') | +-- + | * AC241830FFDDC8943AB31CBD47D758E79F7953EA | +- -- + 1 row in set 1 warning (0.00 sec) mysql > create user 'zhangyi'@'localhost'-> identified by password' * AC241830FFDDC8943AB31CBD47D758E79F7953EA' Query OK, 0 rows affected, 1 warning (0.00 sec)

The above command is to encrypt the 123.com string first, and then to create a user, you can write the encrypted password directly, but you need to add the password keyword.

After creation, zhangyi users can log in to the database locally using the password 123.com.

2. Use the Grant statement to create a new user mysql > grant select on test01.* to 'zhanger'@'localhost' identifiedd by' 123.com'

While creating the user zhanger, the above directive specifies that it only allows local login with a password of 123.com, and that the user has select permission for all tables in the test01 library.

Verify:

[root@mysql ~] # mysql-uzhanger-p123.com mysql > show databases; +-+ | Database | +-+ | information_schema | | test01 | +-+ 2 rows in set (0.00 sec) mysql > use test01; mysql > show tables +-+ | Tables_in_test01 | +-+ | bank | | student | +-+ 2 rows in set (0.00 sec) mysql > select * from bank +-+-+ | name | money | +-+-+ | lu | 1000 | | qi | 5000 | +-+-+ 2 rows in set (0.00 sec) mysql > update bank set money=600 where name='lu' ERROR 1142 (42000): UPDATE command denied to user 'zhanger'@'localhost' for table' bank' II, delete user 1, delete mysql > drop user zhangyi@localhost;2 using DROP USER statement, delete user mysql > delete from mysql.user where Host='localhost' and user='tom' using delete statement 3. Change the user password 1. Change the user password [root@mysql ~] # mysqladmin-uroot-p password '2008.com' Enter password: [root@mysql ~] # mysql-uroot-p123.com mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user' root'@'localhost' (using password: YES) [root@mysql ~] # mysql-uroot-p2008.com 2, Modify the user table mysql > update mysql.user set authentication_string=password ("123.com") where User= "root" and Host= "localhost" of MySQL database Mysql > flush privileges;3, use set statement to modify current user's password mysql > set password=password ('2019.com'); 4. Root user modify ordinary user password 1) use set statement to modify ordinary user mysql > set password for' liss'@'localhost'=password ('123456'); 2) use update statement to modify ordinary user mysql > update mysql.user set authentication_string=password ("123.com")-> where User='liss' and host='localhost';mysql > flush PRIVILEGES 3) use the Grant statement to modify the ordinary user password mysql > grant select on *. * to 'liss'@'localhost' identified by' 304.composition5. Ordinary users change their own passwords [root@mysql ~] # mysql-uliss-p304.com mysql > set password=password ("lvjianzhao"). 6. Revoke the permissions of ordinary users.

If you have written down how to authorize the user, here you will write down how to revoke the user's existing permissions. As follows:

Mysql > show grants for liss@localhost\ G mysql > revoke select on *. * from 'liss'@'localhost'; mysql > flush PRIVILEGES

-this is the end of this article. Thank you for reading-

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.