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

This article mainly introduces "Overview of MySQL authorization form". In daily operation, I believe many people have doubts about the overview of MySQL authorization form. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Overview of MySQL authorization form"! Next, please follow the editor to study!

I. Overview of MySQL authorization form

One of the peculiarities of the 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 a particular table. This shows the flexibility of the MySQL server in terms of user authorization. This article will give you a detailed description of how the MySQL server handles the granting / revocation of user rights, especially the authorization tables tables_priv and columns_priv of MySQL.

MySQL's authorization system is usually implemented through five tables in the MySQL database: user, db, host, tables_priv, and columns_priv. These tables are used for different purposes, but they are consistent in that they are all sufficient to verify that what the user is going to do is allowed. The fields of each table can be divided into two categories, one is the scope field and the other is the permission field. The scope field is used to identify the host, user, or database, while the permission field is used to determine which actions are allowed for a given host, user, or database. Let's give a brief introduction to the functions of these tables:

Next, we introduce the MySQL user authorization process through the following aspects: first, we introduce the MySQL access control process and answer how the MySQL authorization table works; then, we introduce the tables_priv and columns_priv authorization tables, and we will give explanations and examples related to MySQL's tables_priv table. Finally, we introduce the explanations and some examples related to the columns_priv authorization table.

II. Access control of MySQL server

Now let's look at how the MySQL server controls user access through user privileges. Although this may sound scary at first, through the demonstration of an example, you will find that things are not as difficult to understand as we thought.

First of all, when controlling user access, the system needs to view some authorization tables as filters, which are used from general to special, including:

In addition, once connected to the server, a user can use two types of requests:

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 be duplicated, such as select permissions in the user table, as well as the same permissions in the host and user tables. However, there is a reason for doing so. We can consider the global database-related permissions in the user table, that is, the permissions granted to users in this table are valid for all databases on the server. These permissions can be thought of as superuser privileges. In contrast, the database-related permissions contained in the host and db tables are host-or database-specific. Therefore, it is wise to keep all permissions in this table at "N".

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

Scenario 1: failed connection attempt

The user "alessia" will be denied when connecting to the server. Because the host, user, and / or password do not match what is held in the user table, the user's request is denied.

The database permissions in the context 2:user table are NPowerDB, and the database permissions are Y in the context db table.

1. User wj will succeed when trying to connect.

two。 The user wj attempted to execute the Select command on the database oats.

3. The server looks at the user table and the value of the entry corresponding to the Select command is N, which indicates rejection.

4. The server then looks at the db table and the value of the table entry corresponding to the Select command is Y, which means that it is allowed.

5. The request will be executed successfully because the value of the SELECT field in the user's db table is Y.

The database permissions in the context 3:user table are Y _ # db and the database permissions are N in the context db table.

1. User wj will succeed when trying to connect.

two。 The user wj attempted to execute the Select command on the database oats.

3. The server looks at the user table and the value of the table entry corresponding to the Select command is Y, which means that it is allowed. Because the permissions granted within the user table are global, the request executes successfully.

The database permissions in the context 4:user table are N, and the database permissions in the database db table are N

1. User wj will succeed when trying to connect.

two。 The user wj attempted to execute the Select command on the database oats.

3. The server looks at the user table and the value of the table entry corresponding to the Select command is N, which means that it is rejected.

4. The server now looks at the db table, and the value of the table entry corresponding to the Select command is N, which means no.

5. The server will now look for the tables_priv and columns_ private tables. If the user's request matches the permissions given in the table, access is granted. Otherwise, access will be denied.

The tables_priv and columns_priv tables are described in more detail later.

Scenario 5: let's assume the following are true:

User table-this table determines whether users are allowed to connect to the server. If a connection is allowed, the permissions field is the global permission of the user.

Db table-used to determine which users can access which databases 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 through multiple hosts, the superuser can have the db table list host empty and then populate the host table with the necessary hostname.

Tables_ private table-this table is similar to the db table, except that it is used for tables rather than databases. This table also contains an additional field type, including the timestamp and grantor fields, which are used to store the timestamp and the authorized party. We will explain this table further later in this article.

Columns_priv-- this table functions almost the same as the db and tables_ private tables, except that it provides permissions for specific columns of some tables. This table also has an extra field type, that is, other fields, including a timestamp column to hold the timestamp. We will further explain the columns_ privtable later in this article.

User table

Db table

Host table

Tables_priv table

Columns_priv table

Manage requests (shutdown,reload, etc.)

Database-related requests (insert,delete, etc.)

The value of the host field of user wj in the user table is%.

The value of the host field for user wj in the db table is empty.

What happens then?

1. User wj attempts to connect through a given host.

two。 Assuming that the password is correct, the connection will succeed, because the user table indicates that any host (meaning represented by the character%) is allowed as long as the connection is made through the user name wj and the associated password.

3.The MySQL server will look for the db table, but there is no host specified here.

4. The MySQL server will now view the host table. If the database to which the user connects and the name of the host on which the user established the connection are in the host table, the user can execute the command according to the permissions listed in the host table. Otherwise, the user will not be able to execute the command and will not be able to connect at all.

By understanding the above situation, the reader should have some understanding of the authorization system. Next, let's talk in more detail about the tables_ private table and the columns_ private table.

Third, Tables_ private table and columns_ private table

Of the five authorization tables described above, tables_priv and columns_priv were added later, and their main purpose is to provide fine-grained user control. These two tables are very similar to the authorization table db, but the scope of their control is more detailed, that is, specific tables in a particular database. As mentioned earlier, the superuser can restrict the user's behavior in a database through the authorization table db, with which the superuser can further control the user's actions for each table and each field. Therefore, this provides superusers with more flexible control options.

Before delving into access control to datasheets, let's take a look at the following features:

Authorization form tables_priv

The following is a schematic diagram of an tables_priv table:

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

Wildcards can be used in the host fields of both tables, but wildcards are not allowed in the Db, Table_name, and Column_name fields.

The permissions field is declared as a SET field.

The tables_priv and columns_priv tables should only be modified through the GRANT/REVOKE command. Inserting data into these two tables through the INSERT command can cause server instability!

The table_priv field in the tables_ private table allows the following commands: Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, and Alter.

The column_priv field in the tables_ private table allows the following commands: Select, Insert, Update, and References.

The type field in the columns_ private table allows the following commands: Select, Insert, Update, and References.

At this point, the study of "Overview of MySQL Authorization form" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.