MySQL Security Guide (2) ()
MySQL Security Guide (2) ()
The ORACLE tutorial is: MySQL Security Guide (2) (). MySQL Security Guide (2)
Author: Yan Zi
2.1.3 database and table Permissions
The following permissions apply to database and table operations.
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.
DELETE
Allows you to delete existing records from a table.
DROP
You can delete (discard) databases and tables, but cannot delete indexes.
INDEX
Allows you to create and delete indexes.
REFERENCES
No.
SELECT
You can use the SELECT statement to retrieve data from a table. It is unnecessary for SELECT statements that do not involve tables, such as select now () or SELECT 4/2.
UPDATE
Allow you to modify existing records in the table.
2.1.4 manage 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 .)
The Table_priv column in The tables_priv table is defined:
SET (Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter)
The Column_priv column in The coloums_priv table is defined:
SET (Select, Insert, Update, References)
The column permission is less than the table permission because the column-level permission is less meaningful. For example, you can create a table, but you cannot create an isolated column.
The user table contains some permissions that do not exist in other authorization tables: File_priv, Process_priv, Reload_priv, and Shutdown_priv. These permissions apply to operations that are irrelevant to any specific database or table that you allow the server to perform. It is meaningless to allow a user to shut down the database based on what the current database is.
2.2 how the server controls customer access
When you use MySQL, there are two phases of customer access control. The first phase occurs when you try to connect to the server. The server looks for 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 provided. If no match exists, you cannot connect. If there is a match, establish the connection and continue the second stage. At this stage, for each query you send, the server checks the authorization table to see if you have sufficient permissions to execute the query. The second stage continues until the end of your conversation with the server.
This section describes in detail the principles used by the MySQL server to match the authorization table entries for incoming connection requests or queries, this includes the types of valid values in the scope column of the authorization table, the methods used in combination with the permission information in the authorization table, and the order in which entries in the table are checked.
2.2.1 range column content
Some range columns require text 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 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 digits of 192.168128.
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 using the SQL mode character % or _ to specify a wildcard. One % or blank matches 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 ).
Table 3 case sensitivity of the range columns in the authorization table
Column
Host
User
Password
Db
Table_name
Column_name
Case sensitivity
No
Yes
Yes
Yes
Yes
No
2.2.2 query access Verification
Each time you send a query, the server checks whether you have sufficient permissions to execute it. It checks in the order of user, db, tables_priv, and columns_priv, knowing that you have the proper access permission or you have searched all the tables makes nothing possible. More specifically:
The server checks that the user table matches the record on which you start the connection to view what global permissions you have. If you have them and they are sufficient for the query, the server executes them.
If your global permissions are insufficient, the server will search for you in the db table and add the permissions in the record to your global permissions. If the query result is sufficient, the server
[1]
The ORACLE tutorial is: MySQL Security Guide (2) (). Run it.
If your global and database-level combinations do not have sufficient permissions, the server continues to search for them. First, in the tables_priv table, and then in the columns_priv table.
If you still do not have the permission after checking all the tables, the server rejects your attempt to execute the query.
In the term of Boolean operations, the permissions in the authorization table are used by the server as follows:
User OR tables_priv OR columns_priv
You may wonder why the previous description references only four authorization tables, but actually there are five. In fact, the server checks the access permission as follows:
User OR (db AND host) OR tables_priv OR columns_priv
The first simple expression is because the host table is not affected by the GRANT and REVOKE statements. If you always use GRANT and REVOKE to manage user permissions, you do not need to consider the host table. However, you should know how it works:
When the server checks the database-level permission, it queries the database table for the customer. If the Host column is empty, it means "Check the host table to find which Host can access the database ".
The server searches for db column values with the same records as those from the Db table in the host table. If no host record matches the client host, database-level permissions are not granted. If any of these records does have a Host column value that matches the connected client host, the database table record and the Host table record combine to generate the database-level permissions of the customer.
However, a permission is combined with a logic AND (AND), which means that the customer does not have this permission unless a given permission exists in both tables. In this way, you can grant a basic permission set in the db table, and then use the host table to selectively disable them for specific hosts. For example, you can allow all hosts in your domain to access the database, but disable the database permissions of hosts in less secure regions.
The previous descriptions undoubtedly make access checks sound quite complicated, especially when you think that the server performs permission checks on each query you send. However, this process is very fast, because the server does not actually search for information from each query from the authorization table, instead, it reads the table content into the memory at startup, and then verifies that the query uses a copy in the memory. This greatly improves the performance of access check operations. But there is a very obvious side effect. If you directly modify the authorization table content, the server will not know the permission change.
For example, if you use an INSERT statement to add a new user to the user table to add a new user, the user named in the record cannot connect to the server. This is confusing for new administrators (sometimes experienced veterans). At that time, the solution was simple: After you changed them, you told the server to reload the authorization table content, you can send a flush privileges or execute mysqladmin flush-privileges (or if you have an old version that does not support flush-privileges, use mysqladmin reload .).
2.2.3 matching sequence of range Columns
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. See the examples in the appendix.
2.3 avoid table authorization risks
This section describes some preventive measures during your authorization and the risks arising from the selection of unknown values. Generally, you need to grant the superuser permission in a very stingy way, that is, do not enable the permissions in the user table entries, but use other authorization tables, to restrict user permissions to databases, tables, or columns. Permissions in the user table allow operations on your server or access any table in any database.
Do not grant permissions on the mysql database. A user with the permission to include the authorized table database may modify the table to obtain the permission for any other database. Granting the permission to allow a user to modify a mysql database table actually grants the user a global GRANT permission. If you can directly modify a table, it is equivalent to issuing any GRANT statement you can imagine.
FILE Permission is especially dangerous. Do not authorize it easily. The following is what a person with FILE permissions can do:
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 has the content of your password file. In fact, the content of any publicly readable FILE on the server can be accessed by users with FILE permissions through the network.
The FILE Permission can also be exploited to harm databases on the system that do not have enough permission to set FILE permissions. This is why you should set the data directory to be read only by the server. If the files corresponding to the database table can be read by anyone, not only the users of the user server account can read them, but any users with FILE permissions can also connect to and read them through the network. The following shows the process:
Create a table with a LONGBLOB column:
USER test;
Create table tmp (B LONGBLOB );
Use this table to read the contents of each database table file you want to steal, and then write the table content to a file in your own database:
Load data infile "./other_db/x. frm" into table tmp
Fields escaped by "" lines terminated "";
SELECT * FROM tmp into outfile "y. frm"
Fields escaped by "" lines terminated "";
Delete from tmp;
Load data infile "./other_db/x. ISD" into table tmp
Fields escaped by "" lines terminated "";
SELECT * FROM tmp into outfile "y. ISD"
Fields escaped by "" lines terminated "";
Delete from tmp;
Load data infile "./other_db/x. ISM" into table tmp
Fields escaped by "" lines terminated "";
SELECT * FROM tmp into outfile "y. ISM"
Now you have a new table y that contains other_db.x and you have full access to it.
To avoid attacks in the same way, set the permissions on your data directory based on the commands in "first-part internal security-protect your data directory. You can also use the -- skip-show-database option when starting the server to restrict the use of show databases and show tables for DATABASES with no access permissions. This helps prevent users from finding information about databases and tables that they cannot access.
The ALTER permission can be used in an unwanted way. Suppose you want user1 to access table1 but not tables2. A user with the ALTER permission can use alter table to rename table2 to table1.
Beware of GRANT permissions. Two users with different permissions but with GRANT permissions can make their rights more powerful.
[2]
