Go deep into MySQL Permission System

Abstract: This article describes in detail the principles of the MySQL permission system, including the complete process of MySQL Access confirmation. Another important knowledge is the user and host fields in the authorization table. Understanding the matching sequence of MySQL Server users and the authentication process helps you configure a secure system.

MySQL has an advanced but non-standard security/authorization system. mastering its authorization mechanism is the first step to start operating MySQL databases. For a person familiar with basic SQL operations, it is also a hard part of all MySQL knowledge. This section introduces the operating mechanism of its authorization system and hopes that you can better operate and use this excellent database system.

The MySQL security system is flexible and allows you to set user permissions in multiple ways. Generally, you can use the standard SQL statement GRANT and REVOKE to modify the authorization table for controlling customer access. However, you may be using an old MySQL version that does not support these statements (these statements did not work before 3.22.11), or you may find that user permissions do not seem to work in the way you want. In this case, it is helpful to understand the structure of the MySQL authorization table and how the server uses them to determine access permissions, this understanding allows you to add, delete, or modify user permissions by directly modifying the authorization table. It also allows you to diagnose permission issues when checking these tables.

Structure of the MySQL authorization table

Access to the MySQL database by customers who connect to the server over the network is controlled by the authorization table content. These tables are located in the mysql database and initialized during the first MySQL installation (run the mysql_install_db script ). There are five authorization tables: user, db, host, tables_priv, and columns_priv.

Structure and Function of the authorization table user, db, and host

Table 1 Authorization Table user, db, and host structure

498) this. style. width = 498; "border = 0>

The authorization table has the following functions:

· User table

The user table lists the users that can connect to the server and their passwords, and specifies which global (Super user) permissions they have. All permissions enabled in the user table are global permissions and apply to all databases. For example, if you have enabled the DELETE permission, the users listed here can DELETE records from any table, so you should consider it carefully before doing so.

· Db table

The database table lists the databases, and the user has the permission to access them. The permission specified here applies to all tables in a database.

· Host table

The host table and db table are used in combination to control the database access permissions of a specific host at a good level, which may be better than using the database separately. This table is not affected by the GRANT and REVOKE statements, so you may find that you are not using it at all.

Structure and Function of the tables_priv and columns_priv authorization tables

Table 2 authorization table tables_priv and columns_priv

Authorization Table tables_priv

Authorization Table columns_priv

Scope Column

Host

Host

Db

Db

User

User

Table_name

Table_name

Column_name

Permission Column

Table_priv

Column_priv

Other Columns

Timestamp

Timestamp

Grantor

MySQL does not have a rows_priv table because it does not provide record-level permissions. For example, you cannot restrict the rows in a table that contain specific column values. If you really need this capability, you must use application programming to provide it. If you want to execute the recommended record-Level Lock, you can use the GET_LOCK () function.
The authorization table has the following functions:

· Tables _ priv table

The tables_priv table specifies table-level permissions. The specified Permission applies to all columns in a table.

· Columns _ priv table

The columns_priv table specifies the column-level permission. The specified Permission applies to specific columns of a table.

The tables_priv and columns_priv tables are introduced in MySQL 3.22.11 (the same as the GRANT Statement ). If you have an earlier version of MySQL, your mysql database will only have user, db, and host tables. If you upgrade from an earlier version to 3.22.11 or update without the tables_priv and columns_priv tables, run the mysql_fix_privileges_tables script to create them.

User Permissions

Permission information is stored in the mysql database using the user, db, host, tables_priv, and columns_priv tables ). When MySQL is started and when 7.5 permission changes take effect, the server reads the contents of these database tables.

Database and table Permissions

The following permissions apply to database and table operations.

· SELECT

You can use the SELECT statement to retrieve data from a table. SELECT statements require select permission only when they actually retrieve rows from a table. You can execute a SELECT statement without any permission to access anything in the database on the server. For example, you can use a mysql client as a simple calculator:

Mysql> SELECT 1 + 1;

Mysql> select pi () * 2;

· UPDATE

Allow you to modify existing records in the table.

· INSERT

Allows you to insert records into a table.

· DELETE

Allows you to delete existing records from a table.

· ALTER

Allow you to use the alter table statement. This is actually a simple first-level permission. You must have other permissions. This depends on what operations you want to perform on the database.

· CREATE

You can create databases and tables, but cannot create indexes.

· DROP

You can delete (discard) databases and tables, but cannot delete indexes.

Note: If you grant the drop permission of the mysql database to a user, the user can discard the database that stores the MySQL access permission!

· INDEX

Allows you to create and delete indexes.

· REFERENCES

No.

MySQL management Permissions

The following permissions apply to administrative operations that control server or user authorization.

· FILE

Allows you to tell the server to read or write files on the server host. This permission should not be granted at will. It is very dangerous. For details, see "avoiding the risk of authorization table ". The server is indeed cautious in using this permission within a certain range. You can only read files that anyone can read. The file you are writing must not be an existing file, which prevents you from forcing the server to rewrite important files, such as/etc/passwd or the data directory of another user's database.

If you authorize FILE Permission, make sure that you do not run the server as a UNIX root user, because root can create new files anywhere in the FILE system. If you run the server as a non-privileged user, the server can only create files in the directories accessible to users.

· GRANT

Allow you to GRANT your permissions to others, including GRANT.

· PROCESS

You can use the show process statement or mysqladmin process command to view information about the running threads (processes) on the server. This permission also allows you to use the KILL statement or mysqladmin kill command to KILL the thread.

You can always see or kill your own thread. The PROCESS permission gives you the ability to perform these tasks on any thread.

· RELOAD

Allows you to perform a large number of server management operations. You can issue FLUSH statements. You can also define mysqladmin's reload, refresh, flush-hosts, flush-logs, flush-privileges, and flush-tables commands.

· SHUTDOWN

You can use mysqladmin shutdown to shut down the server.

In the user, db, and host tables, each permission is specified in a separate column. All these columns are declared as an ENUM ("N", "Y") type, so the default value of each permission is "N ". The permission in tables_priv and columns_priv is expressed as a SET, which allows the permission to be specified by a single column in any combination. These two tables are updated compared to the other three tables, which is why they use a more effective representation. (In the future, user, db, and host tables may also be represented by a SET type .)

Contents of the authorization table column

Scope column content

Some range columns require text values, but most of them allow wildcards or other special values.

Table 3 Scope column type

Field name

Type

Host

CHAR (60)

User

CHAR (16)

Password

CHAR (16)

Db

CHAR (64) (tables_priv and columns_priv tables are CHAR (60 ))

· Host

A Host column value can be a Host name or an IP address. The value localhost indicates the local host, but it matches only when you use a localhost host name, instead of when you use the host name. Assume that your local Host name is pit.snke.net and there are two records for you in the user table. One has a Host value or localhost, and the other has pit.snke.net, logs with localhost will match only when you connect to localhost, and others will match only when you connect to pit.snke.net. If you want customers to connect in two ways, you need to have two records in the user table.

You can also use wildcards to specify the Host value. You can use the SQL mode characters "%" and "_", which have the same meaning when you use the LIKE operator in a query (the regex operator is not allowed ). SQL mode characters can be used for host names and IP addresses. For example, % wisc.edu matches any host in the wisc.edu domain, while %. edu matches any host in the School of Education. Similarly, 192.168.% matches any host in the 192.168 Class B subnet, while 192.168.3.% matches any host in the 192.168.3 Class C subnet.

The % value matches all hosts and can be used to allow a user to connect from anywhere. A blank Host value is equivalent to %. (Exception: In the db table, a blank Host value indicates "further check of the host table". This process is described in "query access verification .)

From MySQL 3.23, you can also specify an IP address with a network mask that indicates the network address, for example, 192.168.128.0/17 specifies a 17-bit network address and matches any host whose IP address is the first 17-bit of 192.168.128.

· User

The user name must be text or blank. A blank value matches any user. % As a User value does not mean white space. On the contrary, it matches a literal % name, which may not be what you want.

When an incoming connection is verified through the user table and the matched record contains a blank User value, the customer is considered to be an anonymous user.

· Password

The password value can be null or non-empty. Wildcards are not allowed. A blank password does not match any password. It means that the user must not specify a password.

The password is stored as an encrypted value instead of a literal text. If you store a literal Password in the Password column, the user cannot connect! The GRANT statement and mysqladmin password command automatically encrypt the PASSWORD for you. However, if you use commands such as INSERT, REPLACE, UPDATE, or set password, you must use password ("new_password ") instead of simply specifying the password "new_password.

· Db

In columns_priv and tables_priv tables, the Db value must be the real Database Name (literally). The mode and blank space are not allowed. In db and host, the Db value can be specified literally or use the SQL mode character '%' or '_' to specify a wildcard. Either '%' or blank match any database.

· Table _ name, Column_name

The values in these columns must be literal tables or column names. The mode and blank spaces are not allowed.

Some range columns are considered case-sensitive by the server, while others are not. These principles are summarized in the following table. Note that the Table_name value is always case sensitive. Even if the table name in the query is case sensitive, it depends on the file system of the host running on the server (in UNIX, It is case sensitive, windows is not ).

Some scope columns are considered case-sensitive by the server, while others are not. These principles are summarized in the following table. Note that the Table_name value is always case sensitive. Even if the table name in the query is case sensitive, it depends on the file system of the host running on the server (in UNIX, It is case sensitive, windows is not ).

Table 4 case sensitivity of scope Columns

Column

Case sensitivity

Host

No

User

Yes

Password

Yes

Db

Yes

Table_name

Yes

Column_name

No

Authorization Table User, Db, and Host permission Columns

In the user, db, and host tables, all permission fields are declared as ENUM ('n', 'y') -- each field can have a value of 'n' or 'y ', and the default value is 'n '.

Authorization Table tables_priv and columns_priv permission Columns

In the tables_priv and columns_priv tables, the permission field is declared as the SET field:

Table 5 authorization table tables_priv and columns_priv permission column types

Table Name

Field name

Possible set members

Tables_priv

Table_priv

'Select', 'insert', 'update', 'delete', 'create', 'drop', 'Grant ', 'references', 'index', 'alter'

Tables_priv

Column_priv

'Select', 'insert', 'update', 'references'

Columns_priv

Column_priv

'Select', 'insert', 'update', 'references'

How MySQL permission system works

General process of permission system work

The MySQL permission system ensures that all users can strictly perform what they assume is permitted. When you connect to a MySQL server, your identity is determined by the host you connect to and the user name you specify. The system grants permissions based on your identity and what you want to do.

MySQL considers your host name and username in identity authentication because there is a small reason for assuming that a given user belongs to the same person on the Internet. For example, the user's bill from whitehouse.gov does not have to be the same person as the bill from mosoft.com. MySQL allows you to differentiate users with the same name on different hosts for processing: You can assign a permission set to bill from the whitehouse.gov connection, assign a different permission set for the connection from microsoft.com.

MySQL access control includes two phases:

· Phase 1: The server checks whether you are allowed to connect.

· Phase 2: If you can connect, the server checks every request you send. Check whether you have sufficient permissions to implement it. For example, if you select a row from a table in the database or discard a table from the database, the server determines that you have the select permission on the table or drop permission on the database.

In the two phases of access control, the server uses the user, db, and host tables in the mysql database for the second stage of access control (request validation). If the request involves a table, for more information about servers, see tables_priv and columns_priv.

Simply put, the server uses the following authorization table:

· The range field of the user table determines whether to allow or reject incoming connections. For allowed connections, the permission field specifies the global (superuser) permissions of the user.

· Use db and host tables together:

The db table range field determines the host on which the user can access the database. The permission field determines which operation is allowed.

When you want to apply a given db entry to several hosts, the host table is used as an extension of the db table. For example, if you want a user to use a database from several hosts on your network, set the Host entry in the user's db table to null, then, move each of those hosts to the host table.

Tables_priv and columns_priv tables are similar to db tables, but more refined: they are applied at the table and column level, not at the database level.

Note that the management permission (reload, shutdown, and so on) is only specified in the user table. This is because administrative operations are performed by the server itself and are not specific databases, so there is no reason to list such permissions in other authorization tables. In fact, you only need to ask the user table to determine whether to perform a management operation.

The file Permission is only specified in the user table. It is not administrative, but the ability to read or thank files on the server host is independent of the database you are accessing.

When the mysqld server is started, read the authorization table once. The change to the authorization table takes effect when the 7.5 permission change takes effect.

A useful diagnostic tool is the mysqlaccess Script, which is provided by Carlier Yves to MySQL for distribution. Use the -- help option to call mysqlaccess to find out how it works. Note: mysqlaccess only checks access using user, db, and host tables. It does not have checklist or column-level permissions.

Access control, Phase 1: Connection Validation

When you try to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can supply the correct password for authentication. Otherwise, the server accepts the connection and enters stage 2 and waits for the request.

Your identity is based on two pieces of information:

· You connect from that host

· Your MySQL user name

The identity check uses three user table (Host, User, and Password) range fields for execution. The server accepts the connection only when a user table entry matches your host name and user name and you provide the correct password.

The range field in the user table can be specified as follows:

A Host value can be a Host name or an IP number, or 'localhost' indicates the local Host.

You can use the wildcard characters "%" and "_" in the Host field.

A Host value '%' matches any Host name. A blank Host value is equivalent to '% '. Note that these values match to create a host connected to your server!

Wildcard characters are not allowed in the User field, but you can specify a blank value, which matches any name. If the user table matches the incoming connection entry with a blank user name, the user is considered an anonymous user (a user without a name) rather than the actual name specified by the customer. This means that a blank user name is used for further access checks during connection (that is, during stage 2 ).

The Password field can be blank. This does not mean that any password is matched. It means that the user must not specify a password for connection.

A non-blank Password value indicates the encrypted Password. MySQL does not store passwords in plain text format that anyone can read. On the contrary, the PASSWORD provided by a user attempting to connect is encrypted (using the PASSWORD () function ), and compare it with the encrypted version that stores the user table. If they match, the password is correct.

The following example shows how to apply the combination of Host and user entries in various User tables to the incoming connection:

Table 6 combination of Host and User entries

Host Value

User Value

Connected by matched entries

'Host .domain.cn'

'Gwen'

Gwen, connect from host.domain.cn

'Host .domain.cn'

''

Any user connected from host.domain.cn,

'%'

'Gwen'

Gwen, connect from any host

'%'

''

Any user, connected from any host

'% .Loc.gov'

'Gwen'

Gwen, any host connection from the loc.gov domain

'X. y. %'

'Gwen'

Gwen, connected from x.y.net, x.y.com, x.y.edu, etc. (This may be useless)

'1970. 155.166.177'

'Gwen'

Gwen, connect from a host with a 144.155.166.177 IP Address

'1970. 144. %'

'Gwen'

Gwen, from 144. 155.166 C Any Host connection of the class Subnet

Since you can use the IP wildcard value in the Host field (for example, '123. 155.166.% 'matches each host on a sub-network.) Someone may attempt to explore this capability by naming a host as 144.155.166.somewhere.com. To prevent such an attempt, MySQL does not allow matching the host name starting with a number and a vertex. In this way, if you use a host named like 1.2.foo.com, its name will never match the Host column in the authorization table. Only one IP number can match the IP wildcard value.

An incoming connection can be matched by more than one entry in the user table. For example, a connection from host.domain.cn by Gwen matches multiple entries as described above. If more than one match exists, how does the server choose which one to use? When the server starts, it reads the user table and sorts the table to solve this problem. When a user tries to connect to the table, the entries are browsed in order, and the first matched entries are used.

The MySQL Server sorts the records in the authorization table in a specific way, and then matches the incoming connections by browsing the records in order. The first match is used. It is important to understand the sorting sequence used by MySQL, especially for the user table.

When the server reads the content of the user table, it sorts the records based on the values in the Host and User columns, and the Host values play a decisive role (the same Host values are listed together, and then sort by User value ). However, sorting is not a lexicographical order (sort by words), but partial. Remember that literal words take precedence over patterns. This means that if you are connecting to the server from client.your.net and the Host has two values: client.your.net and % .your.net, select first. Similarly, % .your.net takes precedence over %. net and then %. This is also true for IP address matching. In a word, the more specific, the higher the priority.

The sorting of the user table is as follows. Assume that the user table looks like this:

+ ----------- + ---------- +-
| Host | User |...
+ ----------- + ---------- +-
| % | Root |...
| % | Jerry |...
| Localhost | root |...
| Localhost |...
+ ----------- + ---------- +-

When the server reads data from a table, it is sorted in the order of the most specific Host values ('%' in the Host column means "any Host" and is the least specific ). Entries with the same Host value are sorted in the order of the most specific User values (a blank User value means "any User" and is the least specific ). The final sorted user table looks like this:

+ ----------- + ---------- +-
| Host | User |...
+ ----------- + ---------- +-
| Localhost | root |...
| Localhost |...
| % | Jerry |...
| % | Root |...
+ ----------- + ---------- +-

When a connection is attempted, the server browses the sorted entries and uses the first matching. For a connection from localhost by jeffrey, the 'localhost' entry in the Host column first matches. Those entries with blank usernames match the connection host name and username. (The '%'/'Jeffrey 'entry will also match, but it is not the first match in the Table .)

This is another example. Assume that the user table looks like this:

+ ---------------- + ---------- +-
| Host | User |...
+ ---------------- + ---------- +-
| % | Jerry |...
| Host.domain.cn |...
+ ---------------- + ---------- +-

The sorted table looks like this:

+ ---------------- + ---------- +-
| Host | User |...
+ ---------------- + ---------- +-
| Host.domain.cn |...
| % | Jerry |...
+ ---------------- + ---------- +-

A connection from host.domain.cn by jerry is matched by the first entry, while a connection from whitehouse.gov by jerry is matched by the second.

The general misunderstanding is that, for a given user name, when the server tries to find a match for the connection, all entries that explicitly name the user will be used first. This is obviously not a fact. This is illustrated in the previous example. A connection from host.domain.cn by jerry is not matched by the entry containing 'Jerry 'as the User field value, but is matched by a question without a User name!

If you have a server connection problem, print out the user table and manually sort it to see where the first match is.

Access control, Phase 2: Request confirmation

Once you establish a connection, the server enters Stage 2. For each request sent over this connection, the server checks whether you have sufficient permissions to execute it based on the type of operation you want to perform. This is where the permission field in the authorization table plays a role. These permissions can be applied to any sub-user, db, host, tables_priv, or columns_priv table. Authorize the table to use the GRANT and REVOKE commands. (You can find it helpful to refer to Chapter 7 how the permission system works. It lists the fields displayed in each permission table .)

The user table grants you the permission on a global basis. This Permission applies no matter what the current database is. For example, if the user table grants you the delete permission, you can delete the row from any database on the server host! In other words, the user table permission is the Super user permission. It is wise to grant only the permissions of the user table to a Super user, such as a server or database supervisor. For other users, you should set the permissions in the user table to 'N' and grant permissions only on the basis of a specific database to use the db and host tables.

Database-specific permissions are granted to db and host tables. The value of the range field can be specified as follows:

The wildcard characters "%" and "_" can be used in the Host and Db fields of two tables.

The '%' Host value in the db table means "any Host". A blank host value in the db table means "consult the Host table for further information ".

A '%' or blank host value in the Host table means "any host ".

Either '%' or a blank Db value in the two tables means "any database ".

A blank User value in the two tables matches the anonymous User.

Db and host tables are read and sorted when the server is started (and read the user table at the same time ). Db tables are sorted by Host, Db, and User range fields, and host tables are sorted by Host and Db range fields. For the user table, the most specific value is placed first and then the least specific value is finally placed. When the server looks for a matched entry, it uses the first match it finds.

Tables_priv and columns_priv tables grant table and column-specific permissions. The value of the range field can be specified as follows:

The wildcards "%" and "_" can be used in the Host fields of the two tables.

Either '%' or a blank Host in the two tables means "any Host ".

The Db, Table_name, and Column_name fields in the two tables cannot contain wildcards or spaces.

The tables_priv and columns_priv tables are sorted on the Host, Db, and User fields. This is similar to the sorting of db tables. Although only the Host field can contain wildcards, the sorting is simpler.

The request validation process is described below. (If you are familiar with the source code of the access check, you will notice that the description here is slightly different from the algorithm used in the code. The description is equivalent to what the code actually does; it is just different from making the interpretation easier .)

For management requests (shutdown, reload, etc.), the server only checks the user table entries, because it is the only table with the specified management permission. If the entry permits the requested operation, the access is authorized; otherwise, the access is rejected. For example, if you want to execute mysqladmin shutdown, but your user table entries do not grant you the shutdown permission, access is denied without checking the db or host table. (Because they do not include the columns of Shutdown_priv, there is no need to do so .)

For database-related requests (insert, update, and so on), the server first checks the user's Global (superuser) permissions by searching the user table entries. If the entry allows the requested operation, the access is authorized. If the global permissions in the user table are insufficient, the server checks the db and host tables to determine the specific user database permissions:

The server finds a match in the Host, db, and User fields of the Db table. Host and User are the Host names and MySQL usernames of the connected users. The Db field corresponds to the database you want to access. If no Host or User entry exists, access is denied.

If the entries in the db table