Learning MySQL

Checking Privileges

We’ve explained how to grant privileges and how to understand the scope of those privileges. This section explains how to identify the privileges that a user has, and how to revoke those privileges.

If you’ve been following our examples, you have created more than 10 users so far in this chapter, and you probably can’t remember all of them. It’s important to know the users you have defined and the privileges that these users have, and that you understand how connections are verified: without careful management, you can accidentally allow more privileges than you planned, or allow connections by users you didn’t want to grant access to. Fortunately, there are a few tools available to help you explore access privileges.

The simplest method to check the privileges of a user is to use the SHOW GRANTS statement. You can execute this statement to check the privileges of other users only if you have access to the mysql database; however, you can always check your own privileges. If you want to experiment, it’s best to log in now as the root user or another user with sufficient global privileges. We explain the role of the mysql database in privilege management later in Managing Privileges with SQL.”

After logging in to the monitor, you can check the current user’s privileges with:

mysql> SHOW GRANTS; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ ...