Learning MySQL

Managing Privileges with SQL

MySQL privileges are managed in five tables in the mysql database. You can manage this database yourself, using queries to manage users and privileges rather than using the GRANT and REVOKE statements. It’s useful to know how to do this, because it can save you time and allow you to access features that aren’t available through GRANT and REVOKE. This section explains how the privileges are managed and shows you how to modify them directly.

The privileges are managed in the mysql database. As we’ve discussed previously, only administrators should have access to this database and, therefore, you’ll usually need to log in as the root user to follow the steps in this section. In MySQL 5.0, the database contains 17 tables, but only 5 are relevant to privileges: user, db, tables_priv, columns_priv, and host.

The user Table

The user table manages users and global privileges. Its structure is straightforward, even though it has around 30 columns. Each row includes a User, Password, and Host column; these are the credentials that are used to match against connection attempts and authenticate users. All three are optional; the User and Password values are optional because MySQL includes support for anonymous access and because it’s possible for a user to not have a password (although this isn’t recommended). We explain why the Host value is optional later in this section. Each row also contains a Y or N for each possible privilege—for example, Select_priv and Alter_priv ...