Shulou(Shulou.com)05/31 Report--

MySQL database authorization principles, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Server restart

When mysqld starts, all authorization table contents are read into memory and take effect from then on.

The case of being applied immediately by the server

Changes made 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 table (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to load the authorization table, otherwise your changes will not take effect unless you restart the server.

Impact on existing customer connection

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

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

The database permission change takes effect on the next USE db_name command.

Global permission changes and password changes take effect the next time the customer connects.

Authorization principle

No matter how careful you are, you can't avoid leaving it when authorizing users. I hope the following content will give you some help. You should generally follow these rules.

Only root users have the right to rewrite the authorization table

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

In general, you may not make this error, but after installing the new distribution, the initial authorization table. This loophole exists, and you may make mistakes if you don't understand the contents of the authorization form at this time.

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

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

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

Anonymous access is granted to users to connect locally to a database with the name test and any database whose name starts with test_. Anonymous users can do anything to the database, but do not have administrative privileges.

In general, it is recommended that you delete anonymous user records:

Mysql > DELETE FROM user WHERE User= ""

Further, delete any anonymous users from other authorization tables, such as db, tables_priv, and columns_priv, with the User column.

In addition, set the password for the root user.

About the settings of users, passwords and hosts

Use a password for all MySQL users.

Remember, if other_user doesn't have a password, anyone can simply log in as anyone else with mysql-u other_user db_name. For client / server applications, it is a common practice that customers can specify any user name. Before you run it, you can change all users' passwords by editing the mysql_install_db script, or just MySQL root passwords, like this:

Shell > mysql-u root mysql

Mysql > UPDATE user SET Password=PASSWORD (new_password)

-> WHERE user=root

Mysql > FLUSH PRIVILEGES

Delete anonymous user

The existence of anonymous users is not only easy to cause access denial errors, but also produce serious security vulnerabilities. After installing the authorization table, anonymous users are installed automatically. By default, you can connect with any user name, do not need 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= ""

Pay attention to the use of wildcard host names, try to narrow the scope of the host name, suitable for the user's host is enough, do not let the user does not use the host in the authorization table.

If you don't trust your DNS, you should use the IP number instead of the hostname in the authorization table. In principle, the-- secure option should make hostnames more secure for mysqld. In any case, you should be very careful with hostnames that contain wildcards!

Grant appropriate permissions to the user

Authorize the user to use sufficient permissions, do not give additional permissions.

For example, for the requirement that users only need to retrieve data tables, you can grant SELECT permissions, but not write permissions such as UPDATE, INSERT, etc., and don't be afraid to be called a miser.

Permissions that may create security vulnerabilities

Grant permissions allow users to relinquish their permissions to other users. Two users with different permissions and grant permissions can merge permissions.

The alter permission can be used to override the permission system by renaming the table. Because ALTER permissions can be used in any way you don't envision. For example, a user user1 can access table1, but not table2. But if the user user1 has ALTER privileges, you may upset your imagination by using ALTER TABLE to rename table2 to table1.

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

Permissions that may create serious security vulnerabilities

Do not give PROCESS permissions to all users. The output of mysqladmin processlist shows the body of the currently executed query, which may be visible to any user who is allowed to execute that command if another user issues a UPDATE user SET password=PASSWORD (not_secure) query. Mysqld reserves an additional connection for users with process privileges so that a MySQL root user can log in and check, even if all normal connections are in use.

Do not give FILE permissions to all users. Any user with this permission can write a file on a file system with mysqld daemon privileges! 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 file that is accessible to the Unix user running the server. This can be abused because not only can users with the server host account read them, but any client with FILE privileges can also read them over the network. Various files in your database directory and system may become files shared around the world! For example, by loading "/ etc/passwd" into a database table using LOAD DATA, it can then be read in using SELECT.

The following procedure shows how to do this:

1. Create a table with LONGBLOB column:

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 datasheet data like this:

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 accomplished by the MySQL permission system

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

You cannot explicitly specify that a given user should be denied access. That is, you can't obviously match a user and then refuse to connect.

You cannot specify that a user has the right to create or discard tables in a database, nor can you create or discard the database itself.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.