10.1 Account Basics

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.

Database-Specific Passwords

We indicated that users are stored as username/password/location. It's important to note that one qualifier not included is the database. For instance:

mysql> GRANT SELECT ON Foo.* to 'nobody'@'localhost' IDENTIFIED BY 'FooPass'; mysql> GRANT SELECT ON Bar.* to 'nobody'@'localhost' IDENTIFIED BY 'BarPass';

You might think, to look at that, that user nobody connects to Foo using FooPass as his password and to Bar using BarPass as his password. That's not the case. What actually happens is that nobody has his password changed in the users table to BarPass, and any connections to the Bar database using FooPass will fail to authenticate.

This is especially important because it means that if you want to limit access for an application to one database and not another, your codebase may have the password to "its" database encoded into it. If someone sees that source code, and you use the same MySQL user for some other application that accesses a different database, the person who sees one set of source code will now know how to gain access to the other database.

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).

10.1.1 Privileges

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 boolean—either 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.

Table 10-1. Access control privileges

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.

10.1.1.1 Global privileges

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 executed—including 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!

< Day Day Up >