Detailed explanation of MySQL user and permission management

This article uses examples to describe the management of MySQL users and permissions. Share with you for your reference, the details are as follows:

Users connect to MySQL and can perform various queries. These are all maintained by the MySQL user and permission functions behind the scenes.

The data exchange between users and database servers is divided into two stages:

(1) Do you have the right to connect? (2) Do you have the right to perform this operation?

1. Do you have the right to connect?

How does the server determine whether the user has the right to connect?

in accordance with:

1) Where are you from? host
2) Who are you? user
3) What is your password? password

These three information of users are stored in the user table in the MySQL database.

Modify the host domain so that the IP can connect

mysql>update user set host='192.168.137.123' where user = 'root';
mysql>flush privileges; -- flush privileges

Change User Password

mysql>update user set password=password('11111111') where xxx;
mysql>flush privileges; -- flush privileges

2. Do you have the right to perform this operation?

In MySQL, there is a library called MySQL library. In this library, there are three tables, one of which is the user table, which stores the permission information of all users. One is the db table, which stores the permission information of all users at the database layer. One is the tables_priv table, which stores the permission information of all users at the table level.

When a user logs in, the user table can first restrict user logins, and secondly, it also saves the user's global permissions. If the user does not have any permissions, the db table will be used to check whether the user has permission to operate a certain database. If neither of them exists, the table_priv table will be used to check whether the user has permission to operate a certain table. If yes, the user can operate the table according to the existing permissions.

1) Global authorization and revocation

Global authorization format:

grant [permission 1, permission 2, permission 3] on *.* to user@'host' identified by 'password'

Common permissions: all, create, drop, select, insert, delete, update

Authorization:

Create user lisi, set the host to 192.168.191.%, and the % wildcard indicates that any host ending with 192.168.191.xxx can connect. The password is 12345678.

grant all on *.* to lisi@'192.168.191.%' identified by '12345678';

Reclaim permissions:

revoke all on *.* from lisi@'192.168.191.%';

2) Database-level authorization and revocation

Requirement: Let the lisi user have all operation permissions of the mysqlmaster database

Authorization:

grant all on mysqlmaster.* to lisi@'192.168.191.%' identified by '12345678';

Take Back:

revoke all on mysqlmaster.* from lisi@'192.168.191.%';

3) Table-level authorization and revocation

Requirement: Let user lisi have the permissions for insert, update, and select operations on the goods table in the mysqlmaster database.

Authorization:

grant insert,update,select on mysqlmaster.goods to lisi@'192.168.191.%' identified by '12345678';

Take Back:

revoke insert,update,select on mysqlmaster.goods from lisi@'192.168.191.%';

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.