5.8. The MySQL Access Privilege System

MySQL 5.0

previous page next page

5.8. The MySQL Access Privilege System

MySQL has an advanced but non-standard security and privilege system. The following discussion describes how it works.

5.8.1. What the Privilege System Does

The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as , , , and .

Additional functionality includes the ability to have anonymous users and to grant privileges for MySQL-specific functions such as and administrative operations.

5.8.2. 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. When you issue requests after connecting, 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 who connects from need not be the same person as the user who connects from . MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by from , and a different set of privileges for connections by from .

MySQL access control involves two stages when you run a client program that connects to the server:

  • Stage 1: The server checks whether it should allow you to connect.

  • Stage 2: Assuming that you can connect, the server checks each statement you issue to determine 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 privilege for the table or the privilege for the database.

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

The server stores privilege information in the grant tables of the database (that is, in the database named ). The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in Section 5.8.7, “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 statements such as and to set up accounts and control the privileges available to each one. See Section 13.5.1, “Account Management Statements”. 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 , , and tables in the database at both stages of access control. The columns in the and tables are shown here. The table is similar to the table but has a specialized use as described in Section 5.8.6, “Access Control, Stage 2: Request Verification”.

Table Name user db
Scope columns
 
 
Privilege columns
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
   
   
   
   
   
   
   
   
Security columns  
   
   
   
Resource control columns  
   
   
   

was present in MySQL 5.0.0, but did not become operational until MySQL 5.0.3.

The and columns were added in MySQL 5.0.1.

The , , and columns were added in MySQL 5.0.3.

During the second stage of access control, the server performs request verification to make sure that each client has sufficient privileges for each request that it issues. In addition to the , , and grant tables, the server may also consult the and tables for requests that involve tables. The and tables provide finer privilege control at the table and column levels. They have the following columns:

Table Name tables_priv columns_priv
Scope columns
 
 
 
   
Privilege columns
   
Other columns
   

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

For verification of requests that involve stored routines, the server may consult the table. This table has the following columns:

Table Name procs_priv
Scope columns
 
 
 
 
Privilege columns
Other columns
 

The table exists as of MySQL 5.0.3. The column was added in MySQL 5.0.6. It is an column with values of or to indicate the type of routine the row refers to. This column allows privileges to be granted separately for a function and a procedure with the same name.

The and 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 row (entry) in the tables; that is, the context in which the row applies. For example, a table row with and values of and would be used for authenticating connections made to the server from the host by a client that specifies a username of . Similarly, a table row with , , and column values of , and would be used when connects from the host to access the database. The and tables contain scope columns indicating tables or table/column combinations to which each row applies. The scope columns indicate the stored routine to which each row applies.

  • Privilege columns indicate which privileges are granted by a table row; 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. Section 5.8.6, “Access Control, Stage 2: Request Verification”, describes the rules that are used to do this.

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

Column Name Type

For access-checking purposes, comparisons of values are case-insensitive. , , , and values are case sensitive. and values are case insensitive.

In the , , and tables, each privilege is listed in a separate column that is declared as . In other words, each privilege can be disabled or enabled, with the default being disabled.

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

Table Name Column Name Possible Set Elements

Briefly, the server uses the grant tables in the following manner:

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

    Note: Because any global privilege is considered a privilege for all databases, any global privilege enables a user to see all database names with or by examining the table of .

  • The 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 table is used in conjunction with the table when you want a given table row 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 value empty in the user's table row, then populate the table with a row for each of those hosts. This mechanism is described more detail in Section 5.8.6, “Access Control, Stage 2: Request Verification”.

    Note: The table must be modified directly with statements such as , , and . It is not affected by statements such as and that modify the grant tables indirectly. Most MySQL installations need not use this table at all.

  • The and tables are similar to the 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.

  • The table applies to stored routines. A privilege granted at the routine level applies only to a single routine.

Administrative privileges (such as or ) are specified only in the table. The reason for this is that 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 table.

The privilege also is specified only in the 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 statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in Section 5.8.7, “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. To check the privileges for a given account, use the statement. (See Section 13.5.4.12, “ Syntax”.) For example, to determine the privileges that are granted to an account with and values of and , issue this statement: 

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

For additional help in diagnosing privilege-related problems, see Section 5.8.8, “Causes of Errors”. For general advice on security issues, see Section 5.7, “General Security Issues”.

5.8.3. Privileges Provided by MySQL

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

The names used in the and statements to refer to privileges are shown in the following table, along with the 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 Section 13.5.1.3, “ Syntax”.

Privilege Column Context
databases, tables, or indexes
databases or tables
databases, tables, or stored routines
databases or tables
tables
tables
tables
tables
tables
tables
views
views
stored routines
stored routines
stored routines
file access on server host
server administration
server administration
server administration
server administration
server administration
server administration
server administration
server administration
server administration
server administration

Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

and were added in MySQL 5.0.1. , , and were added in MySQL 5.0.3. Although was present in MySQL 5.0.0, it did not become operational until MySQL 5.0.3.

To create or alter stored routines if binary logging is enabled, you may also need the privilege, as described in Section 17.4, “Binary Logging of Stored Routines and Triggers”.

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

The , , , and privileges allow you to perform operations on rows in existing tables in a database. is also required for the , , and table-maintenance statements.

statements require the privilege only if they actually retrieve rows from a table. Some 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: 

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

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

The privilege enables you to use to change the structure of or rename tables.

The privilege is needed for creating stored routines (functions and procedures). privilege is needed for altering or dropping stored routines, and is needed for executing stored routines.

The privilege enables you to give to other users those privileges that you yourself possess. It can be used for databases, tables, and stored routines.

The privilege gives you permission to read and write files on the server host using the and statements. A user who has the 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 privilege also enables the user to create new files in any directory where the MySQL server has write access. As a security measure, the server will not overwrite existing files.

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 enables you to execute:

Privilege Commands Permitted to Privilege Holders
, , , , , , ,

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

The command shuts down the server. There is no corresponding SQL statement.

The command displays information about the threads executing within the server (that is, information about the statements being executed by clients). The command terminates server threads. You can always display or kill your own threads, but you need the privilege to display threads initiated by other users and the privilege to kill them. See Section 13.5.5.3, “ Syntax”.

The privilege enables the use of the keyword in statements.

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

The privilege enables the use of and .

The privilege should be granted to accounts that are used by slave servers to 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 privilege allows the account to see database names by issuing the 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 option. Note that any global privilege is a privilege for the database.

It is a good idea to grant to an account only those privileges that it needs. You should exercise particular caution in granting the and administrative privileges:

  • The 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 to transfer its contents to the client host.

  • The privilege enables users to give their privileges to other users. Two users that have different privileges and with the privilege are able to combine privileges.

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

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

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

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

  • Privileges granted for the 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 table 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.

  • A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine.

5.8.4. Connecting to the MySQL Server

MySQL client programs generally expect you to specify certain 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>  -u  -p

Alternative forms of the , , and options are , , and . Note that there is no space between or and the password following it.

If you use a or option but do not specify the password value, the client program prompts 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 Section 5.9.6, “Keeping Your Password Secure”.

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

  • The default hostname is .

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

  • No password is supplied if neither nor is given.

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

shell> 
shell> 
shell> 
shell>

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 section of an option file. The relevant section of the file might look like this: 

    [client]
host=
user=
password=

    Section 4.3.2, “Using Option Files”, discusses option files further.

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

5.8.5. 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, and 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 table scope columns (, , and ). The server accepts the connection only if the and columns in some table row match the client hostname and username and the client supplies the password specified in that row.

values in the table may be specified as follows:

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

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

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

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

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

    client_ip & netmask = host_ip

    That is, for the 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 in the range from to .

    Note: The netmask can only be used to tell the server to use 8, 16, 24, or 32 bits of the address. Examples:

    • : anything on the 192 class A network

    • : anything on the 192.168 class B network

    • : anything on the 192.168.1 class C network

    • : only this specific IP

    The following netmask (28 bits) will not work: 

    192.168.0.1/255.255.255.240

  • A blank value in a table row means that its privileges should be combined with those in the row in the table that matches the client hostname. The privileges are combined using an AND (intersection) operation, not OR (union). Section 5.8.6, “Access Control, Stage 2: Request Verification”, discusses use of the table further.

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

Because you can use IP wildcard values in the column (for example, to match every host on a subnet), someone could try to exploit this capability by naming a host . 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 , its name never matches the column of the grant tables. An IP wildcard value can match only IP numbers, not hostnames.

In the column, wildcard characters are not allowed, but you can specify a blank value, which matches any name. If the table row 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 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 values in the 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 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 never give anyone access to it. In particular, do not give non-administrative users read access to tables in the database.

MySQL 5.0 employs the stronger authentication method (first implemented in MySQL 4.1) 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 database is captured. Section 5.8.9, “Password Hashing as of MySQL 4.1”, discusses password encryption further.

The following table shows how various combinations of and values in the table apply to incoming connections.

Value Value Allowable Connections
, connecting from
Any user, connecting from
, connecting from any host
Any user, connecting from any host
, connecting from any host in the domain
, connecting from , , , and so on (this is probably not useful)
, connecting from the host with IP address
, connecting from any host in the class C subnet
Same as previous example

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

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 table into memory, it sorts the rows.

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

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

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

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

When the server reads the table into memory, it orders the rows with the most-specific values first. Literal hostnames and IP numbers are the most specific. The pattern means “any host” and is least specific. Rows with the same value are ordered with the most-specific values first (a blank value means “any user” and is least specific). For the 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 rows and uses the first match found. For a connection from by , two of the rows from the table match: the one with and values of and , and the one with values of and . The row appears first in sorted order, so that is the one the server uses.

Here is another example. Suppose that the 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 from is matched by the first row, whereas a connection by from is matched by the second.

It is a common misconception to think that, for a given username, all rows that explicitly name that user are 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 by is first matched not by the row containing as the column value, but by the row with no username. As a result, is 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 function. (See Section 12.9.3, “Information Functions”.) It returns a value in @ format that indicates the and values from the matching table row. Suppose that connects and issues the following query: 

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

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

Another thing you can do to diagnose authentication problems is to print out the table and sort it by hand to see where the first match is being made.

5.8.6. Access Control, Stage 2: Request Verification

After you establish a connection, the server enters Stage 2 of access control. For each request that you issue via that 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 , , , , , or tables. (You may find it helpful to refer to Section 5.8.2, “How the Privilege System Works”, which lists the columns present in each of the grant tables.)

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

The and 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 and columns of either table. These have the same meaning as for pattern-matching operations performed with the operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (‘’) as part of a database name, specify it as ‘’ in the statement.

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

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

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

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

The server reads the and tables into memory and sorts them at the same time that it reads the table. The server sorts the table based on the , , and scope columns, and sorts the table based on the and scope columns. As with the 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 , and tables grant table-specific, column-specific, and routine-specific privileges. Values in the scope columns of these tables can take the following forms:

  • The wildcard characters ‘’ and ‘’ can be used in the column. These have the same meaning as for pattern-matching operations performed with the operator.

  • A or blank value means “any host.”

  • The , , and columns cannot contain wildcards or be blank.

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

The server uses the sorted tables to verify each request that it receives. For requests that require administrative privileges such as or , the server checks only the table row because that is the only table that specifies administrative privileges. The server grants access if the row allows the requested operation and denies access otherwise. For example, if you want to execute mysqladmin shutdown but your table row doesn't grant the privilege to you, the server denies access without even checking the or tables. (They contain no column, so there is no need to do so.)

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

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

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

  3. If the matching table row's column is blank, it signifies that the table enumerates which hosts should be allowed access to the database. In this case, a further lookup is done in the table to find a match on the and columns. If no table row 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 and table entries; that is, the privileges that are in both entries. (This way you can grant general privileges in the table row and then selectively restrict them on a host-by-host basis using the table entries.)

After determining the database-specific privileges granted by the and table entries, the server adds them to the global privileges granted by the 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 and tables, adds those to the user's privileges, and allows or denies access based on the result. For stored routine operations, the server uses the table rather than and .

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
OR routine privileges

It may not be apparent why, if the global row 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 statement, you need both the and the privileges. Your privileges might be such that the table row grants one privilege and the table row 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 table is not affected by the or 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 maintain a list of secure servers. For example, at TcX, the table contains a list of all machines on the local network. These are granted all privileges.

You can also use the table to indicate hosts that are not secure. Suppose that you have a machine 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 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 changes to the grant tables (for example, by using ) to make sure that your access privileges are actually set up the way you think they are.

5.8.7. When Privilege Changes Take Effect

When mysqld starts, it reads all grant table contents into memory. The in-memory tables 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 statement.

    Note: Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database or executing a statement.

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

If you modify the grant tables indirectly using statements such as , , or , the server notices these changes and loads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as , , or , 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 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 have no effect until you restart the server. This may leave you wondering why your changes do not seem to make any difference!

5.8.8. 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> 
ERROR 2003: Can't connect to MySQL server on '' (111)
shell> 
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 the one on which the server is listening. To correct this when you invoke a client program, specify a option to indicate the proper port number, or a option to indicate the proper named pipe or Unix socket file. To find out where the socket file is, you can use this command: 

    shell>

  • 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, or RPM distributions on Linux), the installation process initializes the database containing the grant tables. For distributions that do not do this, you must initialize the grant tables manually by running the mysql_install_db script. For details, see Section 2.10.2, “Unix Post-Installation Procedures”.

    One way to determine whether you need to initialize the grant tables is to look for a directory under the data directory. (The data directory normally is named or and is located under your MySQL installation directory.) Make sure that you have a file named in the 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>

    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>

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

  • If you have updated an existing MySQL installation to a newer version, did you run the mysql_upgrade 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 Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • 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> 
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

    For information on how to deal with this, see Section 5.8.9, “Password Hashing as of MySQL 4.1”, and Section A.2.3, “”.

  • If you try to connect as and get the following error, it means that you do not have a row in the table with a column value of 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 option and edit your file or file to add an entry for your host.

  • Remember that client programs use connection parameters specified in option files or environment variables. If a client program seems to be sending incorrect default connection parameters when you have not specified them on the command line, check your environment and any applicable option files. For example, if you get when you run a client without any options, make sure that you have not 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 option. For example: 

    shell>

    The option files that clients use are listed in Section 4.3.2, “Using Option Files”. Environment variables are listed in Appendix F, Environment Variables.

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

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

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

    For information on changing passwords, see Section 5.9.5, “Assigning Account Passwords”.

    If you have lost or forgotten the password, you can restart mysqld with to change the password. See Section A.4.1, “How to Reset the Root Password”.

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

    SET PASSWORD FOR 'abe'@'' = 'eagle';

    Instead, set the password like this: 

    SET PASSWORD FOR 'abe'@'' = PASSWORD('eagle');

    The function is unnecessary when you specify a password using the or (beginning with MySQL 5.0.2) statements, or the mysqladmin password command. Each of those automatically uses to encrypt the password. See Section 5.9.5, “Assigning Account Passwords”, and Section 13.5.1.1, “ Syntax”.

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

    To avoid this problem on such systems, you can use a 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 option that uses the actual hostname of the local host. In this case, the hostname must be specified in a table row on the server host, even though you are running the client program on the same host as the server.

  • If you get an error when trying to connect to the database with , you may have a problem with the table. Check this by executing and issuing this SQL statement: 

    SELECT * FROM user;

    The result should include a row with the and columns matching your computer's hostname and your MySQL username.

  • The error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password. Normally, you should have one row in the 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 , it means that you tried to log in without a 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 table with a 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 do not know the IP number or hostname of the machine from which you are connecting, you should put a row with as the column value in the table. After trying to connect from the client machine, use a query to see how you really did connect. (Then change the in the table row to the actual hostname that shows up in the log. Otherwise, your system is left 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 library than the one you are using. In this case, you should either upgrade your operating system or , or download a source distribution of MySQL version and compile it yourself. A source RPM is normally trivial to compile and install, so this is not 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>  -h  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 Section 7.5.6, “How MySQL Uses DNS”.

    Some permanent solutions are:

    • Determine 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 or .

    • Start mysqld with the option.

    • Start mysqld with the option.

    • On Unix, if you are running the server and the client on the same machine, connect to . Unix connections to 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 works but -u root test results in (where is the actual hostname of the local host), you may not have the correct name for your host in the table. A common problem here is that the value in the table row 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 in the table, but your DNS tells MySQL that your hostname is , the entry does not work. Try adding an entry to the table that contains the IP number of your host as the column value. (Alternatively, you could add an entry to the table with a value that contains a wildcard; for example, . However, use of hostnames ending with ‘’ is insecure and is not recommended!)

  • If test works but does not, you have not granted database access for to the given user.

  • If works when executed on the server host, but -u does not 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 cannot figure out why you get , remove from the table all entries that have values containing wildcards (entries that contain ‘’ or ‘’). A very common error is to insert a new entry with = and =', thinking that this allows you to specify to connect from the same machine. The reason that this does not work is that the default privileges include an entry with = and =. Because that entry has a value that is more specific than , it is used in preference to the new entry when connecting from ! The correct procedure is to insert a second entry with = and =', or to delete the entry with = and =. After deleting the entry, remember to issue a statement to reload the grant tables.

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

    Access to database denied

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

  • If you are able to connect to the MySQL server, but get an message whenever you issue a or statement, your entry in the table does not have the privilege enabled.

  • If you change the grant tables directly (for example, by using , , or statements) and your changes seem to be ignored, remember that you must execute a 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 password with an 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 Section 5.8.7, “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 or -p . 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 and the password; you can also use the syntax to specify the password. If you use the option with no password value, MySQL prompts you for the password.)

  • For testing, start the mysqld server with the 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 option. This enables 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, ). This prints host and user information about attempted connections, as well as information about each command issued. See Section E.1.2, “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. To file a bug report, see the instructions at Section 1.8, “How to Report Bugs or Problems”. In some cases, you may need to restart mysqld with to run mysqldump.

5.8.9. Password Hashing as of MySQL 4.1

MySQL user accounts are listed in the table of the database. Each MySQL account is assigned a password, although what is stored in the column of the table is not the plaintext version of the password, but a hash value computed from it. Password hash values are computed by the 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 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 table. The client can do this by using the function to generate a password hash, or by using the or 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 function or uses a or 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 MySQL 4.1 (and newer) servers and clients, which can result in some compatibility problems. A 4.1 or newer 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 or newer server may run into difficulties. For example, a 3.23 mysql client that attempts to connect to a 5.0 server may fail with the following error message: 

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

Another common example of this phenomenon occurs for attempts to use the older PHP extension after upgrading to MySQL 4.1 or newer. (See Section 22.3.1, “Common Problems with MySQL and PHP”.)

The following discussion describes the differences between the old and new password mechanisms, and what you should do if you upgrade your server but need to maintain backward compatibility with pre-4.1 clients. Additional information can be found in Section A.2.3, “”. This information is of particular importance to PHP programmers migrating MySQL databases from version 4.0 or lower to version 4.1 or higher.

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 MySQL 3.23, 4.0, 4.1 Reference Manual.

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

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

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

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

mysql> 
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

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

  • If you perform a new installation of MySQL 5.0, the column is made 41 bytes long automatically.

  • Upgrading from MySQL 4.1 (4.1.1 or later in the 4.1 series) to MySQL 5.0 should not give rise to any issues in this regard because both versions use the same password hashing mechanism. If you wish to upgrade an older release of MySQL to version 5.0, you should upgrade to version 4.1 first, then upgrade the 4.1 installation to 5.0.

A widened 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 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 using accounts that have short hashes.

    • 4.1 and later clients can authenticate using accounts that have short or long hashes.

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

  • Pre-4.1 client authenticating with short password hash

  • 4.1 or later client authenticating with short password hash

  • 4.1 or later client authenticating with long password hash

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

  • The 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 , , or . 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 column.

  • If the column is wide, it can store either short or long password hashes. In this case, , , and generate long hashes unless the server was started with the option. That option forces the server to generate short password hashes instead.

The purpose of the option is to enable 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 and later clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in the 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 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 , 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 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 and later 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 or newer server without using the option. By running the server with , password-changing operations do 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 option is that any passwords you create or change 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 .

The following scenarios are possible for running a 4.1 or later server:

Scenario 1: Short column in user table:

  • Only short hashes can be stored in the column.

  • The server uses only short hashes during client authentication.

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

  • The option can be used but is superfluous because with a short column, the server generates only short password hashes anyway.

Scenario 2: Long column; server not started with option:

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

  • 4.1 and later clients can authenticate using accounts that have short or long hashes.

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

  • For connected clients, password hash-generating operations involving , , or 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 , , or 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 as follows to change an account password: 

SET PASSWORD FOR ''@'' = PASSWORD('mypass');

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

SET PASSWORD FOR ''@'' = OLD_PASSWORD('mypass');

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

Scenario 3: Long column; 4.1 or newer server started with option:

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

  • 4.1 and later 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 ).

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

  • For connected clients, password hash-generating operations involving , , or 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 option prevents generation of long hashes. Also, if you create an account with a long hash before using the option, changing the account's password while 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 .

In scenario 3, 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 is in effect.

5.8.9.1. Implications of Password Hashing Changes for Application Programs

An upgrade to MySQL version 4.1 or later can cause compatibility issues for applications that use to generate passwords for their own purposes. Applications really should not do this, because should be used only to manage passwords for MySQL accounts. But some applications use for their own purposes anyway.

If you upgrade to 4.1 or later from a pre-4.1 version of MySQL and run the server under conditions where it generates long password hashes, an application using for its own passwords breaks. The recommended course of action in such cases is to modify the application to use another function, such as or , to produce hashed values. If that is not possible, you can use the function, which is provided for generate short hashes in the old format. However, you should note that may one day no longer be supported.

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

PHP programmers migrating their MySQL databases from version 4.0 or lower to version 4.1 or higher should see Section 22.3, “MySQL PHP API”.

previous page start next page