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 they 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 surprisingly, 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 that 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';

The account name of a MySQL user can be changed using the RENAME USER statement, the syntax of which is:

RENAME USER user name TO new user name;

For example:

RENAME USER 'johnB'@'localhost' TO 'johnBrown'@'localhost';

The password assigned to a user account can be changed using the SET PASSWORD statement. To change the password for your own account, use the following syntax:

SET PASSWORD = Password('newpassword');

To alter the password for another user, simply include the user account name in a SET PASSWORD FOR statement. For example, to change the password of our example account:

SET PASSWORD FOR 'johnB'@'localhost' = Password('newpassword');

A newly created user can log into the MySQL server but by default has no privileges to do anything once connected. The next task after creating a new user account, therefore, is to add privileges to the account. This is achieved using the GRANT statement.

Before modifying a user's privileges it can be helpful to see what privileges are already set. This can be performed using the SHOW GRANTS statement in conjunction with the user's account name. For example:

SHOW GRANTS FOR 'johnB'@'localhost';
+-------------------------------------------------------------------------------------+
| Grants for johnB@localhost                                                          |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'johnB'@'localhost' IDENTIFIED BY PASSWORD '3a9eb1070a0130ca' |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The statement 'USAGE ON *.*' indicates that the user has no privileges on any database or table. Simply put, the user cannot do anything once logged into the database server.

To add a privilege, for example permission to query any table in a database named MySampleDB, we would issue the following command:

GRANT SELECT on MySampleDB.* TO 'johnB'@'localhost';

Once executed, the above statement will enable user 'johnB' to perform SELECT statements on any table contained in the MySampleDB database. Similarly we could enable johnB to INSERT rows into a table called product contained in the MySampleDB database as follows:

GRANT INSERT on MySampleDB.product TO 'johnB'@'localhost';

It is also perfectly valid to specify multiple privileges in a single GRANT statement, for example:

GRANT INSERT, UPDATE on MySampleDB.product TO 'johnB'@'localhost';

MySQL supports a wide range of privileges which are outlined in the following table: