Learning MySQL

Privileges

So far, we’ve shown you how to add new users and grant privileges for databases, tables, and columns. In this section, we discuss the privileges in more detail and explain which ones are used at the global, database, table, and column level. Then we discuss how the different privilege levels interact.

You can see a list of all available privileges by running the SHOW PRIVILEGES command in the MySQL monitor; Table 9-1 lists some of the more important of these. Each row shows a privilege, followed by a description, and then a list of the four levels at which the privilege can be granted. For example, the second row shows the ALTER privilege that controls whether the ALTER TABLE statement can be used, and shows that it can be controlled at the global, database, and table levels:

Global level

You can use ON *.* in a GRANT statement to grant a user a particular privilege across all databases on the server.

Database level

You can use, for example, music.* to grant a privilege for one or more databases.

Table level

You can use, for example, music.album to grant a privilege for one or more tables in a database.

Column level

Grants access for one or more columns in a table in a database (but isn’t available for ALTER). You grant column-level access using a comma-separated list in parentheses after the privilege, as in, for example:

GRANT SELECT (album_name, album_id) ON music.album

In this chapter, we explain how to manage privileges using the GRANT statement. Many of the statements affected ...