MySQL Authorization Table Overview and MySQL server access control

MySQL databaseMySQL is a database system with many applications and is very popular among users.Authorization TableWhat is it like?MySQL ServerWhat is access control? I will answer your questions one by one.

I. MySQL Authorization Table Overview

One of the features of the MySQL server is that it provides great flexibility in controlling each user's behavior. For example, you can restrict user access to the entire database, restrict user access to specific tables in the database, or prohibit access to specific columns in a specific table. We can see that the flexibility of the MySQL server in user authorization. This article will introduce in detail how the MySQL server handles the grant/Revoke of user permissions, especially the MySQL authorization tables tables_priv and columns_priv.

The MySQL authorization system is usually implemented through five tables in the MySQL database. These tables include user, db, host, tables_priv, and columns_priv. These tables have different purposes, but one thing is the same, that is, they can check whether the user is allowed to do things. Fields in each table can be divided into two categories: Scope field and permission field. The scope field is used to identify the host, user, or database, and the permission field is used to determine which actions are allowed for a given host, user, or database. The following describes the functions of these tables:

User table-- This table determines whether to allow users to connect to the server. If the connection is allowed, the permission field is the global permission of the user.
Db table-- Used to determine which users can access which databases from which hosts. The permission contained in the db table applies to the database identified by this table.
Host table-- This table is used when you want to expand an entry within the range of the db table. For example, if a database allows access from multiple hosts, the Super User can leave the host column empty in the db table and then fill the host table with the necessary host names.
Tables_priv table -- this table is similar to the db table. The difference is that it is used for tables rather than databases. This table also contains another field type, including timestamp and grantor, used to store the timestamp and grantor. This table will be further explained later in this article.
Columns_priv -- this table serves almost the same purpose as db and tables_priv tables. The difference is that it provides permissions for specific columns of some tables. This table also has an additional field type, that is, other fields, including a timestamp column, used to store timestamps. In the later part of this article, we will further describe the columns_priv table.
Next, we will introduce the MySQL user authorization process through the following aspects: First, we will introduce the MySQL access control process and answer how the MySQL authorization table works. Then, we will introduce the tables_priv and columns_priv authorization tables. We will give explanations and examples related to the tables_priv table of MySQL. Finally, we will introduce some explanations and examples related to the columns_priv authorization table.

Ii. MySQL server access control

Now let's take a look at how the MySQL server controls user access through user privileges. Although it sounds quite scary at first glance, through an Example demonstration, you will find that things are not as hard to understand as we think.

First, when controlling user access, the system needs to view some authorization tables used as filters. The usage of these tables ranges from general to special,These tables include:

User table
Db table
Host table
Tables_priv table
Columns_priv table
In addition, once connected to the server, one user can useTwo types of requests:

Manage requests (shutdown, reload, etc)

Database-related requests (insert, delete, etc)

When a user submits a management request, the server only needs to view the user table, because the user table is the only table that contains permissions related to management. However, when you submit a database Request, You need to view more tables.

You may have noticed that the contents of these authorization tables seem to be repeated. For example, the user table has the select permission and the host and user tables have the same permissions. However, this method has its own principle. We can consider the global database-related permissions in the user table. That is to say, the permissions granted to users in this table are valid for all databases on the server. These permissions can be considered as super user permissions. On the contrary, database-related permissions contained in host and db tables are specific to hosts or databases. Therefore, it is wise to keep all the permissions in this table as "N.

Let's assume that our user and db tables are as follows:

 

Based on the above study, I believe that the questions about the MySQL authorization table and MySQL server access control have been solved. Here I just made a preliminary introduction, not very detailed, we will continue to introduce this article in the future, hoping to help you.