This article discusses the basic security feature that should be implemented when a MySQL database server is installed.
When the MySQL server is installed, the passwords for the super user, viz root is set to blank. Note that the MySQL 'root' user is different from the GNU/Linux super user 'root' and eventually the passwords are also different. Of course you can work without setting a 'root' password, but it's not advisable in a production server since anyone can access the database server. The basic security setup is a three step process.
and immediately you have to change the password for the root user.
Step 1: Setting the password for root user:
You can use the mysqladmin command to change the password of a fresh MySQL server :
# mysqladmin password xxxxxxx
Alternatively you can run MySQL queries to update the password. To run the queries you can connect to MySQL database using the following command:
# mysql -u root
This will provide you with a mysql prompt(mysql>)
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
This will set the 'root' password to new_password. The PASSWORD() function ensures that the password is encrypted.
Step 2: Removing anonymous accounts:
You should also remove all the anonymous accounts in MySQL user table, otherwise anonymous users can connect to the server without specifying the username and password. Anonymous users can't create or delete databases/tables. But they can read the system tables such as USER_PRIVILEGES, TRIGGERS, etc.. Run the following query to remove the anonymous users
mysql> DELETE FROM user WHERE User = '';
Step 3: Flush the privileges:
If you change the access privileges from the mysql prompt, don't forget to FLUSH the privileges to make the privileges effective.
mysql> FLUSH PRIVILEGES;
Remember that this shall provide only the basic security feature for the MySQL server. For advanced security topics, please refer to:
http://www.ice2o.com/secure_mysql.php
http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html
http://dev.mysql.com/doc/refman/5.0/en/security.html
