Novell Cool Solutions

Tightening MySQL Security



By:

February 27, 2008 3:15 am

Reads:6,067

Comments:0

Score:Unrated

Print/PDF

Tightening MySQL Security

In this article we are going to look at tightening security within the MySQL daemon. We will look at creating new users and setting limits on there account such as; how many connections they are allowed, the number of SQL (Structured Query Language) statements they can execute per hour and many others.

“MySQL is the world’s most popular open source database software, with over 100 million copies of its software downloaded or distributed throughout its history. With superior speed, reliability, and ease of use, MySQL has become the preferred choice of corporate IT Managers because it eliminates the major problems associated with downtime, maintenance, administration and support.” (MySQL, 2008).

Installation

The installation of MySQL is very simple, you can use the YaST installation utility which will install the MySQL daemon and any dependencies that are required. The YaST utility can be run by either typing “yast sw_single” which starts a curses interface or by typing “yast2 sw_single” which starts a GUI (Graphical User Interface). Once the installation screen appear you can perform a search for the MySQL daemon by searching for the keyword “mysql”, you will also need to install the “mysql-client”.

Configuring MySQL

Once you have installed the MySQL daemon and the MySQL client you can start the MySQL daemon by issuing the “service” command as shown in Figure 1.

linux-48l4:~ # service mysql start

Creating/Updating MySQL privilege database... 

Installing all prepared tables
Fill help tables
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h linux-48l4.site password 'new-password'
See the manual for more instructions.

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:

cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com
Updating MySQL privilege database... 
Fixing privilege tables... 
Starting service MySQL                                                done

linux-48l4:~ #

Figure 1: Starting the MySQL daemon.

Once the MySQL daemon has started you will get instructions on how to set the root password for the MySQL daemon, however, there is a utility which is packaged with the MySQL RPM called: “mysql_secure_installation“. The “mysql_secure_installation” utility asks multiple about you’re current setup as shown in Figure 1.1. I would strongly recommend denying remote root logins, remove the anonymous user account and remove the test database.

linux-48l4:~ # mysql_secure_installation



NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

linux-48l4:~ # 

Figure 1.1: Tightening MySQL security.

Once the “mysql_secure_installation” utility has finished you will now be able to login to the MySQL daemon using the MySQL client as shown in Figure 1.2.

linux-48l4:~ # mysql -h localhost -u root -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.26

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

Figure 1.2: Logging into the MySQL daemon.

Creating users

In this section of the article we will look at creating users and setting resource limits on individual users. In this article I will assume you already have a database which you would like to assign user(s) to, the database I will be using is called “accounting”.

The first user that we will create is called: “damian”, we will allow this user to have full access to the “accounting” database, however, we will only allow this user to connect from a specific IP (Internet Protocol) address of “192.168.2.34”. Figure 2 shows the SQL statements used to create this user and Table 1 explains each section of the SQL statement.

mysql> GRANT ALL ON accounting.* TO 'damian'@'192.168.2.34' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Figure 2: Creating the user “damian”.

Section Description
GRANT ALL This section of the statement sets the privileges. In this example we have given the user full access as indicated by the keyword ALL. If you need to tighten security for this user you could use keywords such as: “UPDATE”, “SELECT”, etc to only allow the user to perform the specified commands.
ON accounting.* This section of the statement specifies which database these privileges apply to. The accounting.* means that the privileges apply to the accounting database and any tables that are within the accounting database indicated by the asterisk(*).
TO ‘damian’@’192.168.2.34′ This section of the statement sets the user who can access this database and where they can connect from. In our example the user damian can only connect from the 192.168.2.34 IP address if the IP address is not matched authentication will fail.
IDENTIFIED BY ‘password'; This section of the statement set a password for the user damian.

Table 1: Figure 2 SQL statement explained.

Once you have created the user you can issue the “SHOW GRANTS FOR” statement as shown in Figure 2.1 to check the users privileges.

mysql> SHOW GRANTS FOR 'damian'@'192.168.2.34';
+------------------------------------------------------------------------------------------------------------------+
| Grants for damian@192.168.2.34                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'damian'@'192.168.2.34' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | 
| GRANT ALL PRIVILEGES ON `accounting`.* TO 'damian'@'192.168.2.34'                                                | 
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Figure 2.1: Viewing the user “damian” permissions.

Now that we have created a user that has full access to the “accounting” database we will create a user called: “jason” who will only be able to issue the “SELECT” statement. Figure 2.2 shows the SQL statements which we will use to create the user “jason” and Table 2 explains each section of the SQL statement.

mysql> GRANT SELECT ON accounting.* TO 'jason'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Figure 2.2: Creating the user “jason” with SELECT only privileges.

Section Description
GRANT SELECT This section of the statement sets the privileges. In this example we are only allowing the SELECT statement to be used all other statements will be denied.
ON accounting.* This section of the statement specifies which database these privileges apply to. The accounting.* means that the privileges apply to the accounting database and any tables that are within the accounting database indicated by the asterisk(*).
TO ‘jason’@’%’ This section of the statement creates the user “jason” and with the wild card (%) allows the user “jason” to connect from any IP address.
IDENTIFIED BY ‘password'; This section of the statement sets the users password.

Table 2: Figure 2.2 explained.

Once the user has been successfully created you can connect as the user “jason” and issue the SELECT statement then followed by a statement that was not specified in the GRANT statement as shown in Figure 2.3.

mysql> SELECT * FROM test;
+--------+------------+---------------+
| name   | surname    | tel_no        |
+--------+------------+---------------+
| Damian | Myerscough | 1234567894378 | 
| Jason  | Myerscough | 1234567894378 | 
| Chisa  | Hasegawa   | 1234567894378 | 
+--------+------------+---------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test VALUES ("Joe", "Blogs", "111111111111");
ERROR 1142 (42000): INSERT command denied to user 'jason'@'localhost' for table 'test'
mysql> 

Figure 2.3: Testing the users privileges

Limiting number of connections

In this section of the article we are going to limit the user “chisa” to two simultaneous connections and any more will be denied. The table that we will be working with is called: “user” which is located in the within the “mysql” database. Figure 3 shows the SQL statements that we will use to enforce a connection limit, Table 3 explains each section of the SQL statement.

mysql> UPDATE user SET max_user_connections=2 WHERE User="chisa";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Figure 3: Restricting the user “chisa” to two simultaneous connections.

Section Description
UPDATE user This section of the SQL statement specifies which table we would like to update.
SET max_user_connections=2 This section of the SQL statement sets the value of “max_user_connections” to be updated with the value of two.
WHERE User=”chisa”; This section of the SQL statement specifies which user this should apply to.

Table 3: Figure 3 explained.

Once you have set the restriction you can try and make more than three simultaneous connections as the user “chisa”, the third connection will be denied as shown in Figure 3.1.

linux-48l4:~ # mysql -h 172.25.147.181 -u chisa -p
Enter password: 
ERROR 1226 (42000): User 'chisa' has exceeded the 'max_user_connections' resource (current value: 2)

Figure 3.1: Checking the connection limit.

Limiting the number of queries per hour

In this section of the article we are going limit the user “chisa” to only two queries per hour. We will be working in the same database and with the same table as we did in the “Limiting number of connections” section. Figure 4 shows the SQL statements used to limit the the number of queries per hour, Table 4 explains each section of the SQL statement.

mysql> UPDATE user SET max_questions=2 WHERE User="chisa";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Figure 4: Limiting the user “chisa” to perform only two queries per hour.

Section Description
UPDATE user This section of the SQL statement specifies which table we would like to update.
SET max_questions=2 This section of the SQL statement sets the value of “max_questions” to be updated with the value of two.
WHERE User=”chisa”; This section of the SQL statement specifies which user this should apply to.

Table 4: Figure 4 explained.

The MySQL daemon will reset the users query count automatically after one hour, thus allowing the user to make queries again until there limit has been exceeded again.

Limiting the number of updates per hour

In this section of the article we are going to limit the number of updates that the user “chisa” can issue. Figure 5 shows the SQL statements used to enforce this limit, Table 5 explains each section of the SQL statement.

mysql> UPDATE user SET max_updates=2 WHERE User="chisa";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Figure 5: Setting UPDATE limits.

Section Description
UPDATE user This section of the SQL statement specifies which table we would like to update.
SET max_updates=2 This section of the SQL statement sets the value of “max_updates” to be updated with the value of two.
WHERE User=”chisa”; This section of the SQL statement specifies which user this should apply to.

Table 5: Figure 5 explained.

Once you have applied the update limit to the user “chisa”, this user will be unable to perform more than two UPDATE statements per hour.

If you need to remove a resource limit you can issue the UPDATE statement and set the value of the resource to zero, which indicates unlimited. In the examples that I provided you may have noticed a “FLUSH PRIVILEGES” statement after each UPDATE or GRANT statement, this is a mandatory statement that needs to be executed otherwise the limits and/or permissions won’t take effect.

Final Thoughts

In this article we only touch on a few of the basic security measures that MySQL has to offer. I hope with this article administrators will start to enforce stricter policies for there MySQL users thus helping mitigate malicious users from causes havoc with data stored within their MySQL daemon.

References

(MySQL, 2008) http://www.mysql.com/company/

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this post.
Loading...Loading...

Categories: Uncategorized

0

Disclaimer: This content is not supported by Novell. It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test it thoroughly before using it in a production environment.

Comment

RSS