Shulou(Shulou.com)06/01 Report--

This paper gives an example 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 do a variety of queries, all of which are maintained by the MySQL user and permissions function.

The user exchanges data with the database server in two phases:

(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?

According to:

1) where are you from? Host

2) who are you? User

3) what is your password? Password

These three pieces of information about the user 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;-- Flushing permission

Modify a user's password

Mysql > update user set password=password ('1111111111') where xxx;mysql > flush privileges;-Flushing permission

2. Do you have the right to perform this operation

In mysql, there is a library called the mysql library, in which there are three tables, one is the user table, and the user table stores permission information for all users. One is the db table, and the db table stores the permission information of all users at the database layer. One is the tables_ private table, and the tables_priv table stores the permission information of all users on the surface.

If the user logs in, the user table can first restrict the user to log in, and then the global permissions of the user are saved. If the user does not have any permissions, then whether the user has the operation permission of a database will be found from the db table. If not, whether the user has the operation permission of a table will be found from the table_ table. If so, the user can operate the table according to the existing permissions.

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

Authorization:

Create a lisi user with a host of 192.168.191% Magi% wildcard indicating that all hosts at the end of the 192.168.191.xxx can connect with a password of 12345678.

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

Revoke the authority:

Revoke all on *. * from lisi@'192.168.191.%'

2) Database-level authorization and recall

Requirement: let lisi users have all the operation rights of mysqlmaster database

Authorization:

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

Recall:

Revoke all on mysqlmaster.* from lisi@'192.168.191.%'

3) Table-level authorization and withdrawal

Requirements: let lisi users have the authority to operate insert, update and select of goods table under mysqlmaster database.

Authorization:

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

Recall:

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

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.