Table of Contents for

MySQL in a Nutshell, 2nd Edition

This utility checks the privileges that a user has for a specific host and database. One use is to run it as a preliminary tool to check for user permissions before proceeding with a customized program that uses one of the APIs.

If MySQL was not installed in the default location for the version you’re using, you’ll have to set the variable MYSQL in the mysqlaccess script. Change it with a plain text editor. Look for the following line (near the beginning) and change the file path to where the mysql client is located:

 $MYSQL = '/usr/local/mysql/bin/mysql';  # path to mysql executable

With regard to the syntax, the hostname is the first argument and is optional. If not given, localhost is assumed. The username given in the second argument is the name of the user for which the utility is checking privileges. The third argument is the database against which to check privileges. The fourth argument involves several possible options, one of which could be the username by which the utility will access the server to gather information on the user named in the second argument. Here is an example of how you might use this utility:

mysqlaccess localhost marie workrequests -U russell -P

In this example, I give the utility the hostname, then the user I’m inquiring about, then the database name for which I want user privilege information. The -U option specifies the username with which to access the server to gather information. This user has full access to the mysql database. The -P instructs the utility to prompt me for a password.

Here are the results of the preceding inquiry:

Access-rights
for USER 'marie', from HOST 'localhost', to DB 'ANY_NEW_DB'
        +-----------------+---+ +-----------------+---+
        | Select_priv     | Y | | Shutdown_priv   | N |
        | Insert_priv     | N | | Process_priv    | N |
        | Update_priv     | N | | File_priv       | N |
        | Delete_priv     | N | | Grant_priv      | N |
        | Create_priv     | N | | References_priv | N |
        | Drop_priv       | N | | Index_priv      | N |
        | Reload_priv     | N | | Alter_priv      | N |
        +-----------------+---+ +-----------------+---+
NOTE:    A password is required for user 'reader' :-(
The following rules are used:
db    : 'No matching rule'
host  : 'Not processed: host-field is not empty in db-table.'
user:'localhost','marie','6ffa06534985249d','Y','N','N','N',
'N','N','N','N','N','N','N','N','N','N'

First, a table is presented that displays the privileges for the combination of the database named, the host given, and the user. This user has only SELECT privileges.

Additionally, the results are given in raw form for each component. This user’s privileges are the same for all databases and hosts (i.e., there are no entries in the db or the host tables in the mysql database), so there aren’t any results for those particular components. For the user component, the command displays details without labels, but they are presented in the order that they are found in the user table in the mysql database. The third field is the password in the encrypted format in which it is stored. The Ys and Ns are the settings for each user privilege.

Here is a list of options available for this utility in alphabetical order: