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