MySQL Security Guide (2) (redirected)

MySQL Security Guide (2)


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

ALTER
allows you to use ALTER TABLE statement, this is actually a simple first-level permission. You must have other permissions, depending on what operations you want to perform on the database.
CREATE
allows you to create databases and tables, but not indexes.
DELETE
allows you to delete existing records from a table.
DROP
allows you to drop (throw away) databases and tables, but not indexes.
INDEX
allows you to create and delete indexes.
REFERENCES
Not currently in use.
SELECT
allows you to retrieve data from a table using the SELECT statement. It is not necessary for SELECT statements that do not involve tables, such as SELECT NOW() or SELECT 4/2.
UPDATE
allows you to modify existing records in the table.
2.1.4 Administrative permissions
The following permissions are used for administrative operations that control the operation of the server or user authorization capabilities.

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 "Avoiding Authorization Table Risks". The server does take some care to keep this permission within limits. You can only read files that can be read by anyone. The file you are writing must not exist. This prevents you from forcing the server to rewrite important files such as /etc/passwd or data directories belonging to someone else's database.
If you grant FILE permissions, make sure you do not run the server as the UNIX root user, because root can create new files anywhere in the file system. If you run the server as an unprivileged user, the server can only create files in directories accessible to the user.

GRANT
allows you to grant your own permissions to others, including GRANT.
PROCESS
allows you to use SHOW PROCESS statement or mysqladmin process command to view information about threads (processes) running in the server. This permission also allows you to use the KILL statement or mysqladmin The kill command kills threads.
You can always see or kill your own threads. PROCESS permission gives you the ability to do these things on any thread.

RELOAD
allows you to perform a wide range of server management operations. You can issue the FLUSH statement, and you can also refer to mysqladmin's 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 of each weight is "N". Permissions in tables_priv and columns_priv are represented by a SET, which allows permissions to be specified in any combination with a single column. These two tables are newer than the other three, which is why they use a more efficient representation. (It is possible that in the future, the user, db and host tables will also be 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 permissions with fewer column levels make sense. For example you can create a table, but you cannot create an isolated column.

The user table contains certain permission columns that do not exist in other authorization tables: File_priv, Process_priv, Reload_priv and Shutdown_priv. These permissions apply to operations you ask the server to perform that are not related to any specific database or table. It makes no sense to allow a user to close a database based on what is currently in the database.

2.2 How the server controls client access
When you use MySQL, there are two stages of client access control. The first phase occurs when you try to connect to the server. The server looks in the user table to see if it can find an entry that matches your name, the host you are connecting from, and the password you provided. If there is no match, you can't connect. If there is a match, establish the connection and continue with the second phase. In this phase, for every query you issue, the server checks the authorization table to see if you have sufficient permissions to execute the query. The second phase continues until the end of your conversation with the server.

This section details the principles used by the MySQL server to match authorization table entries to incoming connection requests or queries. This includes the types of legal values ​​​​in the authorization table range column, the way to combine the permission information in the authorization table, and the table. The order in which entries are checked.

2.2.1 Range Column Contents
Some range columns require literal values, but most of them allow wildcards or other special values.

Host
A Host column value can be a host name or an IP address. The value localhost means localhost, but it only matches when you use a localhost hostname, not when you use a hostname. If your local host name is pit.snake.net and there are two records for you in the user table, one with a Host value or localhost, and the other with pit.snake.net, the record with localhost will only be treated as It matches when you connect to localhost, and others only match when you connect to pit.snake.net. If you want clients to be able to connect in two ways, you need two records in the user table.

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

% 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 means "further check the host table". This process is introduced in "Query Access Verification".)

From MySQL Starting with 3.23, you can also specify an IP address with an indication of the netmask used for the network address, such as 192.168.128.0/17 which specifies a 17-bit network address and matches any host whose IP address is the first 17 digits of 192.168128.

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

When an incoming connection is authenticated through the user table and the matching record contains a blank User value, the client is considered an anonymous user.

PassWord
The password value can be empty or non-empty, wildcards are not allowed. An empty password does not mean that any password will be matched; it means that the user must not specify a password.

The password is stored as an encrypted value, not a literal text. If you store a literal password in the Password column, users will not be able to connect! GRANT statement and mysqladmin The password command automatically encrypts passwords for you, but if you use commands such as INSERT, REPLACE, UPDATE, or SET For commands such as PASSWORD, be sure to use PASSWORD("new_password") instead of simply "new_password" to specify the password.

Db
In columns_priv and tables_priv tables, the Db value must be the real database name (literally), patterns and whitespace are not allowed. In db and host, the Db value can be specified literally or a wildcard can be specified using the SQL pattern characters '%' or '_'. A '%' or blank matches any database.
Table_name, Column_name
The values ​​in these columns must be literal table or column names, patterns and whitespace are not allowed.
Some range columns are considered case-sensitive by the server, the rest are not. These principles are summarized in the table below. Note in particular that Table_name values ​​are always treated as case-sensitive, even though the case-sensitivity of table names in queries depends on the file system of the host the server is running on (UNIX is case-sensitive, Windows is not).

Table 3 Case sensitivity of grant 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 if you have sufficient permissions to execute it, it checks in order user, db, tables_priv and columns_priv until it determines you have the appropriate access or has searched all tables Nothing was found. More specifically:

The server checks the user table for records matching the one you started connecting from to see what global permissions you have. If you have and they are sufficient for the query, the server executes it.
If your global permissions are not enough, the server will search the db table for you and add the permissions in the record to your global permissions. If the results are sufficient for the query, the server executes it.
If your combined global and database-level permissions are insufficient, the server continues to look, first in the tables_priv table, then in the columns_priv table.
If you still do not have permission after checking all tables, the server rejects your attempt to execute the query.
In Boolean terms, the permissions in the authorization table are used by the server like this:

user OR tables_priv OR columns_priv

You may wonder 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 GRANT and REVOKE statements. If you always use GRANT and REVOKE to manage user permissions, you never need to think about the host table. But you should know how it works:

When the server checks database level permissions, it looks up the db table for the client. 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's host, database-level permissions are not granted. If any of these records does have a Host column value that matches the connected client's host, the db table record and the host table record are combined to produce the client's database-level permissions.
However, permissions are combined with a logical AND, which means that unless a given permission is present in both tables, the client does not have that permission. In this way, you can grant a basic set of permissions in the db table and then selectively disable them for specific hosts using the host table. For example, you can allow database access from all hosts in your domain, but turn off database permissions for hosts in less secure zones.

The previous description undoubtedly makes access checking sound like a rather complicated process, especially if you think that the server performs a permission check on every query you issue. However, this process is very fast because the server does not actually check the authorization table. It looks up information for each query. Instead, it reads the contents of the table into memory at startup and then verifies that the query is using an in-memory copy. This greatly improves the performance of access checking operations. But there is a very obvious side effect. If you directly modify the contents of the authorization table, the server will not know about the permission changes.

For example, if you use an INSERT statement to add a new record to the user table to add a new user, the user named in the record will not be able to connect to the server. This was very confusing to new administrators (and sometimes to experienced veterans), and the solution at the time was simple: tell the server to reload the authorization table contents after you changed them. You could 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 specific way and then matches incoming connections by browsing the records in order. The first match found determines which record is 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 the records according to the values ​​in the Host and User columns. The Host value plays a decisive role (the same Host values ​​are arranged together, and then sorted according to the User value). However, sorting is not allodic ordering (ordering by words), it is only partially so. The thing to remember is that literal words take precedence over patterns. This means that if you are connecting to the server from client.your.net and Host has two values, client.your.net and %.your.net, the first one will be selected first. Similarly, %.your.net takes precedence over %.net, then %. The same goes for IP address matching.

In a word, the more specific the priority. See the appendix of this article for examples.

2.3 Avoid authorization table risks
This session introduces some precautions when you authorize, as well as the risks caused by choosing unknown values. In general, you should be very "stingy" in granting superuser permissions, that is, do not enable permissions in entries in the user table, but use other authorization tables to limit user permissions to the database, table, or column. Permissions in the user table allow access to any table in any database that affects your server operations.

Do not grant permissions to mysql database. A user with permissions on the database containing the authorization table may modify the table to gain permissions on any other database. Granting permissions that allow a user to modify mysql database tables also effectively gives the user a global GRANT permission. If the user can directly modify the table, this is equivalent to being able to issue any GRANT statement you can imagine.

FILE permission is particularly dangerous, don’t grant it easily. The following is what a person with FILE permission can do:

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

After issuing these statements, the user already has the contents of your password file. In fact, the contents of any publicly readable file on the server can be accessed through the network by users with FILE permissions.

FILE permissions can also be exploited to compromise databases on systems that do not have sufficiently restrictive file permissions set. This is why you should set up the data directory to be readable only by the server. If the files corresponding to the database tables can be read by anyone, not just users with the user's server account, any user with FILE permissions can also connect over the network and read them. This process is demonstrated below:

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

Use this table to read the contents of each file corresponding to the database table you want to steal, and then write the table contents 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 that contains the contents of other_db.x and you have full access to it.
To avoid others attacking in the same way, according to "Part 1 Set permissions on your data directory using the instructions in "Internal Security - Protect Your Data Directory". You can also use the --skip-show-database option when you start the server to restrict users to databases to which they do not have access permissions. SHOW DATABASES and SHOW TABLES. This helps prevent users from finding information about databases and tables that they cannot access.

ALTER permissions can be used in unintended ways. Suppose you want user1 to have access to table1 but not tables2. A user with ALTER permission can use ALTER TABLE renames table2 to table1 to change the column.

Beware of GRANT permissions. Two users with different permissions but both with GRANT permissions can make each other's rights stronger.

The above is the content of MySQL Security Guide (2) (reprint). For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!