The MySQL Access Privilege System

MySQL has an advanced but non-standard security/privilege system. This section describes how it works.

What the Privilege System Does

The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.

Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

How the Privilege System Works

The MySQL privilege system ensures that all users may perform only the operations allowed to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. The system grants privileges according to your identity and what you want to do.

MySQL considers both your hostname and username in identifying you because there is little reason to assume that a given username belongs to the same person everywhere on the Internet. For example, the user joe who connects from office.com need not be the same person as the user joe who connects from elsewhere.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: You can grant joe one set of privileges for connections from office.com, and a different set of privileges for connections from elsewhere.com.

MySQL access control involves two stages:

  • Stage 1: The server checks whether you are even allowed to connect.

  • Stage 2: Assuming that you can connect, the server checks each statement you issue to see whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

If your privileges are changed (either by yourself or someone else) while you are connected, those changes will not necessarily take effect immediately for the next statement you issue. See the section called “When Privilege Changes Take Effect” for details.

The server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in the section called “When Privilege Changes Take Effect”. Access-control decisions are based on the in-memory copies of the grant tables.

Normally, you manipulate the contents of the grant tables indirectly by using the GRANT and REVOKE statements to set up accounts and control the privileges available to each one. See GRANT. The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.

The server uses the user, db, and host tables in the mysql database at both stages of access control. The columns in these grant tables are shown here:

Table Nameuserdbhost
Scope columnsHostHostHost
 UserDbDb
 PasswordUser 
Privilege columnsSelect_privSelect_privSelect_priv
 Insert_privInsert_privInsert_priv
 Update_privUpdate_privUpdate_priv
 Delete_privDelete_privDelete_priv
 Index_privIndex_privIndex_priv
 Alter_privAlter_privAlter_priv
 Create_privCreate_privCreate_priv
 Drop_privDrop_privDrop_priv
 Grant_privGrant_privGrant_priv
 Create_view_privCreate_view_privCreate_view_priv
 Show_view_privShow_view_privShow_view_priv
 References_privReferences_privReferences_priv
 Reload_priv  
 Shutdown_priv  
 Process_priv  
 File_priv  
 Show_db_priv  
 Super_priv  
 Create_tmp_table_privCreate_tmp_table_privCreate_tmp_table_priv
 Lock_tables_privLock_tables_privLock_tables_priv
 Execute_priv  
 Repl_slave_priv  
 Repl_client_priv  
 ssl_type  
 ssl_cipher  
 x509_issuer  
 x509_subject  
 max_questions  
 max_updates  
 max_connections  

The ssl_type, ssl_cipher, x509_issuer, and x509_subject columns were added in MySQL 4.0.0.

The Create_tmp_table_priv, Execute_priv, Lock_tables_priv, Repl_client_priv, Repl_slave_priv, Show_db_priv, Super_priv, max_questions, max_updates, and max_connections columns were added in MySQL 4.0.2.

The Create_view_priv and Show_view_priv columns were added in MySQL 5.0.1.

During the second stage of access control (request verification), the server may, if the request involves tables, additionally consult the tables_priv and columns_priv tables that provide finer control at the table and column levels. The columns in these tables are shown here:

Table Nametables_privcolumns_priv
Scope columnsHostHost
 DbDb
 UserUser
 Table_nameTable_name
  Column_name
Privilege columnsTable_privColumn_priv
 Column_priv 
Other columnsTimestampTimestamp
 Grantor 

The Timestamp and Grantor columns currently are unused and are discussed no further here.

Each grant table contains scope columns and privilege columns:

  • Scope columns determine the scope of each entry (row) in the tables; that is, the context in which the entry applies. For example, a user table entry with Host and User values of 'thomas.loc.gov' and 'bob' would be used for authenticating connections made to the server from the host thomas.loc.gov by a client that specifies a username of bob. Similarly, a db table entry with Host, User, and Db column values of 'thomas.loc.gov', 'bob' and 'reports' would be used when bob connects from the host thomas.loc.gov to access the reports database. The tables_priv and columns_priv tables contain scope columns indicating tables or table/column combinations to which each entry applies.

  • Privilege columns indicate the privileges granted by a table entry; that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. The rules used to do this are described in the section called “Access Control, Stage 2: Request Verification”.

Scope columns contain strings. They are declared as shown here; the default value for each is the empty string:

Column NameType
HostCHAR(60)
UserCHAR(16)
PasswordCHAR(16)
DbCHAR(64)
Table_nameCHAR(60)
Column_nameCHAR(60)

Before MySQL 3.23, the Db column is CHAR(32) in some tables and CHAR(60) in others.

For access-checking purposes, comparisons of Host values are case-insensitive. User, Password, Db, and Table_name values are case sensitive. Column_name values are case insensitive in MySQL 3.22.12 or later.

In the user, db, and host tables, each privilege is listed in a separate column that is declared as ENUM('N','Y') DEFAULT 'N'. In other words, each privilege can disabled or enabled, with the default being disabled.

In the tables_priv and columns_priv tables, the privilege columns are declared as SET columns. Values in these columns can contain any combination of the privileges controlled by the table:

Table NameColumn NamePossible Set Elements
tables_privTable_priv'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
tables_privColumn_priv'Select', 'Insert', 'Update', 'References'
columns_privColumn_priv'Select', 'Insert', 'Update', 'References'

Briefly, the server uses the grant tables as follows:

  • The user table scope columns determine whether to reject or allow incoming connections. For allowed connections, any privileges granted in the user table indicate the user's global (superuser) privileges. These privileges apply to all databases on the server.

  • The db table scope columns determine which users can access which databases from which hosts. The privilege columns determine which operations are allowed. A privilege granted at the database level applies to the database and to all its tables.

  • The host table is used in conjunction with the db table when you want a given db table entry to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the Host value empty in the user's db table entry, then populate the host table with an entry for each of those hosts. This mechanism is described more detail in the section called “Access Control, Stage 2: Request Verification”.

    Note: The host table is not affected by the GRANT and REVOKE statements. Most MySQL installations need not use this table at all.

  • The tables_priv and columns_priv tables are similar to the db table, but are more fine-grained: They apply at the table and column levels rather than at the database level. A privilege granted at the table level applies to the table and to all its columns. A privilege granted at the column level applies only to a specific column.

Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table. This is because administrative operations are operations on the server itself and are not database-specific, so there is no reason to list these privileges in the other grant tables. In fact, to determine whether you can perform an administrative operation, the server need consult only the user table.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

The mysqld server reads the contents of the grant tables into memory when it starts. You can tell it to re-read the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in the section called “When Privilege Changes Take Effect”.

When you modify the contents of the grant tables, it is a good idea to make sure that your changes set up privileges the way you want. One way to check the privileges for a given account is to use the SHOW GRANTS statement. For example, to determine the privileges that are granted to an account with Host and User values of pc84.example.com and bob, issue this statement:

mysql> SHOW GRANTS FOR 'bob'@'pc84.example.com';

A useful diagnostic tool is the mysqlaccess script, which Yves Carlier has provided for the MySQL distribution. Invoke mysqlaccess with the --help option to find out how it works. Note that mysqlaccess checks access using only the user, db, and host tables. It does not check table or column privileges specified in the tables_priv or columns_priv tables.

For additional help in diagnosing privilege-related problems, see the section called “Causes of Access denied Errors”. For general advice on security issues, see the section called “General Security Issues”.

Privileges Provided by MySQL

Information about account privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database. The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in the section called “When Privilege Changes Take Effect”. Access-control decisions are based on the in-memory copies of the grant tables.

The names used in this manual to refer to the privileges provided by MySQL are shown in the following table, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies. Further information about the meaning of each privilege may be found at GRANT.

PrivilegeColumnContext
ALTERAlter_privtables
DELETEDelete_privtables
INDEXIndex_privtables
INSERTInsert_privtables
SELECTSelect_privtables
UPDATEUpdate_privtables
CREATECreate_privdatabases, tables, or indexes
DROPDrop_privdatabases or tables
GRANTGrant_privdatabases or tables
REFERENCESReferences_privdatabases or tables
CREATE TEMPORARY TABLESCreate_tmp_table_privserver administration
EXECUTEExecute_privserver administration
FILEFile_privfile access on server host
LOCK TABLESLock_tables_privserver administration
PROCESSProcess_privserver administration
RELOADReload_privserver administration
REPLICATION CLIENTRepl_client_privserver administration
REPLICATION SLAVERepl_slave_privserver administration
SHOW DATABASESShow_db_privserver administration
SHUTDOWNShutdown_privserver administration
SUPERSuper_privserver administration

The CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, and SUPER privileges were added in MySQL 4.0.2.

The EXECUTE and REFERENCES privileges currently are unused.

The SELECT, INSERT, UPDATE, and DELETE privileges allow you to perform operations on rows in existing tables in a database.

SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. Some SELECT statements do not access tables and can be executed without permission for any database. For example, you can use the mysql client as a simple calculator to evaluate expressions that make no reference to tables:

mysql> SELECT 1+1;
mysql> SELECT PI()*2;

The CREATE and DROP privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables. If you grant the DROP privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored!

The INDEX privilege allows you to create or drop (remove) indexes. INDEX applies to existing tables. If you have the CREATE privilege for a table, you can include index definitions in the CREATE TABLE statement.

The ALTER privilege allows you to use ALTER TABLE to change the structure of or rename tables.

The GRANT privilege allows you to give to other users those privileges that you yourself possess.

The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also allows the user to create new files in any directory where the MySQL server has write access. Existing files cannot be overwritten.

The remaining privileges are used for administrative operations. Many of them can be performed by using the mysqladmin program or by issuing SQL statements. The following table shows which mysqladmin commands each administrative privilege allows you to execute:

PrivilegeCommands Permitted to Privilege Holders
RELOADflush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload
SHUTDOWNshutdown
PROCESSprocesslist
SUPERkill

The reload command tells the server to re-read the grant tables into memory. flush-privileges is a synonym for reload. The refresh command closes and reopens the log files and flushes all tables. The other flush-xxx commands perform functions similar to refresh, but are more specific and may be preferable in some instances. For example, if you want to flush just the log files, flush-logs is a better choice than refresh.

The shutdown command shuts down the server. This command can be issued only from mysqladmin. There is no corresponding SQL statement.

The processlist command displays information about the threads executing within the server (that is, about the statements being executed by clients associated with other accounts). The kill command terminates server threads. You can always display or kill your own threads, but you need the PROCESS privilege to display threads initiated by other users and the SUPER privilege to kill them. See KILL. Prior to MySQL 4.0.2 when SUPER was introduced, the PROCESS privilege controls the ability to both see and terminate threads for other clients.

The CREATE TEMPORARY TABLES privilege allows the use of the keyword TEMPORARY in CREATE TABLE statements.

The LOCK TABLES privilege allows the use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege. This includes the use of write locks, which prevents anyone else from reading the locked table.

The REPLICATION CLIENT privilege allows the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.

The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers when they connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.

The SHOW DATABASES privilege allows the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the --skip-show-database option.

It is a good idea in general to grant privileges to only those accounts that need them, but you should exercise particular caution in granting administrative privileges:

  • The GRANT privilege allows users to give their privileges to other users. Two users with different privileges and with the GRANT privilege are able to combine privileges.

  • The ALTER privilege may be used to subvert the privilege system by renaming tables.

  • The FILE privilege can be abused to read into a database table any files that the MySQL server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed using SELECT to transfer its contents to the client host.

  • The SHUTDOWN privilege can be abused to deny service to other users entirely by terminating the server.

  • The PROCESS privilege can be used to view the plain text of currently executing queries, including queries that set or change passwords.

  • The SUPER privilege can be used to terminate other clients or change how the server operates.

  • Privileges granted for the mysql database itself can be used to change passwords and other access privilege information. Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password. However, a user with write access to the user table Password column can change an account's password, and then connect to the MySQL server using that account.

There are some things that you cannot do with the MySQL privilege system:

  • You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.

  • You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.

Connecting to the MySQL Server

MySQL client programs generally expect you to specify connection parameters when you want to access a MySQL server:

  • The name of the host where the MySQL server is running

  • Your username

  • Your password

For example, the mysql client can be started as follows from a command-line prompt (indicated here by shell>):

shell> mysql -h host_name -u user_name -pyour_pass

Alternate forms of the -h, -u, and -p options are --host=host_name, --user=user_name, and --password=your_pass. Note that there is no space between -p or --password= and the password following it.

If you use a -p or --password option but do not specify the password value, the client program will prompt you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line. Any user on your system may be able to see a password specified on the command line by executing a command such as ps auxww. See the section called “Keeping Your Password Secure”.

MySQL client programs use default values for any connection parameter option that you do not specify:

  • The default hostname is localhost.

  • The default username is ODBC on Windows and your Unix login name on Unix.

  • No password is supplied if -p is missing.

Thus, for a Unix user with a login name of joe, all of the following commands are equivalent:

shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql

Other MySQL clients behave similarly.

You can specify different default values to be used when you make a connection so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:

  • You can specify connection parameters in the [client] section of an option file. The relevant section of the file might look like this:

    [client]
    host=host_name
    user=user_name
    password=your_pass
    

    Option files are discussed further in the section called “Using Option Files”.

  • You can specify some connection parameters using environment variables. The host can be specified for mysql using MYSQL_HOST. The MySQL username can be specified using USER (this is for Windows and NetWare only). The password can be specified using MYSQL_PWD, although this is insecure; see the section called “Keeping Your Password Secure”. For a list of variables, see Appendix E, Environment Variables.

Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

  • The client host from which you connect

  • Your MySQL username

Identity checking is performed using the three user table scope columns (Host, User, and Password). The server accepts the connection only if the Host and User columns in some user table record match the client hostname and username, and the client supplies the password specified in that record.

Host values in the user table may be specified as follows:

  • A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host.

  • You can use the wildcard characters ‘%’ and ‘_’ in Host column values. These have the same meaning as for pattern-matching operations performed with the LIKE operator. For example, a Host value of '%' matches any hostname, whereas a value of '%.mysql.com' matches any host in the mysql.com domain.

  • As of MySQL 3.23, for Host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example:

    mysql> GRANT ALL PRIVILEGES ON db.*
        -> TO david@'192.58.197.0/255.255.255.0';
    

    This allows david to connect from any client host having an IP number client_ip for which the following condition is true:

    client_ip & netmask = host_ip
    

    That is, for the GRANT statement just shown:

    client_ip & 255.255.255.0 = 192.58.197.0
    

    IP numbers that satisfy this condition and can connect to the MySQL server are those that lie in the range from 192.58.197.0 to 192.58.197.255.

  • A blank Host value in a db table record means that its privileges should be combined with those in the entry in the host table that matches the client hostname. The privileges are combined using an AND (intersection) operation, not OR (union). You can find more information about the host table in the section called “Access Control, Stage 2: Request Verification”.

    A blank Host value in the other grant tables is the same as '%'.

Because you can use IP wildcard values in the Host column (for example, '144.155.166.%' to match every host on a subnet), someone could try to exploit this capability by naming a host 144.155.166.somewhere.com. To foil such attempts, MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you have a host named something like 1.2.foo.com, its name will never match the Host column of the grant tables. An IP wildcard value can match only IP numbers, not hostnames.

In the User column, wildcard characters are not allowed, but you can specify a blank value, which matches any name. If the user table entry that matches an incoming connection has a blank username, the user is considered to be an anonymous user with no name, not a user with the name that the client actually specified. This means that a blank username is used for all further access checking for the duration of the connection (that is, during Stage 2).

The Password column can be blank. This is not a wildcard and does not mean that any password matches. It means that the user must connect without specifying a password.

Non-blank Password values in the user table represent encrypted passwords. MySQL does not store passwords in plaintext form for anyone to see. Rather, the password supplied by a user who is attempting to connect is encrypted (using the PASSWORD() function). The encrypted password then is used during the connection process when checking whether the password is correct. (This is done without the encrypted password ever traveling over the connection.) From MySQL's point of view, the encrypted password is the REAL password, so you should not give anyone access to it! In particular, don't give non-administrative users read access to the tables in the mysql database!

From version 4.1 on, MySQL employs a stronger authentication method that has better password protection during the connection process than in earlier versions. It is secure even if TCP/IP packets are sniffed or the mysql database is captured. Password encryption is discussed further in the section called “Password Hashing in MySQL 4.1”.

The following examples show how various combinations of Host and User values in the user table apply to incoming connections:

HostValueUserValueConnections Matched by Entry
'thomas.loc.gov''fred'fred, connecting from thomas.loc.gov
'thomas.loc.gov'''Any user, connecting from thomas.loc.gov
'%''fred'fred, connecting from any host
'%'''Any user, connecting from any host
'%.loc.gov''fred'fred, connecting from any host in the loc.gov domain
'x.y.%''fred'fred, connecting from x.y.net, x.y.com, x.y.edu, and so on. (this is probably not useful)
'144.155.166.177''fred'fred, connecting from the host with IP address 144.155.166.177
'144.155.166.%''fred'fred, connecting from any host in the 144.155.166 class C subnet
'144.155.166.0/255.255.255.0''fred'Same as previous example

It is possible for the client hostname and username of an incoming connection to match more than one entry in the user table. The preceding set of examples demonstrates this: Several of the entries shown match a connection from thomas.loc.gov by fred.

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

  • Whenever the server reads the user table into memory, it sorts the entries.

  • When a client attempts to connect, the server looks through the entries in sorted order.

  • The server uses the first entry that matches the client hostname and username.

To see how this works, suppose that the user table looks like this:

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-

When the server reads in the table, it orders the entries with the most-specific Host values first. Literal hostnames and IP numbers are the most specific. The pattern '%' means “any host” and is least specific. Entries with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For the user table just shown, the result after sorting looks like this:

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-

When a client attempts to connect, the server looks through the sorted entries and uses the first match found. For a connection from localhost by jeffrey, two of the entries in the table match: the one with Host and User values of 'localhost' and '', and the one with values of '%' and 'jeffrey'. The 'localhost' entry appears first in sorted order, so that is the one the server uses.

Here is another example. Suppose that the user table looks like this:

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| %              | jeffrey  | ...
| thomas.loc.gov |          | ...
+----------------+----------+-

The sorted table looks like this:

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| thomas.loc.gov |          | ...
| %              | jeffrey  | ...
+----------------+----------+-

A connection by jeffrey from thomas.loc.gov is matched by the first entry, whereas a connection by jeffrey from whitehouse.gov is matched by the second.

It is a common misconception to think that, for a given username, all entries that explicitly name that user will be used first when the server attempts to find a match for the connection. This is simply not true. The previous example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the entry containing 'jeffrey' as the User column value, but by the entry with no username! As a result, jeffrey will be authenticated as an anonymous user, even though he specified a username when connecting.

If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the CURRENT_USER() function. It returns a value in user_name@host_name format that indicates the User and Host values from the matching user table record. Suppose that jeffrey connects and issues the following query:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost     |
+----------------+

The result shown here indicates that the matching user table entry had a blank User column value. In other words, the server is treating jeffrey as an anonymous user.

The CURRENT_USER() function is available as of MySQL 4.0.6. See the section called “Information Functions”. Another thing you can do to diagnose authentication problems is to print out the user table and sort it by hand to see where the first match is being made.

Access Control, Stage 2: Request Verification

Once you establish a connection, the server enters Stage 2 of access control. For each request that comes in on the connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, or columns_priv tables. (You may find it helpful to refer to the section called “How the Privilege System Works”, which lists the columns present in each of the grant tables.)

The user table grants privileges that are assigned to you on a global basis and that apply no matter what the current database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! In other words, user table privileges are superuser privileges. It is wise to grant privileges in the user table only to superusers such as database administrators. For other users, you should leave the privileges in the user table set to 'N' and grant privileges at more specific levels only. You can grant privileges for particular databases, tables, or columns.

The db and host tables grant database-specific privileges. Values in the scope columns of these tables can take the following forms:

  • The wildcard characters ‘%’ and ‘_’ can be used in the Host and Db columns of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include ‘_’ character as part of a database name, specify it as ‘\_’ in the GRANT statement.

  • A '%' Host value in the db table means “any host.” A blank Host value in the db table means “consult the host table for further information” (a process that is described later in this section).

  • A '%' or blank Host value in the host table means “any host.”

  • A '%' or blank Db value in either table means “any database.”

  • A blank User value in either table matches the anonymous user.

The server reads in and sorts the db and host tables at the same time that it reads the user table. The server sorts the db table based on the Host, Db, and User scope columns, and sorts the host table based on the Host and Db scope columns. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds.

The tables_priv and columns_priv tables grant table-specific and column-specific privileges. Values in the scope columns of these tables can take the following form:

  • The wildcard characters ‘%’ and ‘_’ can be used in the Host column of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator.

  • A '%' or blank Host value in either table means “any host.”

  • The Db, Table_name, and Column_name columns cannot contain wildcards or be blank in either table.

The server sorts the tables_priv and columns_priv tables based on the Host, Db, and User columns. This is similar to db table sorting, but simpler because only the Host column can contain wildcards.

The request verification process is described here. (If you are familiar with the access-checking source code, you will notice that the description here differs slightly from the algorithm used in the code. The description is equivalent to what the code actually does; it differs only to make the explanation simpler.)

For requests that require administrative privileges such as SHUTDOWN or RELOAD, the server checks only the user table entry because that is the only table that specifies administrative privileges. Access is granted if the entry allows the requested operation and denied otherwise. For example, if you want to execute mysqladmin shutdown but your user table entry doesn't grant the SHUTDOWN privilege to you, the server denies access without even checking the db or host tables. (They contain no Shutdown_priv column, so there is no need to do so.)

For database-related requests (INSERT, UPDATE, and so on), the server first checks the user's global (superuser) privileges by looking in the user table entry. If the entry allows the requested operation, access is granted. If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db and host tables:

  1. The server looks in the db table for a match on the Host, Db, and User columns. The Host and User columns are matched to the connecting user's hostname and MySQL username. The Db column is matched to the database that the user wants to access. If there is no entry for the Host and User, access is denied.

  2. If there is a matching db table entry and its Host column is not blank, that entry defines the user's database-specific privileges.

  3. If the matching db table entry's Host column is blank, it signifies that the host table enumerates which hosts should be allowed access to the database. In this case, a further lookup is done in the host table to find a match on the Host and Db columns. If no host table entry matches, access is denied. If there is a match, the user's database-specific privileges are computed as the intersection (not the union!) of the privileges in the db and host table entries; that is, the privileges that are 'Y' in both entries. (This way you can grant general privileges in the db table entry and then selectively restrict them on a host-by-host basis using the host table entries.)

After determining the database-specific privileges granted by the db and host table entries, the server adds them to the global privileges granted by the user table. If the result allows the requested operation, access is granted. Otherwise, the server successively checks the user's table and column privileges in the tables_priv and columns_priv tables, adds those to the user's privileges, and allows or denies access based on the result.

Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges

It may not be apparent why, if the global user entry privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database, table, and column privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT INTO ... SELECT statement, you need both the INSERT and the SELECT privileges. Your privileges might be such that the user table entry grants one privilege and the db table entry grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the entries in both tables must be combined.

The host table is not affected by the GRANT or REVOKE statements, so it is unused in most MySQL installations. If you modify it directly, you can use it for some specialized purposes, such as to to maintain a list of secure servers. For example, at TcX, the host table contains a list of all machines on the local network. These are granted all privileges.

You can also use the host table to indicate hosts that are not secure. Suppose that you have a machine public.your.domain that is located in a public area that you do not consider secure. You can allow access to all hosts on your network except that machine by using host table entries like this:

+--------------------+----+-
| Host               | Db | ...
+--------------------+----+-
| public.your.domain | %  | ... (all privileges set to 'N')
| %.your.domain      | %  | ... (all privileges set to 'Y')
+--------------------+----+-

Naturally, you should always test your entries in the grant tables (for example, by using SHOW GRANTS or mysqlaccess) to make sure that your access privileges are actually set up the way you think they are.

When Privilege Changes Take Effect

When mysqld starts, all grant table contents are read into memory and become effective for access control at that point.

When the server reloads the grant tables, privileges for existing client connections are affected as follows:

  • Table and column privilege changes take effect with the client's next request.

  • Database privilege changes take effect at the next USE db_name statement.

  • Changes to global privileges and passwords take effect the next time the client connects.

If you modify the grant tables using GRANT, REVOKE, or SET PASSWORD, the server notices these changes and reloads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. To reload the grant tables manually, issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges or mysqladmin reload command.

If you change the grant tables directly but forget to reload them, your changes will have no effect until you restart the server. This may leave you wondering why your changes don't seem to make any difference!

Causes of Access denied Errors

If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.

  • Make sure that the server is running. If it is not running, you cannot connect to it. For example, if you attempt to connect to the server and see a message such as one of those following, one cause might be that the server is not running:

    shell> mysql
    ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
    shell> mysql
    ERROR 2002: Can't connect to local MySQL server through socket
    '/tmp/mysql.sock' (111)
    

    It might also be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from those on which the server is listening. To correct this when you invoke a client program, specify a --port option to indicate the proper port, or a --socket option to indicate the proper named pipe or Unix socket file. To find out what port is used, and where the socket is, you can do:

    shell> netstat -l | grep mysql
    
  • The grant tables must be properly set up so that the server can use them for access control. For some distribution types (such as binary distributions on Windows on RPM distributions on Linux), the installation process initializes the mysql database containing the grant tables. For distributions that do not do this, you should initialize the grant tables manually by running the mysql_install_db script. For details, see the section called “Unix Post-Installation Procedures”.

    One way to determine whether you need to initialize the grant tables is to look for a mysql directory under the data directory. (The data directory normally is named data or var and is located under your MySQL installation directory.) Make sure that you have a file named user.MYD in the mysql database directory. If you do not, execute the mysql_install_db script. After running this script and starting the server, test the initial privileges by executing this command:

    shell> mysql -u root test
    

    The server should let you connect without error.

  • After a fresh installation, you should connect to the server and set up your users and their access permissions:

    shell> mysql -u root mysql
    

    The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the password for the root accounts is something you should do while you're setting up your other MySQL users. For instructions on setting the initial passwords, see the section called “Securing the Initial MySQL Accounts”.

  • If you have updated an existing MySQL installation to a newer version, did you run the mysql_fix_privilege_tables script? If not, do so. The structure of the grant tables changes occasionally when new capabilities are added, so after an upgrade you should always make sure that your tables have the current structure. For instructions, see the section called “Upgrading the Grant Tables”.

  • If a client program receives the following error message when it tries to connect, it means that the server expects passwords in a newer format than the client is capable of generating:

    shell> mysql
    Client does not support authentication protocol requested
    by server; consider upgrading MySQL client
    

    For information on how to deal with this, see the section called “Password Hashing in MySQL 4.1” and the section called “Client does not support authentication protocol”.

  • If you try to connect as root and get the following error, it means that you don't have an entry in the user table with a User column value of 'root' and that mysqld cannot resolve the hostname for your client:

    Access denied for user ''@'unknown' to database mysql
    

    In this case, you must restart the server with the --skip-grant-tables option and edit your /etc/hosts or \windows\hosts file to add an entry for your host.

  • Remember that client programs will use connection parameters specified in option files or environment variables. If a client program seems to be sending incorrect default connection parameters when you don't specify them on the command line, check your environment and any applicable option files. For example, if you get Access denied when you run a client without any options, make sure that you haven't specified an old password in any of your option files!

    You can suppress the use of option files by a client program by invoking it with the --no-defaults option. For example:

    shell> mysqladmin --no-defaults -u root version
    

    The option files that clients use are listed in the section called “Using Option Files”. Environment variables are listed in Appendix E, Environment Variables.

  • If you get the following error, it means that you are using an incorrect root password:

    shell> mysqladmin -u root -pxxxx ver
    Access denied for user 'root'@'localhost' (using password: YES)
    

    If the preceding error occurs even when you haven't specified a password, it means that you have an incorrect password listed in some option file. Try the --no-defaults option as described in the previous item.

    For information on changing passwords, see the section called “Assigning Account Passwords”.

    If you have lost or forgotten the root password, you can restart mysqld with --skip-grant-tables to change the password. See the section called “How to Reset the Root Password”.

  • If you change a password by using SET PASSWORD, INSERT, or UPDATE, you must encrypt the password using the PASSWORD() function. If you do not use PASSWORD() for these statements, the password will not work. For example, the following statement sets a password, but fails to encrypt it, so the user will not be able to connect afterward:

    mysql> SET PASSWORD FOR 'abe'@'host_name' = 'eagle';
    

    Instead, set the password like this:

    mysql> SET PASSWORD FOR 'abe'@'host_name' = PASSWORD('eagle');
    

    The PASSWORD() function is unnecessary when you specify a password using the GRANT statement or the mysqladmin password command, both of which automatically use PASSWORD() to encrypt the password. See the section called “Assigning Account Passwords”.

  • localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly. However, connections to localhost on Unix systems do not work if you are using a MySQL version older than 3.23.27 that uses MIT-pthreads: localhost connections are made using Unix socket files, which were not supported by MIT-pthreads at that time.

    To avoid this problem on such systems, you can use a --host=127.0.0.1 option to name the server host explicitly. This will make a TCP/IP connection to the local mysqld server. You can also use TCP/IP by specifying a --host option that uses the actual hostname of the local host. In this case, the hostname must be specified in a user table entry on the server host, even though you are running the client program on the same host as the server.

  • If you get an Access denied error when trying to connect to the database with mysql -u user_name, you may have a problem with the user table. Check this by executing mysql -u root mysql and issuing this SQL statement:

    mysql> SELECT * FROM user;
    

    The result should include an entry with the Host and User columns matching your computer's hostname and your MySQL username.

  • The Access denied error message will tell you who you are trying to log in as, the client host from which you are trying to connect, and whether or not you were using a password. Normally, you should have one entry in the user table that exactly matches the hostname and username that were given in the error message. For example, if you get an error message that contains using password: NO, it means that you tried to log in without an password.

  • If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

    Host ... is not allowed to connect to this MySQL server
    

    You can fix this by setting up an account for the combination of client hostname and username that you are using when trying to connect.

    If you don't know the IP number or hostname of the machine from which you are connecting, you should put an entry with '%' as the Host column value in the user table and restart mysqld with the --log option on the server machine. After trying to connect from the client machine, the information in the MySQL log will indicate how you really did connect. (Then change the '%' in the user table entry to the actual hostname that shows up in the log. Otherwise, you'll have a system that is insecure because it allows connections from any host for the given username.)

    On Linux, another reason that this error might occur is that you are using a binary MySQL version that is compiled with a different version of the glibc library than the one you are using. In this case, you should either upgrade your operating system or glibc, or download a source distribution of MySQL version and compile it yourself. A source RPM is normally trivial to compile and install, so this isn't a big problem.

  • If you specify a hostname when trying to connect, but get an error message where the hostname is not shown or is an IP number, it means that the MySQL server got an error when trying to resolve the IP number of the client host to a name:

    shell> mysqladmin -u root -pxxxx -h some-hostname ver
    Access denied for user 'root'@'' (using password: YES)
    

    This indicates a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS hostname cache. See the section called “How MySQL Uses DNS”.

    Some permanent solutions are:

    • Try to find out what is wrong with your DNS server and fix it.

    • Specify IP numbers rather than hostnames in the MySQL grant tables.

    • Put an entry for the client machine name in /etc/hosts.

    • Start mysqld with the --skip-name-resolve option.

    • Start mysqld with the --skip-host-cache option.

    • On Unix, if you are running the server and the client on the same machine, connect to localhost. Unix connections to localhost use a Unix socket file rather than TCP/IP.

    • On Windows, if you are running the server and the client on the same machine and the server supports named pipe connections, connect to the hostname . (period). Connections to . use a named pipe rather than TCP/IP.

  • If mysql -u root test works but mysql -h your_hostname -u root test results in Access denied (where your_hostname is the actual hostname of the local host), you may not have the correct name for your host in the user table. A common problem here is that the Host value in the user table entry specifies an unqualified hostname, but your system's name resolution routines return a fully qualified domain name (or vice versa). For example, if you have an entry with host 'tcx' in the user table, but your DNS tells MySQL that your hostname is 'tcx.subnet.se', the entry will not work. Try adding an entry to the user table that contains the IP number of your host as the Host column value. (Alternatively, you could add an entry to the user table with a Host value that contains a wildcard; for example, 'tcx.%'. However, use of hostnames ending with ‘%’ is insecure and is not recommended!)

  • If mysql -u user_name test works but mysql -u user_name other_db_name does not, you have not granted database access for other_db_name to the given user.

  • If mysql -u user_name works when executed on the server host, but mysql -h host_name -u user_name doesn't work when executed on a remote client host, you have not enabled access to the server for the given username from the remote host.

  • If you can't figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain ‘%’ or ‘_’). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this will allow you to specify localhost to connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables.

  • If you get the following error, you may have a problem with the db or host table:

    Access to database denied
    

    If the entry selected from the db table has an empty value in the Host column, make sure that there are one or more corresponding entries in the host table specifying which hosts the db table entry applies to.

  • If you are able to connect to the MySQL server, but get an Access denied message whenever you issue a SELECT ... INTO OUTFILE or LOAD DATA INFILE statement, your entry in the user table doesn't have the FILE privilege enabled.

  • If you change the grant tables directly (for example, by using INSERT, UPDATE, or DELETE statements) and your changes seem to be ignored, remember that you must execute a FLUSH PRIVILEGES statement or a mysqladmin flush-privileges command to cause the server to re-read the privilege tables. Otherwise, your changes have no effect until the next time the server is restarted. Remember that after you change the root password with an UPDATE command, you won't need to specify the new password until after you flush the privileges, because the server won't know you've changed the password yet!

  • If your privileges seem to have changed in the middle of a session, it may be that a MySQL administrator has changed them. Reloading the grant tables affects new client connections, but it also affects existing connections as indicated in the section called “When Privilege Changes Take Effect”.

  • If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to the server with mysql -u user_name db_name or mysql -u user_name -pyour_pass db_name. If you are able to connect using the mysql client, the problem lies with your program, not with the access privileges. (There is no space between -p and the password; you can also use the --password=your_pass syntax to specify the password. If you use the -p option alone, MySQL will prompt you for the password.)

  • For testing, start the mysqld server with the --skip-grant-tables option. Then you can change the MySQL grant tables and use the mysqlaccess script to check whether your modifications have the desired effect. When you are satisfied with your changes, execute mysqladmin flush-privileges to tell the mysqld server to start using the new grant tables. (Reloading the grant tables overrides the --skip-grant-tables option. This allows you to tell the server to begin using the grant tables again without stopping and restarting it.)

  • If everything else fails, start the mysqld server with a debugging option (for example, --debug=d,general,query). This will print host and user information about attempted connections, as well as information about each command issued. See the section called “Creating Trace Files”.

  • If you have any other problems with the MySQL grant tables and feel you must post the problem to the mailing list, always provide a dump of the MySQL grant tables. You can dump the tables with the mysqldump mysql command. As always, post your problem using the mysqlbug script. See the section called “How to Report Bugs or Problems”. In some cases, you may need to restart mysqld with --skip-grant-tables to run mysqldump.

Password Hashing in MySQL 4.1

MySQL user accounts are listed in the user table of the mysql database. Each MySQL account is assigned a password, although what is stored in the Password column of the user table is not the plaintext version of the password, but a hash value computed from it. Password hash values are computed by the PASSWORD() function.

MySQL uses passwords in two phases of client/server communication:

  • When a client attempts to connect to the server, there is an initial authentication step in which the client must present a password that has a hash value matching the hash value stored in the user table for the account that the client wants to use.

  • After the client connects, it can (if it has sufficient privileges) set or change the password hashes for accounts listed in the user table. The client can do this by using the PASSWORD() function to generate a password hash, or by using the GRANT or SET PASSWORD statements.

In other words, the server uses hash values during authentication when a client first attempts to connect. The server generates hash values if a connected client invokes the PASSWORD() function or uses a GRANT or SET PASSWORD statement to set or change a password.

The password hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by the 4.1 server and 4.1 clients, which can result in some compatibility problems. A 4.1 client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 server may run into difficulties. For example, a 4.0 mysql client that attempts to connect to a 4.1 server may fail with the following error message:

shell> mysql -h localhost -u root
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

The following discussion describes the differences between the old and new password mechanisms, and what you should do if you upgrade your server to 4.1 but need to maintain backward compatibility with pre-4.1 clients. Additional information can be found in the section called “Client does not support authentication protocol”.

Note: This discussion contrasts 4.1 behavior with pre-4.1 behavior, but the 4.1 behavior described here actually begins with 4.1.1. MySQL 4.1.0 is an “odd” release because it has a slightly different mechanism than that implemented in 4.1.1 and up. Differences between 4.1.0 and more recent versions are described further in the section called “Password Hashing in MySQL 4.1.0”.

Prior to MySQL 4.1, password hashes computed by the PASSWORD() function are 16 bytes long. Such hashes look like this:

mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e   |
+--------------------+

The Password column of the user table (in which these hashes are stored) also is 16 bytes long before MySQL 4.1.

As of MySQL 4.1, the PASSWORD() function has been modified to produce a longer 41-byte hash value:

mysql> SELECT PASSWORD('mypass');
+-----------------------------------------------+
| PASSWORD('mypass')                            |
+-----------------------------------------------+
| *43c8aa34cdc98eddd3de1fe9a9c2c2a9f92bb2098d75 |
+-----------------------------------------------+

Accordingly, the Password column in the user table also must be 41 bytes long to store these values:

  • If you perform a new installation of MySQL 4.1, the Password column will be made 41 bytes long automatically.

  • If you upgrade an older installation to 4.1, you should run the mysql_fix_privilege_tables script to increase the length of the Password column from 16 to 41 bytes. (The script does not change existing password values, which remain 16 bytes long.)

A widened Password column can store password hashes in both the old and new formats. The format of any given password hash value can be determined two ways:

  • The obvious difference is the length (16 bytes versus 41 bytes).

  • A second difference is that password hashes in the new format always begin with a ‘*’ character, whereas passwords in the old format never do.

The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.

The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.

The way in which the server uses password hashes during authentication is affected by the width of the Password column:

  • If the column is short, only short-hash authentication is used.

  • If the column is long, it can hold either short or long hashes, and the server can use either format:

    • Pre-4.1 clients can connect, although because they know only about the old hashing mechanism, they can authenticate only for accounts that have short hashes.

    • 4.1 clients can authenticate for accounts that have short or long hashes.

For short-hash accounts, the authentication process is actually a bit more secure for 4.1 clients than for older clients. In terms of security, the gradient from least to most secure is:

  • Pre-4.1 client authenticating for account with short password hash

  • 4.1 client authenticating for account with short password hash

  • 4.1 client authenticating for account with long password hash

The way in which the server generates password hashes for connected clients is affected by the width of the Password column and by the --old-passwords option. A 4.1 server generates long hashes only if certain conditions are met: The Password column must be wide enough to hold long values and the --old-passwords option must not be given. These conditions apply as follows:

  • The Password column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. This is the behavior that occurs if you have upgraded to 4.1 but have not yet run the mysql_fix_privilege_tables script to widen the Password column.

  • If the Password column is wide, it can store either short or long password hashes. In this case, PASSWORD(), GRANT, and SET PASSWORD generate long hashes unless the server was started with the --old-passwords option. That option forces the server to generate short password hashes instead.

The purpose of the --old-passwords option is to allow you to maintain backward compatibility with pre-4.1 clients under circumstances where the server would otherwise generate long password hashes. The option doesn't affect authentication (4.1 clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in the user table as the result of a password-changing operation. Were that to occur, the account no longer could be used by pre-4.1 clients. Without the --old-passwords option, the following undesirable scenario is possible:

  • An old client connects to an account that has a short password hash.

  • The client changes its own password. Without --old-passwords, this results in the account having a long password hash.

  • The next time the old client attempts to connect to the account, it cannot, because the account now has a long password hash that requires the new hashing mechanism during authentication. (Once an account has a long password hash in the user table, only 4.1 clients can authenticate for it, because pre-4.1 clients do not understand long hashes.)

This scenario illustrates that, if you must support older pre-4.1 clients, it is dangerous to run a 4.1 server without using the --old-passwords option. By running the server with --old-passwords, password-changing operations will not generate long password hashes and thus do not cause accounts to become inaccessible to older clients. (Those clients cannot inadvertently lock themselves out by changing their password and ending up with a long password hash.)

The downside of the --old-passwords option is that any passwords you create or change will use short hashes, even for 4.1 clients. Thus, you lose the additional security provided by long password hashes. If you want to create an account that has a long hash (for example, for use by 4.1 clients), you must do so while running the server without --old-passwords.

The following scenarios are possible for running a 4.1 server:

Scenario 1: Short Password column in user table:

  • Only short hashes can be stored in the Password column.

  • The server uses only short hashes during client authentication.

  • For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use short hashes exclusively. Any change to an account's password results in that account having a short password hash.

  • The --old-passwords option can be used but is superfluous because with a short Password column, the server will generate only short password hashes anyway.

Scenario 2: Long Password column; server not started with --old-passwords option:

  • Short or long hashes can be stored in the Password column.

  • 4.1 clients can authenticate for accounts that have short or long hashes.

  • Pre-4.1 clients can authenticate only for accounts that have short hashes.

  • For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use long hashes exclusively. A change to an account's password results in that account having a long password hash.

As indicated earlier, a danger in this scenario is that it is possible for accounts that have a short password hash to become inaccessible to pre-4.1 clients. A change to such an account's password made via GRANT, PASSWORD(), or SET PASSWORD results in the account being given a long password hash. From that point on, no pre-4.1 client can authenticate to that account until the client upgrades to 4.1.

To deal with this problem, you can change a password in a special way. For example, normally you use SET PASSWORD as follows to change an account password:

mysql> SET PASSWORD FOR 'some_user'@'some_host' = PASSWORD('mypass');

To change the password but create a short hash, use the OLD_PASSWORD() function instead:

mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

OLD_PASSWORD() is useful for situations in which you explicitly want to generate a short hash.

Scenario 3: Long Password column; server started with --old-passwords option:

  • Short or long hashes can be stored in the Password column.

  • 4.1 clients can authenticate for accounts that have short or long hashes (but note that it is possible to create long hashes only when the server is started without --old-passwords).

  • Pre-4.1 clients can authenticate only for accounts that have short hashes.

  • For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use short hashes exclusively. Any change to an account's password results in that account having a short password hash.

In this scenario, you cannot create accounts that have long password hashes, because the --old-passwords option prevents generation of long hashes. Also, if you create an account with a long hash before using the --old-passwords option, changing the account's password while --old-passwords is in effect results in the account being given a short password, causing it to lose the security benefits of a longer hash.

The disadvantages for these scenarios may be summarized as follows:

In scenario 1, you cannot take advantage of longer hashes that provide more secure authentication.

In scenario 2, accounts with short hashes become inaccessible to pre-4.1 clients if you change their passwords without explicitly using OLD_PASSWORD().

In scenario 3, --old-passwords prevents accounts with short hashes from becoming inaccessible, but password-changing operations cause accounts with long hashes to revert to short hashes, and you cannot change them back to long hashes while --old-passwords is in effect.

Implications of Password Hashing Changes for Application Programs

An upgrade to MySQL 4.1 can cause a compatibility issue for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts. But some applications use PASSWORD() for their own purposes anyway.

If you upgrade to 4.1 and run the server under conditions where it generates long password hashes, an application that uses PASSWORD() for its own passwords will break. The recommended course of action is to modify the application to use another function, such as SHA1() or MD5(), to produce hashed values. If that is not possible, you can use the OLD_PASSWORD() function, which is provided to generate short hashes in the old format. But note that OLD_PASSWORD() may one day no longer be supported.

If the server is running under circumstances where it generates short hashes, OLD_PASSWORD() is available but is equivalent to PASSWORD().

Password Hashing in MySQL 4.1.0

Password hashing in MySQL 4.1.0 differs from hashing in 4.1.1 and up. The 4.1.0 differences are:

  • Password hashes are 45 bytes long rather than 41 bytes.

  • The PASSWORD() function is non-repeatable. That is, with a given argument X, successive calls to PASSWORD(X) generate different results.

These differences make authentication in 4.1.0 incompatible with that of releases that follow it. If you have upgraded to MySQL 4.1.0, it is recommended that you upgrade to a newer version as soon as possible. After you do, reassign any long passwords in the user table so that they are compatible with the 41-byte format.