Detailed explanation of MySQL user and authority management

Time:2021-5-1

This paper introduces the management of MySQL users and permissions. The details are as follows:

When users connect to MySQL, they can do all kinds of queries, which are maintained by MySQL users and permissions.

The interaction between users and database server 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?

basis:

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

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

Modify the host domain so that IP can connect

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

Change user password

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

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 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 in the database layer. One is tables_ Priv tables_ Priv table stores the permission information of all users on the surface.

When a user logs in, the user table first restricts the user to log in, and then saves the global permissions of the user. If the user does not have any permissions, it will find out whether the user has the operation permissions of a database from the DB table. If not, it will search the database from the table_ Priv table to find whether the user has the operation permission of a table. If so, the user can operate the table according to the existing permission.

1) Global authorization and recall

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

to grant authorization:

Create a Lisi user. The host is 192.168.191.%. The% wildcard indicates that all hosts ending in 192.168.191.xxx can be connected. The password is 12345678.


grant all on *.* to [email protected]'192.168.191.%' identified by '12345678';

Recall permission:


revoke all on *.* from [email protected]'192.168.191.%';

2) Database level authorization and recall

Requirement: let Lisi users have all the operation permissions of mysqlmaster database

to grant authorization:


grant all on mysqlmaster.* to [email protected]'192.168.191.%' identified by '12345678';

take back:


revoke all on mysqlmaster.* from [email protected]'192.168.191.%';

3) Table level authorization and recall

Requirement: let Lisi users have the right to insert, update and select the goods table in mysqlmaster database.

to grant authorization:


grant insert,update,select on mysqlmaster.goods to [email protected]'192.168.191.%' identified by '12345678';

take back:


revoke insert,update,select on mysqlmaster.goods from [email protected]'192.168.191.%';

For more information about mysql, readers interested in it can see the topics of this website: complete collection of MySQL query skills, summary of MySQL transaction operation skills, complete collection of MySQL stored procedure skills, summary of MySQL database lock skills and summary of MySQL common functions

I hope this article will be helpful to your MySQL database design.

Recommended Today

AttributeError: ‘Settings’ object has no attribute ‘HBase’

Django custom configuration error: attributeerror: ‘Settings’ object has no attribute’ HBase ‘ If we need to useHbaseIf so, you can followMysqlSimilarly, write the relevant configuration information to thesettingsFile, but I encountered a problem, that is, I can’t import it. In [12]: settings.HBASE ————————————————————————— AttributeError Traceback (most recent call last) <ipython-input-12-22bd47148938> in <module> —-> 1 settings.HBASE […]