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

This article mainly introduces "the brief introduction of MySQL access control". In the daily operation, I believe that many people have doubts about the brief introduction of MySQL access control. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "brief introduction of MySQL access control". Next, please follow the editor to study!

Background: MySQL-5.7.11-GA

A brief introduction to 1.MySQL access Control

two。 Demand

3. Several problems to be solved in implementation

4. Design of the preliminary scheme

5. The idea of improving the scheme

A brief introduction to 1.MySQL access Control

In terms of scope, the permission control of MySQL can be divided into three levels: MySQL level, DB level and object level.

The MySQL level refers to operations that have an impact on the MySQL instance itself, including but not limited to modifying parameters of MySQL, such as buffer_pool_size The obvious feature of shutting down / starting MySQL, etc., is that the MySQL-level operation permission does not specify a specific database (also means that it is effective for the database in any MySQL instance, similar to the meaning of global permission), (special case: create a database, although the database name is specified, but the database itself does not exist, so it can be regarded as a MySQL-level permission)

DB level refers to the operations for existing specific databases, including modifying the character set of the database, creating or deleting specific existing databases, etc. The obvious feature is that the operation permissions at DB level are limited to specific databases.

Object-level operations mainly affect objects within a database, such as tables, indexes, stored procedures, etc.

Mutual influence of permissions between different levels: using intersection, that is, MySQL level + DB level + object level

It can be concluded that there are two characteristics of MySQL permission setting.

When setting DB-level and object-level permissions, you must specify specific DB and objects. If the DB and objects are unknown, you can only rely on MySQL-level permissions to set global permissions, which are effective for existing / newly created DB and objects.

If permissions are set at a high level, the intersection of all levels of permissions will be taken. For example, if create,insert on *. * (MySQL level) is set, in create on testdb.* (DB level), insert prohibited at MySQL level (global permissions) will not take effect, and insert data can still be transferred to the testdb table. However, when create,insert on testdb.* (DB level) and create on *. * (MySQL level) are set, users can perform insert operations on testdb, which means that at a high level, setting "No insert permission" does not override the "insert permission" granted separately at a low level.

As shown in the figure:

Implementation of permission control in MySQL: specific permission settings are stored in some tables of the MySQL system library, and MySQL-level settings exist in the user table. When a client tries to connect to a MySQL instance, it will first check the permissions in this table (including account name, password, HOST verification), and then connect to the MySQL instance.

Other levels of settings are saved in tables such as db,tables_priv,column_priv, and the details can be viewed in the database.

two。 Demand

Want to have a "admin" role with all the permissions of root, except to modify the global settings and master-slave settings of MySQL.

3. Several problems to be solved in implementation

The first thing that comes to mind is: grant all on *. * to admin@'%' with grant option, and then revoke super on *. * from admin@'%', but here comes the problem. Under this setting, admin can directly update mysql.user the table, and then manually add super permissions.

And if you want to be able to create a new DB and give new users various permissions for the new DB, you need at least the permission to add, delete, modify and query the Create+, and the object is global (on *. *), then admin can also modify the mysql.user table.

4. Design of the preliminary scheme

Use the shell script to add sudo mysql-u-p-e to create a new DB, create a new user, authorize, etc., and then prohibit the system users from manipulating the cat,vim,vi and other text of the script.

There are also many problems with this scheme: other users can log in to the DB Server; script and the root password saved in the root script may be obtained in ways they are not familiar with; etc...

5. The idea of improving the scheme

It would be nice to have a web, which can block all the details and operations on the server side, and you can do it by clicking on the page _ (: checking "∠) _

At this point, the study of "A brief introduction to MySQL access Control" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.