Detailed explanation of MySQL user rights verification and management methods

This article uses examples to illustrate how to verify and manage MySQL user permissions. Share with you for your reference, the details are as follows:

1. Mysql permissions are verified in two stages

1. The server checks whether the connection is allowed: username, password, host address.

2. Check whether each request has the authority to implement.

2. Mysql permission list

3. Mysql user rights management operations

1. Permission query:

(1) View all mysql users and their permissions:

select * from mysql.user\G;

(Format display)

(2) View the current MySQL user permissions:

show grants;

(3) View a user's permissions:

show grants for username@host;

Example:

show grants for root@localhost;

2. Create a MySQL user:

Method 1: Use the create user command to create an account.

create user 'username'@'host' identified by 'password';

Example:

create user 'wjt'@'localhost' identified by 'wujuntian';

Method 2: Insert a user record directly into the mysql.user table.

Example:

Notice:

When using method 2, be sure to execute flush privileges to refresh permissions. Secondly, after MySQL 5.7, the password field of the mysql.user table has been replaced by authentication_string, so "password" should be changed to "authentication_string", and the password must be encrypted using the password function.

3. MySQL user deletion:

drop user 'username'@'host';

4. Grant Mysql user permissions:

The newly created user has no permissions by default and needs to be granted permissions using the grant command.

The full format of the grant instruction is:

Grant permission list on database name. table name to 'user name'@'host' identified by 'password' with grant option;

Example:

grant all privileges on *.* to 'wjt'@'localhost' identified by "wujuntian" with grant option;

You can use "*" to represent all databases or all tables, and "%" to represent any host address.

You can use grant to repeatedly add permissions to users to stack permissions.

with grant option : This option means that the user can grant the permissions he or she has to others.

Remember to refresh permissions after authorization:

flush privileges;

5. Reclaim Mysql user rights:

The format of the revoke instruction is:

revoke permission list on database name.data table name from username@host;

Example:

revoke select on test.user from wjt@localhost;

Notice:

In fact, when the GRANT statement is executed, if the target account does not exist in the permission table, the account is created; if it already exists, the permission is added.

The usage permission cannot be revoked, that is, REVOKE user permissions cannot delete the user.

6. Rename the account:

rename user 'old username'@'old host' to 'new username'@'new host';

Example:

rename user 'wujuntian'@'localhost' to 'ajun'@'localhost';

7. Change the MySQL user password:

Method 1: Use the set password command.

set password for 'username'@'host' = password('new password');

Example:

set password for 'root'@'localhost' = password('123456');

Method 2: Modify the password (or authentication_string) field in the mysql.user table.

Example:

Notice:

This method requires you to execute the "flush privileges;" command to refresh the privileges, otherwise the password change will not take effect. After MySQL 5.7, "password" should be changed to "authentication_string".

Method 3: Use the grant command to change the password during authorization:

grant select on database name.table name to username@host identified by 'new password' with grant option;

Example:

Method 4: Run the mysqladmin script file.

This file is generally in the bin directory under the MySQL installation directory. Enter the directory and enter commands according to the following two specific situations (only the root user has this permission).

(1) The user does not have a password:

mysqladmin -u username password new password;

(2) User already has a password:

mysqladmin -u username -p password new password;

(After pressing Enter, you will be prompted to enter the old password. Enter it and the change will be successful.)

Notice:

When changing the password, you must use the PASSWORD function (you don't need to write it for mysqladmin and GRANT, as it will be added automatically).

8. Forgot password to log in to mysql:

Method 1:

First stop the running Mysql service, enter the bin directory under the mysql installation directory in the command line window, and run the mysqld file under the -skip-grant-tables parameter (it is safer to run the mysqld_safe file on Linux system):

mysqld --skip-grant-tables

This allows you to skip Mysql's access control and enter the MySQL database as an administrator in the console. In addition, open another command line window, enter the bin directory under the MySQL installation directory, directly enter: mysql, press Enter, you can log in to MySQL, and then you can reset the password (Note: at this time, only the second method of the four methods in "Mysql user password modification" can be used!). After the setting is successful, exit and restart the Mysql service.

Method 2: Modify the MySQL configuration file my.ini.

In fact, the principle is the same as method 1, both use the --skip-grant-tables parameter provided by MySQL to skip MySQL access control. Open the MySQL configuration file my.ini, add "skip-grant-tables" under '[mysqld]', save, restart the MySQL service, and then you can log in to MySQL without a password to change the password.

The "mysql" database in Mysql stores the permission information data table of all Mysql users. When MySQL is started, all permission table contents are read into memory, and the contents in memory are used directly for permission judgment. Modifications made to the permission table using grant, revoke, or set password are immediately noticed by the server. The essence of the GRANT operation is to refresh permissions after modifying the permission table. However, if you modify the permission table manually, such as using insert, update, delete, etc. to operate the permission table, you should execute a flush privileges command, which will cause the server to re-read the permission table contents into memory so that the changes can take effect. If you do not execute this command, you must restart the MySQL service for it to take effect. Therefore, it is best to use grant, revoke, or set password to operate the permission table, which can save the trouble of executing the flush privileges command, and if you forget to execute this command, you will be very crazy. . .

Not only that, to delete or rename users, it is best to use the drop user or rename user commands respectively, rather than the delete or update commands. The former will not only operate on the mysql.user data table, but will also update records in other permission tables, while the latter will only operate on the data in the mysql.user table. This will cause many problems because the user's permission information does not only exist in the mysql.user table. For example, if you use delete to delete a user in the mysql.user table, but do not operate other permission data tables, then the permission records about the user in other permission data tables such as tables_priv will still exist. The next time you want to use the create user command to create a user with the same name, it will fail. You can only use the insert into command to insert records into the mysql.user table, or delete the records related to the user name in other permission data tables first. Using the update command to rename a user can also cause big problems. After the rename, the user loses many permissions, and the records of the original user name in other permission tables become useless records, unless you perform the same update operation on each permission table, which is very troublesome. So, use drop user and rename user. One command can let the system automatically complete everything for you. Why not do it!

Mysql permission check:

MySQL first checks whether it has permission for the larger scope, and if not, it then checks for the smaller scope. For example: first check whether there is select permission on this database. If so, allow execution. If not, check whether there is select permission on the table, all the way to the finest granularity. If there is no permission, execution is refused. Therefore, the finer the granularity of control, the more steps there are in permission verification and the worse the performance.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.