MySQL Troubleshooting

Permission Issues

MySQL has a complex privilege scheme, allowing you to tune precisely which users and hosts are allowed to perform one or another operation. Since version 5.5, MySQL also has pluggable authentication.

Although it has advantages, this scheme is complicated. For example, having user1@hostA different from user2@hostA and user1@hostB makes it easy to mix up their privileges. It is even easier to do this when the username is the same and the host changes.

MySQL allows you to tune access at the object and connection level. You can restrict a user’s access to a particular table, column, and so on.

Users usually experience two kinds of permission issues:

• Users who should be able to connect to the server find they cannot, or users who should not be able to connect find that they can.

• Users can connect to the server, but can’t use objects to which they are supposed to have access, or can use objects to which they are not supposed to have access.

Before you start troubleshooting these problems, you need to find out whether you can connect to the server.

After you succeed in connecting as the user you’re troubleshooting (we will discuss the case when connection is not possible a bit later in this chapter), run the query:

SELECT USER(), CURRENT_USER()

The USER() function returns the connection parameters used when the user connects. These are usually the username that was specified and the hostname of a box where the client is running. CURRENT_USER() returns the username and hostname pair of those privileges chosen from privilege tables. These are the username and hostname pairs used by mysqld to check access to database objects. By comparing the results of these functions, you can find out why mysqld uses privileges that are different from what you expected. A typical problem is trying to use a % wildcard for the hostname:

root> GRANT ALL ON book.* TO sveta@'%';
Query OK, 0 rows affected (0.00 sec)

root> GRANT SELECT ON book.* TO sveta@'localhost';
Query OK, 0 rows affected (0.00 sec)

If I now connect as sveta and try to create a table, I get an error:

$mysql -usveta book
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.1.52 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1(f1 INT);
ERROR 1142 (42000): CREATE command denied to user 'sveta'@'localhost' for table 't1'

The problem is that the MySQL server expands sveta to sveta@localhost, not to the wild card:

mysql> SELECT user(), current_user();
+-----------------+-----------------+
| user()          | current_user()  |
+-----------------+-----------------+
| sveta@localhost | sveta@localhost |
+-----------------+-----------------+
1 row in set (0.00 sec)

If you don’t understand why one or another host was chosen, run a query like this:

mysql> SELECT user, host FROM mysql.user WHERE user='sveta' ORDER
BY host DESC;
+-------+-----------+
| user  | host      |
+-------+-----------+
| sveta | localhost |
| sveta | %         |
+-------+-----------+
2 rows in set (0.00 sec)

MySQL sorts rows in this table from the most specific to the least specific host value and uses the first value found. Therefore, it connected me as the user account sveta@localhost, which does not have CREATE privileges.

• USER() and CURRENT_USER(), together with the query SELECT user, host FROM mysql.user ORDER BY host DESC, are the first resort in case of a permission issue.

Another issue with privileges arises when it is not possible to connect as the specified user. In this case, you can usually learn the reason from the error message, which looks similar to the following:

$mysql -usveta books
ERROR 1044 (42000): Access denied for user 'sveta'@'localhost' to database 'books'

After seeing this, you know the user credentials. Connect as the root superuser, and check whether such a user exists and has the required privileges:

mysql> SELECT user, host FROM mysql.user WHERE user='sveta' ORDER
BY host DESC;
+-------+-----------+
| user  | host      |
+-------+-----------+
| sveta | localhost |
| sveta | %         |
+-------+-----------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'sveta'@'localhost';
+-------------------------------------------------+
| Grants for sveta@localhost                      |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'sveta'@'localhost'       |
| GRANT SELECT ON `book`.* TO 'sveta'@'localhost' |
+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'sveta'@'%';
+-------------------------------------------------+
| Grants for sveta@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'sveta'@'%'               |
| GRANT ALL PRIVILEGES ON `book`.* TO 'sveta'@'%' |
+-------------------------------------------------+
2 rows in set (0.00 sec)

In this output, you can see that user 'sveta'@'localhost' has privileges only on the database named book, but no privileges on the books database. Now you can fix the problem: give user sveta@localhost the necessary privileges.

The previous examples discussed users who lacked necessary privileges. Users who are granted superfluous privileges can be handled in the same way; you just need to revoke the unnecessary privileges.

Warning

MySQL privileges are detached from objects they control: this means mysqld does not check for the existence of an object when you grant a privilege on it and does not remove a privilege when all objects it grants access to are removed. This is both a great advantage, because it allows us to grant necessary privileges in advance, and a potential cause of an issue if used without care.

As a best practice, I recommend careful study of how MySQL privileges work. This is especially important if you grant privileges on the object level because you should understand how a grant on one level affects grants of others. The same considerations apply to revoking privileges, which can be even more critical because if you think you revoked a privilege and it is still present, this allows unwanted access.