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

This article shows you what the implementation principle of access control in MySQL database is, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MySQL access control actually consists of two functional modules, one is the user management module responsible for "guarding the MySQL gate", and the other is the access control module responsible for monitoring every action of visitors. The user management module determines whether the user can log in to the database, while the access control module determines what can be done in the database. The following is a simple flowchart for implementing access control in MySQL.

1. User management

In MySQL, the implementation of user access control is relatively simple, all authorized users are stored in a system table: mysql.user, of course, this table not only stores the basic information of authorized users, but also stores some detailed permission information. The user management module needs to use very little information, mainly Host,User,Password, which are all in the mysql.user table.

two。 access control

When the client connection is verified by the user management module and can be connected to MySQL Server, a variety of Query and Command will be sent to MySQL Server to achieve various functions of the client application. When the MySQL receives the request from the client, the access control module needs to verify whether the user meets the permissions required by the submitted request. The permission verification process starts from the maximum range of permissions to the minimum range of permissions to verify each permission of each object involved in turn.

When verifying all the required permissions, MySQL will first look up the permission data stored in the memory structure, first look for Global Level permissions, if all the required permissions are defined in Global Level (GRANT or REVOKE), then complete the permission verification (pass or deny). If the definitions of all permissions are not found, it will continue to look for Database Level permissions later to verify the required permissions that are not defined by Global Level. If you still can't find all the required permission definitions, MySQL continues to look for a smaller domain of permission definitions, that is, Table Level, and finally Column Level or Routine Level.

3. Five authorization forms

The mysql database contains five main authorization tables.

User table: contains user accounts and global permissions columns. MySQL uses the user table to accept or reject connections from the host. The permissions granted in the user table are valid for all databases on the MySQL server.

Db table: contains database-level permissions. MySQL uses database tables to determine which database and which host the user can access. The privileges granted at the database level in the db table apply to the database, where all objects belong, such as tables, triggers, views, stored procedures, and so on.

Table_priv and columns_ private tables: contains table-level and column-level permissions. The permissions granted in the table_ private table apply to the table and its columns, while the permissions granted in the columns_ private table apply only to specific columns of the table.

Procs_ private table: contains permissions for stored functions and stored procedures.

4. Reference access Authorization Policy

Here is a copy of my usual mysql database authorization strategy for reference only.

/ / for root account set global validate_password_policy=0;-set criteria for judging password based on password length (validate_password_length) grant all privilges on *. * to root@'localhost' identified by 'password'; grant all privilges on *. * to root@'%' identified by' password';-decide whether to open / / for daily operation and maintenance accounts grant select, insert, update, delete on database_name.* to hwb@'%' identified by 'password' according to the specific circumstances. Grant create,alter,drop,references on database_name.* to hwb@'%'; grant create temporary tables on database_name.* to hwb@'%'; grant index on database_name.* to hwb@'%'; grant create view on database_name.* to hwb@'%'; grant show view on database_name.* to hwb@'%'; grant create routine on database_name.* to hwb@'%' -- View stored procedures, function status grant alter routine on database_name.* to hwb@'%';-- delete stored procedures, functions grant execute on database_name.* to hwb@'%'; grant all privileges on mysql.* to hwb@'%' identified by 'password' -- do not operate on other users' functions or stored procedures without adding / / connect to the application account grant all privileges on database_name.* to hwb2@' application server IP' identified by 'password'; grant all privileges on mysql.* to hwb2@' application server IP' -- cannot operate on other users' functions or stored procedures without adding / / for read-only accounts (export database permissions) grant select on database_name.* to hwbread@'%' identified by 'hwbread123'; grant select on mysql.* to hwbread@'%'; grant show view on database_name.* to hwbread@'%'; grant file on *. * to hwbread@'%' -- if database_name.* will ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES what is the principle of access control in MySQL database? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.