mysql| Security | Security 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 that matches the connected client hosts
