Detailed Explanation of MySQL User and Authority Management

The example of this paper describes the management of MySQL users and privileges. Share it for your reference, as follows:

Users connect to MySQL and can make various queries, which are all maintained by MySQL users and rights functions.

Users interact with the database server in 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 judge whether the user has the right to connect?

Basis:

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

These three pieces of user information are stored in the user table in the mysql library.

Modify the host domain so that IP can be connected

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

Modify user password

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

2. Do you have the right to perform this operation

In mysql, one library is the mysql library. In this library, there are three tables, one is the user table, and the user table stores the permission information of all users. One is the db table, and the db table stores the permission information of all users in the database layer. One is the tables_priv table, and the tables_priv table stores the permission information of all users in the surface layer.

User login, The user table first restricts user login, Secondly, the global authority of the user is saved. If the user does not have any authority, it will look up whether the user has the operation authority of a certain database from the db table. If there is no, it will look up whether the user has the operation authority of a certain table from the table_priv table. If there is, the user can operate the table according to the existing authority.

1) Global authorization and retraction

Global authorization format:

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

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

Authorization:

Create an lisi user, host is 192.168. 191.%, and the% wildcard character means that all hosts ending in 192.168. 191. xxx can connect, and the password is 12345678.

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

Withdraw authority:

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

2) Database-level authorization and retraction

Requirements: Let lisi users have all operation permissions of mysqlmaster database

Authorization:

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

Withdraw:

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

3) Table-level authorization and withdrawal

Requirements: Let lisi users have mysqlmaster database goods table insert, update, select 3 operation authority.

Authorization:

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

Withdraw:

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

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.