Managing Users and Privileges - Learning MySQL - page 308

Global Database Table Column

music artist

artist

artist

album

flight

track

university

played

Figure 9-1. The privilege hierarchy

UPDATE statement on any table or column in any database. If you grant privileges for
only the music database, the privileges are available for just it and its tables and columns.
Privileges never propagate up the hierarchy; for example, if you grant privileges for only
a column, those privileges don't apply for the table, database, or server.
When you run a statement, your privileges to run that statement are determined using
a logical OR operation. The operation checks whether you have any of the following for
the statement:
• Global privileges
• Database privileges
• Table privileges
• Column privileges
If any of these permit the statement, it proceeds. This has an important consequence:
if you allow a privilege for a statement at a level, it doesn't matter if it's allowed or
disallowed at another level. This can lead to unexpected behavior. For example, if you
revoke a previously granted permission to SELECT from the artist table, access will not
be revoked if the user still has the SELECT privilege to the music database or at the global
level. “Users and Hosts” discusses how users are allowed or disallowed server connec-
tions, and “Revoking Privileges” explains how to revoke privileges.

Users and Hosts
So far, we've discussed the steps to grant privileges, as well as how these privileges
interact in a hierarchy. However, we've skipped over the basic principles of connecting
to the server and explaining how MySQL validates a connection. This section covers
these topics and helps you understand how you connect to a MySQL server.