MySQL Users and Security

In today's information based society nothing is more valuable than information. Today, that information is almost certainly stored in a database of some form or another. Whether it is a corporate sales database, a customer credit card database, or a table containing a list of names and social security numbers, there is a good chance that there are people out there who would love to get access to that data. It is for this reason that it is vital that a MySQL database be kept secure.

In this chapter we will provide an overview of securing user access to MySQL based databases.

MySQL security works by limiting both the users who have access to a database and what they are allowed to do once that have access. This requires careful consideration of issues such as who is allowed to read from or write to particular database tables and which users have permission to delete tables or use other MySQL features.

The first step securing a database is to find out which users already have access. This information is stored, not suprisingly, in a MySQL database called mysql.

The mysql database contains a table called user which in turn contains a number of columns including the user login name and the users various privileges and connection rights. To obtain a list of users run the following command:

SELECT user FROM user;

A newly installed MySQL database will only list one user, the root user:

mysql> select user from user;
+----------+
| user     |
+----------+
| root     |
+----------+
7 rows in set (0.

A database that is more established will likely contain more users, some of which will have been manually created, and others, as in the case below, created as a result of installing a third-party which uses MySQL.

mysql> select user from user;
+----------+
| user     |
+----------+
| remote   |
|          |
| phptest  |
| root     |
| wikiuser |
|          |
| root     |
+----------+
7 rows in set (0.00 sec)

In order to add a new user account it is necessary to use the CREATE USER statement. The creation of a new user account requires the user login name and an optional password. Regardless of the fact that the password is optional, it is unwise to add a new account without a password.

The syntax for creating a user account is as follows:

CREATE user name IDENTIFIED BY 'password';

For example, to create a new account for a user called johnB which is protected by a password we can issue the following statement:

CREATE USER 'johnB'@'localhost' IDENTIFIED BY 'yrthujoi';