MySQL access control implementation principle

MySQL access control implementation principle

MySQL access control is actually composed of two functional modules, from the second chapter of the first part of the structure can be seen
, one is the user Management module that is responsible for "guarding MySQL gates" and the other is responsible for monitoring the visitors ' every move
The access control module. The user Management module determines whether a visitor can enter the door, and the access control module determines each guest
There's nothing you can get out of the door. Here is a simple flowchart for implementing access control in MySQL (see Figure 4-2):

1. User Management
Let's start by looking at how the user Management module works. In MySQL, the implementation of the User Access Control section is relatively simple
Single, all authorized users are stored in a system table: Mysql.user, of course, this table is not just for authorized users
The basic information, but also contains a partial refinement of the permission information. The user management module needs to use very little information, mainly
Host,user,password these three items, all in the Mysql.user table, are as follows:
Sky@localhost: (none) 12:35:04> use MySQL;
Database changed
Sky@localhost:mysql 12:35:08> DESC User;
+---------------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------------+------+-----+---------+-------+
| Host | char (60) | NO | PRI | | |
| User | char (16) | NO | PRI | | |
| Password | char (41) | NO | | | |
... ...
+---------------+--------------------+------+-----+---------+-------+
If a user wants to access MySQL, at least three of the data listed above is required for MySQL to determine if
It's time for him to "get in". These three items are actually part of the volume: the host name of the visitor's source (or the host IP address information)
And the visitor's visit "Password" (login username and login pin), either of these two parts is not able to match on
Could not allow the user management module of the guard door to open the door. Where the Host information is stored by the MySQL allow the corresponding
The User's trusted host, which can be a specific hostname (for example: mytest) or a domain name (e.g. www.domain.com),
It can also be a collection of domain names (for example:%.domain.com) with a "%" that acts as a wildcard, or it can be a specific
An IP address (for example, 1.2.3.4) can also be a collection of domain names with wildcards (for example: 1.2.3.%) can also use "%"
To represent any host, is not to make any restrictions on the visitor's host. such as the following settings:
Root@localhost:mysql 01:18:12> SELECT Host,user,password from the user ORDER by
User
+--------------------+------+-------------------------------------------+
| Host | user | password |
+--------------------+------+-------------------------------------------+
| % | ABC | |
| *.jianzhaoyang.com | ABC | |
| localhost | ABC | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 |
| 1.2.3.4 | ABC | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 |
| 1.2.3.* | def | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 |
| % | def | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 |
| localhost | def | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 |
... ...
+--------------------+------+-------------------------------------------+
But there's a special access restriction, and if you want to access it through localhost, you have to have a
Authorization information specifically for localhost, even if it is not restricted to any host. As shown in the following example, there is a def@%
User settings, but if you do not use the-H parameter to access it, the login is rejected because MySQL, by default
Will connect to localhost:
sky@sky:~$ Mysql-u Def-p
Enter Password:
ERROR 1045 (28000): Access denied for user ' def ' @ ' localhost ' (using
Password:yes)
However, when the host address of the access is explicitly specified by the-h parameter, it is no problem, as follows:
sky@sky:~$ mysql-u def-p-H 127.0.0.1
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 17
Server Version:5.0.51a-log Source Distribution
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.
def@127.0.0.1: (None) 01:26:04>
If we have a localhost access authorization, you can specify the login host without using the-H parameter to connect the default
Recognized localhost:
sky@sky:~$ Mysql-u Abc-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 18
Server Version:5.0.51a-log Source Distribution
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Abc@localhost: (none) 01:27:19> exit
Bye
If MySQL is running, we have a permission adjustment to the system, and when the permissions are adjusted
Will it take effect?
Let's start by knowing when MySQL's permission information is stored in the memory structure is updated: FLUSH privileges will be strong
Allows MySQL to update the Load-to-memory permission information; GRANT, REVOKE, or CREATE user and DROP user
The operation will update the in-memory information directly; restarting MySQL will let MySQL completely read from grant tables
Limited information.
When does the permissions information in the memory structure update to the user who is already connected?
Changes to the permission information for the Global level are only used when the new connection is changed, for
The connected session will not be affected. The permission information for Database level is modified only if
The client request executes the "use database_name" command before the new permission letter is used in the re-check
Interest. So there are times when you change the permissions of the two level of the more urgent Global and Database,
You may need to kill a session that is already connected to MySQL by using the "kill" command to force them to reconnect so
With the updated permissions. For the Table level and Column level permissions, the next time you need to use the
The permissions of the Query are applied when requested, that is, for the application, the two level of permissions, update the
will take effect immediately, without the need to execute a "KILL" command.
2. Access control
When the client connection is authenticated by the user Management module, the MySQL Server can be connected and a variety of
Query and Command to MySQL Server to implement various functions of the client application. When MySQL receives a customer
The access control module is required to verify that the user is satisfied with the requested permission for the submitted request. Permission School
The procedure is to check each right of each object involved, starting from the maximum range of permissions toward the minimum scope
Limit.
When verifying all the required permissions, MySQL first looks for the permission data stored in the memory structure, first checking
For global level permissions, if the required permissions are defined at the global level (GRANT or REVOKE),
The permission check (pass or deny) is complete, and if no definition of all permissions is found, the lookup continues
Database level permission to verify the required permissions that are not defined by the Global level, if still not able to
Finding the definition of all the required permissions, MySQL will continue to look for a smaller range of permission definition fields, which is the Table
Level, and finally the Column level or Routine level.
Below, we will request the following Query as a client through Abc@localhost connection:
SELECT id,name from test.t4 where status = ' deleted ';

In front we learned that MySQL's grant tables had mysql.user,mysql.db,mysql.host,
Mysql.table_priv and Mysql.column_priv These five, I came up with a mysql.host outside the four are non-
Often easy to understand, each table for a logical object in MySQL, the permission to hold a specific level, except
Mysql.host slightly different. Let's see if the Mysql.host permission table is in MySQL's access control.
What kind of character is it?
The mysql.host features in the MySQL Access control module are special, and several other grant tables
Not quite the same. The first is that the permission data in the Mysql.host is not (and cannot) be granted through grant or REVOKE
or removed, the data in it must be modified manually by using the INSERT, UPDATE, and DELETE commands. Followed by
The permission data cannot be applied separately, and must be valid with the data in the MYSQL.DB permission table. and only
When there is an incomplete (special setting in some scenarios) in the mysql.db, the Access control module is
Mysql.host to find out if there is a corresponding supplementary permission data implementation to achieve the purpose of permission verification, such as
As shown in. Data (db) for all conditions that satisfy the permission checksum cannot be found in mysql.db. User = ' abc ' and
Db.host = ' localhost ' and db. database_name = ' test '), it means that the mysql.db cannot be completed
So that it does not directly validate the db. Whether the value of Select_priv is ' Y '. But there are mysql.db.
Db. User = ' abc ' and DB. database_name = ' Test ' and db.host = ' a permission information
exists, you may notice that the db.host in this permission message is NULL, and note that the value is null instead of '% '.
Wildcard characters Oh. When MySQL notices that a permission message exists, it should be stored in the mysql.host.
Permission information to come out. At this point, MySQL detects if there are any of the following conditions in the Mysql.host
Permission information: Host. host = ' localhost ' and host. Db = ' Test '. If it exists, it starts
The checksum of the Select_priv permission. Because the permissions information exists in both mysql.db and Mysql.host, it is
The combination of information can meet the requirements, so the Select_priv calibration also requires that both tables are ' Y ' to meet the requirements,
Pass the checksum.
We have made it clear that MySQL's permissions are granted to "Username@hostname", that is to say, at least
User name and host name both can determine the permissions of a visitor. And since hostname can be a wildcard character
Domain name, or it can be an IP address segment with wildcard characters. So if the same user has two permission information, a
is for a specific domain name, and the other is the domain name that contains the wildcard character, and the former belongs to the latter. This time MySQL
How do you determine permission information? In fact, MySQL always takes precedence over the more precise range of permissions. Internal MySQL will press the
According to username and hostname to make a sort, for the same username permissions, the closer the host information to access
Source host, the higher the sort position, the earlier the check is used. Also, MySQL is in the process of checking permissions,
Once a matching permission is found, no further search will be made to see if there is a matching permission information, and the direct completion of the
Testing process.
As you can see, there are max_questions,max_updates in the Mysql.user this permission table,
Max_connections,max_user_connections these four columns, the first three columns start with the MySQL4.0.2 version
The ability to restrict access to the resources used by the user per hour, and the final max_user_connections
From the MySQL5.0.3 version only, the difference between him and max_connections is to limit the delay of the user's connection
The total number of times, not the number of connections per hour. To make these four restrictions effective, you need to create a user or grant a user
The right time to add the following four seed sentences:
Max_questions:with max_queries_per_hour N;
Max_updates:with max_updates_per_hour N;
Max_connections:with max_connections_per_hour N;
Max_user_connections:max_user_connections.
Four clauses can be used at the same time, such as:
"With Max_queries_per_hour Max_connections_per_hour 10
Max_user_connections 10000 ".

MySQL access control implementation principle