Team LiB
Previous Section Next Section

External Security: Preventing Unauthorized 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 on your behalf the grant tables that control client access. However, you might find that user privileges don't seem to be working the way you want. For such situations, 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 MySQL 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. (In fact, the section "Setting Up MySQL Accounts Without GRANT Statements" later in this chapter discusses how to modify the grant tables directly to achieve the same results that you get by issuing GRANT statements.)

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). These tables are named user, db, tables_priv, columns_priv, and host. They are used as follows:

  • The user table lists accounts for users that can connect to the server, their passwords, and which global (superuser) privileges each user has, if any. It's important to recognize that any privileges that are enabled in the user table are global privileges that apply to all databases. For example, if you enable the DELETE privilege in a user table entry, the account associated with the entry can delete records from any table in any database. Think carefully before you do this.

    Because of the superuser nature of privileges specified in the user table, it's generally best to leave all the privileges turned off for entries in this table and list more specific privileges in other tables that are more restrictive. There are two exceptions to this principle:

    • Superusers such as root and other administrative accounts need global privileges to operate the server. These accounts tend to be few.

    • A few specific global privileges usually can be granted safely. These pertain to creating temporary tables, locking tables, and being able to use the SHOW DATABASES statement. Many installations will grant these; others where tighter control is desired or necessary will not.

    The user table also has columns for SSL options that pertain to the establishment of secure connections with SSL, and columns for resource management that can be used to prevent a given account from monopolizing the server.

  • The db table lists which accounts have privileges for which databases. If you grant a privilege here, it applies to all tables in a database.

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

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

  • 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 it's likely that you will never use it at all.

The structure of each grant table is shown in the next several tables, broken down by type of column. All grant tables contain two primary kinds of columns: scope-of-access columns that determine when an entry applies, and privilege columns that determine which privileges an entry grants. The privilege columns can be subdivided further into columns for administrative operations and those that are related to database and table operations. The user table has additional columns for SSL connections and resource management; these are present only in the user table because they apply globally. Some of the grant tables contain other miscellaneous columns, but they don't concern us here because they have no bearing on account management.

Table 12.1. Grant Table Scope-of-Access Columns

Scope-of-Access Columns

user Table

db Table

tables_priv Table

columns_priv Table

host Table

Host

Host

Host

Host

Host

User

User

User

User

 

Password

Db

Db

Db

Db

  

Table_name

Table_name

 
   

Column_name

 


Table 12.2. Grant Table Privilege Columns

ADMINISTRATIVE PRIVILEGE COLUMNS

user Table

db Table

host Table

File_priv

  

Grant_priv

Grant_priv

Grant_priv

Process_priv

  

Reload_priv

  

Repl_client_priv

  

Repl_slave_priv

  

Show_db_priv

  

Shutdown_priv

  

Super_priv

  

DATABASE/TABLE PRIVILEGE COLUMNS

user Table

db Table

host Table

Alter_priv

Alter_priv

Alter_priv

Alter_routine_priv

Alter_routine_priv

Alter_routine_priv

Create_priv

Create_priv

Create_priv

Create_routine_priv

Create_routine_priv

Create_routine_priv

Create_tmp_table_priv

Create_tmp_table_priv

Create_tmp_table_priv

Create_view_priv

Create_view_priv

Create_view_priv

Delete_priv

Delete_priv

Delete_priv

Drop_priv

Drop_priv

Drop_priv

Execute_priv

Execute_priv

Execute_priv

Index_priv

Index_priv

Index_priv

Insert_priv

Insert_priv

Insert_priv

Lock_tables_priv

Lock_tables_priv

Lock_tables_priv

References_priv

References_priv

References_priv

Select_priv

Select_priv

Select_priv

Show_view_priv

Show_view_priv

Show_view_priv

Update_priv

Update_priv

Update_priv

tables_priv Table

columns_priv Table

 

Table_priv

Column_priv

 

Column_priv

  


Table 12.3. Grant Table SSL and Resource Management Columns (user Table Only)

SSL Columns

Resource Management Columns

ssl_type

max_connections

ssl_cipher

max_questions

x509_issuer

max_updates

x509_subject

max_user_connections


The grant table system includes tables_priv and columns_priv tables for setting up table-specific and column-specific privileges. However, there is no analogous rows_priv table, because MySQL doesn't provide record-level privileges. For example, you cannot restrict a user's access to just those rows in a table that contain a particular value in some column. If you need this capability, you must implement it within your own applications. (One way to implement cooperative record-level locking is to use advisory locking functions such as GET_LOCK() and RELEASE_LOCK(). The procedure for this is described in "Advisory Locking Functions," in Appendix C, "Operator and Function Reference.")

New releases of MySQL sometimes add new privileges. When upgrading an existing MySQL installation to such a version, it's necessary to update the grant tables before you can use the new privileges. The procedure for doing this is given in "Dealing with Changes to Grant Table Structure," in Chapter 11.

Grant Table Scope-of-Access Columns

The grant table scope columns are used to determine which rows to use when a given account attempts to perform a given operation. Each grant table entry contains Host and User columns to indicate that the entry applies to connections from a given host by a particular user. For example, a user table record with localhost and bill in the Host and User columns would be used for connections from the local host by bill, but not for connections by betty. (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. The db table contains a Db column to indicate which database the entry applies to. Similarly, rows in the tables_priv and columns_priv tables contain scope columns that further narrow their 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 matches the values listed in the scope columns. The privileges supported by MySQL are shown in the following lists, which describe the administrative privileges and the privileges that control database and table access. Each list uses the privilege names that are used for the GRANT statement. For the most part, these privilege names bear an obvious resemblance to the names of privilege columns in the user, db, and host tables. For example, the SELECT privilege corresponds to the Select_priv column.

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. To keep the use of this privilege within certain bounds, the server takes certain precautions:

    • You can access only files that are world-readable, and thus likely not to be considered protected in any way.

    • Any file that you want to write 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. (If this constraint were not enforced, you could completely replace the contents of the grant tables in the mysql database, for example.)

    Despite these precautions by the server, this privilege should not be granted without just cause; it can be extremely dangerous, as discussed in "Grant Table Risks to Avoid." If you do 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 filesystem. By running the server from an ordinary login account, the server can create files only in directories accessible to that account.

  • GRANT OPTION

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

  • PROCESS

    The MySQL server is multi-threaded such that each client connection is serviced by a separate thread. These threads may be thought of as processes running within the server. The PROCESS privilege allows you to use the SHOW PROCESSLIST statement or the mysqladmin processlist command to view information about threads that are currently executing. (You can always see your own threads, even without the PROCESS privilege. What this privilege adds is the global ability to see any thread, even those associated with other users.)

  • RELOAD

    Allows you to perform a variety of administrative server operations. With the RELOAD privilege, you have the ability to issue statements such as FLUSH and RESET. You also can perform the following mysqladmin commands: reload, refresh, flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, and flush-threads.

  • REPLICATION CLIENT

    Allows you to inquire about the location and status of master and slave servers using the SHOW MASTER STATUS and SHOW SLAVE STATUS statements.

  • REPLICATION SLAVE

    Allows a client to connect to a master server and request slave server updates, and to use the SHOW SLAVE HOSTS and SHOW BINLOG EVENTS statements. It's granted to accounts used by slave servers.

  • SHOW DATABASES

    Allows you to see all database names by issuing the SHOW DATABASES statement. If you don't have this privilege, you can see a given database name only if you have some privilege for it. However, note that this ability is conveyed by any global privilege that applies to databases, which includes the CREATE TEMPORARY TABLES and LOCK TABLES privileges that commonly are granted globally. To ensure that only users who have the SHOW DATABASES privilege can use the SHOW DATABASES statement, start the server with the --skip-show-database option.

  • SHUTDOWN

    Allows you to shut down the server, for example, with the mysqladmin shutdown command.

  • SUPER

    Allows you to kill server threads with the KILL statement or the mysqladmin kill command. (You can always kill your own threads, even without the SUPER privilege. What this privilege adds is the global ability to kill any thread, even those associated with other users.)

    Other statements allowed by this privilege are SET for modifying global system variables and the global transaction isolation level, CHANGE MASTER, LOAD DATA FROM MASTER, PURGE MASTER LOGS, SHOW INNODB STATUS, SHOW LOGS, SHOW MASTER STATUS, SHOW SLAVE STATUS, START SLAVE, and STOP SLAVE. SUPER also allows you to perform DES decryption (with the DES_DECRYPT() function) based on the keys stored in the DES key file.

    The SUPER privilege allows the use of mysqladmin debug, and it overrides any max_connections setting when connecting to the server, so that you can access the connection slot that the server reserves for administrative connections even when all the regular slots are taken.

Database and Table Privileges

The following privileges apply to operations on databases and tables:

  • ALTER

    Allows you to use the ALTER TABLE statement, although you might also need additional privileges, depending on what you want to do with the table.

  • ALTER ROUTINE

    Allows you to alter or drop stored procedures and functions. This privilege was introduced in MySQL 5.0.3.

  • CREATE

    Allows you to create databases and tables. This privilege does not allow you to create indexes on a table, except those defined initially in its CREATE TABLE statement.

  • CREATE ROUTINE

    Allows you to create stored procedures and functions. This privilege was introduced in MySQL 5.0.3.

  • CREATE TEMPORARY TABLES

    Allows you to create temporary tables with the CREATE TEMPORARY TABLE statement.

  • CREATE VIEW

    Allows you to create views. This privilege was introduced in MySQL 5.0.1.

  • DELETE

    Allows you to remove existing records from tables.

  • DROP

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

  • EXECUTE

    Allows you to execute stored procedures and functions. This privilege was not implemented until MySQL 5.0.3.

  • INDEX

    Allows you to create or drop indexes from tables, assign indexes to key caches, and preload indexes into key caches.

  • INSERT

    Allows you to insert new records in tables.

  • LOCK TABLES

    Allows you to lock tables by issuing explicit LOCK TABLES statements. This privilege applies only to tables for which you also have the SELECT privilege, but allows you to place read or write locks, not just read locks. The privilege does not apply to locks that are acquired implicitly on your behalf by the server during the process of statement execution. Such locks are set and released automatically regardless of your LOCK TABLES privilege setting.

  • REFERENCES

    This privilege currently is unused. Eventually it may be used to define who can set up foreign key constraints.

  • SELECT

    Allows you to retrieve data from tables using SELECT statements. This privilege is unnecessary for SELECT statements such as SELECT NOW() or SELECT 4/2, which do nothing more than evaluate expressions and involve no tables.

  • SHOW VIEW

    Allows use of the SHOW CREATE VIEW statement to see view definitions. This privilege was introduced in MySQL 5.0.1.

  • UPDATE

    Allows you to modify existing records in tables.

Some operations require a combination of privileges. For example, REPLACE may implicitly cause a DELETE followed by an INSERT, so it requires both the DELETE and INSERT privileges.

How the Grant Tables Represent Privileges

In the user, db, and host tables, each privilege is specified as a separate column. These columns are all defined to have a type of ENUM('N','Y'), with a default value of 'N' (off). For example, the Select_priv column is defined like this:

Select_priv ENUM('N','Y') NOT NULL DEFAULT 'N'

Privileges in the tables_priv and columns_priv tables are represented by a SET, which allows any combination of privileges to be stored in a single column. The Table_priv column in the tables_priv table is defined like this:

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

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

SET('Select','Insert','Update','References') NOT NULL DEFAULT ''

The reason there are fewer column privileges than table privileges is that fewer operations make sense at the column level. For example, you can delete a row from a table to remove it, but you can't delete individual columns of a row.

Note that INSERT exists at the column level. If you have the INSERT privilege only for some columns in a table, you can specify values only for those columns when inserting new records; the other columns will be set to their default values.

The tables_priv and columns_priv tables are newer than the other three, which is why they use the more efficient SET representation to list multiple privileges in a single column. (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 user table contains several administrative privilege columns that are not present in any of the other grant tables, such as File_priv, Process_priv, Reload_priv, and Shutdown_priv. Such privileges are present only in the user table because they are global privileges that are not associated with any particular database or table. It doesn't make sense to allow or not allow a user to shut down the server based on what the current database is, for example.

Grant Table SSL-Related Columns

Several columns in the user table apply to authentication of secure connections over SSL. The primary column is ssl_type, which indicates whether and what type of secure connection is required for an account. ssl_type is represented as an ENUM with four possible values:

ssl_type ENUM('','ANY','X509','SPECIFIED') NOT NULL

The ssl_type values have the following meanings:

  • '' (the empty string) indicates that the account is not required to use secure connections. This is the default value; it's used when you set up an account but do not specify any REQUIRE clause or when you specify REQUIRE NONE explicitly.

  • 'ANY' indicates that the account must use a secure connection, but that it can be any kind of secure connection; it's a kind of "generic" requirement. The column is set to this value when you specify REQUIRE SSL in a GRANT statement.

  • 'X509' indicates that the account must use a secure connection and that the client must supply a valid X509 certificate. The contents of the certificate are not otherwise relevant. The column is set to this value when you specify REQUIRE X509.

  • 'SPECIFIED' indicates that the client must use a secure connection that meets specific requirements. The column is set to this value when you specify any combination of ISSUER, SUBJECT, or CIPHER values in the REQUIRE clause.

For all ssl_type values except 'SPECIFIED', the server ignores the values in the other SSL-related columns when validating client connection attempts. For 'SPECIFIED', the server checks the other columns, and for any that have non-empty values, the client must supply matching information. These other columns are as follows:

  • ssl_cipher

    If non-empty, this column indicates the cipher method that the client must use when connecting. It can be used to prevent the client from using weak cipher methods.

  • x509_issuer

    If non-empty, this column indicates the issuer value that must be found in the X509 certificate presented by the client.

  • x509_subject

    If non-empty, this column indicates the subject value that must be found in the X509 certificate presented by the client.

ssl_cipher, x509_issuer, and x509_subject all are represented in the user table as BLOB columns.

More information about using SSL for secure connections is given later in the chapter, in "Setting Up Secure Connections."

Grant Table Resource Management Columns

The following columns in the user table allow you to limit the extent to which any given MySQL account can consume server resources:

  • max_connections

    The number of times per hour the account can connect to the server. A value of zero means "no limit." Although this column has the same name as the max_connections system variable, the two are unrelated.

  • max_questions

    The number of statements per hour the account can issue. A value of zero means "no limit."

  • max_updates

    Like max_questions, but applies more specifically to statements that modify data. A value of zero means "no limit."

  • max_user_connections

    The maximum number of simultaneous client connections allowed to the account. If the value is zero, the server assesses the simultaneous-connection limit against the global value of the max_user_connections system variable. A value greater than zero takes precedence over the max_user_connections system variable. This column was introduced in MySQL 5.0.3.

If the server restarts, the current counters are reset to zero. A reset also occurs, except for the max_user_connections value, if you reload the grant tables or issue a FLUSH USER_RESOURCES statement.

More information about setting account limits is given in "Limiting an Account's Resource Consumption," in Chapter 11.

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 the host you're connecting from, your name, and the password you supplied. If there is no match, you can't connect. If there is a match, the server also checks the user table SSL and resource management columns:

  • If you've reached your connections-per-hour or simultaneous-connections limit, the connection is rejected.

  • If the user table entry indicates that secure connections are required, the server determines whether the credentials you supply match those required in the SSL-related columns. If not, the connection is rejected.

If everything checks out okay, the server establishes the connection and you proceed to the second stage. If you are making a secure connection, your client and the server encrypt the traffic between them.

In the second stage, the server checks two things for each statement you issue. First, it checks your statements-per-hour and updates-per-hour limits. Second, the server checks the grant tables to verify that you have sufficient privileges to perform the statement. The limits are checked before your access privileges because if you've reached the limits, there is little point in checking your privileges. The second stage continues until you disconnect from the server.

The following discussion describes in some detail the rules that the MySQL server uses to match grant table entries to incoming client connection requests and to statements. This includes the types of values that are legal in the grant table scope columns, how privilege values from different grant tables are combined, and the order in which the server searches entries from a given grant table.

Scope Column Contents

Each scope column is governed by rules that define what kinds of values are legal and how the server interprets those values. Some of the scope columns require literal values, but most of them allow wildcard or other special values.

  • Host

    A Host column value can be a hostname or an IP number. The value localhost means the local host. It matches when a client connects from the local host to one of the server's local network interfaces, defined as follows:

    • The Unix socket file, on Unix systems.

    • A named pipe or shared memory, on Windows.

    • The TCP loopback interface, that is, the interface with an IP number of 127.0.0.1. This works on any system.

    However, localhost does not match if the client connects using the host's actual name or IP number. Suppose that the name of the local host is cobra.snake.net and there are two entries for a user named bob in the user table, one with a Host value of localhost and the other with a value of cobra.snake.net. The entry with localhost match if bob connects from the local host using either of the following commands, on either Unix or Windows:

    % mysql -p -u bob -h localhost
    % mysql -p -u bob -h 127.0.0.1
    

    In addition, on Windows, the localhost entry matches if bob connects like this:

    C:\> mysql -p -u bob -h .
    C:\> mysql -p -u bob --protocol=pipe
    C:\> mysql -p -u bob --protocol=memory
    

    The entry with a Host value of cobra.snake.net matches if bob connects from the local host using the server's hostname (cobra.snake.net) or the IP number that corresponds to the hostname. The connection will use TCP/IP in both cases.

    You can also specify Host values using wildcards. The '%' and '_' SQL pattern characters may be used and have the same meaning as when you use the LIKE operator in a query. (Regular expressions of the type used with REGEXP are not allowed.) The SQL pattern characters work both for names and for IP numbers. For example, %.kitebird.com matches any host in the kitebird.com 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.

    A Host value of % matches any host at all, and may be used to allow a user to connect from anywhere. With one exception, a blank Host value in a grant table is the same as %. (The exception is the db table, for which a blank Host value means "check the host table for further information." This process is described in "Statement Access Verification.")

    You can also specify a network number with a netmask indicating which bits of the client IP address must match the network number. For example, 192.168.128.0/255.255.255.0 specifies a 24-bit network number and matches any client host for which the first 24 bits of its IP address have a value equal to 192.168.128. You can think of this as another kind of wildcard.

  • User

    Usernames must be either literal values or blank (empty). A blank value matches any name and thus means "anonymous." Otherwise the value matches exactly the name specified. In particular, % 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, if the first matching entry 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 to modify the grant tables directly, be sure to specify the password using PASSWORD('new_password') rather than just 'new_password'.

  • Db

    In the db and host tables, Db values may be specified literally or by using the '%' or '_' SQL pattern characters to specify a wildcard. A value of % or blank matches any database. (In the db table, a blank Host value causes the host table to be checked as well for database-level privileges. See "Statement Access Verification.") In the columns_priv and tables_priv tables, Db values must be literal database names. They match exactly the name specified; patterns and empty values are not allowed.

  • Table_name, Column_name

    Values in these columns must be literal table or column names and match exactly the name specified; patterns and empty values are not allowed.

Some scope columns are treated by the server as case sensitive, whereas others are not, as summarized in Table 12.4. Note in particular that Db and Table_name values are always treated as case sensitive, even though treatment of database and table names in statements depends on the case sensitivity of the filesystem on which the server runs (typically case sensitive under Unix, and not case sensitive under Windows).

Table 12.4. Case Sensitivity in Grant Table Scope Columns

Column

Case Sensitive

Host

No

User

Yes

Password

Yes

Db

Yes

Table_name

Yes

Column_name

No


How Passwords Are Stored in the user Table

The MySQL server encrypts passwords with the PASSWORD() function before storing them in the user table. This prevents passwords from being exposed as plain text even to users who have read access to the table. It seems to be a common assumption that PASSWORD() implements the same kind of encryption as is used for Unix passwords, but that is not true. The two kinds of encryption are similar in that both are one-way and not reversible, but MySQL doesn't use the same encryption algorithm that Unix does. This means that even if you use your Unix password as your MySQL password, you shouldn't expect the encrypted password strings to match. To perform Unix encryption for a MySQL application, use the CRYPT() function rather than PASSWORD(). If you're curious about what other encryption options are available for use in your applications, see "Security and Compression Functions," in Appendix C, "Operator and Function Reference."


Statement Access Verification

Each time you issue a statement, the server determines whether you've reached your statement resource limits. These limits are given by the max_questions and max_updates values stored in the user table. If you have not reached your limits, the server also checks whether you have sufficient privileges to execute the statement. It determines your access privileges by checking, in order, the privileges from the user, db, tables_priv, and columns_priv tables, until the server either verifies that you have proper access or it has searched all the tables in vain. More specifically:

  1. The server checks the user table entry that matched when you connected initially, to see what global privileges you have. If you have any such privileges and they are sufficient for the statement, the server executes it.

  2. If your global privileges are insufficient, the server looks for an entry for you in the db table. If it finds one, it adds the privileges in that entry to your global privileges. If the result is sufficient for the statement, the server executes it.

  3. If the combination of your global and database-level privileges is insufficient, the server keeps looking, first in the tables_priv table and then in the columns_priv table, checking at each step whether you have the necessary privileges to execute the statement.

  4. If, after all the tables have been checked, you still don't have the privileges needed to execute the statement, the server rejects your attempt to do so.

In boolean terms, the privileges from the grant tables are combined by the server as follows:

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 server really checks access permissions like this:

user OR (db AND host) OR tables_priv OR columns_priv

I showed the simpler expression first because the more complex expression will never come into play for most MySQL installations. That's because the host table that appears in the more complex expression is completely unaffected by the GRANT and REVOKE statements. The host table is affected only if you manipulate it directly with INSERT, UPDATE, and so forth. This means that if you adopt the usual administrative policy of managing user accounts with GRANT and REVOKE, your host table will never be used and you can forget about it entirely.

I have on occasion inquired on the MySQL mailing lists to find out how many people actually use the host table. The response always has been rather minimal, so it appears that this table sees very little use in practice. Nevertheless, if you do want to use the host table, here's how it works:

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

  2. To check the host table, the server looks 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 an entry does 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 (not OR), which means that the client doesn't have a given privilege unless it's present in both the db table and host table entries. In this way, you can grant a basic set of privileges in the db table entry, and 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 physical locations.

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 statement that clients issue. However, the process is quite fast because the server doesn't actually look up information from the grant tables for every statement. Instead, it reads the contents of the tables into memory when it starts, and then verifies statements using the in-memory copies. This gives a performance boost to access-checking operations. Furthermore, if you keep your privilege specifications simple, you can ensure that access checking is as fast as possible. When the server reads the grant tables into memory, it notices whether any accounts have resource limits, and whether any have table-level or column-level privileges. If not, it knows that it need not check any of those types of information when checking privileges for statements issued by clients. This means the server can omit certain steps from the full access-checking procedure.

The use of in-memory copies of the grant tables for access checking has an 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, that in itself will not allow the user named in the entry 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.

There is no need to tell the server to reload the grant tables when you use GRANT, REVOKE, SET PASSWORD, CREATE USER, DROP USER, or RENAME USER to set up or modify user accounts. The server maps those statements onto operations that modify the grant tables, and then refreshes the in-memory copies of the tables automatically.

Scope Column Matching Order

The MySQL server sorts entries from the grant tables in a particular way, and 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 sort 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 columns. The Host column is dominant, so entries with the same Host value are sorted together, and then ordered according to the User value. However, sorting is not lexical, or rather, it's only partially so. The principle to keep in mind is that literal values are preferred over patterns, and more-specific patterns are preferred over less-specific 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 in turn is preferred over %. Matching for IP numbers works that way, too. For a client connecting from a host with an IP number of 192.168.3.14, enTRies with the following Host values all match, but are preferred in the order shown:

192.168.3.14
192.168.3.%
192.168.%
192.%
%

Another principle to remember is that when the server tries to match user table entries, it looks for a Host value match first and a User value match second. It's a common error to think that the server looks for a User match first.

A Privilege Puzzle

This section describes a particular scenario that demonstrates why it's useful to understand the order in which the server searches user table entries when validating connection attempts. It also shows how to solve a problem that seems to be fairly common with new MySQL installations, at least judged by the frequency with which it comes up on the MySQL mailing lists: A MySQL administrator sets up a new installation, including the default root and anonymous-user entries in the user table. A good administrator will assign passwords for the root accounts, but it's common to leave the anonymous users as is, with no passwords. Now, suppose that the administrator wants to set up a new account for a user who will be connecting from several different hosts. The easiest way to allow this is by creating the account with % as the host part of the account name in the GRANT statement so that the user can connect from anywhere:

GRANT ALL ON sampdb.* TO 'fred'@'%' IDENTIFIED BY 'cocoa';

The intent here is to grant the user fred all privileges for the sampdb database and to allow him to connect from any host he likes. Unfortunately, the probable result is that fred will be able to connect from any host, except the server host itself! Suppose that the server host is named cobra.snake.net. When fred tries to connect from boa.snake.net, the attempt succeeds:

% mysql -p -u fred -h cobra.snake.net sampdb
Enter password: cocoa
mysql>

But if fred logs in on the server host cobra.snake.net and tries to connect, the attempt fails, even though fred supplies his password correctly:

% mysql -p -u fred -h localhost sampdb
Enter password: cocoa
ERROR 1045 (28000): Access denied for user 'fred'@'localhost'
(using password: YES)

This problem occurs if your user table contains the default anonymous-user entries (the entries with blank usernames). These entries are created by the mysql_install_db initialization script under Unix and are present in the pre-initialized user table included with Windows distributions. The reason the second connection attempt fails is that when the server attempts to validate fred, one of the anonymous-user entries takes precedence over fred's entry in the matching order. The anonymous-user entry requires the user to connect with no password (rather than with the password cocoa), so a password mismatch results.

Why does this happen? To understand what's going on, it's necessary to consider both how MySQL's grant tables are set up initially and how the server uses user table entries when it validates client connections. For example, under Unix, when you run the mysql_install_db script on cobra.snake.net to initialize the grant tables, the resulting user table contains rows with Host and User values that look like this:[1]

[1] A detailed description of the initial user table entries is given in "Securing a New MySQL Installation," in Chapter 11, "General MySQL Administration."

+-----------------+------+
| Host            | User |
+-----------------+------+
| localhost       | root |
| cobra.snake.net | root |
| localhost       |      |
| cobra.snake.net |      |
+-----------------+------+

The first two entries allow root to connect to the server on the local host by specifying either localhost or the host's actual name. The second two entries allow users to connect anonymously to the local server. After the administrator sets up the account for fred with the GRANT statement shown earlier, the user table contains these entries:

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

But the order of the entries as shown is not the order that the server uses when validating connection requests. Instead, it sorts entries by host first and then by user within host, putting more-specific values first and less-specific values last:

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

The two entries with localhost in the Host column sort together, with the entry for root first because that's a more specific username than the blank value. The entries with cobra.snake.net sort together in a similar way. Furthermore, all four of these entries have a literal Host value without any wildcard characters, so they all sort ahead of the entry for fred, which does use a wildcard character in its Host value. In particular, both of the anonymous-user entries take precedence over fred's entry in the sort order.

The result is that when fred attempts to connect from the local host, one of the entries with a blank username matches before the entry containing % in the Host column. The blank password in the anonymous-user entry doesn't match fred's password of cocoa, so the connection fails. One implication of this phenomenon is that it is possible for fred to connect from the local host, but only if he specifies no password. Unfortunately, then he will be validated as an anonymous user and won't have the privileges associated with the fred@% account.

What all this means is that although it's very convenient to use wildcards when you set up an account for a user who will connect from multiple hosts, the user may have problems connecting from the local host due to the anonymous entries in the user table.

What is the solution to this problem? Actually, you can solve it two ways. First, you can set up another account for fred that explicitly lists localhost as the host value:

GRANT ALL ON sampdb.* TO 'fred'@'localhost' IDENTIFIED BY 'cocoa';

If you do that, the server will sort the entries from the user table as follows:

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

Now when fred connects from the local host, the entry with localhost and fred will match ahead of the anonymous-user entries. When he connects from any other host, the entry with % and fred will match. The downside of having two entries for fred is that whenever you want to change his privileges or password, you'll have to make the change twice.

The second solution is much easier: Delete the anonymous accounts from the user table. To do this, you cannot use REVOKE, because that only revokes privileges; it won't remove account entries from the user table. Instead, use DELETE and then flush the privileges to tell the server to reread the grant tables:

% mysql -p -u root
mysql> DELETE FROM mysql.user WHERE User = '';
mysql> FLUSH PRIVILEGES;

The sort order of the remaining entries becomes:

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

Now when fred attempts to connect from the local host, he'll succeed, because there won't be any user table entries that will match ahead of his.

In general, I recommend that if you want to make your life easier as an administrator, you should delete the anonymous-user entries that are present in the initial grant tables. (In my view, these entries are not very useful, and they tend to cause more problems than they're worth.)

The puzzle presented in this section addresses a specific situation, but contains a more general lesson. If privileges for a given account don't work the way you expect, look in the grant tables to see if there's some entry containing Host values that are more specific than the entry for the user in question and that will match connection attempts by that user. If so, that may explain the problem. You might need to make the user's entry more specific, or add another entry to cover the more specific case. The SHOW GRANTS statement can be useful for checking what privileges accounts have.

Grant Table Risks to Avoid

This section describes precautions to observe when you grant privileges, and the attendant risks of unwise choices.

Avoid creating anonymous-user accounts. Even if they don't have sufficient privileges to cause damage directly, allowing a user to connect still may provide access to that user to look around and gather information such as what databases and tables you have, or to monitor the server with SHOW STATUS and SHOW VARIABLES.

Find accounts that have no passwords and either remove them or assign passwords. To find such accounts, use this query:

mysql> SELECT Host, User FROM mysql.user WHERE Password = '';

Find accounts that have password hash values in the older pre-MySQL 4.1 format and change them to the more secure password hash format that is used as of MySQL 4.1. Values in the older format have a length of 16 and do not begin with the '*' character, so you can identify them using either of these statements:

mysql> SELECT Host, User FROM mysql.user WHERE LENGTH(Password) = 16;
mysql> SELECT Host, User FROM mysql.user WHERE Password NOT LIKE '*%';

Note, however, that you cannot institute this security measure unless all client programs that connect to your server are from MySQL 4.1 or later and know how to authenticate using the newer password mechanism. Assuming that you can use newer passwords for all clients, you need only make sure that the server was not started with the --old-passwords option, and then use SET PASSWORD to set the password for each account that has an old-format password. The new password format will be used for each account. For additional security, start the server with the --secure-auth option. Otherwise, a client can reset its password to the old format with OLD_PASSWORD() and then connect using that password. --secure-auth prevents clients from connecting unless they have a new-format password.

Unless you really need to use patterns in hostname specifiers, avoid doing so when setting up accounts. Broadening the range of hosts from which a given user can connect also broadens the range from which an imposter claiming to be that user can try to break in.

Grant superuser privileges sparingly. That is, don't enable privileges in user table entries. Those privileges are global and allow the user to affect the operation of your server or to access any table in any database. Grant privileges at a more specific level instead, to restrict user access to particular databases, tables, or columns.

Don't grant privileges for the mysql database, because it contains the grant tables. A user with privileges for that database may be able to modify its tables to acquire privileges on any other database as well. In effect, granting privileges that allow a user to modify the mysql database tables gives that user a global GRANT OPTION privilege. (After all, if the user can modify the tables directly, that's equivalent to being able to issue any GRANT statement you can think of.)

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

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;

After executing those statements, the user has access to the contents of your server host's password file just by issuing a SELECT:

SELECT * FROM etc_passwd;

The name of any publicly readable file on the server host may be substituted for /etc/passwd in the LOAD DATA statement. If the user has connected from a remote host, the effect is that granting the FILE privilege gives that user network access to potentially a large portion of your server host's filesystem.

The FILE privilege also can be exploited to compromise databases on systems that aren't set up with sufficiently restrictive data directory permissions. This is one 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:

1.
Create a table containing a LONGBLOB column:

USE test;
CREATE TABLE tmp (b LONGBLOB);

2.
Use the table to read in the contents of each of the files that correspond to the table you want to steal. For example, if a user has a MyISAM table named x in a database other_db, the table is represented by three files, x.frm, x.MYD, and x.MYI. You can read those files and copy them into corresponding files in the test database like this:

LOAD DATA INFILE './other_db/x.frm' INTO TABLE tmp
    FIELDS ESCAPED BY '' LINES TERMINATED BY '';
SELECT * FROM tmp INTO OUTFILE 'x.frm'
    FIELDS ESCAPED BY '' LINES TERMINATED BY '';
DELETE FROM tmp;
LOAD DATA INFILE './other_db/x.MYD' INTO TABLE tmp
    FIELDS ESCAPED BY '' LINES TERMINATED BY '';
SELECT * FROM tmp INTO OUTFILE 'x.MYD'
    FIELDS ESCAPED BY '' LINES TERMINATED BY '';
DELETE FROM tmp;
LOAD DATA INFILE './other_db/x.MYI' INTO TABLE tmp
    FIELDS ESCAPED BY '' LINES TERMINATED BY '';
SELECT * FROM tmp INTO OUTFILE 'x.MYI'
    FIELDS ESCAPED BY '' LINES TERMINATED BY '';

3.
After executing those statements, the test database directory also will contain files named x.frm, x.MYD, and x.MYI. In other words, the test database will contain a table x that is a stolen duplicate of the table in the other_db database.

To avoid having someone attack your users' tables in the same way, set the permissions on your data directory contents according to the instructions in given earlier in "Securing Your MySQL Installation." As an additional measure, avoid granting the SHOW DATABASE privilege and run the server with the --skip-show-database option. This prevents users from using SHOW DATABASES and SHOW TABLES for databases to which they have no access, and helps to keep users from finding out about databases and tables they shouldn't be accessing.

The dangers of the FILE privilege are amplified if you run the MySQL server as root. That's inadvisable in the first place, and is particularly so when combined with FILE. Because root can create files anywhere in the filesystem, a user with the FILE privilege can do so as well, even a user who has connected from a remote host. The server won't create a file that already exists, but it's sometimes possible to create new files that will alter the operation of the server host or compromise its security. For example, if any of the files /etc/resolv.conf, /etc/hosts.equiv, /etc/hosts.lpd, or /etc/sudoers do not already exist, a user who is able to cause the MySQL server to create them can drastically change the way your server host behaves. To avoid these problems, don't run mysqld as root. (See "Running the Server Using an Unprivileged Login Account," in Chapter 11.)

The PROCESS and SUPER privileges should be granted only to trusted MySQL accounts. With PROCESS, a user can use SHOW PROCESSLIST to see the text of statements being executed by the server. This allows a user to snoop on other users and possibly see information that should remain private. With SUPER, the user can kill threads that belong to other users, disrupting their activities. SUPER also allows a user to purge log files and perform other actions that can compromise server operation.

Don't give the RELOAD privilege to people who don't need it. RELOAD allows a user to issue FLUSH and RESET statements, which can be abused in several ways:

  • Binary and relay log files are created with names that form a numbered sequence. If you have configured the server to perform binary or relay logging, each FLUSH LOGS statement creates the next log in the sequence. A user with the RELOAD privilege who performs many log flushing operations can cause the server to create large numbers of files.

  • A user with the RELOAD privilege can defeat the resource management mechanism by reloading the grant tables with FLUSH PRIVILEGES or with FLUSH USER_RESOURCES. Both statements reset resource management counters to zero.

  • FLUSH TABLES can be used repeatedly to cause the server to flush its open-table cache, which degrades performance by preventing the server from taking advantage of the cache. Similarly, RESET QUERY CACHE can be used to negate the benefits of the query cache.

  • RESET MASTER LOGS causes a replication master server to delete all of its binary logs even if they are still in use, which removes the information necessary to maintain replication integrity.

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

Setting Up MySQL Accounts Without GRANT Statements

Normally, the GRANT and REVOKE statements suffice to manage MySQL accounts and access privileges. However, it's also possible to modify the contents of the grant tables directly using statements such as INSERT and DELETE. It's easier to do that if you understand how the GRANT statement modifies the grant tables because you'll know what kind of INSERT and DELETE statements correspond to various GRANT and REVOKE statements.

GRANT acts to modify the grant tables as follows:

  • 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. In addition, if you specify global privileges in the GRANT statement, those privileges are recorded in the privilege columns of the user table entry. SSL or resource-limit options specified in the REQUIRE or WITH clauses of a GRANT statement are recorded in the SSL-related and resource-management columns.

  • If you specify database-level privileges in the GRANT statement, the username and hostname are recorded in the User and Host columns of a db table entry. The database for which you granted privileges is recorded in the Db column, and the privileges that you granted are recorded in the privilege 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 principles in mind, you should be able to do anything GRANT does without using GRANT itself. But remember that after modifying the grant tables directly, you must tell the server to reload them or it won't notice your changes. If you forget to do that, you'll be wondering why your changes seem to have no effect. You can force a reload by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges command.

The following GRANT statement uses ON *.* to specify global privileges. It creates an account for a superuser who has 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. It also will turn on all the privileges there because that's where superuser (global) privileges are stored. To do the same thing with INSERT, the statement looks like this:

INSERT INTO user VALUES('localhost','ethel',PASSWORD('coffee'),
    'Y','Y','Y','Y','Y','Y','Y','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. This statement assumes that the user table has 21 privilege columns, which is the number present in MySQL 4.1 as I write. Because the structure of the grant tables has changed on occasion, you might have a different number. Use SHOW COLUMNS to find out just what privilege columns your user table contains, and adjust the INSERT statement accordingly. Note too that although the GRANT statement encrypts the password for you, INSERT does not; it's necessary to use the PASSWORD() function to encrypt passwords in your INSERT statements.

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

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

You may remember this statement from Chapter 11, where we created an account for log file maintenance purposes that has privileges for flushing the server logs. The equivalent INSERT statement for this GRANT statement is a bit simpler than for the preceding one, so it's easier to list the column names and specify only the one privilege column. Each of the other privilege columns will be set to its default value ('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 sampdb.* TO 'boris'@'localhost' IDENTIFIED BY 'ruby';

These privileges are not global, so they won't be stored in the user table. However, to duplicate the effect of this GRANT statement, it's necessary to create a record in the user table so that the user can connect (if there is not already such a record). This means that a user table record is needed along with a db table record that indicates the database-level privileges:

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

The 'N' value in the second statement is for the GRANT OPTION privilege. Setting the column to 'Y' instead would duplicate the effect of a database-level GRANT statement that includes a WITH GRANT OPTION clause.

To set table-level or column-level privileges, use INSERT statements for the tables_priv or columns_priv tables. However, for these tables, 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.

To modify privileges for a MySQL account that already exists, use UPDATE rather than INSERT. This is true whether you are adding or revoking privileges.

To remove an account entirely, use DELETE to remove entries from each grant table in which the account appears. For example, to remove an account for mike@%.snake.net, issue these statements:

DELETE FROM user WHERE User = 'mike' AND Host = '%.snake.net';
DELETE FROM db WHERE User = 'mike' AND Host = '%.snake.net';
DELETE FROM host WHERE User = 'mike' AND Host = '%.snake.net';
DELETE FROM tables_priv WHERE User = 'mike' AND Host = '%.snake.net';
DELETE FROM columns_priv WHERE User = 'mike' AND Host = '%.snake.net';

    Team LiB
    Previous Section Next Section