Consider first the example of a typical Unix login. You have a username and a password, along with, possibly, some other information such as the login owner's full name, telephone number, or other information. There is no distinction between the user dredd coming from foo.example.com and dredd coming from bar.example.com. To Unix, they are one and the same.
Each account in MySQL is composed of a username, password, and location (usually hostname, IP address, or wildcard). As we'll see, having a location associated with the username adds a bit of complexity to an otherwise simple system. The user joe who logs in from joe.example.com may or may not be the same as the joe who logs in from sally.example.com. From MySQL's point of view, they are completely different. They may even have different passwords and privileges.
MySQL uses a series of grant tables to keep track of users and the various privileges they can have. The tables are ordinary MyISAM tables[1] that live in the mysql database. Storing the security information itself in MySQL makes a lot of sense. It allows you to use standard SQL queries to make any security changes. There are no additional configuration files for MySQL to process. But, this also means that if the server is improperly configured, any user could make security changes!
[1] And they must remain ordinary MyISAM tables. Don't change their type.
Over the lifetime of a typical database connection, MySQL may perform three different types of security checks:
Authentication
Who are you? For each incoming connection, MySQL checks your username, the password you supplied, and the host from which you are connecting. Once it knows who you are, the information is used to determine your privileges.
Authorization
What are you allowed to do? Shutting down the server, for example, requires that you have the shutdown privilege.
Access control
What data are you allowed to see and/or manipulate? When you try to read or modify data, MySQL checks to see that you've been granted permission to see or change the columns you are selecting.
As you'll see, authorization and access control can be a bit difficult to distinguish in MySQL. Just remember that authorization applies to global privileges (discussed shortly), while access control applies to typical queries (SELECT, UPDATE, and so on).
Access control is made up of several privileges that control how you may use and manipulate the various objects in MySQL: databases, tables, columns, and indexes. For any combination of objects, the privileges are all booleaneither you have them or you don't. These per-object privileges are named after the SQL queries you use to trigger their checks. For example, you need the select privilege on a table to SELECT data from it.
Here's the full list of per-object privileges:
Select
Insert
Update
Index
Alter
Create
Grant
References
Not all privileges apply to each type of object in MySQL. The insert privilege is checked for all of them, but the alter privilege applies only to databases and tables. That makes perfect sense, because you insert data into columns all the time, but there's no ALTER COLUMN command in SQL. Table 10-1 lists which privileges apply to each type of object in MySQL.
|
Privilege |
Databases |
Tables |
Columns |
|---|---|---|---|
|
Select |
|
|
|
|
Insert |
|
|
|
|
Update |
|
|
|
|
Delete |
|
| |
|
Index |
|
| |
|
Alter |
|
| |
|
Create |
|
| |
|
Drop |
|
| |
|
Grant |
|
| |
|
References |
|
|
|
While most of those privileges are rather straightforward, a few deserve some additional explanation:
Select
The select privilege is required for SELECT queries that access data stored in MySQL. No privilege is needed to perform simple math (SELECT 2*5), date/time conversions (SELECT Unix_TIMESTAMP(NOW( ))) and formatting, or various utility functions (SELECT MD5('hello world')).
Index
This single privilege allows you to create and drop indexes. Even though index changes are made via ALTER TABLE commands, the index privilege is what matters.
Grant
When using the GRANT command (described later), you may specify WITH GRANT OPTION to give the user the grant privilege on a table. This privilege allows the user to grant any rights you have granted him to other users. In other words, he can share his privileges with another user.
References
The references privilege controls whether or not you may reference a column in a given table as part of a foreign key constraint.
In addition to the per-object privileges, there is a group of privileges that are concerned with the functioning of MySQL itself and are applied server-wide. These are the authorization checks mentioned earlier:
Reload
The reload privilege is the least harmful of the server-wide privileges. It allows you to execute the various FLUSH commands, such as FLUSH TABLES, FLUSH STATUS, and so on.
Shutdown
This privilege allows you to shut down MySQL.
Process
The process privilege allows you to execute the SHOW PROCESSLIST and KILL commands. By watching the processlist in MySQL, you can capture raw SQL queries as they are being executedincluding the queries that set passwords.
File
This privilege controls whether you can execute a LOAD DATA INFILE... command. The danger in allowing this is that a user can use the command to read an arbitrary file into a table, as long as it is readable by the mysqld process.
Super
This privilege allows you to KILL any query on the server. Without it, you're limited to only those queries that belong to you.
Each server-wide privilege has far-reaching security implications, so be very cautious when granting any of them!