Six steps to secure sensitive data in MySQL

If you're using MySQL, there are some easy things you can do to secure your systems and significantly reduce the risk of unauthorised access to your sensitive data.

The most valuable asset for technology-based organisations is usually the customer or product information in their databases. And so, a critical part of database administration in such organisations consists of securing these databases against outside attack and hardware/software failures.

In most cases, hardware and software failures are handled through a data backup regimen. Most databases come with built-in tools to automate the entire process, making this aspect of the job relatively painless and error-free. What's not so simple, however, is the second half of the puzzle: making sure that outside hackers can't get into the system and either steal or damage the information contained therein. And unfortunately, there usually isn't an automated way to solve this problem; rather, it requires you, the administrator, to manually put in place roadblocks and obstacles to trip up would-be hackers and to ensure that your company's data stays secure.

A common reason for not securing databases is that it is "difficult" and "complicated". While this is certainly true, if you're using MySQL, there are some easy things you can do to significantly reduce the risk you face. This tutorial lists six such items, but you can find many more in the MySQL manual and discussion forums.

Step 1: Remove wildcards in the grant tables

The MySQL access control system works through a series of so-called grant tables, which make it possible to define access levels for each user at the database, table or column level. While these tables do allow administrators to set blanket permissions for a user or set of tables using wildcards, doing so is inherently dangerous because a hacker could use a single compromised account to gain access to other parts of the system. For this reason, be precise when assigning users privileges and always ensure that users have only as much access as they need to perform their tasks. In particular, be wary of assigning the SUPER privilege to individual users, as this level allows users to manipulate basic server configuration and access all databases.

Tip: Use the SHOW PRIVILEGES command for each user account to audit your grant tables and see if the use of wildcard permissions is appropriate.

Step 2: Require the use of secure passwords

User accounts are only as secure as the passwords used to protect them. For this reason, the very first thing you should do when you install MySQL is assign a password to the MySQL root account (empty by default). Once you've closed this gaping hole, the next step is to require that every user account must have a password and ensure that passwords do not use easily-recognisable heuristics such as birthdays, user names or dictionary words.

Tip: Use the MySQL --secure-auth option to prevent the use of older, less secure MySQL password formats.

Step 3: Check the permissions of configuration files

Very often, to make server connections faster and more convenient, both individual users and server administrators store their user account passwords in their per-user MySQL options file. However, this password is stored in plain-text within the file and can easily be read. Therefore, it's important to ensure that such per-user configuration files are not viewable by other users of the system, and are stored in non-public locations. Ideally, you'd want the per-user configuration to be stored in the user's home directory with permissions 0600.

Step 4: Encrypt client-server transmissions

An important issue in the MySQL (and any) client-server architecture involves the security of data being transmitted over the network. If client-server transactions take place in plaintext, it is possible for a hacker to "sniff" the data packets being transmitted and thus gain access to sensitive information. You can close this hole either by enabling SSL in your MySQL configuration, or by using a secure shell utility like OpenSSH to create a secure encrypted "tunnel" for your data to pass through. Encrypting your client-server connection in this manner makes it extremely hard for unauthorised users to read the data going back and forth.

Step 5: Disable remote access

If your users don't need to access the server remotely, you can significantly reduce the risk of a network attack by forcing all MySQL connections to take place via the UNIX socket file. This is accomplished by starting the server with the --skip-networking option. Doing this blocks TCP/IP network connections to MySQL and ensures that no user can remotely connect to the system.

Tip: An enhancement to this would be to add the directive bind-address=127.0.0.1 in your MySQL server configuration, to force MySQL to bind to the IP address of the local machine and thus ensure that only users on the same system can connect to MySQL.

Step 6: Actively monitor the MySQL access log

MySQL comes with a number of different log files, which keep track of client connections, queries and server errors. Of these, the most important is the general query log, which logs each client connection and disconnection with a timestamp, and also records each query executed by a client. If you suspect unusual activity, such as that associated with a network break-in attempt, it's a good idea to monitor this log to gain an understanding of the source of the activity.

Protecting your MySQL databases is an ongoing task, and so you shouldn't rest easy once you've done the steps above. Visit the MySQL manual and the MySQL forums for more security tips, and be proactive in monitoring and updating the security of your system. Good luck!

CODE in MS SQL:  

USE [Forums]

GO

/****** Object:  StoredProcedure [dbo].[deleteUser]    Script Date: 10/19/2007 14:45:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[deleteUser]

(

	@userID as int

)

as

	delete from f_users where id = @userID

	delete from f_blogEntry where userID = @userID

	delete from f_blogCategory where userID = @userID

	delete from f_avatar where userID = @userID

	delete from f_forumModerator where userID = @userID

	delete from f_forumSubscription where userID = @userID

	delete from f_subscription where userID = @userID

	delete from f_userGroupUser where userID = @userID









CODE in MYSQL :



create procedure `forums`.`deleteUser`

(

	$userID  int

)

BEGIN

	delete from f_users where id = @userID

	delete from f_blogEntry where userID = @userID

	delete from f_blogCategory where userID = @userID

	delete from f_avatar where userID = @userID

	delete from f_forumModerator where userID = @userID

	delete from f_forumSubscription where userID = @userID

	delete from f_subscription where userID = @userID

	delete from f_userGroupUser where userID = @userID



END;