View source for MySQL Users and Security

<table border="0" cellspacing="0" width="100%">
<tr>
<td width="20%">[[MySQL Data Aggregation Functions|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Administering and Monitoring MySQL using the MySQL Workbench|Next]]</td>
<tr>
<td width="20%">MySQL Data Aggregation Functions<td align="center"><td width="20%" align="right">Administering and Monitoring MySQL using the MySQL Workbench</td>
</table>
<hr>


<google>BUY_MYSQL</google>


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 ==

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.

== Getting Information about Users ==

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:

<pre>
SELECT user FROM user;
</pre>

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

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

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.

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

== 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:

<pre>
CREATE USER 'johnB'@'localhost' IDENTIFIED BY 'yrthujoi';
</pre>

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

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

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:

<pre>
CREATE USER 'johnB'@'%' IDENTIFIED BY 'yrthujoi';
</pre>

== 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:

<pre>
DROP USER 'johnB'@'localhost';
</pre>

== 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:

<pre>
RENAME USER 'johnB'@'localhost' TO 'johnBrown'@'localhost';
</pre>

== 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:

<pre>
SET PASSWORD FOR 'johnB'@'localhost' = Password('newpassword');
</pre>

== 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:

<pre>
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)
</pre>

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:

<pre>
GRANT SELECT on MySampleDB.* TO 'johnB'@'localhost';
</pre>

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:

<pre>
GRANT INSERT on MySampleDB.product TO 'johnB'@'localhost';
</pre>

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

<pre>
GRANT INSERT, UPDATE on MySampleDB.product TO 'johnB'@'localhost';
</pre>

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

<google>ADSDAQBOX_FLOW</google>
<table border="1" cellspacing="0">
<tr style="background:#efefef;">
<th>Setting</th>
<th>Description</th>
</tr>
<tr>
<td>ALL [PRIVILEGES]</td>
<td>Sets all simple privileges except GRANT OPTION
</td>
</tr>

<tr>
<td>ALTER</td>
<td>Enable the use of ALTER TABLE statement
</td>
</tr>
<tr>
<td>ALTER ROUTINE</td>
<td>Enable stored routines to be altered or dropped</td>
</tr>
<tr>
<td>CREATE</td>

<td>Enable the use of CREATE TABLE statement
</td>
</tr>
<tr>
<td>CREATE ROUTINE</td>
<td>Enable creation of stored routines</td>
</tr>
<tr>
<td>CREATE TEMPORARY TABLES</td>
<td>Enable the use of CREATE TEMPORARY TABLE statement

</td>
</tr>
<tr>
<td>CREATE USER</td>
<td>Enable the use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.</td>
</tr>
<tr>

<td>CREATE VIEW</td>
<td>Enable the use of CREATE VIEW statement
</td>
</tr>
<tr>
<td>DELETE</td>
<td>Enable the use of DELETE statement
</td>
</tr>
<tr>
<td>DROP</td>

<td>Enable the use of DROP TABLE statement
</td>
</tr>
<tr>
<td>EXECUTE</td>
<td>Enable the user to execute stored routines</td>
</tr>
<tr>
<td>FILE</td>
<td>Enable the use of SELECT INTO OUTFILE and LOAD DATA INFILE

</td>
</tr>
<tr>
<td>INDEX</td>
<td>Enable the use of CREATE INDEX and DROP INDEX statements
</td>
</tr>
<tr>
<td>INSERT</td>
<td>Enable the use of INSERT

</td>
</tr>
<tr>
<td>LOCK TABLES</td>
<td>Enable the use of LOCK TABLES on tables for which the user has the SELECT privilege</td>
</tr>
<tr>
<td>PROCESS</td>

<td>Enable the user to see all processes with SHOW PROCESSLIST
</td>
</tr>
<tr>
<td>REFERENCES</td>
<td>Not implemented</td>
</tr>
<tr>
<td>RELOAD</td>
<td>Enable the use of the FLUSH statement

</td>
</tr>
<tr>
<td>REPLICATION CLIENT</td>
<td>Enable the user to ask for slave or master server locations</td>
</tr>
<tr>
<td>REPLICATION SLAVE</td>
<td>Needed for replication slaves (reads binary log events from the master)</td>
</tr>
<tr>
<td>SELECT</td>

<td>Enable the use of SELECT
</td>
</tr>
<tr>
<td>SHOW DATABASES</td>
<td>
SHOW DATABASES shows all databases</td>
</tr>
<tr>
<td>SHOW VIEW</td>

<td>Enable the use of SHOW CREATE VIEW
</td>
</tr>
<tr>
<td>SHUTDOWN</td>
<td>Enable the use of mysqladmin shutdown
</td>
</tr>
<tr>
<td>SUPER</td>
<td>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</td>
</tr>
<tr>
<td>UPDATE</td>
<td>Enable the use of UPDATE

</td>
</tr>
<tr>
<td>USAGE</td>
<td>Synonym for no privileges</td>
</tr>
<tr>
<td>GRANT OPTION</td>
<td>Enable privileges to be granted</td>
</tr>
</table>

<google>BUY_MYSQL_BOTTOM</google>

<hr>
<table border="0" cellspacing="0" width="100%">
<tr>
<td width="20%">[[MySQL Data Aggregation Functions|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Administering and Monitoring MySQL using the MySQL Workbench|Next]]</td>
<tr>
<td width="20%">MySQL Data Aggregation Functions<td align="center"><td width="20%" align="right">Administering and Monitoring MySQL using the MySQL Workbench</td>
</table>