Mysql security, mysql

Mysql security, mysql

1. Set the directory permission correctly:
The principle of setting directory permissions is to separate software from data, as follows:
1. Install mysql under a separate user
2. During installation, the installation is performed as the root user. By default, the mysql software has the root permission.
3. After installation, set the data directory permission to the user permission for running mysql, for example:

Chown –R mysql:mysql /home/mysql/data

2. Avoid using the root permission to run mysql as much as possible:
After setting directory permissions, you can start, stop mysql, and perform routine maintenance under the mysql user, there is no need to su to root and then use-user = mysql to start and close mysql, so there is no need to authorize the maintenance staff root permission, the most important thing is that any user with FILE permissions can use root to create files.

3. delete an anonymous account:
After mysql is installed, an empty account is automatically installed. Normal users only need to execute the mysql command to log on to mysql, causing potential risks to the system. We recommend that you delete this empty account:

 

4. Set a password for the mysql root Account:
After Mysql is installed, the default root password is blank. You need to change the root password immediately:

[zzx@localhost data]$ mysql –urootmysql> set password=password('123');Query OK, 0 rows affected (0.00 sec)

5. Set the security password and modify it regularly:
Use a secure password whenever possible. We recommend that you use a string consisting of more than six letters, numbers, underlines, and special characters.

6. Grant only account permissions:
You only need to grant the required permissions to common users, for example:

Grant select,insert,update,delete on tablename to ‘username’@’hostname’;

7 except root, no user shall have access to the mysql database user table:
If you have the permission to access the user table (select, update, insert, and delete) in the mysql database, you can easily add, modify, and delete other user permissions, causing system security risks.

8. Do not grant FILE, PROCESS, or SUPER permissions to accounts other than administrators:
The FILE permission can be abused to read any files that MySQL can read on the server host into the database table. Include anyone-readable files and files in the server data directory. You can use SELECT to access the database table and transmit its content to the client.

Do not grant FILE permissions to non-administrator users. Any user with this permission can write a file in the file system with the mysqld daemon permission! For greater security, all files generated by SELECT... into outfile are writable to everyone, and you cannot overwrite existing files.

9. Security issues caused by load data local:
Transmission of the MySQL server startup file from the client to the server host. Theoretically, the patched server can tell the client program to transmit the selected files from the server, rather than the Files specified by the client using the load data statement. In this way, the server can access any file on the client that the client has read access permissions. In the Web environment, the customer connects from the Web server, you can use load data local to read any files that the Web server process has read access permissions (assuming that you can run any commands on the SQL Server ). In this environment, the MySQL server is actually a Web server, rather than a program run by the user who connects to the Web server.
Solution:
Available

--local-infile=0 

Enable mysqld to disable all load data local commands from the server.

For the mysql command line client, you can enable load data local by specifying the -- LOCAL-infile [= 1] option, or disable it using the -- local-infile = 0 option. Similarly, for mysqlimport, -- local or-L options, enable local data file loading. In any case, the server needs to enable related options for successful local loading.

10 avoid using symlinks to access tables as much as possible:
Do not allow table symbolic links. (You can disable the -- skip-symbolic-links option ). If you use root to run mysqld, it is particularly important because anyone with write access to the server's data directory can delete any files in the system!

11 The drop table command does not revoke the previous access authorization:
When you drop a table, the permissions of other users are not revoked. As a result, when you recreate a table with the same name, the permissions of other users are automatically granted to the table, resulting in permission outflow. Therefore, when deleting a table, you must cancel the corresponding permissions of other users on the table.

 

12 if possible, add access IP address restrictions to all users:
Adding an ip address limit to all users will deny connections from all unknown hosts and ensure that only trusted hosts can be connected. For example:

Grant select on dbname.* to ‘username’@’ip’ identified by ‘passwd’;

13. Strict control over operating system accounts and permissions:
Strictly control the account and permissions of the operating system on the database server, for example, locking a mysql user
All other users log on with an independent account. The administrator can use a common user to manage mysql, or use root su to manage mysql. Modifying any resources of a mysql user is prohibited.

14 add a Firewall:

15 other security settings:
1 allow-suspicious-udfs:
This option controls whether user-defined functions with only xxx characters can be loaded into the main function. By default, this option is disabled and can only load udfs with at least auxiliary characters. This prevents functions from loading shared object files that never contain valid udfs.

2 old-passwords:
Force the server to generate a short (pre-4.1) password hash for the new password. It is useful to ensure compatibility when the server must support Old Client versions.

 

3 safe-user-create:
If enabled, you cannot use the GRANT statement to create a new user, unless you have the INSERT permission for the mysql. user table. If you want to authorize a user to create a new user, you must grant the following permissions to the user:

mysql> GRANT INSERT(user) ON mysql.user TO 'user_name '@'host_name';

In this way, you cannot directly change the permission column. You must use the GRANT statement to GRANT this permission to other users.

4 secure-auth:
An account with an old (pre-4.1) password cannot be authenticated.

5 skip-grant-tables:
This option causes the server to not use the permission system at all. This gives everyone the right to access all databases! (By executing the mysqladmin flush-privileges or mysqladmin reload command, or executing the flush privileges statement, you can tell a running server to start using the authorization table again .)

6 skip-networking:
TCP/IP connections are not allowed on the network. All connections to mysqld must be made through Unix sockets.

7 skip-show-database:
This option allows only users with the show databases permission to execute the show databases statement. This statement displays all database names. If this option is not used, all users are allowed to execute show databases, but only the names of the DATABASES with show databases or partial database permissions are displayed. Note that global permissions refer to database permissions.