MySQL database authorization principles

Server restart

When mysqld is started, all authorization table content is read into the memory and takes effect from that time.

Immediately applied by the server

Modifications made to the authorization table using Grant, revoke, or set password will be immediately noticed by the server.

Directly modify the authorization table

If you manually modify the authorization table (using insert, update, and so on), you should execute a flush privileges statement or run mysqladmin flush-privileges to tell the server to load the authorization table again, otherwise, your change will not take effect unless you restart the server.

Impact on existing customer connections

When the server notices that the authorization table has been changed, the existing customer connection has the following impact:

· The table and column permissions take effect upon the customer's next request.

· The database permission change takes effect in the next use db_name command.

· Changes in global permissions and passwords take effect the next time a customer connects.

Authorization principles

No matter how careful you are, you will inevitably leave vulnerabilities when authorizing users. I hope the following content can help you. You should generally abide by these rules.

Only the root user has the permission to override the authorized table.

Do not grant the permission to rewrite the authorization table to other users except the root user (of course, if you can use another user instead of the root user for management to increase security ). In this way, you can overwrite the existing permissions by modifying the authorization table. Generate security vulnerabilities.

In general, you may not make this mistake, but after installing the new distribution, the initial authorization table. This vulnerability exists. If you do not know the content of the authorization table, you may make mistakes.

On Unix (Linux), after installing MySQL according to instructions in the manual, you must run the mysql_install_db script to create the MySQL database and initial permissions containing the authorization table. On Windows, run the setup program in the distribution to initialize the data directory and MySQL database. It is assumed that the server is also running.

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

· You can connect to the local host as root without specifying a password. Root users have all permissions (including management permissions) and can do anything. (By The Way, MySQL Super Users have the same name as UNIX Super Users, and they have nothing to do with each other .)

· Anonymous Access authorized users can connect to a database named test and any database named test _ locally. Anonymous Users can do anything to the database, but have no management permission.

Generally, we recommend that you delete anonymous user records:

Mysql> Delete from user where user = "";

Furthermore, you can delete any anonymous users in other authorization tables. Tables with user columns include dB, tables_priv, and columns_priv.

In addition, set the password for the root user.

User, password, and host settings

· Use passwords for all mysql users.

Remember, if other_user does not have a password, anyone can simply use MySQL-u other_user db_name to log on as any other person. For client/server applications, it is common for customers to specify any user name. Before you run it, You can edit the mysql_install_db script to change the passwords of all users, or just the MySQL Root Password, as shown in the following code:

Reference content is as follows: Shell> mysql-u root MySQL Mysql> Update user SET Password = PASSWORD ('new _ password ') -> Where user = 'root '; Mysql> flush privileges;

· Delete Anonymous Users

Anonymous users are not only prone to access denial errors, but also serious security vulnerabilities. After the authorization table is installed, anonymous users are automatically installed. You can use any user name to connect without a password and have the permission to modify the authorization table.

You can delete anonymous users as follows:

Reference content is as follows: Shell> mysql-u root-P MySQL Mysql> Delete from user where user = "";

· Pay attention to the use of the wildcard host name to minimize the host name range. This is sufficient for your host. Do not leave unused hosts in the authorization table.

· If you do not trust your DNS, you should use IP numbers instead of host names in the authorization table. In principle, the -- secure option should make the host name safer for mysqld. In any case, you should be very careful when using a host name containing wildcards!

Grant appropriate permissions to users

· Authorize users with sufficient permissions and do not grant additional permissions.

For example, if you only need to retrieve a data table, you can grant the select permission. You cannot grant update, insert, and other write permissions.

· Permissions that may generate Security Vulnerabilities

Grant permissions allow users to give up their permissions to other users. The two users with different permissions and grant permissions can merge the permissions.

The alter permission can be used to overturn the permission system by renaming the table. Because the alter permission may be used in any method you have not imagined. For example, a user named user1 can access Table 1 but cannot access table 2. However, if user1 has the alter permission, you may use alter table to rename Table2 to table1.

Shutdown permission can be abused by terminating the server to completely refuse to serve other users.

· Permissions that may generate severe security vulnerabilities

Do not grant the process permission to all users. The output of mysqladmin processlist shows the current query body. If another user sends an update user SET Password = PASSWORD ('not _ Secure ') query, any user allowed to execute that command may be able to see it. Mysqld retains an additional connection for a user with the process permission so that a MySQL Root User can log on and check it even if all normal connections are in use.

Do not grant the File Permission to all users. Any user with this permission can write a file in the file system with the mysqld daemon permission! To make this safer, all files generated using Select... into OUTFILE are readable to everyone, and you cannot overwrite existing files.

The File Permission can also be used to read any files that can be accessed by Unix users who are running servers. This may be abused because not only users with the server host account can read them, but any client with File Permission can also read them through the network. Your Database directory and various system files may be shared across the world! For example, load "/etc/passwd" into a database table by using load data, and then it can be read using select.

The following procedure describes how to perform this operation:

1. Create a table with longblob columns:

Mysql> use test;
Mysql> Create Table temp (B longblob );

2. Use this table to read the content 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 data from your data table as follows:

Reference content is as follows: 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;

Then the user has a new table another. Data, which can be fully accessed.

Tasks that cannot be completed by the MySQL Permission System

You cannot use the MySQL permission system to do the following:

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

You cannot specify a user with the right to create or discard a table in a database, nor create or discard the database itself.