MySQL offers simple but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.
In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements:
* MySQL processes must run under a unique UID/GID that is not used by any other system process.
* Only local access to MySQL need to be allowed.(some exceptions for jobs/backups)
* MySQL root's account must be protected by a complex/hard to guess password.
* The administrator's account (root) need to be renamed.
* Anonymous access to the database (by using the nobody account) must be disabled.
MySQL Security risks can be categorized into the following.
* Filesystem security risks. MySQL Installation (basedir) and database information (datadir) and other log (querylog/slowlog) files that contain information about queries that clients execute. These files/directories need to be protected so that other users who have login accounts on the server host cannot access them directly.
* Network security risks. The MySQL server provides access to databases by allowing clients to connect and make requests. Information about client accounts is stored in the mysql database. Each account should be set up with privileges that provide access only to the data the accounts needs to see or modify.
MySQL root account has full privileges to perform any database operation, so it's important to assign the account a password that is not easily guessed. Note that usernames and passwords for MySQL accounts are unrelated to those for system login accounts. OS login and MySQL login both should not be the same.
Restrict anonymous remote access
Grant access to specific users from specific hosts only. Do not grant access from all hosts.
Do not grant the PROCESS or SUPER privilege to non-administrative users. The output of mysqladmin processlist and SHOW PROCESSLIST shows the statements currently being executed, so any user who is allowed to see the server process list might be able to see statements issued by other users such as UPDATE user SET password=PASSWORD(pwd).
mysqld reserves an extra connection for users who have the SUPER privilege, so that a MySQL root user can log in and check server activity even if all normal connections are in use. This is very useful when MySQL reaches the max_connections threshold. This cannot be beneficial; if users root (which ever has SUPER privilege) is used for all client connections.
Do not grant the FILE privilege to non-administrative users. Any user that has this privilege can write a file anywhere in the file system with the privileges of the mysqld daemon. To make this a bit safer, files generated with SELECT ... INTO OUTFILE do not overwrite existing files and are writable by everyone.
The FILE privilege may also be used to read any file that is accessible to the Unix user that the server runs as. With this privilege, user can read any file into a database table. This could be abused, for example, by using LOAD DATA to load /etc/passwd into a table, which then can be displayed with SELECT.
Improve local security
Use different socket file for both client and server connections. The following parameter should be changed in the [client] section of /etc/my.cnf:
[client] socket = /tmp/mysql.sock
Change admin password
One of the most important steps in securing MySQL is changing the database administrator's password, which is empty by default. In order to change the administrator's password, follow the steps:
mysql -u root mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
It is good practice not to change passwords from the command line,(instead change at database level). This is especially important when other users working on the server. In that case the password could be easily revealed, e.g. by using the "ps aux" command or reviewing history files, when improper access rights are set to them.
Change admin name
It is also recommended to change the default name of administrator's account (root), to a different, harder to guess one. Such a change will make it difficult to perform brute-force and dictionary attacks on the administrator's password.
mysql> update user set user="mysqluser" where user="root"; mysql> flush privileges;
Remove history
We should also remove the content of the MySQL history file (~/.mysql_history, ~/.history, ~/.bash_history,~/.mysql_history) in which all executed SQL commands are being stored (especially passwords, which are stored as plain text).
User Access Privileges
We can create accounts for specific databases which will be used by specific applications. These accounts should have access rights only to the databases which are used by the specific applications. In particular, they should not have any access rights to the mysql database, nor any system or administrative privileges (FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, SUPER etc.). Application users should not granted all privileges to database with Grant option from any host.
mysql> select user,host,password from user; GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD 'xxxxxx' | GRANT ALL PRIVILEGES ON `user1`.* TO 'glist'@'%' WITH GRANT OPTION
