Chapter 6. Security

6.1.2.1. GRANT and REVOKE

GRANT privilege  [(column)] [, privilege [(columns)], ...]
ON table1, table2, ..., tablen
TO user [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password'], ...]
[WITH GRANT OPTION]

The simplest form of this statement looks like the following SQL statement:

GRANT SELECT ON Book to andy;

This statement gives the user andy the ability to read data from the table Book. The GRANT statement has three basic components: the privilege, the object, and the user.

The privilege is a keyword that describes the operation the user is being granted. MySQL supports the following privileges:

Table 6-2. Privileges and the objects to which they apply

The ON clause does not address privileges targeting columns. When applying a privilege to a column, you still specify the table in the ON clause, but you specify the column right after the name of the privilege:

GRANT SELECT ( title, authorID ) ON Library.Book TO andy;

In this case, we have granted andy the ability to execute queries limited to the title and authorID from the Book table in the database Library.

The final component of the GRANT statement specifies who is being granted the privilege. The simplest form identifies users without indicating where they are connecting from:

GRANT ALL ON Library.* TO andy, tim, randy, george;

[email protected]

A valid MySQL username is any 16 characters or less. These characters do not need to be ASCII characters, but we recommend ASCII characters since some clients are not able to handle alternative character sets. If a username does consist of characters other than ASCII alphanumeric characters, you must enclose it in either single or double quotes. Usernames are case insensitive. In other words, MySQL treats fred, Fred, and fReD as the same user.

You can alternatively specify that same subnet using a netmask: "192.168.2.0/255.255.255.0". Failure to specify a location is the same as specifying user @"%". If you specify a host name, you should make sure it resolves via your host's configuration file or DNS.

TIP: As a general rule, you should always provide passwords for new users. Blank passwords are huge security holes for the database.

TIP: The default root user has complete control over every aspect of MySQL. Because a clean MySQL installation provides a root user with no password set, the very first thing you should do once you have installed MySQL is change the root password! As an added layer of security, you can go into the MySQL security tables described later in this chapter and change the name of the DBA user from root to something else.

REVOKE privilege [(columns)] [, privilege [(columns)] ...]
ON table1, table2, ..., tablen
FROM user1, user2, ..., usern

Only a few elements of the REVOKE statement differ from the GRANT statement:

6.1.2.2. The security tables

Table 6-3. The schema for the user table

The most basic way to add a user to MySQL is:

INSERT INTO user ( User, Host, Password )
VALUES ( 'randy', 'www.mysql.com', PASSWORD('randyspass') );

This new user will not be able to do anything with MySQL since you have not provided the user with any privileges. The xxx_priv columns contain the privileges assigned to individual users. The values of these columns can be either 'Y' or 'N'.

The final four columns are new to MySQL 4.0. They exist to support SSL and X.509 certificates.

1. MySQL considers more-specific Host values before less-specific values. In other words, MySQL first considers values with no wildcards followed by mixed values and, finally, pure wildcards. MySQL views subnets to be less specific than individual hosts. MySQL considers the value "%" last.

2. MySQL examines rows with the same Host value according to the specificity of their User values. MySQL considers blank User values last. A blank User value therefore defines the default access rights for a given host. MySQL ships with default access rights for the localhost.

Consider the following User/Host values from the user table:

1. root/localhost

2. andy/localhost

3. [blank]/localhost

4. andy/"%"

5. tim/"%.imaginary.com"

6. randy/"%"

Table 6-4. Resolution of various User/Host combinations

When MySQL finds no match in the User table for a connection, it rejects the connection. When it does find a match, it checks the password provided by the connection against the value in the Password column. If there is a match, the connection is allowed. Otherwise, the connection is denied.

Once a client connection is allowed, MySQL performs security checks for every SQL statement executed by the query. These security checks require all the security tables.

During query execution, MySQL first consults the user table using the row matched when the user connected. This row contains the user's global rights. In other words, if a user has a 'Y' value for a given privilege in this table, no further security checks are made—the operation is allowed. You should therefore be extremely cautious when setting privileges in the user table.

Table 6-5. The schema for the db table

Table 6-6. The schema for the host table

This table is basically an extension of the db table. It provides default database access privileges for specific hosts where requests originate. MySQL checks the host table for matching Host and Db values. When it finds such a row, it uses the privileges defined in that row combined with the values from the matching row in the db table to determine whether to allow the user's statement to execute. The fact that the privilege must be positive in both tables is critical. This feature enables you to define a privilege granted to most people but selectively denied for specific hosts.

Table 6-7. The schema of the tables_priv table

The remaining fields are new to this table. The Grantor field stores the name of the user who granted the particular privilege, and the Timestamp field indicates when the privilege was granted or modified. The final two columns, Table_priv and Column_priv, contain set values. For the Table_priv column, the values indicate the privileges that apply to the table as a whole. The Column_priv values, on the other hand, indicate the privileges applicable to individual columns.

Table 6-8. The schema of the columns_priv table