External Security: Securing Network Access

The MySQL security system is flexible. It allows you to set up user access privileges in many different ways. Normally, you do this by using the GRANT and REVOKE statements, which modify for you the grant tables that control client access. However, you may have an older version of MySQL that does not support these statements (they were not functional prior to MySQL 3.22.11), or you may find that user privileges don't seem to be working the way you want. For situations like this, it's helpful to understand the structure of the MySQL grant tables and how the server uses them to determine access permissions. Such an understanding allows you to add, remove, or modify user privileges by modifying the grant tables directly. It also allows you to diagnose privilege problems when you examine the tables.

I assume that you've read "Managing User Accounts" in Chapter 11 and that you understand how the GRANT and REVOKE statements work. GRANT and REVOKE provide a convenient way for you to set up MySQL user accounts and associate privileges with them, but they are just a front end. All the real action takes place in the MySQL grant tables.

Structure and Contents of the MySQL Grant Tables

Access to MySQL databases by clients that connect to the server over the network is controlled by the contents of the grant tables. These tables are located in the mysql database and are initialized during the process of installing MySQL on a machine for the first time (as described in Appendix A, "Obtaining and Installing Software," for example). The five grant tables, user, db, host, tables_priv, and columns_priv, are shown in Tables 12.1 and 12.2.

The contents of the grant tables are used as follows:

• user

  The user table lists users that may connect to the server and their passwords, and it specifies which global (superuser) privileges they have, if any. Any privileges that are enabled in the user table entry are global privileges and apply to all databases. If you enable the DELETE privilege here, the user listed in the entry can delete records from any table, for example. Think carefully before you do this.

  Generally, it's best to leave all the privileges turned off in user table entries and specify privileges using the other tables, which are more restrictive. The exceptions are for superusers such as root, but these are usually rare.

• db

  The db table lists databases and which users have privileges for accessing them. A privilege specified here applies to all tables in a database.

• host

  The host table is used in combination with the db table to control database access privileges to particular hosts at a finer level than is possible with the db table alone. This table is unaffected by the GRANT and REVOKE statements, so you may find that you never use it at all.

• tables_priv

  The tables_priv table specifies table-level privileges. A privilege specified here applies to all columns in a table.

• columns_priv

  The columns_priv table specifies column-level privileges. A privilege specified here applies to a particular column in a table.

In the section "Setting Up Users Without GRANT," we'll discuss how the GRANT statement acts to modify these tables and how you can achieve the same results by modifying the grant tables directly.

The tables_priv and columns_priv tables were introduced in MySQL 3.22.11 (at the same time as the GRANT statement). If you have an older version of MySQL, your mysql database will have only the user, db, and host tables. If you have upgraded to 3.22.11 or later from an older version, but do not have the tables_priv and columns_priv tables, run the mysql_fix_privileges_tables script to create them.

There is no rows_priv table because MySQL doesn't provide record-level privileges. For example, you cannot restrict a user to only those rows in a table that contain a particular value in some column. If you need this capability, you must provide it using application programming. If you want to perform advisory record-level locking, you can do that using the GET_LOCK() function described in Appendix C, "Operator and Function Reference."

The grant tables contain two kinds of columns: scope columns that determine when an entry applies, and privilege columns that determine which privileges an entry grants. (Some of the grant tables contain other miscellaneous columns, but they don't concern us here.)

Grant Table Scope Columns

The grant table scope columns specify when table entries apply. Each grant table entry contains User and Host columns to indicate that the entry applies when the given user connects from the given host. (The host table is an exception; it's used in a special way that we won't get into just yet.) The other tables contain additional scope columns. For example, the db table contains a Db column to indicate which database the entry applies to. Similarly, the tables_priv and columns_priv tables contain scope fields that narrow the scope to a particular table in a database or column in a table.

Grant Table Privilege Columns

The grant tables also contain privilege columns. These indicate which privileges are held by the user who is specified in the scope columns. The privileges supported by MySQL are shown in the following list. The list uses the privilege names that are used for the GRANT statement. For the most part, the names of the privilege columns in the user, db, and host tables bear an obvious resemblance to the privilege names discussed in connection with the GRANT statement in Chapter 11. For example, the Select_priv column corresponds to the SELECT privilege.

Database and Table Privileges

The following privileges apply to operations on databases and tables:

• ALTER

  Allows you to use the ALTER TABLE statement. This actually is simply a first-level privilege; you must have additional privileges, depending on what you want to do with the table.

• CREATE

  Allows you to create databases and tables. This does not allow you to create indexes.

• DELETE

  Allows you to remove existing records from tables.

• DROP

  Allows you to drop databases and tables. This does not allow you to drop indexes.

• INDEX

  Allows you to create or drop indexes from tables.

• INSERT

  Allows you to insert new records in tables.

• REFERENCES

  This is currently unused.

• SELECT

  Allows you to retrieve data from tables using SELECT statements. This privilege is unnecessary for SELECT statements that do not involve tables, such as SELECT NOW() or SELECT 4/2.

• UPDATE

  Allows you to modify existing records in tables.

Administrative Privileges

The following privileges apply to administrative operations that control the operation of the server or a user's ability to grant privileges:

• FILE

  Allows you to tell the server to read or write files on the server host. This privilege should not be granted without just cause; it is dangerous, as discussed in "Grant Table Risks to Avoid." The server does take certain precautions to keep the use of this privilege within certain bounds. You can read only files that are world-readable, and thus likely not to be considered protected in any way. A file that you are writing must not already exist. This prevents you from coercing the server into overwriting important files, such as /etc/passwd, or database files in a database belonging to someone else. For example, if this constraint were not enforced, you could completely replace the contents of the grant tables in the mysql database.

  If you grant the FILE privilege, be sure not to run the server as the UNIX root user, because root can create new files anywhere in the file system. If you run the server as an unprivileged user, the server can create files only in directories accessible to that user.

• GRANT

  Allows you to give to other users the privileges you have yourself, including the GRANT privilege.

• PROCESS

  Allows you to view information about the threads (processes) currently executing within the server by using the SHOW PROCESSLIST statement or the mysqladmin processlist command. This privilege also allows you to kill threads with the KILL statement or the mysqladmin kill command.

  You can always see or kill your own threads. The PROCESS privilege gives you the ability to do these things with any thread.

• RELOAD

  Allows you to perform a variety of server administration operations. You can issue the FLUSH SQL statement. You can also perform the mysqladmin commands reload, refresh, flush-hosts, flush-logs, flush-privileges, and flush-tables.

  This is not normally a dangerous privilege to grant, even though it is administrative in nature.

• SHUTDOWN

  Allows you to shut down the server with mysqladmin shutdown.

  In the user, db, and host tables, each privilege is specified as a separate column. These columns are all declared with a type of ENUM("N","Y"), so the default value for every privilege is "N" (off). Privileges in the tables_priv and columns_priv tables are represented by a SET, which allows privileges to be specified in any combination using a single column. These two tables are newer than the other three, which is why they use the more efficient representation. (It's possible that the user, db, and host tables may be reorganized in the future to represent privileges by SET columns as well.)

The Table_priv column in the tables_priv table is defined like this:

SET('Select','Insert','Update','Delete','Create','Drop',
    'Grant','References','Index','Alter')

The Column_priv column in the columns_priv table is defined like this:

SET('Select','Insert','Update','References')

There are fewer column privileges than table privileges because fewer operations make sense at the column level. For example, you can create a table, but you can't create an isolated column.

The user table contains columns for some privileges that are not present in any of the other grant tables: File_priv, Process_priv, Reload_priv, and Shutdown_priv. These privileges apply to operations you tell the server to perform that aren't associated with any particular database or table. It doesn't make sense to allow a user to shut down the server based on what the current database is, for example.

How the Server Controls Client Access

There are two stages of client access control when you use MySQL. The first stage occurs when you attempt to connect to the server. The server looks at the user table to see if it can find an entry that matches your name, the host you're connecting from, and the password you supplied. If there is no match, you can't connect. If there is a match, the connection is established and you proceed to the second stage. In this stage, for each query you issue, the server checks the grant tables to see whether or not you have sufficient privileges to perform the query. The second stage continues until your session with the server ends.

This section describes in more detail the rules that the MySQL server uses to match grant table entries to incoming client connection requests and to queries. This includes the types of values that are legal in the grant table scope columns, the way privilege information in the grant tables is combined, and the order in which table entries are checked.

Scope Column Contents

Some of the scope columns require literal values, but most of them allow wildcard or other special values.

• Host

  A Host column value may be a hostname or an IP number. The value localhost means the local host, but it matches only if you actually connect using a host value of localhost, not if you connect using the hostname. Suppose the name of the local host is pit-viper.snake.net and there are two entries for you in the user table, one with a Host value or localhost and the other with a value of pit-viper.snake.net. The entry with localhost will match only if you connect to localhost. The other will match only if you connect to pit-viper.snake.net. If you want users to be able to connect either way, you need two entries in the user table.

  You may also specify Host values using wildcards. The SQL pattern characters "%" and "_" may be used and have the same meaning as when you use the LIKE operator in a query. (REGEX patterns are not allowed.) The SQL pattern characters work for both names and IP numbers. For example, %.wisc.edu matches any host in the wisc.edu domain, and %.edu matches any host at any educational institution. Similarly, 192.168.% matches any host in the 192.168 class B subnet, whereas 192.168.3.% matches any host in the 192.168.3 class C subnet.

  The value % matches any host at all, and may be used to allow a user to connect from anywhere. A blank Host value is the same as %. (Exception: In the db table, a blank Host value means "check the host table for further information." This process is described in "Query Access Verification."

  As of MySQL 3.23, you can also specify IP numbers with a netmask indicating the number of bits to use for the network number. For example, 192.168.128.0/17 specifies a 17-bit network number and matches any host with 192.168.128 in the first 17 bits of its IP address.

• User

  Usernames must be either literal values or blank (empty). A blank value matches any user. % as a User value does not mean blank; instead, it matches a user with a literal name of "%", which is probably not what you want.

  When an incoming connection is verified against the user table and the entry that matches contains a blank User value, the client is considered to be an anonymous user.

• Password

  Password values are either blank (empty) or non-blank, and wildcards are not allowed. A blank password doesn't mean that any password matches. It means that the user must specify no password.

  Passwords are stored as encrypted values, not literal text. If you store a literal password in the Password column, the user will not be able to connect! The GRANT statement and the mysqladmin password command encrypt the password for you automatically, but if you use statements such as INSERT, REPLACE,UPDATE, or SET PASSWORD, be sure to specify the password using PASSWORD("new_password") rather than simply using "new_password".

• Db

  In the columns_priv and tables_priv tables, Db values must be literal database names; patterns and blanks are not allowed. In the db and host tables, Db values may be specified literally or by using the SQL pattern characters "%" or "_" to specify a wildcard. A value of % or blank matches any database.

• Table_name, Column_name

  Values in these columns must be literal table or column names; patterns and blanks are not allowed.

  Some scope columns are treated by the server as case sensitive; others are not. The rules are summarized in Table 12.3. Note in particular that Table_name values are always treated as case sensitive, even though treatment of table names in queries depends on the case sensitivity of the file system on which the server runs (case sensitive under UNIX, but not under Windows).

Query Access Verification

Each time you issue a query, the server checks whether or not you have sufficient privileges to execute it. It does this by checking, in order, the user, db, tables_priv, and columns_priv tables, until it either determines you have proper access or it has searched all the tables in vain. More specifically:

In boolean terms, the privileges in the grant tables are used by the server like this:

user OR db OR tables_priv OR columns_priv

I see that you're wondering why the preceding description refers to only four grant tables when there are five grant tables. Okay, you caught me. The way the server really checks access permissions is like this:

user OR (db AND host) OR tables_priv OR columns_priv

I showed the simpler expression first because the host table is not affected by the GRANT or REVOKE statements. If you always use GRANT and REVOKE to manage user privileges, you'll never need to think about the host table. But here's how it works, if you want to know:

• When the server checks for database-level privileges, it looks at the db table entry for the client. If the Host column value is blank,it means "Check the host table to find out which hosts can access the database."

• The server looks in the host table for entries with the same Db column value as the entry from the db table. If no host table entry matches the client host, no database-level privileges are granted. If any of those entries do have a Host column value that matches the host from which the client is connecting, the db table entry and the host table entry are combined to produce the client's database-level privileges.

  However, the privileges are combined using a logical AND, which means that the client doesn't have a given privilege unless it's present in both entries. In this way, you can grant a basic set of privileges in the db table entry, then selectively disable them for particular hosts using host table entries. For example, you might allow access to a database from all hosts in your domain, but turn off database privileges for hosts that are located in less secure areas.

The preceding description no doubt makes access checking sound like a rather complicated process, especially when you consider that the server checks privileges for every single query that clients issue. However, the process is quite fast because the server doesn't actually look up information from the grant tables for every query. Instead, it reads the contents of the tables into memory when it starts up, then verifies queries using the in-memory copies. This gives a performance boost to access-checking operations, but has a rather important side effect: If you change the contents of the grant tables directly, the server won't notice the privilege change.

For example, if you add a new MySQL user by using an INSERT statement to add a new record to the user table, the user named in the entry won't be able to connect to the server. This is something that often confuses new administrators (and sometimes more-experienced ones!), but the solution is quite simple: Tell the server to reload the contents of the grant tables after you change them. You can do this by issuing a FLUSH PRIVILEGES statement or by executing mysqladmin flush-privileges (or mysqladmin reload if you have an old version that doesn't support flush-privileges).

Scope Column Matching Order

The MySQL server sorts entries in the grant tables in a particular way, then tries to match incoming connections by looking through the entries in order. The first match found determines the entry that is used. It's important to understand the sorting order that MySQL uses, especially for the user table. This seems to trip up a lot of people in their attempts to understand MySQL security.

When the server reads the contents of the user table, it sorts entries according to the values in the Host and User column. The Host value is the dominant value (entries with the same Host value are sorted together, then ordered according to the User value). However, sorting is not lexicographic, or rather, it's only partially so. The principle to keep in mind is that literal values are preferred over patterns. This means that if you're connecting from boa.snake.net and there are entries with Host values of boa.snake.net and %.snake.net, the first entry will be preferred. Similarly, %.snake.net is preferred over %.net, which is in turn preferred over %. IP number matching works that way, too. For a client connecting from a host with an IP number of 192.168.3.14, entries with Host values of 192.168.3.4, 192.168.3.%, 192.168.%, 192.%, and % will match, in that order.

Grant Table Risks to Avoid

This section describes some precautions to observe when you grant privileges, and the attendant risks of unwise choices. In general, you want to grant superuser privileges sparingly. That is, don't enable privileges in user table entries. Use the other grant tables instead, to restrict user privileges to particular databases, tables, or columns. Privileges in the user table allow the user to affect the operation of your server or to access any table in any database.

Don't grant privileges for the mysql database. A user with privileges on the database containing the grant tables may be able to modify the tables to acquire privileges on any other database as well. Granting privileges that allow a user to modify the mysql database tables also effectively gives that user a global GRANT privilege. If the user can modify the tables directly, it's pretty much equivalent to being able to issue any GRANT statement you can think of.

The FILE privilege is particularly dangerous; don't grant it lightly. Here's an example of something a user with the FILE privilege can do:

CREATE TABLE etc_passwd (pwd_entry TEXT);
LOAD DATA INFILE "/etc/passwd" INTO TABLE etc_passwd;
SELECT * FROM etc_passwd;

After issuing these statements, the user has the contents of your password file. In fact, the contents of any publicly readable file on the server can be accessed over the network by any user with the FILE privilege.

The FILE privilege also can be exploited to compromise databases on systems that aren't set up with sufficiently restrictive file permissions. This is a reason why you should set the data directory contents to be readable only by the server. If files corresponding to database tables are world readable, not only can any user with an account on the server host read them, but any client user with the FILE privilege can connect over the network and read them, too! The following procedure demonstrates how:

• Create a table with a LONGBLOB column:

  USE test
  CREATE TABLE tmp (b LONGBLOB)
  

• Use the table to read in the contents of each file corresponding to the table you want to steal, then write out the table contents to a file in your own database:

  LOAD DATA INFILE "./other_db/x.frm" INTO TABLE tmp
      FIELDS ESCAPED BY "" LINES TERMINATED BY ""
  SELECT * FROM tmp INTO OUTFILE "y.frm"
      FIELDS ESCAPED BY "" LINES TERMINATED BY ""
  DELETE FROM tmp
  LOAD DATA INFILE "./other_db/x.ISD" INTO TABLE tmp
      FIELDS ESCAPED BY "" LINES TERMINATED BY ""SELECT * 
  FROM tmp INTO OUTFILE "y.ISD""
      FIELDS ESCAPED BY "" LINES TERMINATED BY ""
  DELETE FROM tmp
  LOAD DATA INFILE "./other_db/x.ISM" INTO TABLE tmp
      FIELDS ESCAPED BY "" LINES TERMINATED BY ""
  SELECT * FROM tmp INTO OUTFILE "y.ISM"
      FIELDS ESCAPED BY "' LINES TERMINATED BY ""
  

• You now have a new table y containing the contents of other_db.x, and you have full access to it.

To avoid having someone attack you in the same way, set the permissions on your data directory contents according to the instructions in "Internal Security: Securing Data Directory Access." You can also use the --skip-show-database option when you start the server to limit users from using SHOW DATABASES and from using SHOW TABLES for databases they have no access to. This helps prevent users from finding out about databases and tables they shouldn't be accessing.

The ALTER privilege can be used in ways you may not intend. Suppose you want user1 to be able to access table1 but not table2. A user with the ALTER privilege may be able to subvert your intent by using ALTER TABLE to rename table2 to table1.

Be careful with the GRANT privilege. Two users with different privileges that both have the GRANT privilege can make each other's access rights more powerful.

Setting Up Users Without GRANT

If you have a version of MySQL prior to 3.22.11, you can't use the GRANT (or REVOKE) statements to set up users and access privileges, but you can modify the contents of the grant tables directly. Much of this is easier if you understand how the GRANT statement acts to modify the grant tables. Then you can just do the same thing yourself by issuing INSERT statements manually. (The INSERT statement may be ugly and difficult to enter correctly, but that's a different issue. This ugliness is in fact one reason why GRANT is so much easier to use.)

When you issue a GRANT statement, you specify a username and hostname, and possibly a password. A user table entry is created for the user, and these values are recorded in the User, Host, and Password columns of the entry. If you specify global privileges in the GRANT statement, those privileges are recorded in the privileges columns of the entry. One thing to be careful about is that the GRANT statement encrypts the password for you, but INSERT does not; you'll need to use the PASSWORD() function to encrypt passwords in your INSERT statements.

If you specified database-level privileges, the username and hostname are recorded in the User and Host columns of a db table entry. The database you granted privileges for is recorded in the Db column, and the privileges you granted are recorded in the privileges columns.

For table-level and column-level privileges, the effects are similar. Entries are created in the tables_priv and columns_priv tables to record the username, hostname, and database, as well as the table or table and column as necessary. The privileges granted are recorded in the privilege columns.

If you keep the preceding description in mind, you should be able to do what GRANT does even without the GRANT statement. Remember that when you modify the grant tables directly, you'll need to tell the server to reload the grant tables or it won't notice your changes. You can force a reload by executing a mysqladmin flush-privileges or mysqladmin reload command. If you forget to do this, you'll be wondering why the server isn't doing what you want.

The following GRANT statement creates a superuser with all privileges, including the ability to grant privileges to other users:

GRANT ALL ON *.* TO ethel@localhost IDENTIFIED BY "coffee"
    WITH GRANT OPTION

The statement will create an entry for ethel@localhost in the user table, and turn on all the privileges there because that's where superuser (global) privileges are stored. To do the same thing with INSERT, the statement is:

INSERT INTO user VALUES("localhost","ethel",PASSWORD("coffee"),
   "Y","Y","Y","Y","Y',"Y","Y","Y","Y","Y","Y","Y","Y","Y")

That's one ugly INSERT statement! You may even find that it doesn't work, depending on your version of MySQL. The structure of the grant tables has changed on occasion and you may not have 14 privilege columns in your user table. Use SHOW COLUMNS to find out just what privilege columns each of your grant tables contains, and adjust your INSERT statements accordingly.

The following GRANT statement also creates a user with superuser status, but for only a single privilege:

GRANT RELOAD ON *.* TO flush@localhost IDENTIFIED BY"flushpass"

You may remember this statement from when we created the flush user in Chapter 11. The INSERT statement for this example is a bit simpler than for the preceding one; it's easier to list the column names and specify only the one privilege column. All the others will be set to the default of "N":

INSERT INTO user (Host,User,Password,Reload_priv)
    VALUES("localhost","flush",PASSWORD("flushpass"),"Y")

Database-level privileges are granted with an ON db_name.* clause rather than ON *.*:

GRANT ALL ON samp_db.* TO boris@localhost IDENTIFIED BY "ruby"

These privileges are not global, so they won't be stored in the user table. We still need to create an entry in the user table (so that the user can connect), but we also need to create a db table entry to record the database-level privileges:

INSERT INTO user (Host,User,Password)
    VALUES("localhost","boris",PASSWORD("ruby"))
INSERT INTO db VALUES("localhost","samp_db",'boris',
"Y","Y","Y","Y","Y","Y","N","Y","Y',"Y")

The "N" column is for the GRANT privilege; for a database-level GRANT statement that has WITH GRANT OPTION at the end, you'd set that column to "Y" instead.

To set table-level or column-level privileges, you use INSERT statements for the tables_priv or columns_priv tables. Of course, if you don't have the GRANT statement, you won't have those tables, either, because they all appeared in MySQL at the same time. If you do have the tables and want to manipulate them manually for some reason, be aware that you don't enable privileges using individual columns. You set either the tables_priv.Table_priv or columns_priv.Column_priv column to a SET value consisting of the privileges you want to enable. For example, to enable SELECT and INSERT privileges for a table, you'd set the Table_priv column to a value of "Select,Insert" in the relevant tables_priv entry.

If you want to modify privileges for a user whose MySQL account already exists, use UPDATE rather than INSERT. This is true whether you are adding or revoking privileges. To remove a user entirely, use DELETE to remove entries from each grant table in which the user has them.

If you prefer to avoid issuing queries to modify the grant tables directly, you may want to take a look at the mysqlaccess and mysql_setpermissions scripts that come with the MySQL distribution.

GRANT ALL ON samp_db.* TO fred@%.snake.net IDENTIFIED BY "cocoa"

% mysql -u fred -pcocoa samp_db
ERROR 1045: Access denied for user: 'fred@localhost'
(Using password: YES)

+---------------------+------+
| Host                | User |
+---------------------+------+
| localhost           | root |
| pit-viper.snake.net | root |
| localhost           |      |
| pit-viper.snake.net |      |
+---------------------+------+

+---------------------+------+
| Host                | User |

+---------------------+------+
| localhost           | root |
| pit-viper.snake.net | root |
| localhost           |      |
| pit-viper.snake.net |      |
| %.snake.net         | fred |
+---------------------+------+

+---------------------+------+

| Host                | User |
+---------------------+------+
| localhost           | root |
| localhost           |      |
| pit-viper.snake.net | root |
| pit-viper.snake.net |      |
| %.snake.net         | fred |
+---------------------+------+

mysql> DELETE FROM user WHERE User = "";