MySQL Users and Security

Previous	Table of Contents
MySQL Data Aggregation Functions

---

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.

Contents

MySQL Security

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.

Getting Information About Users

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)

Creating a New MySQL User

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

Deleting a MySQL User

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

Renaming a MySQL User

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

Changing the Password for a MySQL User

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

User Privileges

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:

Setting	Descritpion
ALL [PRIVILEGES]	Sets all simple privileges except GRANT OPTION
ALTER	Enable the use of ALTER TABLE statement
ALTER ROUTINE	Enable stored routines to be altered or dropped
CREATE	Enable the use of CREATE TABLE statement
CREATE ROUTINE	Enable creation of stored routines
CREATE TEMPORARY TABLES	Enable the use of CREATE TEMPORARY TABLE statement
CREATE USER	Enable the use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW	Enable the use of CREATE VIEW statement
DELETE	Enable the use of DELETE statement
DROP	Enable the use of DROP TABLE statement
EXECUTE	Enable the user to execute stored routines
FILE	Enable the use of SELECT INTO OUTFILE and LOAD DATA INFILE
INDEX	Enable the use of CREATE INDEX and DROP INDEX statements
INSERT	Enable the use of INSERT
LOCK TABLES	Enable the use of LOCK TABLES on tables for which the user has the SELECT privilege
PROCESS	Enable the user to see all processes with SHOW PROCESSLIST
REFERENCES	Not implemented
RELOAD	Enable the use of the FLUSH statement
REPLICATION CLIENT	Enable the user to ask for slave or master server locations
REPLICATION SLAVE	Needed for replication slaves (reads binary log events from the master)
SELECT	Enable the use of SELECT
SHOW DATABASES	SHOW DATABASES shows all databases
SHOW VIEW	Enable the use of SHOW CREATE VIEW
SHUTDOWN	Enable the use of mysqladmin shutdown
SUPER	Enable the use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows single connection if max_connections is reached
UPDATE	Enable the use of UPDATE
USAGE	Synonym for no privileges
GRANT OPTION	Enable privileges to be granted

---

Previous	Table of Contents
MySQL Data Aggregation Functions