Secure network access to MySQL database server

mysql| Security | access | server | data | database | Network MySQL security system is flexible. It allows you to set user access rights in many different ways. Typically, the grant and REVOKE statements are used to modify the authorization table that controls client access. However, you may have an older version of MySQL that does not support these statements in the previous version of MySQL3.22.11
, or it may be found that the user's permissions do not seem to work as expected. 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. You know that a degree, you can modify the authorization table directly to add, delete or modify the user's permissions, but also in the 觳 Xing?br> diagnostic permissions issues.
The author assumes that you have read the section on "User account Management" in chapter 11th and understand how Grant and R e V O K e statements work. GRANT and revoke provide a convenient way to set up MySQL accounts and related permissions, but they are just a front-end. All the real operations happen in the MySQL authorization table.

The structure and content of the MySQL authorization table

Access to the MySQL database by clients connected to the server on the network is controlled by the content of the authorization table. These tables are located in the MySQL database and initialized during the first installation of MySQL (as described in Appendix A). Table 12-1 and Table 12-2 show the column five authorization tables, which are user, DB, host, Tables_priv, and C o l um n s _ priv.


The contents of the authorization table are used in the following terms:
The User User table lists the users who can connect to the server and password, and specifies what global (superuser) permissions The user has (if any). Any permissions that are allowed in the user table entry are global permissions and apply to all databases. For example, if delete permission is allowed here, the users listed in the item can delete records from any table. Therefore, be careful before doing this, usually it is best to turn off all permissions in the user table entry and use other tables with more restrictions to specify permissions. For super users (such as r o T), this is an exception, but it is generally rare.

The DB DB table lists the database and which users have permission to access the databases. The permissions specified here apply to all tables in the database.
The host host table is used in conjunction with the DB table to control database access to a specific host at a finer level. The table is not affected by the grant and REVOKE statements, so you will find that it is not used at all.
Tables_priv tables_priv table to specify table-level permissions. The permissions you specify here apply to all columns in the table.
Columns_priv The Columns_priv table to specify the column-level permissions. The permissions you specify here apply to specific columns in the table.
In the 12. 2. In section 4, "Do not build users with Grant," we will discuss how the GRANT statement modifies these tables and how to achieve the same result by directly modifying the authorization table.
The Tables_priv and Columns_priv tables are introduced in MySQL3.22.11 (in conjunction with the GRANT statement). If you have an older version of MySQL, the MySQL database has only the user, DB, and host tables. If you have upgraded from an older version to 3. 2 2. More than 11 versions, but there are still no tables_priv and Columns_priv tables, you can run mysql_fix_privileges_table scripts to create them.
There are no rows_priv tables, because MySQL does not provide record-level permissions. For example, you cannot allow users to limit only those rows in a table that contains a value in some columns. If you need this capability, you must write your application. If you want to perform a consulting record-level lock (Advisory Record-level locking), use the Get_lock () function described in Appendix C.
The authorization table contains two types of columns: scope columns (scope column) and permission columns (privilege column), which determine when an item is available, which determines which permissions an item can grant (some authorization tables contain all sorts of other columns, but this is not our concern here).
1. Scope Column of Authorization table
The Authorization Table scope column specifies when a table entry is to be used. The user and host columns are included in each authorization table entry to indicate that the item is applied when a particular user is connected from a particular host (the host table is an exception and is used in a particular method that we have not contacted). Additional scope columns are included in other tables. For example, the DB table contains a DB column to indicate which database the item applies to. Similarly, the Tables_priv and Columns_priv tables contain scope fields that reduce the scope to columns in a particular table or table in a database.
2. Authorization Table Permission Column
The authorization table also contains permission columns. These columns indicate which permissions are owned by the user specified in the scope column. Permissions that are supported by MySQL are displayed in the following list. The list uses the permission name used for the GRANT statement. For the most part, the name of the permission column in the user, DB, and host table is discussed in the connection to the 11th chapter using the GRANT statement
The permission names are obviously the same. For example, the Select_priv column corresponds to the Select permission.
3. Database and Table permissions
The following permissions apply to database and table operations:
Alter allows the use of ALTER TABLE statements. This is actually a simple first level permission, and you have to have additional permissions based on what you intend to do with the table.
Create allows database and tables to be created. This permission does not allow you to create an index.
Delete allows existing records to be deleted from the table.
Drop allows you to delete databases and tables. Deleting indexes is not allowed.
Index allows you to create or delete indexes on a table.
Insert allows new records to be inserted in the table.
REFERENCES not used.
Select allows you to retrieve data from a table with a SELECT statement. This permission is not necessary for a SELECT statement that does not contain a table, such as select Now () or select 4/2.
Update allows you to modify existing records in the table.
4. Administrative authority
The following permissions apply to administrative actions that control server operations or user authorization capabilities:
File allows the server to read and write files on the server host. This permission should not be granted if there is no valid reason, such as 12. 2. Section 3, "The risk to be avoided by the authorization form", is dangerous. The server takes certain precautions to qualify the permission for use within a specific scope. You can read only files that are readable by the world, so there is no need to consider any form of protection. The file that is writing is not allowed to exist. This prevents the server from overwriting important files (such as/E t c/pass w D) or database files belonging to other users. For example, if you do not apply this constraint, you may replace the contents of the authorization table in all MySQL databases.
If you grant file permissions, you cannot run the server as the root user of Unix because root can create new files anywhere on the file system. If you run the server as an unauthorized user, the server creates the file only in a directory that the user can access.
Grant allows you to grant permissions to other users, including grant permissions.
PROCESS allows you to query for thread information that is running in the server by using the show Processlist statement or the mysqladmin processlist command. This permission also allows the thread to be canceled using the KILL statement or the Mysqla d m i n kill command. You can always view or cancel your own threads. PROCESS permissions give you the ability to perform these operations on any thread.
RELOAD allows multiple server management operations to be performed. You can publish flush SQL statements and execute mysqladmin commands r e l o a D, r E F R e s H, f l US h-host S, f l US h-l o G S, flush-privileges and F l US H-tables.
This is usually not a dangerous privilege, although it is actually administrative.
SHUTDOWN allows the server to be shut down using mysqladmin SHUTDOWN.
In the user, DB, and host tables, each permission is given as a separate column. These columns are declared with an enum ("N", "Y") type, so the default value for all permissions is "N" (o ff). The permissions for the Tables_priv and Columns_priv tables are represented by the SET statement, which allows permissions to be specified using any combination of a single column. These two tables are newer than the other three tables, which is why they use more efficient representations (user, DB, and host are likely to be modified in the future and set to represent permissions).
The Table_priv column of the TABLE_PRIV table is defined as follows:
SET (' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' alter ')
The Column_priv column of the COLUMNS_PRIV table is defined as follows:
SET (' Select ', ' Insert ', ' Update ', ' References ')
Column permissions are much less than table permissions because there are few meaningful operations at the column level. For example, you can create a table, but you cannot create an orphaned column.
The user table contains columns for certain permissions that do not appear in any other authorization table, such as f i l e _ priv, P R o C e s _ Priv, Reload_priv, and Shutdown_priv tables. These permissions apply to performing operations with any particular database or table-Independent server. For example, it makes no sense to have users shut down the server based on the current database.

How the server controls client access

There are two stages to client access control when using MySQL. The first stage occurs when the server is connected. The server looks up the user table to see if it can find an entry that matches your name, the host you are connecting to, and the password you provide. If you do not match, you cannot connect. If a match is made, the connection is established and the second stage continues. In this phase, for each query that you publish, the server checks the authorization table to see if you have sufficient permissions to execute the query. The second phase continues until the session about the server ends.
This section discusses in detail the rules that MySQL uses to match authorization table entries to incoming client connection requests and queries. This includes the types of values that are valid in the scope column of the authorization table, the method of combining the permission information in the authorization table, and the order in which the table items are checked.
1. Scope Column Contents
Some scope columns require a direct amount, but most columns allow wildcard characters or other special values.
Host this column value can be either a host name or an IP number. The meaning of the localhost value is the local host, but only if you actually use the localhost host value instead of using the host name to connect. Assume the local host name is P i t-v i per. S n A k E. N e T, and there are two entries in the user table, one with the host value or localhost, and the other with the P i t-v i per.snake.net value. Items with localhost values are only matched when you connect to localhost. The other item matches only if you connect to P i t-v i per.snake.net. If you want to allow users to connect in any one way, you need two items in the user table. You can also specify the value of the host with a wildcard character. The SQL pattern character '% ' and ' _ ' can also be used and have the same meaning as the LIKE operator in the query. SQL mode (not allow Regex mode) characters are available for first name and IP number. For example,%.wise.edu will match any host in the wise.edu domain. Similarly, 192.168.% matches any host in the class 192.168 B subnet, so 192.168.3.% matches any host in the 192.168.3 Class C subnet.
The value '% ' always matches any host to allow a user to connect from anywhere. The value of the empty H o S is the same as the '% ' (exception: In the DB table, the blank host value means "Check for further information on the host table").
Since MySQL3.23, you can also specify an IP number by using a network mask that indicates the number of bits of the network number. For example, 192.168.128.0/17 specifies a 17-bit network number and uses 19 2 of the first 17 digits of the IP address. 16 8. 12 8 matches all the hosts.
The user username must be either direct or blank (empty). The blank value matches any user. % of the user value does not mean white space, instead it matches the user with the direct name of%, which may not be what you want.
The client is considered an anonymous user when an input connection corresponds to a user table and the item that matches contains a blank user value.
The Password password value is either blank (empty) or non-empty, but wildcard characters are not allowed. A blank password does not mean that it matches any password. It means that the user does not have to specify a password. Passwords are stored with encrypted values rather than direct values. If a direct password is stored in the password column, the user will not be able to connect! The GRANT statement and the mysqladmin Password command automatically encrypt passwords, but if you use statements such as INSERT, REPLACE, UPDATE, or set password, you must use PA s s w O R D ("N E W _ PA s S W o r d ") instead of simply specifying the password with" N e w _ pass W o r d ".
DB in the Columns_priv and Tables_priv tables, the DB value must be a direct database name, not a schema and a blank name. In the DN and host tables, the DB value can be specified with a direct amount or by using the SQL mode character '% ' or ' _ ' that specifies the wildcard character. The% value or white space will match any database.
Ta B L E _ name, column_name the values in these columns must be a direct amount of table or column names, and schema values and whitespace are not allowed.
Some scope columns are considered case-sensitive by the server, while others are not. These rules are summarized in table 12-3. In particular, the TABLE_NAME value is always case-sensitive, even if the table name processing in the query is based on the file system run by the server, and its case sensitivity (case-sensitive in UNIX and case-insensitive in Windows).

2. Query access check
Every time you publish a query, the server checks to see if you have sufficient permissions to execute the query. It is done by examining the user, DB, Table_priv, and Columns_priv tables in turn until it determines that you have the appropriate access rights or that all the tables have been searched in vain. To be more precise:
1 When you connect at the beginning to view the global permissions that you have, the server checks for a matching user table entry. If you have global permissions and the query is satisfied, the server executes the query.
2 If global permissions are not met, the server looks for an item in the DB table and adds the item's permissions to your global permissions. If the result satisfies the query, the server executes the query.
3 If the combination of global and database-level permissions is not enough, the server will keep looking, first in the tables _ Priv table, and then in the Columns_priv table.
4 If you find that you do not have permission after all tables have been checked, the server will refuse to execute the query.
The password is stored in the user table as an encrypted string in the user table, so you do not know what the user's password is, even if you already have access to the table. Generally, it seems that the PA s W O R D () function performs the same encryption method as the UNIX password, but that is not the case.
When two encryption methods are one-way and irreversible, they are the same, but MySQL does not use the same encryption algorithm as UNIX. This means that even if you use the UNIX password as the password for MySQL, do not want the encrypted password string to match. If you want to use UNIX encryption for your application, you should use the C rypt () function instead of the PA s W O R D ().
Under logical conditions, authorization tables are used by the server in the following form:
User or DB or Tables_priv or Columns_priv
You may be wondering why the above description only covers four of the five authorization tables. Hit, the server actually checks access permissions in the following ways:
User or (DB and host) or Tables_priv or Columns_priv
The author first gives a simpler expression, because the host table is not affected by the grant or REVOKE statement. If you continue to use grant and revoke to manage user rights, you will not need to consider the host table. But here's a discussion of the table for you to know:
When the server checks database-level permissions, it looks at the client's DB table entries. If the host column value is blank, it means "check the host table to find which servers can access the database."
The server uses the same DB column as the DB table entry to view the host table. If the host table entry does not match the client host, the database-level permissions are not granted. If these items match the host column values of the hosts that the client is connecting to, the entries of the DB table and the host table are combined to produce database-level permissions for the client.
However, the permission is combined with a logical and, meaning that it does not have the specified permission unless the client appears in two items at the same time. In this way, you can grant a basic set of permissions in this DB table entry, and then use the host table entry to selectively disable these permissions for specific hosts. For example, a database is allowed to be accessed from all hosts in the domain, but database permissions are not allowed for hosts that are located in unsecured scopes.
The above description is undoubtedly an access check, which sounds like a fairly complex process, especially given the limited amount of checks that the server has to issue for each individual query that the client publishes. However, the process is fairly fast because the server does not actually check the authorization table information for each query. Instead, when the server starts, it reads the contents of the table into memory, but
The query is then validated with a copy of memory (i n-m e m o r y). This has a significant negative effect on the performance of access check operations: If you modify the contents of the authorization table directly, the server will not notice the change in permissions.
For example, if you increase a user by adding a new record to the user table by using an INSERT statement, the user specified in the entry will not be able to connect to the server. This is a question of confusing new administrators (and sometimes more experienced administrators), but the solution is simple: Tell the server to reload its contents after you modify the authorization table. This can be done either by publishing flush privileges statements or by executing mysqladmin flush-privileges (or mysqladmin reload, if older versions of Flush-privileges are not supported).
3. Scope Column Matching order
The MySQL server sorts the entries in the authorization table in a particular way, and then tries to match the input connections by looking at all the items sequentially. The first found match determines which items are used. It is important to understand the sort order that MySQL uses, especially for the user table. It's like it's hard to understand a lot of MySQL security
People.
When the server reads the contents of the user table, it sorts the items based on the values of the host and user columns. The host value is a field value (items with the same host value are stored together and sorted by the user value). However, the sort is not a dictionary, or is not exactly the case. The principle to keep in mind is that the direct value takes precedence over the schema value. This means that if you are moving from
Boa.snake.net, the direct value will be preferred if the host value is connected and with Boa.snake.net and%.snake.net. Similarly,%.snake.net takes precedence over%. N e T, so on,%.net takes precedence over%. The IP number is also matched in this way. For clients that are connected from a host with a 192.168.3.14 IP number, they have 19 2. 16 8. 3. 4, 19 2. 16 8. 3. %, 19 2. 16 8. The%, 192.%, and% host values will match in this order.

The risk that the authorization form should avoid

This section discusses some of the precautions to be followed in the delegation of authority and the risks that maintenance personnel take in making imprudent choices. In general, giving users a super user right should be conservative. In other words, do not place permissions in the user table. Instead, use other authorization tables to limit the user's permissions to a particular database, table, or column. The permissions in the user table can cause users to affect the operation of the server or access any table in any database.
Do not authorize the MySQL database. This database connects the authorization table, and users who have permissions on the database can modify those tables to gain permissions on any other database. Allowing users to modify the MySQL database table's authorization can also give the user a global grant permission. If the user can modify the table directly, it is equivalent to releasing any grant
Statement.
FILE privileges are quite dangerous and do not easily authorize them. The following is an example of a file privileged User:
CREATE TABLE etc_passwd (pwd_entry TEXT);
LOAD DATA INFILE "/etc/passwd" into TABLE etc_passwd;
SELECT * from ETC_PASSWD;
When these statements are published, the user has the contents of your password file. In fact, in a server, the content of any public readable file can be accessed by any user with file permissions over the network.
File permissions can also be used to compromise a database in the system that cannot be built with enough restricted file permissions. This is why the contents of the data directory should be set to be a reason that can only be read by the server. If the files that correspond to a database table are globally readable, not only can users of the server Host account read them, but any client with file permissions can also connect and read through the network! The following procedure explains how to do this:
To create a table with Longblob columns:
Use test
CREATE TABLE tmp (b longblob)
Use this table to read the contents of each file of the table you want to steal, and then write the contents of the table to your own database file:

Now you have a new table y that contains other_db.x content that you can access completely.
To prevent someone from attacking you in the same way, you should set permissions on the data directory according to the guidelines for internal security: Secure Data Directory access. You can also use the-skip-show-database option when you start the server to restrict access to databases that users cannot access by using show database and S H O W TABLE S. This will help prevent users from finding databases and tables that should not be accessed.
ALTER permission may be used in any way that you do not envisage. Suppose you want to User1 access to Table1 but cannot access t a b l e 2. However, a user with ALTER permission may disturb your idea by renaming table2 to Table1 by using alter TABLE.
Be careful to use grant permissions. If two users have different permissions but have grant permissions, they can make each other's access more powerful.

Without grant to create a user

If you are a previous version of MySQL3.22.11, you cannot use the grant (or R e V O K e) statement to establish user and access rights, but you can modify the contents of the authorization table directly. This problem is easy to understand if you understand how the GRANT statement modifies the authorization statement. You can do it yourself by manually publishing the INSERT statement (INSERT statement is difficult to enter correctly, but this is another issue.) In fact, the problem is why grant statements are designed so easily to be used.
When the GRANT statement is published, the user name and host number are specified, and the password may also be specified. The user table entry is established for the users, which are recorded in the U Ser, Host, and password columns of the item. One thing to consider carefully is that the GRANT statement encrypts the password, and the insert does not, and it needs to encrypt the password in the INSERT statement with the password () function.
If you have specified database-level permissions, the username and host names are recorded in the user and host columns of the DB table entry. Authorized databases are recorded in the DB column, and the permissions that are granted are recorded in the Permission column.
For table-level and column-level permissions, the effect is similar. Create entries in the Tables_priv and Columns_priv tables to record user names, host names and databases, and the tables or tables and columns that you want. The permissions that are granted are recorded in the Permission column.
If you remember the above discussion, grant can work even without the grant statement. Keep in mind that when you modify the authorization table directly, you need to tell the server to reload the authorization table, otherwise it will not be aware of these changes. You can impose a reload operation by executing the mysqladmin flush-privileges or mysqladmin reload command. If you forget
This step, the server will not work as you intended.
The following grant statement creates a Superuser with full permissions, including the ability to authorize other users:
Grant all ' *.* to ethel@localhost identfiedby ' coffee ' with GRANT OPTION
The statement creates the Ethel@localhost entry for the user table and turns on all permissions, because the super (global) user is stored there. The same statement for doing the same work with inserts is:

This is a hard to see INSERT statement! You may even find that it does not work on your MySQL version. The structure of the authorization table may have changed and there may not be 14 permission columns in the user table. At this point, you should use the S H O W COLUMNS to find out what permissions columns each of the authorization tables contains, and then adjust the INSERT statement.
The following grant statement also creates a user with Superuser status, but only for individual permissions:
GRANT all on *.* to Flush@localhost identfiedby "Flushpass"
You may recall that we used this statement when we established the flush user in chapter 11th. The INSERT statement in this example is simpler than the previous one, which makes it easy to list the column names and specify a unique permission column. All other columns will be set to the default value "N":

The permissions at the database level are granted with the on db_name.* clause rather than on *.*:
GRANT all on samp_db.* to Boris@localhost Identfiedby "Ruby"
These permissions are not global and therefore cannot be stored in the user table. We still need to create an item in the user table (so that the user can connect), but we also need to create a DB entry to log database-level permissions:

The "N" column is for grant permission, and the column should be set to "Y" for a database-level GRANT statement with Grant OPTION at the end.
To set table-level or column-level permissions, INSERT statements are used for TABLES_PRIV or Columns_priv tables. Of course, without the GRANT statement, these tables will not be available because they all appear in the MySQL version at the same time. If you have these tables, but for some reason you want to manipulate them manually, you cannot use a single column to make permissions valid. You either set up Tables _ Priv. Table_priv or set up c o l um n s _ priv. Column_priv is a set value that consists of the permissions you allow. For example, to allow select and insert permissions on a table, you should set the Table_priv column as a "Select, insert" value in the relative Tables_priv entry.
If you want to modify a user's permissions (the user's MySQL account already exists), you should use update rather than insert. This is true whether you are adding or canceling permissions. To remove a complete user, use Delete to delete the item from each authorization table that the user is in.
If you want to avoid publishing queries to directly modify the authorization table, take a look at the Mysqla C C e SS and Mysql_setpermissions scripts with the MySQL distribution.
A permission challenge, Part II
In chapter 11th, "One of the permissions challenges, the first part", we see an authorization situation in which permissions do not have the desired effect. Let's briefly describe the problem, and often a problem with the new MySQL installation is that the administrator wants to add an item to the user that allows them to connect from several hosts. The most straightforward way to do this is to use the hostname specifier that contains the '% ' wildcard character, so the administrator creates a user with a statement similar to the following:
GRANT all on samp_db.* to fred@%.snake.net identfiedby "cocoa"
Fred happened to have a server host account, so he tried to connect from here:

Why is that? To continue understanding, you must also consider how mysql_install_db establishes the initial authorization table and how the server uses the user table's entries to match the client connection. When the database is initialized by running Mysql_ i n s t a l _ db, it creates an entry with the user table with the following host and user values:

The first two items allow R o T to connect to the local host by specifying the localhost or host name. The latter two allow anonymous connections from the local host. When this item is added for Fred, the user table will contain these items:

When the server starts, it reads the entries and stores them (first host, then the host's user) in the order that the values are more detailed before, not detailed in the following:


Two items with localhost are stored together, and the item with root is placed in front of it because it is more specific and detailed than the blank. Items with P i t-v i per.snake.net are stored together in a similar way. These items are direct host values without any wildcard characters, so they all take precedence over the items in Fred, and the Fred entry uses a wildcard character. In particular, anonymous user entries take precedence over Fred's entries in the order in which they are stored.
As a result, when Fred tries to connect from a local host, an item match with a blank user name is preceded by an item that contains%.snake.net in the host column. The password for this entry is blank because the default anonymous user does not have a password. Because Fred specified a password at the time of the connection, an error match and an error connection are generated.
The thing to remember here is that although it is convenient to specify a host that users can connect to by using wildcards, you may have problems connecting from the local host as long as you keep anonymous users in the user table.
In general, I recommend that you delete anonymous user entries. This will make your job a little easier:
Mysql> DELETE from user WHERE user= "";
If you want to be more thorough, you can also remove anonymous user entries from other authorization tables. The table with the User column is db, Tables_priv, and C o l um n s _ priv.
The challenge presented in this section is a special case, but it contains more general content. If a user's permissions do not work the way you want, check the authorization table to see if there are some items that contain more detailed host values than the user's item being discussed, and the host value that attempts to match the connection through that user. If so, the cause of the problem may be it. You may need to make the user's entry more verbose (or add another item to include more details).