The authorization principle of MySQL Database

Server reboot situation

When the mysqld is started, all the authorization table contents are read into the memory and come into effect from then on.

Be immediately applied by the server

Modifications to the authorization table with GRANT, REVOKE, or set password are immediately noticed by the server.

Direct modification of Authorization form

If you manually modify the authorization form (using INSERT, UPDATE, and so on), you should execute a flush privileges statement or run Mysqladmin flush-privileges tell the server to reload the authorization form, or your changes will not take effect. Unless you reboot the server.

Impact on existing customer connections

When the server notices that the authorization table has been changed, existing customer connections have the following effects:

• Table and column permissions take effect on the customer's next request.

• Database permission changes take effect on the next use db_name command.

• Global permissions change and password changes take effect the next time a client connects.

Principle of authorization

No matter how careful it is inevitable in the user authorization to leave loopholes, I hope the following content can give you some help, you should generally abide by these rules.

Only the root user has the right to overwrite the authorization table

Do not grant authorization table overrides to users other than root (of course, if you can manage with another user instead of the root user to increase security). Because of this, the user can override the existing permissions by overwriting the authorization table. Create a security vulnerability.

In general, you may not make this mistake, but after installing the new distribution, the initial authorization form. This vulnerability exists and you may make a mistake if you do not understand the contents of the authorization table.

On Unix (Linux), after installing MySQL in accordance with the manual instructions, you must run the mysql_install_db script to build the MySQL database and initial permissions that contain the authorization table. On Windows, run the Setup program in the distribution to initialize the data directory and the MySQL database. Assume that the server is also running.

When you first install MySQL on a machine, the authorization table in the MySQL database is initialized like this:

• You can connect as root from the local host (localhost) without specifying a password. The root user has all the permissions (including administrative privileges) and can do anything. (By the way, MySQL Superuser and Unix superuser have the same name, they have nothing to do with each other.) ）

• Anonymous access is granted to a database that the user can start with a local connection named Test and any name Test_. Anonymous users can do anything with the database, but without administrative privileges.

Generally, it is recommended that you delete anonymous user records:

Mysql> DELETE from user WHERE user= "";

Further, delete any anonymous users in other authorization tables, and the table with the User column has db, Tables_priv, and Columns_priv.

Also, set the password for the root user.

About user, password, and host settings

• Use passwords for all MySQL users.

Remember, if Other_user does not have a password, anyone can simply log in as any other person with Mysql-u Other_user db_name. For client/server applications, it is common practice for customers to specify any user name. Before you run it, you can change the password of all users by editing the mysql_install_db script, or just the password of MySQL root, like this:

shell> mysql-u Root MySQL
mysql> UPDATE user SET Password=password (' New_password ')
-> WHERE user= ' root ';
mysql> FLUSH privileges;

• Delete anonymous users

The presence of anonymous users is not only an easy access rejection error, but also a serious security vulnerability, which automatically installs anonymous users after the authorization table is installed. By default, you can connect with any user name, do not require a password, and have permission to modify the authorization table.

You can delete anonymous users like this:

Shell>mysql–u root–p MySQL
Mysql>delete from user where user= "";

• Note the use of wildcard host name, as far as possible to narrow the scope of the host name, suitable for the user's host is enough, do not let users do not use the host to stay in the authorization table.

• If you do not trust your DNS, you should use the IP number instead of the hostname in the authorization form. In principle, the--secure option should make the host name more secure for mysqld. In any case, you should be very careful to use the hostname containing the wildcard character!

Give users the right permissions

• Empower users with sufficient permissions, and do not grant additional permissions.

For example, for users to retrieve only the needs of the data table, give select permission, can not give update, insert and other write permissions, do not be afraid to be said as a miser.

• Permissions that may create a security vulnerability

Grant permissions allow users to discard their permissions to other users. 2 users with different permissions and grant permissions can merge permissions.

ALTER permission can be used to override a permission system by renaming the table. Because ALTER permission may be used in any way that you do not envisage. For example, a user user1 can access table1, but cannot access table2. However, if the user user1 with ALTER permission, you might be able to disrupt your idea by renaming table2 to Table1 by using ALTER TABLE.

Shutdown permissions can be abused by terminating the server and completely denying service to other users.

• Permissions that may cause serious security vulnerabilities

Do not leave process permissions to all users. The output of the Mysqladmin processlist displays the currently executing query body, if another user issues an update user SET Password=password (' not_secure ') query, Any user who is allowed to execute that command may see it. MYSQLD maintains an additional connection for users with process privileges so that a MySQL root user can log in and check even if all the normal connections are in use.

Do not give file permissions to all users. Any user with this permission can write a file in a file system that has the privileges of the mysqld daemon! To make this safer, use SELECT ... All files generated by into outfile are readable to everyone and you cannot overwrite files that already exist.

File permissions can also be used to read any files that are accessible to UNIX users running the server. This can be abused because not only are users of the server's host account able to read them, but any client with file permissions can read them over the network. Your database directory and the various files of the system may become a worldwide shared file! For example, loading "/etc/passwd" into a database table by using load data, and then it can be read in with SELECT.

The following procedure explains how to do this:

1. A table with Longblob columns:

mysql> use test;
mysql> CREATE TABLE Temp (b longblob);

2, use this table to read the contents of the file you want to steal:

Mysql>load DATA INFILE "/etc/passwd" into TABLE temp
->fields escaped by "" LINES terminated by "";
Mysql>select * from temp;

3, you can steal your data form:

Mysql>load DATA INFILE "./other_db/data.frm" Into TABLE temp
->fields escaped by "" LINES terminated by "";
Mysql>select * from temp into outfile "./another_db/data.frm"
->fields escaped by "" LINES terminated by "";
Mysql>delete from temp;
Mysql>load DATA INFILE "./other_db/data. MyD "Into TABLE temp
->fields escaped by "" LINES terminated by "";
Mysql>select * from temp into outfile "./another_db/data. MyD "
->fields escaped by "" LINES terminated by "";
Mysql>delete from temp;
Mysql>load DATA INFILE "./other_db/data. Myi "Into TABLE temp
->fields escaped by "" LINES terminated by "";
Mysql>select * from temp into outfile "./another_db/data. Myi "
->fields escaped by "" LINES terminated by "";
Mysql>delete from temp;

The user then has a new table another.data that can be fully accessed.

Tasks that the MySQL permissions system cannot complete

There are some things you can't do with MySQL privilege system:

You cannot clearly specify that a given user should be denied access. That is, you cannot clearly match a user and then reject the connection.