Five tables of the MySQL authorization system

The MySQL authorization system is implemented by five tables in the database. The following describes the tables related to the MySQL authorization system in detail. If you are interested, take a look.

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 extend an entry within the database table range. 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.

Implementation of Mysql multi-Table Deletion

Back up data based on Mysql tables

MySQL Show statement usage

How to clear a table in Mysql

Command for viewing the Mysql table structure