GO IT WORLD | IT TECH | IT NEWS

1.Summary

The MySQL user management mechanism is powerful, flexible, and often misused. The mysql database contains the various permission tables that allow access to be controlled based on user, host, and the action being performed. You can update the tables directly with SQL statements (in which case flushing the tables activates the changes) or through the more convenient GRANT and REVOKE statements.

The first task in a new installation should be to issue a root password. Until then, anyone can connect as root and have full access to everything. You can use mysqladmin, the SET statement, or GRANT to do this.

MySQL allows SSL connections for added security. This is not installed by default because it has performance implications.

You also learned some general principles for securing your data:

• Never issue a user the root password. They should always be connecting with another username.

• Never give anyone access to the user table, even for reading. Just viewing the encrypted password is enough to potentially allow a user full access.

• Always issue the minimum permissions you can. Issuing minimum permissions means that the user table contains N for all columns.

• For critical data, it must be possible to trace changes made by individuals. In general, people interact with the database through an application. The burden for managing access on an individual level then usually falls on the application.

• Ensure you cannot connect as the root user without a password from any server.

• Passwords should never be stored in plain text and should not be dictionary words.

• Check the user privileges every now and again and make sure that no one has granted anyone else inappropriate privileges.

2.Application Security

Most security holes are caused by poor applications. There are a number of common pitfalls to avoid:

• Never trust user data. Always verify any data entered by a user.

• Inserting quotations in a website form is a common cause of breakages. For example, an application takes a username and password and runs a query such as SELECT * FROM passwords WHERE username=’$username’ AND password=’$password’. Poorly designed applications will allow $password to contain something like aaa’;DELETE FROM passwords;. MySQL parsing the query thinks the single quote after the three a’s is the end of the query and then happily performs the next query. Most languages have simple functions to avoid this, escaping any quotations in the string, such as mysql_real_escape_ string() in C or addslashes() in PHP.

• Check the size of the data. A complex calculation may work well with a single digit number, but a 250-digit number passed by a user may cause the application to crash.

• Remove any special characters from strings passed to MySQL.

• Use quotes around numbers as well as strings.

3.System Security

By default, MySQL runs as its own user on Unix. The user and group are mysql. Never be tempted to allow anyone access to the system as the mysql user—it should only be for the database itself. MySQL also creates a separate directory where it places data files. This directory is accessible only to the mysql user. The default settings have been chosen for a reason; keep to these principles:

• Separate the data directory.

• Secure the data directory (no one else should be able to read, much less write, in the MySQL data directory).

• Run MySQL as its own user.

4.Security Issues with LOAD DATA LOCAL

Restoring from a client machine may be convenient, but it does come with a security risk. Someone could use LOAD DATA LOCAL to read any files to which the user they are connecting as has access. They could do this by creating a table and reading from the table after they have loaded the data. If they were connecting as the same user as the web server, and have the right access to run queries, this becomes dangerous. By default, MySQL allows LOAD DATA LOCAL. To prevent this and disallow all LOAD DATA LOCAL, start the MySQL server with the –local-infile=0 option. You could also compile MySQL without the –enable-local-infile option.

Managing Users and Permissions

MySQL has a well-designed, flexible, and easy-to-manage permissions system. Permissions are what allow or disallow certain users or host machines from connecting to the database server and from performing certain operations on the databases, tables, or even certain columns in the tables.

For example, take some possible scenarios:

• A news website includes a database server, a web server, and an intranet where staff update the news. Connections from the web server should only have permission to perform SELECT queries on the database, and connections from the intranet would allow UPDATE and INSERT queries for the staff.

• A financial transactions system has one database containing a log of records and one database containing customer balances. UPDATEs are permitted on the customer balance database, but not on the log database.

• A booking system has ordinary users who can only insert records to a particular table and an administrator who can update this table.

The mysql Database

When MySQL is installed, the mysql database is one of those automatically created. A thorough knowledge of the tables in this database is vital to be able to effectively administer security on the system. Six tables in the mysql database affect system access:

mysql> USE mysql;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

Table .1: The MySQL Tables

Table	Description
user	Lists users and the associated hosts and passwords that may access the server, as well as theglobal permissions they have. It’s best to disallow any global permissions and instead specifically allow them access in one of the other tables.
db	Lists databases that users may access. Permissions granted here apply to all tables in the database.
host	Together with the db table allows a more controlled form of access based on the particular host.
tables_priv	Lists access to specific tables. Permissions granted here apply to all columns in the table.
columns_priv	Lists access to specific columns.
func	Not yet used.

What Fields the Tables Contain

Let’s take a look at the tables in the mysql database. Your distribution may contain some slight differences.

mysql> SHOW COLUMNS FROM user;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| Host                  | varchar(60)
                           binary      |      | PRI |         |       |
| User                  | varchar(16)
                           binary      |      | PRI |         |       |
| Password              | varchar(16)
                           binary      |      |     |         |       |
| Select_priv           | enum('N','Y')|      |     | N       |       |
| Insert_priv           | enum('N','Y')|      |     | N       |       |
| Update_priv           | enum('N','Y')|      |     | N       |       |
| Delete_priv           | enum('N','Y')|      |     | N       |       |
| Create_priv           | enum('N','Y')|      |     | N       |       |
| Drop_priv             | enum('N','Y')|      |     | N       |       |
| Reload_priv           | enum('N','Y')|      |     | N       |       |
| Shutdown_priv         | enum('N','Y')|      |     | N       |       |
| Process_priv          | enum('N','Y')|      |     | N       |       |
| File_priv             | enum('N','Y')|      |     | N       |       |
| Grant_priv            | enum('N','Y')|      |     | N       |       |
| References_priv       | enum('N','Y')|      |     | N       |       |
| Index_priv            | enum('N','Y')|      |     | N       |       |
| Alter_priv            | enum('N','Y')|      |     | N       |       |
| Show_db_priv          | enum('N','Y')|      |     | N       |       |
| Super_priv            | enum('N','Y')|      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')|      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')|      |     | N       |       |
| Execute_priv          | enum('N','Y')|      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')|      |     | N       |       |
| Repl_client_priv      | enum('N','Y')|      |     | N       |       |
| ssl_type              | enum('','ANY'
                         ,'X509'
                         ,'SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob         |      |     |         |       |
| x509_issuer           | blob         |      |     |         |       |
| x509_subject          | blob         |      |     |         |       |
| max_questions         | int(11)
                           unsigned    |      |     | 0       |       |
| max_updates           | int(11)
                           unsigned    |      |     | 0       |       |
| max_connections       | int(11)
                           unsigned    |      |     | 0       |       |
+-----------------------+--------------+------+-----+---------+-------+
31 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM db;  
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| Db              | char(64) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
13 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM host;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| Db              | char(64) binary |      | PRI |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
12 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM tables_priv;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| Host        | char(60) binary      |      | PRI |         |       |
| Db          | char(64) binary      |      | PRI |         |       |
| User        | char(16) binary      |      | PRI |         |       |
| Table_name  | char(60) binary      |      | PRI |         |       |
| Grantor     | char(77)             |      | MUL |         |       |
| Timestamp   | timestamp(14)        | YES  |     | NULL    |       |
| Table_priv  |set('Select','Insert',
               'Update', 'Delete',
               'Create', 'Drop',
               'Grant', 'References',
               'Index', 'Alter')     |      |     |         |       |
| Column_priv |set('Select','Insert',
               'Update','References')|      |     |         |       |
+-------------+----------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM columns_priv; ;
+-------------+-----------------------+------+-----+---------+-------+
| Field       | Type                  | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+-------+
| Host        | char(60) binary       |      | PRI |         |       |
| Db          | char(64) binary       |      | PRI |         |       |
| User        | char(16) binary       |      | PRI |         |       |
| Table_name  | char(64) binary       |      | PRI |         |       |
| Column_name | char(64) binary       |      | PRI |         |       |
| Timestamp   | timestamp(14)         | YES  |     | NULL    |       |
| Column_priv | set('Select','Insert',
               'Update','References') |      |     |         |       |
+-------------+-----------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM func;
+-------+------------------------------+------+-----+----------+-------+
| Field | Type                         | Null | Key | Default  | Extra |
+-------+------------------------------+------+-----+----------+-------+
| name  | char(64) binary              |      | PRI |          |       |
| ret   | tinyint(1)                   |      |     | 0        |       |
| dl    | char(128)                    |      |     |          |       |
| type  | enum('function','aggregate') |      |     | function |       |
+-------+------------------------------+------+-----+----------+-------+

Table 2 describes the various privileges.

Table 2: What the Columns Mean

Column	Description
Host	The host machine from which the user connects.
User	The username supplied for the connection (the -u option).
Password	The password the user connects as (the -p option).
Db	The database on which the user is trying to perform the operation.
Select_priv	Permission to return data from a table (a SELECT statement). SELECT results thatcan be calculated without needing to access a table still return a result even if the user does not have SELECT privileges.
Insert_priv	Permission to add new records to the table (an INSERT statement).
Update_priv	Permission to modify data in a table (an UPDATE statement).
Delete_priv	Permission to remove records from a table (a DELETE statement).
Create_priv	Permission to create databases and tables.
Drop_priv	Permission to drop databases or tables.
Reload_priv	Permission to reload the database (a FLUSH statement or a reload, refresh, or flush issued from mysqladmin).
Shutdown_priv	Permission to shut down the server.
Process_priv	Permission to view the current MySQL processes or kill MySQL processes (for SHOW PROCESSLIST or KILL SQL statements).
File_priv	Permission to read and write files on the server (for LOAD DATA INFILE or SELECT INTO OUTFILE statements). Any files that the MySQL user can read are readable.
Grant_priv	Permission to grant privileges available to the user to other users.
References_priv	Not currently used by MySQL.
Index_priv	Permission to create, modify, or drop indexes.
Alter_priv	Permission to change the structure of a table (an ALTER statement).
Show_db_priv	Permission to see all databases.
Super_priv	Permission to connect, even if the maximum number of connections is reached, and perform the CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL commands.
Create_tmp_table_priv	Permission to create a temporary table (CREATE TEMPORARY TABLE).
Lock_tables_priv	Permission to lock a table for which the user has SELECT permission.
Execute_priv	Permission to run stored procedures (scheduled for MySQL 5).
Repl_client_priv	Permission to ask about replication slaves and masters.
Repl_slave_priv	Permission to replicate (see Chapter 12, "Database Replication").
ssl_type	Permission to connect is only granted if Secure Sockets Layer (SSL) is used.
ssl_cipher	Permission to connect is only granted if a specific cipher is present.
x509_issuer	Permission to connect is only granted if the certificate is issued by a specific issuer.
x509_subject	Permission to connect is only granted if the certificate contains a specific subject.
max_questions	Maximum number of queries the user can perform per hour.
max_updates	Maximum number of updates the user can perform per hour.
max_connections	Maximum number of times the user can connect per hour.

How MySQL Examines Permissions to Allow Access

When a user tries to connect, MySQL examines the user table first to confirm that the particular user, host, and password combination is listed. If not, the user is denied access. When a user is trying to connect directly to a database, the db table will also be examined if the user gets through the other checks. If the user does not have permission to connect to that database, access is denied.

When a connected user tries to perform an administrative operation (for example, mysqladmin shutdown), MySQL examines the column related to the operation from the user table. If permission for the required operation is granted, the operation goes ahead. If not, the operation fails.

When a connected user tries to perform a database-related operation (SELECT, UPDATE, and so on), MySQL examines the related field from the user table. If permission for the required operation (for the SELECT, UPDATE, and so on) is granted, the operation is permitted. If not, MySQL goes to the next step.

The db table is examined next. MySQL looks for the database on which the user is performing the operation. If this does not exist, permission is denied, and the operation fails. If the database does exist, and the host and user match, the field relating to the operation is examined. If permission is granted for the required operation, the operation succeeds. If permission is not granted, MySQL proceeds to the next step. If the database and user combination does exist, and the host field is blank, MySQL examines the host table to see whether the host can perform the required operation. If the host and database are found in the host table, the related field on both the host and db tables determines whether the operation succeeds. If permission is granted on both tables, the operation succeeds. If not, MySQL proceeds to the next step.

MySQL examines the tables_priv table taking into account the table(s) on which the operation is being performed. If the host, user, db, and table combination do not exist, the operation fails. If they do exist, the related field is examined. If permission is not granted, MySQL proceeds to the next step. If permission is granted, the operation succeeds.

Finally, MySQL examines the columns_priv tables, taking into account the table columns being used in the operation. If permission related to the required operation is granted here, the operation succeeds. If not, if fails.

The order of precedence for the MySQL permission tables is shown in Figure 1.

Figure 1: Precedence for MySQL permission tables

How to Populate the Permission Tables

The permission tables are populated with some default values:

mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+---
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password
         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
4 rows in set (0.05 sec)
mysql> SELECT * FROM db;  
+------+---------+------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+----
--------+------------+
| Host | Db      | User | Select_priv | Insert_priv | Update_priv |
Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv |
Index_priv | Alter_priv |

+------+---------+------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+----
--------+------------+

| %    | test    |      | Y           | Y           | Y           | Y            | Y           | Y         | N          | Y               | Y         |
Y          |
| %    | test\_% |      | Y           | Y           | Y           | Y
| Y           | Y         | N          | Y               | Y          |
Y          |
+------+---------+------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+----
--------+------------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM host;
Empty set (0.00 sec)
mysql> SELECT * FROM tables_priv;
Empty set (0.00 sec)
mysql> SELECT * FROM columns_priv;
Empty set (0.00 sec)

Notice that the default settings are not secure. Anyone can connect from the local host as the root user and have total authority. An anonymous user (where no username is supplied) can connect from the local host to the default test database and to any database where the name begins with test.

Note

In Unix, MySQL uses the Unix login username if no username is supplied. This means that someone logged in as root can simply enter MySQL without specifying a username, and they will have full permissions.

One of the first tasks to perform in a new installation is to set new permissions and at least a new password for the root user.

Manipulating the Permission Tables Directly

There are two ways to set permissions—by using the MySQL GRANT and REVOKE statements or by directly changing the values in the tables. The easiest and most convenient way is with the GRANT and REVOKE statements, but it’s important to understand how the tables affect permissions. For now, you’re going to look at changing permissions by changing the values in the tables with the basic INSERT, UPDATE, and DELETE SQL statements. You’ll see the other method later in the section titled "Using GRANT and REVOKE to Manipulate the Permission Tables." To add a password for the root user, you would write the following:

mysql> UPDATE user SET password=PASSWORD('g00r002b') WHERE user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Notice the use of the PASSWORD() function. You must use this function when updating the tables directly. It encrypts the password so that it cannot be read simply by viewing the contents of the tables. For example:

mysql> SELECT host,user,password FROM user;
+---------------------+------+------------------+
| host                | user | password         |
+---------------------+------+------------------+
| localhost           | root | 43b591f759a842a9 |
| test.testhost.co.za | root | 43b591f759a842a9 |
| localhost           |      |                  |
| test.testhost.co.za |      |                  |
+---------------------+------+------------------+
4 rows in set (0.00 sec)

Warning

Be careful when directly changing permissions. Neglecting the WHERE clause would mean that all passwords change, and suddenly no existing users would be able to connect.

Changes to the permissions do not take effect immediately when made directly to the MySQL tables. MySQL needs to reread the grant tables. You can force it to do this by issuing FLUSH PRIVILEGES, mysqladmin flush-privileges, or mysqladmin reload.

mysql> INSERT INTO user (Host,User,Password) VALUES ('localhost',
 'administrator', PASSWORD('admin_pwd'));
Query OK, 1 row affected (0.09 sec)

Before the permissions are flushed, this data does not take effect. You can connect as the administrator user without any password:

% mysql -uadministrator;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.1-alpha-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Before the database is reloaded, connecting as administrator is accepted, because, not finding the specific name, connecting is the same as for an anonymous user where no password is needed. You can see this by looking at the third and fourth records from the user table. After the flush, the administrator user can no longer connect without a password.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
 % mysql -uadministrator;
ERROR 1045: Access denied for user: 'administrator@localhost'
 (Using password: NO)

It’s never good to use the root user for anything but administration. Day-to-day connections should be through users with permissions developed especially for the tasks that user performs. For this sales system, you’re going to add two users—an administrator and a regular user. The administrator will have full permissions to do anything, and the regular user will have certain limitations. To add the administrator, you could simply add a record to the user table, giving a full set of permissions to the administrator. But that would mean the administrator of the sales rep system would have full access to any other database that gets developed on the system. It’s almost always better to limit permissions at a user level and then activate permissions on a lower level. You’re going to add a record to the user and to the database table to do this. I use an INSERT statement without specifying fields (for ease of typing) with the db table example, in case you’re following these examples. Be sure that the fields match the fields in the tables from your distribution, in case they have changed:

mysql> INSERT INTO user (host,user,password)
VALUES('localhost','administrator',PASSWORD('l3tm31n'));
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO db 
VALUES('localhost','firstdb','administrator','y','y','y','y','n','n',
'n','n','n','n');
Query OK, 1 row affected (0.01 sec)

The tables now contain the following:

mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y        | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |

| localhost                 |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |

+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
5 rows in set (0.05 sec)

mysql> SELECT * FROM db;;
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| Host      | Db      | User          | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv |
References_priv | Index_priv | Alter_priv |

+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| %         | test    |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| %         | test\_% |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| localhost | firstdb | administrator | Y           | Y           | Y
| Y           | N           | N         | N          | N
| N          | N          |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+

3 rows in set (0.01 sec)

The administrator can connect to the database with the password but only perform data manipulation on the firstdb database.

Remember to flush the tables before these permissions take effect:

% mysqladmin reload -u root -p
Enter password:
% mysql mysql;
ERROR 1045: Access denied for user: 'root@localhost' (Using
 password: NO)

If you were not logged in as root, you’d get an error indicating that the anonymous user does not have permission:

% mysql mysql;
ERROR 1044: Access denied for user: '@localhost' to database
 'mysql'

Using GRANT and REVOKE to Manipulate the Permission Tables

Rather than updating the tables directly and having to flush the database, an easier way is to use the GRANT and REVOKE statements to manage permissions. The basic GRANT syntax is as follows:

GRANT privilege ON table_or_database_name TO user_name@hostname
 IDENTIFIED BY 'password'

To add a regular user for this sales system, you could do the following:

mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n_2';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |

| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N       | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | regular_user    | 1bfcf83b2eb5e59   | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
6 rows in set (0.05 sec)
mysql> SELECT * FROM db;
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| Host      | Db      | User          | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv |
References_priv | Index_priv | Alter_priv |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| %         | test    |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| %         | test\_% |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| localhost | firstdb | administrator | Y           | Y          | Y
| Y           | N           | N         | N          | N
| N          | N          |
| localhost | sales   | regular_user  | Y           | N           | N
| N           | N           | N         | N          | N
| N          | N          |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
4 rows in set (0.01 sec)

The password is automatically encrypted when issued with GRANT, so there is no need to use the PASSWORD() function to encrypt it. In fact, if you do, you’ll re-encrypt the password! You can change the password by reissuing the same permissions with a new password.

You can also revoke permissions in the same way as you grant them:

mysql> REVOKE SELECT ON sales.* FROM regular_user@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | regular_user    | 1bfcf83b2eb5e59   | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
6 rows in set (0.05 sec)
mysql> SELECT * FROM db;
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| Host      | Db      | User          | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv |
References_priv | Index_priv | Alter_priv |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+

| %         | test    |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| %         | test\_% |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| localhost | firstdb | administrator | Y           | Y           | Y
| Y           | N           | N         | N          | N
| N          | N          |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+

3 rows in set (0.00 sec)

Notice that all trace of the user has been removed from the db table but that the user still exists in the user table. There is no way to remove this from the table without directly deleting it. A user with no permissions (called USAGE permission) can still connect to the server and access some information, such as, for early versions of MySQL 4, viewing the existing databases! For example:

mysql> exit
Bye
% mysql -uregular_user -pl3tm37n_2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 4.0.1-alpha
-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| firstdb  |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

To remove all traces of the user, delete them from the user table directly (while connected as root):

mysql> exit
Bye
% mysql mysql -uroot -pg00r002b
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 4.0.1-alpha
-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELETE FROM user WHERE user='regular_user';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
5 rows in set (0.05 sec)

Table 3 describes all the privileges available.

Table 3: Privileges

Privilege	Description
ALL	Grants all the basic permissions.
ALL PRIVILEGES	Same as ALL.
ALTER	Permission to change the structure of a table (an ALTER statement), excluding indexes.
CREATE	Permission to create databases or tables, excluding indexes.
CREATE TEMPORARY TABLES	Permission to create a temporary table (CREATE TEMPORARY TABLE statement).
DELETE	Permission to remove records from a table (a DELETE statement).
DROP	Permission to drop databases or tables, excluding indexes.
EXECUTE	Permission to run stored procedures (scheduled for MySQL 5).
FILE	Permission to read and write files on the server (for LOAD DATA INFILE or SELECT INTO OUTFILE statements). Any files that the MySQL user can read are readable.
GRANT	Permission to grant permissions owned by the user to another user.
INDEX	Permission to create, modify, or drop indexes.
INSERT	Permission to add new records to the table (an INSERT statement).
LOCK TABLES	Permission to lock a table for which the user has SELECT permission.
PROCESS	Permission to view the current MySQL processes or kill MySQL processes (for SHOW PROCESSLIST or KILL SQL statements).
REFERENCES	Not currently used by MySQL.
RELOAD	Permission to reload the database (a FLUSH statement or a reload, refresh, or flush issued from mysqladmin).
REPLICATION CLIENT	Permission to ask about the replication slaves and masters.
REPLICATION SLAVE	Permission to replicate from the server (slaves need this to replicate).
SHOW DATABASES	Permission to see all databases.
SELECT	Permission to return data from a table (a SELECT statement).
SHUTDOWN	Permission to shut down the server.
SUPER	Permission to connect, even if the maximum number of connections is reached, and perform the CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL commands.
UPDATE	Permission to modify data in a table (an UPDATE statement).
USAGE	Permission to connect to the server and perform statements available to all (for early versions of MySQL 4 this included SHOW DATABASES).

The earlier example granted permissions for all tables in the sales database. You can easily manipulate this by changing the database and table names you grant on.

Table 14.4: Database and Table Names

Name	Description
*.*	All tables in all databases
*	All tables in the current database
databasename.*	All tables in the database databasename
databasename.tablename	The table tablename in the database databasename

For example:

mysql> GRANT SELECT ON *.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n_2';  
Query OK, 0 rows affected (0.00 sec)

Because permission is granted on all databases, there is no need for an entry in the database table; just the user table, with the field select_priv set to Y:

mysql> SELECT * FROM user;
+---------------------+---------------+------------------+-------------
+-------------+-------------+-------------+-------------+-----------+--
-----------+---------------+--------------+-----------+------------+---
--------------+------------+------------+----------+------------+------
-------+--------------+
| Host                | User          | Password         | Select_priv
| Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv |
References_priv | Index_priv | Alter_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject |
+---------------------+---------------+------------------+-------------
+-------------+-------------+-------------+-------------+-----------+--
-----------+---------------+--------------+-----------+------------+---
--------------+------------+------------+----------+------------+------
-------+--------------+
| localhost           | root          | 43b591f759a842a9 | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | NONE     |            |             |
|
| test.testhost.co.za | root          | 43b591f759a842a9 | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | NONE     |            |             |
|
| localhost           |               |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
| test.testhost.co.za |               |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
| localhost           | administrator | 74126e0c6742d7e9 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
| localhost           | regular_user  | 1bfcf83b2eb5e591 | Y
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
+---------------------+---------------+------------------+-------------
+-------------+-------------+-------------+-------------+-----------+--
-----------+---------------+--------------+-----------+------------+---
--------------+------------+------------+----------+------------+------
-------+--------------+
6 rows in set (0.00 sec)

Using SET to Set User Passwords

Another way to change passwords is with the SET PASSWORD statement. Any nonanonymous user can change their own password in this way. (This is another reason to assign users carefully. It’s not unheard of for a user to change their password, denying others access, because they do not realize they share this username!)

You can set a password for the user you’re connected as follows:

mysql> SET PASSWORD=PASSWORD('g00r002b2');
Query OK, 0 rows affected (0.00 sec)

A user with access to the user table in the mysql database can set passwords for other users too, by specifying the user:

mysql> SET PASSWORD FOR root=PASSWORD('g00r002b');
Query OK, 0 rows affected (0.00 sec)

Remember to use the PASSWORD() function to encrypt the password. If you don’t, the password is stored in the user table as plain text, but because on connection the given password is automatically encrypted before being compared to the password in the user table, you will not be able to connect (if you try this, you’ll need to refer to the section titled "What to Do If You Can’t Connect…" afterward to continue):

mysql> SET PASSWORD FOR root='g00r002b';
Query OK, 0 rows affected (0.00 sec)

Now, after exiting you won’t be able to reconnect as root:

% mysql -uroot -pg00r002b2
ERROR 1045: Access denied for user: 'root@localhost' (Using
 password: YES)

Using mysqladmin to Set User Passwords

When using mysqladmin, as with the GRANT statement, you should not use the PASSWORD() function:

% mysqladmin -uroot -pg00r002b password g00r002b

Wildcard Permissions

There’s no need to enter 1,001 hosts if that’s how many hosts to which you need to grant access. MySQL accepts wildcards in the host table. For example, the following allows a user to connect from a host ending with marsorbust.co.za:

mysql> GRANT SELECT ON sales.* TO regular_user@"%.
marsorbust.co.za"
 IDENTIFIED BY 'l3tm37n';
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM user WHERE host LIKE '%mars%';
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| %.marsorbust.co.za          | regular_user     | 1bfcf83b2eb5e591 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
1 row in set (0.05 sec)

The quotes in the GRANT statement allow wildcards, or any special characters, to be used. You can of course also insert wildcards into the MySQL tables directly.

What to Do If You Can’t Connect or Have No Permissions

It’s not impossible. Maybe you revoke yourself into oblivion, use DELETE where you shouldn’t have, or even damage the files holding the permission tables, and now you can’t connect at all, even as root. Fear not; there is a solution!

First, stop MySQL completely. As the root user on Unix, if you run MySQL out of /init.d, you may be able to run the following:

% /etc/rc.d/init.d/mysql stop
Killing mysqld with pid 5091
Wait for mysqld to exit\c
.\c
.\c
.\c
.\c
020612 01:14:41  mysqld ended

 done

If not, still as root, you’ll need to kill the specific MySQL-related processes:

% ps -ax |grep mysql
 5195 pts/0    S      0:00 sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/lo
 5230 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5232 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5233 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5234 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5235 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5236 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5237 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5238 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5239 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5240 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5241 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
% kill 5195 5230 5232 5233 5234 5235 5236 5237 5238 5239 5240 5241  
mysqld ended

In the eventuality that this doesn’t work, you may have to use kill -9 (followed by the process ID) to really kill the process.

On Windows, you can simply use the task manager to close MySQL.

Then, restart MySQL without the grant tables (this ignores any permission restrictions):

% mysqld_safe --skip-grant-tables

And now you should be able to add a root password, either through directly manipulating the tables directly, with GRANT, or with mysqladmin:

 % mysqladmin -u root password 'g00r002b'

Don’t forget to stop the server, and restart it without –skip-grant-tables, to activate your root password.

What to Do If the User Table Becomes Corrupted

Sometimes it can happen that the user table has become corrupted, so you still cannot change the password with mysqladmin. This happened to me once after a crash and could conceivably happen if someone tampers with the files directly. If you want to follow this example, you can simulate the loss of the user table by renaming it, and then flushing the tables (the original will probably still be cached otherwise):

% mv user.MYD user_bak.olddata
% mysql -uroot -pg00r002b;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.1-alpha-
max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>FLUSH TABLES;
mysql> SELECT * FROM user;
ERROR 1016: Can't open file: 'user.MYD'. (errno: 145)

If this is the case, you should still start MySQL without the grant tables and then try dropping the table:

mysql> DROP TABLE user; 
Query OK, 0 rows affected (0.01 sec)

CREATE TABLE user (
  Host varchar(60) binary NOT NULL default '',
  User varchar(16) binary NOT NULL default '',
  Password varchar(16) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Reload_priv enum('N','Y') NOT NULL default 'N',
  Shutdown_priv enum('N','Y') NOT NULL default 'N',
  Process_priv enum('N','Y') NOT NULL default 'N',
  File_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  Show_db_priv enum('N','Y') NOT NULL default 'N',
  Super_priv enum('N','Y') NOT NULL default 'N',
  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',
  Lock_tables_priv enum('N','Y') NOT NULL default 'N',
  Execute_priv enum('N','Y') NOT NULL default 'N',
  Repl_slave_priv enum('N','Y') NOT NULL default 'N',
  Repl_client_priv enum('N','Y') NOT NULL default 'N',
  ssl_type enum('','ANY','X509','SPECIFIED') NOT NULL default '',
  ssl_cipher blob NOT NULL,
  x509_issuer blob NOT NULL,
  x509_subject blob NOT NULL,
  max_questions int(11) unsigned NOT NULL default '0',
  max_updates int(11) unsigned NOT NULL default '0',
  max_connections int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
Query OK, 0 rows affected (0.00 sec)

This probably still won’t give you permission, however:

mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n';
ERROR 1047: Unknown command
mysql> exit
Bye
[root@test mysql]# mysqladmin -uroot password 'g00r002b'
mysqladmin: unable to change password; error: 'You must have privileges
 to update tables in the mysql database to be able to change passwords
 for others'

You’ll need to insert some values into the table once more. Here you add the default values. Be sure they match the columns for the user table you created, if it is different:

[root@test mysql]# mysql mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.0.1-alpha
-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>INSERT INTO user VALUES ('localhost', 'root', '', 'Y',
 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.01 sec)
mysql>INSERT INTO user VALUES ('%', 'root', '', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user VALUES ('localhost', '', '', 'N', 'N'
, 'N',
 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user VALUES ('localhost', '', '', 'N', 'N'
, 'N',
 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
 'N', 'N',
 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)

You’ll need to reload (or flush the privilege tables) in order to activate the permissions, and then once again you can start issuing commands:

mysql> exit
Bye
[root@test mysql]# mysqladmin reload
[root@test mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.1-alpha-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n';
Query OK, 0 rows affected (0.00 sec)

Other Grant Options

By default, MySQL does not allow a user to pass their permissions onto someone else. And, being a control freak, I don’t suggest you allow your users to do this. You’ve probably got a good reason for not granting them the permission in the first place, and you wouldn’t want another user to override this. But, if you must—perhaps in a situation where there are multiple trusted users—there is a way. The WITH GRANT OPTION will allow a user to grant any permissions that they have to another user.

The following demonstrates this in action, using two databases: sales and customer. The administrator creates a regular_user2, with permission to perform SELECT queries on the sales database, and then grants the GRANT option to the first regular_user, who has permission to SELECT on the customer database, who will then in turn grant the same rights to regular_user2:

mysql> GRANT SELECT ON sales.* TO regular_user2@localhost IDENTIFIED BY 'l3tm37n';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED
 BY 'l3tm37n' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
% mysql -u regular_user2 -pl3tm37n
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.1-alpha-
max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED
 BY 'l3tm37n' WITH GRANT OPTION;
ERROR 1044: Access denied for user: 'regular_user2@localhost' to database
 'customer'

Regular_user2 cannot grant anything to another user:

mysql> exit
Bye
[root@test /root]# mysql -u regular_user -pl3tm37n
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.1-alpha-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED
 BY 'l3tm37n' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

However, regular_user can grant permissions to regular_user2.

There are some other useful options that help avoid the possibility of one user hogging connections. These limit queries, updates, or connections to a certain number per hour. The three options are as follows:

MAX_QUERIES_PER_HOUR n
MAX_UPDATES_PER_HOUR n
MAX_CONNECTIONS_PER_HOUR n

Without these three options, the only limit on user activity is the max_user_connections variable. But this is global, and you cannot limit one kind of user from an activity.

Where Could User Limitation Be Useful?

Limiting database-intensive queries, such as searches that involve many joins of large tables, can be useful. The application could connect as a different user with this limit. The application is still a better place to do this limiting, but an hourly limit may be useful in some situations—for example, situations where there is potential for denial of service attacks, or situations where one user could make multiple connections and cause the performance of the database server to degrade.

Which Type of User Limitation to Use?

Connections usually have the lowest impact on the database, but there is still the possibility that

max_user_connections could be taken up by one user. Setting MAX_CONNECTIONS_PER_HOUR is the most cautious option.

Sometimes you may not be worried about the number of connections, but rather about a user performing multiple queries at the same time or performing extraneous queries. You could set

MAX_QUERIES_PER_HOUR to avoid users performing unnecessary queries and wasting resources or performing too many of a possible heavy query in a short space of time.

UPDATEs have a higher performance impact than SELECTs and a very marked impact where table locking is used (such as the default MyISAM table type). You may use MAX_ UPDATES_PER_HOUR to limit updates for performance reasons or where users need not make many updates.

Tip

It pays to be paranoid. Users can abuse the database, intentionally or not. If you cannot see a use for them to have certain permissions, do not grant them. It’s always easier to add permissions than to take away permissions once they’ve been granted. I’ve encountered large systems where the security consisted only of one user and one password. When this was compromised, it proved impossible to gracefully add limitations.

As an example of user limitations, you can limit regular_user2 to two connections per hour:

mysql> GRANT SELECT ON sales.* TO regular_user2@localhost IDENTIFIED BY 'l3tm37n' 
  WITH MAX_CONNECTIONS_PER_HOUR 2;
Query OK, 0 rows affected (0.00 sec)

If regular_user2 exceeds the number of connections, they’ll get an error such as follows:

ERROR 1226: User 'regular_user2' has exceeded the 'max_connections'
 resource (current value: 2)

Similarly, if MAX_QUERIES_PER_HOUR was assigned and exceeded, the error message would be as follows:

ERROR 1226: User 'regular_user2' has exceeded the 'max_questions'
 resource (current value: 4)

Tip

Be reasonable with your limitations. If a user should only perform one query an hour, realize that they could enter an incorrect query and have to perform a second one.

A Strategy for Managing Users Securely

The more complex your needs, the more complex your strategy will have to be. Simple websites can often suffice with two users: an administration user who can update data and a user for the website application who can only perform SELECTs from certain tables, for example. The general principle is to grant the user only the privileges they need and no more. If they need more at a later stage, it is easy to grant them additional ones. But taking them away is another matter!

MySQL users are mostly of three kinds: There are individual people (for example, Anique or Channette), applications (for example, a salary system or the news website), and roles (for example, updating the news or updating the salaries). These may overlap to various degrees—for example, Anique may update both salaries and news, using both applications and performing both roles. The DBA needs to decide whether to issue Anique and Channette their own passwords, issue passwords to the news and salary systems, or create a user based upon whether the news or salaries are being updated.

If you opt for users as individuals and issue Anique her own password, she only has to remember one login to the database. But then she needs to be given access to update both the salary and news databases. If she, or the application she is using, makes a mistake, there is the possibility of her damaging data on which she should not even be working. For example, if the salary and news databases both have a days_data table—with the news database version growing continually until it is archived and the salary data being manually removed after it has been processed—there is the possibility of her removing the news table when she meant to remove the salary table.

If you opt for users as applications, you solve some of these problems. However, it seems a user now has to remember two passwords. Also, you cannot track which user has made which changes to the database. You have a solution, however, because where security is necessary, it’s likely that the individual will have to log into the application (potentially allowing you to track the changes an individual makes to the database), and the application then logs into the database. The user could have the same username and password to both applications, but they could never destroy news data when connected as the salary application (as you’d not have given the salary user permission to update the news database).

Applications, though, often have many roles, with many levels of user. Perhaps anyone may view their own salary details, but only an administrator can update them. Giving the application permission to update data potentially allows an ordinary user to update the data. Consider also the development process: A trusted senior developer builds the salary administration component of the application, and a team of junior developers builds the salary-viewing component. Issuing the same password to the application allows the junior developers to update the data when they don’t need to and probably shouldn’t be allowed to update it. In this case, you could issue usernames based upon a combination of role and application (salary administration, salary viewing, news administrator, news viewing).

Some principles to keep in mind include the following:

• Never issue a user the root password. They should always be connecting with another username and password.

• Always issue the minimum permissions you can. (But be reasonable! You’ll always get some sadists who take great glee in granting permission on a query-by-query basis. For example, allowing the user to read the surname column, then forcing them to come begging for more permission when they need to read the first name shortly afterward.) The global permissions assigned in the user table should always be N, though, and then access to specific databases granted in the db table.

• For critical data, it must be possible to trace changes made by individuals. In general, people interact with the database through an application. The burden for managing access on an individual level then usually falls on the application.

Avoiding Granting Dangerous Privileges

Although you should always issue the minimum privileges required, there are some privileges that are particularly dangerous, where the security risk may outweigh the convenience factor. Remember that you should never grant access on a global level.

The following privileges in particular could be security risks:

Any privileges on the mysql database  A malicious user can still gain access even after being able to only view the encrypted passwords.

ALTER  A malicious user could make changes to the privilege tables, such as renaming them, which renders them ineffective.

DROP  If a user can DROP the mysql database, the permission limitations will no longer be in place.

FILE  Users with the FILE privilege can potentially access any file that is readable by all. They can also create a file that has the MySQL user privileges.

GRANT  This allows users to give their privileges to others who may not be as trustworthy as the original user.

PROCESS  Queries that are running can be viewed in plain text, which includes any that change or set passwords.

SHUTDOWN  It’s unlikely a DBA will be fooled into granting this privilege easily, and it should go without saying that users with the SHUTDOWN privilege can shut down the server and deny access to everyone.