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

This article shares with you the content of a sample analysis of MySQL user rights authentication and management methods. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Mysql permissions are verified in two stages.

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

two。 Check whether each request has permission to implement.

2. Mysql permission list

Permission level permission description create database, table or index create database, table or index permission drop database or table delete database or table permission grant option database, table or saved program gives permission option references database or table foreign key permission alter table to change table Such as adding fields, indexes, Modify fields, etc. Delete table delete data permissions index table index permissions insert table insert permissions select query permissions update update permissions create view view create view permissions view view permissions alter routine stored procedure change stored procedure permissions create routine stored procedure creation stored procedure permissions execute stored procedure permissions file access permissions on the file server host create temporary tables server Server management create temporary table permissions lock tables server management lock table permissions create user server management create user rights proccess server management view process permissions reload server management execute flush-hosts Permissions of flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload, etc. Replication client server management replication permissions replication slave server management replication permissions show databases server management view database permissions shutdown server management turn off database permissions super server management executes kill thread permissions

3. Mysql user rights management operation

1. Permission query:

(1) View all users of mysql and their permissions:

Select * from mysql.user\ G

(formatted display)

(2) View the current mysql user permissions:

Show grants

(3) View the permissions of a user:

Show grants for user name @ host

Example:

Show grants for root@localhost

2. Mysql user creation:

Method 1: use the create user command to create.

Create user 'username' @ 'host' identified by 'password'

Example:

Create user 'wjt'@'localhost' identified by' wujuntian'

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

Example:

The copy code is as follows:

Insert into mysql.user set user='wujuntian',host='localhost',password=password ('123123')

Note:

Use method 2 be sure to remember to perform flush privileges refresh permissions. Second, after mysql5.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. Delete Mysql users:

Drop user 'username' @ 'host'

4. Mysql user permission is granted:

The newly created user does not have permissions by default, and you need to use the grant directive to grant permissions.

The full format of the grant instruction:

Grant permission list on database name. Datasheet name to 'username' @ '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 data tables, and "%" to indicate any host address.

You can use grant to add permissions to users repeatedly for superimposition of permissions.

With grant option: this option means that the user can delegate the permissions he has to others.

Remember to refresh permissions after authorization:

Flush privileges

5. Mysql user rights recovery:

Revoke instruction format:

Revoke permission list on database name. Datasheet name from username @ host

Example:

Revoke select on test.user from wjt@localhost

Note:

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 execution permission is added.

Usage rights cannot be reclaimed, that is, the REVOKE user rights cannot delete users.

6. Rename the account:

Rename user 'old user name' @ 'old host' to 'new user name' @ 'new host'

Example:

Rename user 'wujuntian'@'localhost' to' ajun'@'localhost'

7. Mysql user password modification:

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:

The copy code is as follows:

Update mysql.user set password=password ('123123') where user='root' and host='localhost'

Note:

This method must execute the "flush privileges;" directive to refresh permissions, otherwise the password change will not take effect. Mysql5.7 should change "password" to "authentication_string" in the future.

Method 3: use the grant instruction to change the password during authorization:

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

Example:

The copy code is as follows:

Grant select on test.user to ajun@localhost identified by '111111' with grant option

Method 4: run the mysqladmin script file.

This file is typically located in the bin directory under the mysql installation directory. Enter the directory and enter commands based on the following two specific situations (only root users have this permission).

(1) the user does not have a password:

Mysqladmin-u username password new password

(2) the user already has a password:

Mysqladmin-u username-p password new password

(enter will prompt for the old password, which can be modified successfully after entering. )

Note:

Be sure to use the PASSWORD function when changing your password (mysqladmin and GRANT are not written and will be added automatically).

8. Forget your password and log in to mysql:

Method 1:

Stop the running Mysql service first, 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 for the Linux system to run the mysqld_safe file):

Mysqld-skip-grant-tables

This allows you to skip Mysql's access control and enter the mysql database as an administrator on the console. In addition, open a command line window, enter the bin directory under the mysql installation directory, directly enter: mysql, enter, you can log in to mysql, and then you can reset the password (note: at this time, "Mysql user password change" in the four methods can only be used by the second method! ). Exit after successful setup and restart the Mysql service.

Method 2: modify the mysql configuration file my.ini.

In fact, the principle and method are the same, using the skip-grant-tables parameter provided by Mysql to skip the access control of Mysql. Open the mysql configuration file my.ini, add "skip-grant-tables" under'[mysqld]', save and 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 a data table of permission information for all Mysql users. When Mysql starts, all the contents of the permission table are read into memory, and the contents in memory are directly used to determine the permissions. Changes made to the permission table with grant, revoke, or set password will be immediately noticed by the server, and the essence of the GRANT operation is to refresh permissions after modifying the permission table. However, if you manually modify the permission table, such as using insert, update, delete, and so on, you should execute a flush privileges command, which will cause the server to re-read the contents of the permission table into memory, so that the change will take effect. If the command is not executed, the mysql service must be restarted for it to take effect. Therefore, it is best to use grant, revoke or set password to operate on the permission table. You can save the trouble of executing the flush privileges command, and you will freak out if you forget to execute this command.

Not only that, it is best to use drop user and rename user commands to delete and rename users, rather than delete and update commands. The former will not only operate on the mysql.user data table, but also update the records of other permission tables, while the latter will only operate on the data of the mysql.user table, which will lead to a lot of problems, because the user's permission information not only exists in the mysql.user table. For example, if you use delete to delete a user in the mysql.user table, but do not manipulate other permission data tables, then other permission data tables such as tables_priv still exist. Next time, if you want to use the create user command to create a user with the same name, you 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 permissions data tables first. Using the update command to rename a user can also be a big problem. After renaming, the user loses a lot of permissions, while the record of the original user name in other permission tables becomes a useless record, unless you update every permission table in the same way, but this is troublesome. So, use drop user, rename user, a command can let the system automatically help you do everything, why not do it!

Mysql permission check:

Mysql first checks whether it has permission for a large area, and if it does not go to a small area to check. For example, first check whether you have select permission on the database, and if so, allow it to be executed. If not, check whether you have select permission on the table until the finest granularity and no permission, then refuse to execute. Therefore, the finer the granularity control, the more steps for permission verification, and the worse the performance.

Thank you for reading! This is the end of this article on "sample Analysis of MySQL user Rights Verification and Management methods". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.