MySQL Security Guide (2) (Turn)

mysql| Security | Security MySQL Security Guide (2)

Release Date: 2000-5-10
Content:
--------------------------------------------------------------------------------

MySQL Security Guide (2)

Author: Yan Zi


2.1.3 Database and Table permissions
The following permissions apply to operations on databases and tables.

Alter
Allows you to use the ALTER TABLE statement, which is actually a simple first level permission that you must have by other permissions, which see what you want to do with the database.
CREATE
Allows you to create databases and tables, but does not allow indexes to be created.
DELETE
Allows you to delete existing records from the table.
DROP
Allows you to delete (discard) databases and tables, but does not allow deletion of indexes.
INDEX
Allows you to create and delete indexes.
REFERENCES
Not at the moment.
SELECT
Allows you to retrieve data from a table using the SELECT statement. It is not necessary for a SELECT statement that does not involve a table, such as select Now () or select 4/2.
UPDATE
Allows you to modify the existing records in the table.
2.1.4 Administrative rights
The following permissions are used for administrative operations that control the operation of the server or user authorization capability.

FILE
Allows you to tell the server to read or write files on the server host. This permission should not be granted casually, it is dangerous, see "Evade authorization table risk". The server is indeed more cautious about keeping this right 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 data directories belonging to other people's databases.
If you authorize file permissions, make sure that you do not run the server as root for UNIX because root can create new files anywhere on the file system. If you run a server as an unprivileged user, the server can only create files in directories that are accessible to users.

GRANT
Allows you to grant your own permission to others, including Grant.
PROCESS
Allows you to view information about the threads (processes) that are running within the server by using the show process statement or the mysqladmin process command. This permission also allows you to kill the thread with the KILL statement or the Mysqladmin kill command.
You can always see or kill your own thread. Process permissions give you the ability to do these things to any thread.

RELOAD
Allows you to perform a number of server management operations. You can issue flush statements, and you can also refer to mysqladmin reload, refresh, flush-hosts, Flush-logs, Flush-privileges, and Flush-tables commands.
SHUTDOWN
Allows you to shut down the server with mysqladmin shutdown.
In the user, DB, and host tables, each permission is specified in a separate column. These columns are all declared as an enum ("N", "Y") type, so the default value for each right is "N". Permissions in Tables_priv and Columns_priv are represented as a set, which allows permissions to be specified in any combination with a single column. These two tables are newer than the other three tables, which is why they use a more efficient representation. (possibly in the future, user, DB, and host tables are also represented by a set type.) ）

The Table_priv column in the Tables_priv table is defined as:

SET (' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter ')
The Column_priv column in the Coloums_priv table is defined as:

SET (' Select ', ' Insert ', ' Update ', ' References ')
Column permissions are less than table permissions because of the lesser permissions at the column level. For example you can create a table, but you cannot create an orphaned column.

The user table contains columns for permissions that do not exist on other authorization tables: File_priv, Process_priv, Reload_priv, and Shutdown_priv. These permissions apply to actions that are not relevant to any particular database or table that you want the server to perform. It is pointless to allow a user to shut down a database based on what the current database is.

2.2 How the server controls customer access
When you use MySQL, there are two stages to customer access control. The first stage occurs when you try to connect to the server. The server looks up the user table to see if it can find an entry that matches your name, the host you are connecting from there, and the password you provide. If there is no match, you cannot connect. If there is a match, establish the connection and proceed to the second stage. At this stage, for each query you send out, the server checks the authorization table to see if you have sufficient permissions to execute the query, and the second stage continues until you end the conversation with the server.

This section details the rules used by MySQL servers to match authorization table entries to incoming connection requests or queries, including the type of valid value in the scope column of the authorization table, the way in which the permission information in the authorization table is combined, and the order in which entries in the table are checked.

2.2.1 Range Column Content
Some range columns require literal values, but most of them allow wildcard characters or other special values.

Host
A host column value can be either a hostname or an IP address. A value localhost means a local host, but it only matches when you use a localhost hostname, not when you are using the host name. If your local hostname is pit.snake.net and there are two records for you in the user table, one has a host value or localhost, and the other has a pit.snake.net, the localhost record will only match when you connect localhost, others in the connection P Match when It.snake.net. If you want to allow customers to connect in two ways, you need to have two records in the user table.

You can also specify a host value with a wildcard character. You can use the pattern characters "%" and "_" in SQL and have the same meaning when you use a LIKE operator in a query (no regex operators are allowed). SQL mode characters can be used for both host names and IP addresses. such as%wisc.edu matches any host within the wisc.edu domain, while%.edu matches any educational college host. 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 equal to%. (Exception: In the DB table, a blank host value is "Further checks the host table", as described in "Query access validation.") ）

From MySQL 3.23, you can also specify an IP address that indicates the network mask for the network address, such as 192.168.128.0/17 specifies a 17-bit network address and matches any host whose IP address is 192.168128 before 17 digits.

User
The user name must be literal or blank. A blank value matches any user. % as a user value does not mean blank, instead it matches a literal% name, which may not be what you want.

When a incoming connection is validated against the user table, the record that matches it contains a blank user value, and the customer is considered an anonymous subscriber.

Password
The password value can be either empty or non-empty, and wildcard characters are not allowed. A null password does not mean matching any password, it means that the user must not specify a password.

The password is stored as an encrypted value, not as a literal text. If you store a literal password in the password column, the user will not be able to connect! The GRANT statement and the mysqladmin Password command automatically encrypt passwords for you, but if you use commands such as INSERT, REPLACE, UPDATE, or set password, you must use password ("New_password") Instead of a simple "new_password" to specify a password.

Db
In the Columns_priv and Tables_priv tables, the DB value must be a true database name (literally), and no schema or whitespace is allowed. In DB and host, the DB value can be specified either literally or using the SQL mode character '% ' or ' _ ' to specify a wildcard character. A '% ' or white space matches any database.
Table_name,column_name
The values in these columns must be literal table or column names, and no pattern or whitespace is allowed.
Some range columns are considered case sensitive by the server, and the rest is not. These principles are summarized in the following table. Note that table_name values are always treated as case sensitive, even if the case sensitivity of the table names in the query is treated as if the file system of the host on which the server is running (Unix is case sensitive and Windows is not).

Table 3 case sensitivity for authorization table range columns
Column
Host
User
Password
Db
table_name
column_name
Case sensitivity
No
Yes
Yes
Yes
Yes
No


2.2.2 Query Access Verification
Every time you issue a query, the server checks to see if you have sufficient permissions to execute it, checking in the order of user, DB, Tables_priv, and Columns_priv, knowing that it determines that you have the appropriate access rights or that you have searched all the tables for nothing. More specifically:

The server checks that the user table matches the record you started the connection to see what global permissions you have. If you have and they are sufficient for the query, the server executes it.
If you have insufficient global permissions, the server looks for you in the DB table and adds the permissions in that record to your global permissions. If the result is sufficient for the query, the server executes it.
If your global and database-level combination of permissions is not enough, the server continues to find, first in the Tables_priv table, and then columns_priv the table.
If you do not have permission after checking all tables, the server rejects your attempt to execute the query.
In the terms of Boolean operations, permissions in authorization tables are used by the server:

User or Tables_priv OR columns_priv

You may be wondering why the previous description only refers to 4 authorization tables, but there are actually 5. In fact, the server checks access permissions like this:

User or (DB and host) or Tables_priv or Columns_priv

The first simpler expression is because the host table is not affected by the grant and REVOKE statements. If you always manage user rights with Grant and revoke, you never have to consider the host table. But its working principle you should know:

When the server checks database-level permissions, it looks for the DB table for the customer. If the host column is empty, it means "check the host table to find out which host can access the database."
The server looks in the host table for the same DB column value as the record from the DB table. If no host record matches the client host, database-level permissions are not granted. If any one of these records does have a host column value that matches the connection's client host, the DB table record and the host table record combine to generate the client's database-level permissions.
However, permissions are combined with a logical and (with), which means that the client does not have that permission unless a given permission is available in two tables. In this way, you can grant a basic set of permissions in the DB table and then use the host table to selectively disable them for a particular host. If you can allow access to the database from all hosts in your domain, but turn off the database permissions of those hosts in the less secure zone.

The preceding description undoubtedly makes the access check sound a rather complicated process, in particular, you assume that the server has permission checks on every query you make, but this process is fast because the server does not actually look up information from the authorization table for each query, instead it reads the contents of the table into memory at startup, Then verify that the query uses an in-memory copy. This greatly improves the performance of the access check operation. But there is a very obvious side effect. If you modify the contents of the authorization table directly, the server will not be aware of the change in permissions.

For example, if you add a new user by adding a new record to the user table with an INSERT statement, the user named in the record will not be able to connect to the server. This is very confusing for administrators--and sometimes for experienced veterans--when the solution is simple: After you change them, tell the server to overload the authorization table content, and you can send a flush privileges or execute mysqladmin Flush-privileges (or if you have an older version that does not support flush-privileges, use mysqladmin reload. ）。

2.2.3 Range Column Matching order
The MySQL server sorts the records in the authorization table in a particular way, and then the incoming connections are matched by a sequential browsing of the records. The first match found determines which records are used. It is important to understand the sort order used by MySQL, especially for the user table.

When the server reads the contents of the user table, it sorts records based on the values in the host and the user columns, and the host value is determined (the same host values are sorted together and then ordered according to the user value). However, the sort is not a code order (sorted by word), it is only partly. Keep in mind that the literal word takes precedence over the pattern. This means that if you are connecting to a server from Client.your.net and the host has client.your.net and%.your.net two values, the first one is selected. Similarly,%.your.net takes precedence over%.net, then%. The same is true for IP address matching.

In a word, the more specific the more priority. You can see the examples in the appendix of this article.

2.3 Avoid authorization table risk
This session introduces some of the precautions you have given when you authorize, as well as the risks associated with the choice of unknown value. Generally, you will be "stingy" to grant superuser privileges, that is, do not enable permissions in entries in the user table, and use other authorization tables to restrict user rights to databases, tables, or columns. Permissions in the user table allow you to affect the operation of your server or access any table in any database.

Do not grant permissions to the MySQL database. A user with permissions to the authorization table database might modify the table to obtain permissions on any other database. Granting permission to allow one user to modify a MySQL database table also actually gives the user a global grant permission. If the user can modify the table directly, it is equivalent to being able to emit any grant statement that you can imagine.

File permissions are particularly dangerous and do not authorize it easily. Here's what a person with file permissions can do to remove:

CREATE TABLE etc_passwd (pwd_entry TEXT);
LOAD DATA INFILE "/etc/passwd" into TABLE etc_passwd;
SELECT * from ETC_PASSWD;

After these statements are issued, the user already has the contents of your password file. In fact, any content that is publicly readable on the server can be accessed over the network by a user who has file permissions.

File permissions can also be used to compromise a database on a system that does not have permission to set sufficient rights. That's why you should set the data directory to read only by the server. If a file corresponding to a database table can be read by anyone, not just the user server account users can read, any users with file permissions can also connect and read through the network. This procedure is shown below:

Create a table with a LONGBLOB column:
USER test;
CREATE TABLE tmp (b longblob);

Use this table to read each content that corresponds to the database table file you want to steal, and then write the contents of the table to a file in your own database:

LOAD DATA INFILE "./other_db/x.frm" into TABLE tmp
FIELDS escaped by "" LINES terminated by "";
SELECT * from tmp into outfile "Y.FRM"
FIELDS escaped by "" LINES terminated by "";
DELETE from TMP;
LOAD DATA INFILE "./OTHER_DB/X.ISD" into TABLE tmp
FIELDS escaped by "" LINES terminated by "";
SELECT * from tmp into outfile "Y.ISD"
FIELDS escaped by "" LINES terminated by "";
DELETE from TMP;
LOAD DATA INFILE "./other_db/x.ism" into TABLE tmp
FIELDS escaped by "" LINES terminated by "";
SELECT * from tmp into outfile "Y.ism"
Now you have a new table Y, which contains the other_db.x content and you have carte blanche to access it.
To avoid being attacked in the same way, set the permissions on your data directory according to the instructions in the "first part of internal security-protecting your data directory." You can also use the--skip-show-database option when you start the server to restrict users to using show databases and show TABLES for databases to which they do not have access rights. This helps prevent users from finding information about databases and tables that they cannot access.

ALTER permission can be used in a way that is not desired. Suppose you want User1 to have access to Table1 but not to tables2. A user with ALTER permission can rename table2 to Table1 to cynical by using ALTER TABLE.

Beware of grant permissions. Two users with different permissions, but with grant permissions, can make each other's rights more powerful.