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';

We can verify the new user has been added by querying the user table:

mysql> SELECT host, user, password FROM user WHERE user='johnB';
+-----------+-------+------------------+
| host      | user  | password         |
+-----------+-------+------------------+
| localhost | johnB | 2c7ed55a48a81f36 |
+-----------+-------+------------------+
1 row in set (0.00 sec)

As we can see, the password is not stored in plain text in the user table and has instead been encrypted by MySQL so that it cannot be obtained simply by performing a SELECT query on the table.

You may have noted that we specified that johnB could only connect from 'localhost', in other words the same system on which the MySQL server is running. This means that if johnB tries to connect to the MySQL server from a client running on a remote system, the connection will fail. In order to create an account which can connect from a particular host, simply specify the host name or IP address in place of the localhost in the above example. Alternatively, to allow a user to connect to the MySQL server from any remote host, simply use the '%' character in place of the host name:

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

An existing user account may be deleted using the DROP USER statement, the syntax for which is:

DROP USER user name;

For example:

DROP USER 'johnB'@'localhost';