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

How to learn MySQL authorization table in depth, for this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

We'll walk you through MySQL's authorization tables in detail and show you some examples of how MySQL enhances these authorization tables to implement user access control. We'll start with the MySQL access control process and then explain the tables_priv and columns_priv authorization tables. We'll give explanations and examples about MySQL's tables_priv table. Finally, we introduce the columns_priv authorization table and its examples.

I. Overview of MySQL authorization table

One of the characteristics of MySQL Server is that it provides great flexibility in controlling the behavior of each user. For example, we can restrict user access to the entire database, restrict user access to specific tables in the database, or prohibit access to specific columns in specific tables. This shows MySQL Server's flexibility in terms of user authorization. This article will explain in detail how MySQL Server handles granting/revoking user permissions, especially MySQL's authorization tables_priv and columns_priv.

MySQL's authorization system is typically implemented through five tables in the MySQL database: user, db, host, tables_priv, and columns_priv. The purpose of these tables varies, but they all have one thing in common: they can verify that what the user wants to do is allowed. The fields of each table can be broken down into two categories, one for scope fields and one for permission fields. The scope field identifies the host, user, or database; the permissions field determines which actions are allowed for a given host, user, or database. Here is a brief description of what these tables do:

user table-This table determines whether users are allowed to connect to the server. If connections are allowed, the permissions field is the global permissions for the user.

db table--Used to determine which databases can be accessed by which users from which hosts. The permissions contained in the db table apply to the database identified by this table.

host table--This table is used when you want to extend an entry within the scope of the db table. For example, if a db allows access from multiple hosts, the superuser can have the db table leave the host column blank and then populate the host table with the necessary host names.

tables_priv table--This table is similar to the db table except it is used for tables rather than databases. This table also contains an additional field type, including timestamp and grantor fields for storing timestamps and authorizers. We will explain this table further later in this article.

columns_priv--This table acts almost like db and tables_priv tables except that it provides permissions on specific columns of certain tables. This table also has an additional field type, that is, other fields, including a timestamp column for storing timestamps. Later in this article, we'll explain the columns_priv table further.

Below, we describe the MySQL user authorization process in the following ways: first, we describe the MySQL access control process and explain how the MySQL authorization table works; then, we describe the tables_priv and columns_priv authorization tables and we give explanations and examples related to MySQL's tables_priv table. Finally, we introduce explanations and examples related to the columns_priv authorization table.

Access Control for MySQL Server

Now let's see how MySQL Server controls user access through user privileges. Although this sounds scary at first glance, an example demonstrates that things are not as difficult to understand as we think.

First of all, when controlling user access, the system needs to look at some authorization tables as filters. The use process of these tables ranges from general to special. These tables include:

User table

DB table

Host table

Tables_priv

Columns_priv table

Additionally, once connected to the server, a user can make two types of requests:

Administrative requests (shutdown,reload, etc.)

Database related requests (insert, delete, etc)

When a user submits an administrative request, the server only needs to look at the user table because the user table is the only table that contains permissions related to administrative work. However, when a user submits a database request, there are more tables to view.

You may have noticed that the contents of these authorization tables seem to duplicate, for example, select permissions in the user table, and the same permissions in the host and user tables. But there is a reason for this. Consider database-dependent permissions globally in the user table, that is, permissions granted to users in this table are valid for all databases on the server. These privileges can be considered superuser privileges. In contrast, database-related permissions contained in the host and db tables are host-or database-specific. Therefore, it is a wise choice to keep all permissions in this list as "N."

Let's assume that our user and db tables look like this:

The following is a brief description of the meaning of each field:

Host--For which hosts?

Db--For which databases are connected from the host above?

User--For which users from the above hosts?

Table_name--Which tables in the above database apply to?

Table_priv--What permissions are given to this table?

Column_priv--What permissions are given to the fields in this table?

Timestamp--When was this permission granted?

Grantor-Who granted this permission to this user?

The best way to really understand tables_priv is to learn by example, and here are some examples.

Example 1:

%>GRANTSELECTON italy TO wj@314interactive.com;

What does this command do? The above command allows user wj from host 314interactive.com to go up to the next SELECT statement in table italy. Keep in mind that you need to access this table only if the SELECT field in the db or host table for a given database/host and username has a value of N. If one of the SELECT fields in the db or host table for a given database/host and username has a value of Y, then there is no need to control the tables_priv table.

Example 2:

%>GRANTSELECT, INSERTON oats.italy TO wj@314interactive.com;

What does this command do? The above command allows user wj from host 314interactive.com to execute SELECT and INSERT statements against data table italy in database oats.

Example 3:

%>REVOKESELECTon oats.italy from wj@314interactive.com

What does this command do? The above command revokes SELECT permission from user wj from host 314interactive.com for table italy in database oats.

It is important to realize that the information contained in tables_priv only takes effect if the host/db table does not allow the user the permissions required to perform the requested function. If the given permission is Y in the host/db table, then there is no need to examine the tables_priv table.

Example 4:

%>GRANTSELECT(id,name,address,phone),update(address,phone) ON

company.customers TO gilmore@314interactive.com;

What does this command do? The above command grants SELECT permission on the id, name, address, and phone fields in the customers table in the company database, and UPDATE permission on the address and phone fields.

What impact will this have? This command modifies the tables_priv and columns_priv tables because it references them and specific fields within them.

Example 5:

%>REVOKEUPDATE(address,phone) ON company.customers FROM gilmore@314interactive.com;

What does this command do? This command revokes UPDATE permissions on the address and phone fields in the customers table in the company database.

What impact will this have? Because the command directly relates to certain fields in a given table, the columns_priv table and tables_priv table are updated as well.

As mentioned earlier, authorization tables are used only when necessary, for example, if the higher priority table provides the appropriate permissions, then there is no need to consult the lower priority authorization table. If the value for the command in the high-priority list is N, then you need to look further into the low-priority grant list.

No matter what level of permissions, as long as there is a level allowed, access can succeed, this is reasonable, for example, I only want a user to access a table in a database, then the global and database level access permissions must be N, only at the table level is Y. If a user is Y at the global level, then he will succeed regardless of whether he is Y at the database level and table level. Because MYSQL is no longer validated at the database and table levels as long as Y is validated globally.

About how to learn MySQL authorization table questions to share here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.