General Security Issues and the MySQL Access Privilege System

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

General Security Guidelines

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, we emphasize the necessity of fully protecting the entire server host (not simply the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines whenever possible:

Do not ever give anyone (except the mysql root user) access to the user table in the mysql database! This is critical. The encrypted password is the real password in MySQL. Anyone who knows the password which is listed in the user table and has access to the host listed for the account can easily log in as that user.
Learn the MySQL access privilege system. The GRANT and REVOKE commands are used for controlling access to MySQL. Do not grant any more privileges than necessary. Never grant privileges to all hosts.
Checklist:
- Try mysql -u root. If you are able to connect successfully to the server without being asked for a password, you have problems. Anyone can connect to your MySQL server as the MySQL root user with full privileges! Review the MySQL installation instructions, paying particular attention to the item about setting a root password.
- Use the command SHOW GRANTS and check to see who has access to what. Remove those privileges that are not necessary using the REVOKE command.
Do not keep any plain-text passwords in your database. When your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead use MD5( ) or another one-way hashing function.
Do not choose passwords from dictionaries. There are special programs to break them. Even passwords like “xfish98” are very bad. Much better is “duag98” which contains the same word “fish” but typed one key to the left on a standard QWERTY keyboard. Another method is to use “Mhall” which is taken from the first characters of each word in the sentence “Mary had a little lamb.” This is easy to remember and type, but difficult to guess for someone who does not know it.
Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarised zone (DMZ).
Checklist:
- Try to scan your ports from the Internet using a tool such as nmap. MySQL uses port 3306 by default. This port should be inaccessible from untrusted hosts. Another simple way to check whether your MySQL port is open is to try the following command from some remote machine, where server_host is the hostname of your MySQL server:
```
shell> telnet server_host 3306
```
  If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If telnet just hangs or the connection is refused, everything is OK; the port is blocked.
Do not trust any data entered by your users. They can try to trick your code by entering special or escaped character sequences in web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like "; DROP DATABASE mysql;“. This is an extreme example, but large security leaks and data loss may occur as a result of hackers using similar techniques, if you do not prepare for them.
Also remember to check numeric data. A common mistake is to protect only strings. Sometimes people think that if a database contains only publicly available data that it need not be protected. This is incorrect. At least denial-of-service type attacks can be performed on such databases. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT * FROM table WHERE ID='234' rather than SELECT * FROM table WHERE ID=234. MySQL automatically converts this string to a number and strips all non-numeric symbols from it.
Checklist:
- All web applications:
  - Try to enter ' and " in all your web forms. If you get any kind of MySQL error, investigate the problem right away.
  - Try to modify any dynamic URLs by adding %22 (“), %23 (#), and %27 (') in the URL.
  - Try to modify datatypes in dynamic URLs from numeric ones to character ones containing characters from previous examples. Your application should be safe against this and similar attacks.
  - Try to enter characters, spaces, and special symbols instead of numbers in numeric fields. Your application should remove them before passing them to MySQL or your application should generate an error. Passing unchecked values to MySQL is very dangerous!
  - Check data sizes before passing them to MySQL.
  - Consider having your application connect to the database using a different username than the one you use for administrative purposes. Do not give your applications any more access privileges than they need.
- Users of PHP:
  - Check out the addslashes( ) function. As of PHP 4.0.3, a mysql_escape_string( ) function is available that is based on the function of the same name in the MySQL C API.
- Users of MySQL C API:
  - Check out the mysql_real_escape_string( ) API call.
- Users of MySQL++:
  - Check out the escape and quote modifiers for query streams.
- Users of Perl DBI:
  - Check out the quote( ) method or use placeholders.
- Users of Java JDBC:
  - Use a PreparedStatement object and placeholders.
Do not transmit plain (unencrypted) data over the Internet. Such data is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections as of Version 4.0.0. SSH port-forwarding can be used to create an encrypted (and compressed) tunnel for the communication.
Learn to use the tcpdump and strings utilities. For most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:
```
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings
```
(This works under Linux and should work with small modifications under other systems.) Warning: If you do not see data this doesn’t always actually mean that it is encrypted. If you need high security, you should consult with a security expert.

How to Make MySQL Secure Against Crackers

When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication.

All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL Version 3.22 and above) to make things much harder. To make things even more secure you should use ssh. You can find an open source ssh client at http://www.openssh.org/, and a commercial ssh client at http://www.ssh.com/. With this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL client.

If you are using MySQL 4.0, you can also use internal openssl support. See Section 4.3.8.

To make a MySQL system secure, you should strongly consider the following suggestions:

Use passwords for all MySQL users. Remember that anyone can log in as any other person as simply as mysql -u other_user db_name if other_user has no password. It is common behavior with client/server applications that the client may specify any username. You can change the password of all users by editing the mysql_install_db script before you run it, or only the password for the MySQL root user like this:
```
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->             WHERE user='root';
mysql> FLUSH PRIVILEGES;
```
Don’t run the MySQL daemon as the Unix root user. This is very dangerous because any user with the file privilege will be able to create files as root (for example, ~root/.bashrc). To prevent this, mysqld will refuse to run as root unless it is specified directly using a --user=root option.
mysqld can be run as an ordinary unprivileged user instead. You can also create a new Unix user mysql to make everything even more secure. If you run mysqld as another Unix user, you don’t need to change the root username in the user table, because MySQL usernames have nothing to do with Unix usernames. To start mysqld as another Unix user, add a user line that specifies the username to the [mysqld] group of the /etc/my.cnf option file or the my.cnf option file in the server’s data directory. For example:
```
[mysqld]
user=mysql
```
This will cause the server to start as the designated user whether you start it manually or by using safe_mysqld or mysql.server. For more details, see Section A.3.2.
Don’t support symlinks to tables (this can be disabled with the --skip-symlink option). This is especially important if you run mysqld as root, as anyone who has write access to the mysqld data directories could then delete any file in the system! See Section 5.6.1.2.
Check that the Unix user that mysqld runs as is the only user with read/write privileges in the database directories.
Don’t give the process privilege to all users. The output of mysqladmin processlist shows the text of the currently executing queries, so any user who is allowed to execute that command might be able to see if another user issues an UPDATE user SET password=PASSWORD('not_secure') query.
mysqld reserves an extra connection for users who have the process privilege so that a MySQL root user can log in and check things even if all normal connections are in use.
Don’t give the file privilege to all users. Any user who has this privilege can write a file anywhere in the filesystem with the privileges of the mysqld daemon! To make this a bit safer, all files generated with SELECT ... INTO OUTFILE are readable to everyone, and you cannot overwrite existing files.
The file privilege may also be used to read any file accessible to the Unix user that the server runs as. This could be abused, for example, by using LOAD DATA to load /etc/passwd into a table, which can then be read with SELECT.
If you don’t trust your DNS, you should use IP numbers instead of hostnames in the grant tables. In any case, you should be very careful about creating grant table entries using hostname values that contain wildcards!
If you want to restrict the number of connections for a single user, you can do this by setting the max_user_connections variable in mysqld.

Startup Options for mysqld Concerning Security

The following mysqld options affect security:

--local-infile[=(0|1)]

If one uses --local-infile=0, one can’t use LOAD DATA LOCAL INFILE.

--safe-show-database

With this option, SHOW DATABASES returns only those databases for which the user has some kind of privilege.

--safe-user-create

If this is enabled, a user can’t create new users with the GRANT command, if the user doesn’t have the insert privilege for the mysql.user table. If you want to give a user access to just create new users with those privileges that the user has a right to grant, you should give the user the following privilege:

mysql> GRANT INSERT(user) ON mysql.user TO 'user'@'hostname';

This will ensure that the user can’t change any privilege columns directly, but has to use the GRANT command to give privileges to other users.

--skip-grant-tables

This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)

--skip-name-resolve

Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.

--skip-networking

Don’t allow TCP/IP connections over the network. All connections to mysqld must be made via Unix sockets. This option is unsuitable for systems that use MIT-pthreads because the MIT-pthreads package doesn’t support Unix sockets.

--skip-show-database

With this option, the SHOW DATABASES statement doesn’t return anything.

Security Issues with LOAD DATA LOCAL

In MySQL 3.23.49 and MySQL 4.0.2, we added some new options to deal with possible security issues when it comes to LOAD DATA LOCAL.

There are two possible problems with supporting this command:

As the reading of the file is initiated from the server, one could theoretically create a patched MySQL server that could read any file on the client machine that the current user has read access to, when the client issues a query against the table.

In a web environment where the clients are connecting from a web server, a user could use LOAD DATA LOCAL to read any files that the web server process has read access to (assuming a user could run any command against the SQL server).

There are two separate fixes for this:

If you don’t configure MySQL with --enable-local-infile, LOAD DATA LOCAL will be disabled by all clients, unless mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) is called in the client. See Section 8.4.3.159.

For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the option --local-infile[=1], or disabled with --local-infile=0.

By default, all MySQL clients and libraries are compiled with --enable-local-infile, to be compatible with MySQL 3.23.48 and before.

One can disable all LOAD DATA LOCAL commands in the MySQL server by starting mysqld with --local-infile=0.

In the case that LOAD DATA INFILE is disabled in the server or the client, you will get the error message (1148):

The used command is not allowed with this MySQL version

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 do exactly the things that they are supposed to be allowed to do. 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 you can connect, the server checks each request 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 makes sure you have the select privilege for the table or the drop privilege for the database.

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

Table name	user	db	host
Scope fields	Host	Host	Host
	User	Db	Db
	Password	User
Privilege fields	Select_priv	Select_priv	Select_priv
	Insert_priv	Insert_priv	Insert_priv
	Update_priv	Update_priv	Update_priv
	Delete_priv	Delete_priv	Delete_priv
	Index_priv	Index_priv	Index_priv
	Alter_priv	Alter_priv	Alter_priv
	Create_priv	Create_priv	Create_priv
	Drop_priv	Drop_priv	Drop_priv
	Grant_priv	Grant_priv	Grant_priv
	References_priv
	Reload_priv
	Shutdown_priv
	Process_priv
	File_priv

For 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. The fields in these tables are shown here:

Table name	tables_priv	columns_priv
Scope fields	Host	Host
	Db	Db
	User	User
	Table_name	Table_name
		Column_name
Privilege fields	Table_priv	Column_priv
	Column_priv
Other fields	Timestamp	Timestamp
	Grantor

Each grant table contains scope fields and privilege fields.

Scope fields determine the scope of each entry 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 by bob from the host thomas.loc.gov. Similarly, a db table entry with Host, User, and Db fields 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 fields indicating tables or table/column combinations to which each entry applies.

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 Versions 3.22.12 or later.

Privilege fields 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 Section 4.2.10.

Scope fields are strings, declared as shown here; the default value for each is the empty string:

Field name	Type	Notes
Host	CHAR(60)
User	CHAR(16)
Password	CHAR(16)
Db	CHAR(64)	(CHAR(60) for the tables_priv and columns_priv tables)
Table_name	CHAR(60)
Column_name	CHAR(60)

In the user, db, and host tables, all privilege fields are declared as ENUM('N','Y')—each can have a value of 'N' or 'Y', and the default value is 'N'.

In the tables_priv and columns_priv tables, the privilege fields are declared as SET fields:

Table name	Field name	Possible set elements
tables_priv	Table_priv	'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
tables_priv	Column_priv	'Select', 'Insert', 'Update', 'References'
columns_priv	Column_priv	'Select', 'Insert', 'Update', 'References'

Briefly, the server uses the grant tables like this:

The user table scope fields determine whether to allow or reject 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 and host tables are used together:
- The db table scope fields determine which users can access which databases from which hosts. The privilege fields determine which operations are allowed.
- The host table is used as an extension of 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 Section 4.2.10.
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.

Note that administrative privileges (reload, shutdown, etc.) 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 such privileges in the other grant tables. In fact, only the user table need be consulted to determine whether you can perform an administrative operation.

The file privilege is specified only in the user table, too. 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 once, when it starts up. Changes to the grant tables take effect as indicated in Section 4.3.3.

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. For help in diagnosing problems, see Section 4.2.11. For advice on security issues, see Section 4.2.2.

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-level privileges.

Privileges Provided by MySQL

Information about user privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables when it starts up and under the circumstances indicated in Section 4.3.3.

The names used in this manual to refer to the privileges provided by MySQL are shown here, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies:

Privilege	Column	Context
select	Select_priv	tables
insert	Insert_priv	tables
update	Update_priv	tables
delete	Delete_priv	tables
index	Index_priv	tables
alter	Alter_priv	tables
create	Create_priv	databases, tables, or indexes
drop	Drop_priv	databases or tables
grant	Grant_priv	databases or tables
references	References_priv	databases or tables
reload	Reload_priv	server administration
shutdown	Shutdown_priv	server administration
process	Process_priv	server administration
file	File_priv	file access on server

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. You can execute certain SELECT statements even without permission to access any of the databases on the server. For example, you could use the mysql client as a simple calculator:

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

The index privilege allows you to create or drop (remove) indexes.

The alter privilege allows you to use ALTER TABLE.

The create and drop privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables.

Note that 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 grant privilege allows you to give to other users those privileges you possess.

The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is granted can read or write any file that the MySQL server can read or write.

The remaining privileges are used for administrative operations, which are performed using the mysqladmin program. The following table shows which mysqladmin commands each administrative privilege allows you to execute:

Privilege	Commands permitted to privilege holders
reload	reload, refresh, flush-privileges, flush-hosts, flush-logs, and flush-tables
shutdown	shutdown
process	processlist, kill

The reload command tells the server to re-read the grant tables. The refresh command flushes all tables and opens and closes the log files. flush-privileges is a synonym for reload. The other flush-* commands perform functions similar to refresh but are more limited in scope, 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.

The processlist command displays information about the threads executing within the server. The kill command kills server threads. You can always display or kill your own threads, but you need the process privilege to display or kill threads initiated by other users. See Section 4.5.5.

It is a good idea in general to grant privileges only to those users who need them, but you should exercise particular caution in granting certain privileges:

The grant privilege allows users to give away 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 any world-readable file on the server into a database table, the contents of which can then be accessed using SELECT. This includes the contents of all databases hosted by the server!
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.
Privileges on the mysql database 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.) If they can access the mysql.user password column, they can use it to log into the MySQL server for the given user. (With sufficient privileges, the same user can replace a password with a different one.)

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 require that you specify connection parameters when you want to access a MySQL server: the host you want to connect to, your username, and your password. For example, the mysql client can be started like this (optional arguments are enclosed between [ and ]):

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.

Note: Specifying a password on the command-line is not secure! Any user on your system may then find out your password by typing a command like: ps auxww. See Section 4.1.2.

mysql uses default values for connection parameters that are missing from the command-line:

The default hostname is localhost.
The default username is your Unix login name.
No password is supplied if -p is missing.

Thus, for a Unix user joe, 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.

On Unix systems, 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 the .my.cnf configuration file in your home directory. The relevant section of the file might look like this:
```
[client]
host=host_name
user=user_name
password=your_pass
```
See Section 4.1.2.
You can specify 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 only). The password can be specified using MYSQL_PWD (but this is insecure; see the next section). See Appendix E.

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 host from which you connect
Your MySQL username

Identity checking is performed using the three user table scope fields (Host, User, and Password). The server accepts the connection only if a user table entry matches your hostname and user name, and you supply the correct password.

Values in the user table scope fields 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 the Host field.
A Host value of '%' matches any hostname.
A blank Host value means that the privilege should be anded with the entry in the host table that matches the given hostname. You can find more information about this in the next chapter.
As of MySQL Version 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 will allow everyone to connect from an IP where the following is true:
```
user_ip & netmask = host_ip.
```
In the preceding example all IP:s in the interval 192.58.197.0 - 192.58.197.255 can connect to the MySQL server.
Wildcard characters are not allowed in the User field, 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 the anonymous user (the user with no name), rather than 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 field can be blank. This does not mean that any password matches. It means the user must connect without specifying a password.

Non-blank Password values 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 is then used when the client/server is checking if the password is correct. (This is done without the encrypted password ever traveling over the connection.) Note that 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 normal users read access to the tables in the mysql database!

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

Host value	User value	Connections 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, etc. (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

Because you can use IP wildcard values in the Host field (for example, '144.155.166.%' to match every host on a subnet), there is the possibility that someone might 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 hostnamed something like 1.2.foo.com, its name will never match the Host column of the grant tables. Only an IP number can match an IP wildcard value.

An incoming connection may be matched by more than one entry in the user table. For example, a connection from thomas.loc.gov by fred would be matched by several of the entries shown in the preceding table. How does the server choose which entry to use if more than one matches? The server resolves this question by sorting the user table after reading it at startup time, then looking through the entries in sorted order when a user attempts to connect. The first matching entry is the one that is used.

user table sorting works as follows. Suppose 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 ('%' in the Host column 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). The resulting sorted user table looks like this:

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

When a connection is attempted, the server looks through the sorted entries and uses the first match found. For a connection from localhost by jeffrey, the entries with 'localhost' in the Host column match first. Of those, the entry with the blank username matches both the connecting hostname and username. (The '%'/'jeffrey' entry would have matched, too, but it is not the first match in the table.)

Here is another example. Suppose 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 from thomas.loc.gov by jeffrey is matched by the first entry, whereas a connection from whitehouse.gov by jeffrey is matched by the second.

A common misconception is 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 field value, but by the entry with no username!

If you have problems connecting to the server, 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. For each request that comes in on the connection, the server checks whether you have sufficient privileges to perform it, based on the type of operation you wish to perform. This is where the privilege fields in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, or columns_priv tables. The grant tables are manipulated with GRANT and REVOKE commands. See Section 4.3.1. (You may find it helpful to refer to Section 4.2.6, which lists the fields 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 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 server or database administrators. For other users, you should leave the privileges in the user table set to 'N' and grant privileges on a database-specific basis only, using the db and host tables.

The db and host tables grant database-specific privileges. Values in the scope fields may be specified as follows:

The wildcard characters % and _ can be used in the Host and Db fields of either table.
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 '%' 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 db and host tables are read in and sorted when the server starts up (at the same time that it reads the user table). The db table is sorted on the Host, Db, and User scope fields, and the host table is sorted on the Host and Db scope fields. 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- and column-specific privileges. Values in the scope fields may be specified as follows:

The wildcard characters % and _ can be used in the Host field of either table.
A '%' or blank Host value in either table means “any host.”
The Db, Table_name, and Column_name fields cannot contain wildcards or be blank in either table.

The tables_priv and columns_priv tables are sorted on the Host, Db, and User fields. This is similar to db table sorting, although the sorting is simpler because only the Host field may contain wildcards.

The request verification process is described next. (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 administrative requests (shutdown, reload, etc.), 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, access is denied 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, etc.), 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:

The server looks in the db table for a match on the Host, Db, and User fields. The Host and User fields are matched to the connecting user’s hostname and MySQL username. The Db field is matched to the database the user wants to access. If there is no entry for the Host and User, access is denied.
If there is a matching db table entry and its Host field is not blank, that entry defines the user’s database-specific privileges.
If the matching db table entry’s Host field 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 fields. 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 checks the user’s table and column privileges in the tables_priv and columns_priv tables and adds those to the user’s privileges. Access is allowed or denied based on the result.

Expressed in boolean terms, the preceding description of how a user’s privileges are calculated may be summarised 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-specific privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT ... SELECT statement, you need both insert and 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 can be used to maintain a list of secure servers.

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 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, using mysqlaccess) to make sure your access privileges are actually set up the way you think they are.

Causes of Access denied Errors

If you encounter Access denied errors when you try to connect to the MySQL server, the following list indicates some courses of action you can take to correct the problem:

After installing MySQL, did you run the mysql_install_db script to set up the initial grant table contents? If not, do so. See Section 4.3.4. Test the initial privileges by executing this command:
```
shell> mysql -u root test
```
The server should let you connect without error. You should also make sure you have a file user.MYD in the MySQL database directory. Ordinarily, this is PATH/var/mysql/user.MYD, where PATH is the pathname to the MySQL installation root.
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 root password is something you should do while you’re setting up your other MySQL users.
If you try to connect as root and get this error:
```
Access denied for user: '@unknown' to database mysql
```
this 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. 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.
If you get an error like the following:
```
shell> mysqladmin -u root -pxxxx ver
Access denied for user: 'root@localhost' (Using password: YES)
```
it means that you are using a wrong password. See Section 4.3.6.
If you have forgotten the root password, you can restart mysqld with --skip-grant-tables to change the password. You can find more about this option later on in this section.
If you get the preceding error even if you haven’t specified a password, you entered a wrong password in some my.ini file. See Section 4.1.2. You can avoid using option files with the --no-defaults option, as follows:
```
shell> mysqladmin --no-defaults -u root ver
```
If you updated an existing MySQL installation from a version earlier than Version 3.22.11 to Version 3.22.11 or later, did you run the mysql_fix_privilege_tables script? If not, do so. The structure of the grant tables changed with MySQL Version 3.22.11 when the GRANT statement became functional.
If your privileges seem to have changed in the middle of a session, it may be that a superuser has changed them. Reloading the grant tables affects new client connections, but it also affects existing connections as indicated in Section 4.3.3.
If you can’t get your password to work, remember that you must use the PASSWORD( ) function if you set the password with the INSERT, UPDATE, or SET PASSWORD statements. The PASSWORD( ) function is unnecessary if you specify the password using the GRANT ... INDENTIFIED BY statement or the mysqladmin password command. See Section 4.3.6.
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 do not work if you are running on a system that uses MIT-pthreads (localhost connections are made using Unix sockets, which are not supported by MIT-pthreads). To avoid this problem on such systems, you should use the --host option to name the server host explicitly. This will make a TCP/IP connection to the mysqld server. In this case, you must have your real hostname in user table entries on the server host. (This is true even if you are running a 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 db_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 host from which you are trying to connect, and whether 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, this means that you tried to login without a password.
If you get the following error connecting from a different host than the one on which the MySQL server is running, there is no row in the user table matching that host:
```
Host ... is not allowed to connect to this MySQL server
```
You can fix this by using the command-line tool mysql (on the server host!) to add a row to the user, db, or host table for the user/hostname combination from which you are trying to connect and then execute mysqladmin flush-privileges. If you are not running MySQL Version 3.22 and 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 replace the '%' in the user table entry with the actual hostname that shows up in the log. Otherwise, you’ll have a system that is insecure.)
Another reason for this error on Linux is that you are using a binary MySQL version that is compiled with a different glibc version than the one you are using. In this case you should either upgrade your OS/glibc or download the source MySQL version and compile this yourself. A source RPM is normally trivial to compile and install, so this isn’t a big problem.
If you get an error message where the hostname is not shown or where the hostname is an IP, even if you try to connect with a hostname:
```
shell> mysqladmin -u root -pxxxx -h some-hostname ver
Access denied for user: 'root@' (Using password: YES)
```
this means that MySQL got some error when trying to resolve the IP to a hostname. In this case you can execute mysqladmin flush-hosts to reset the internal DNS cache. See Section 5.5.5.
Some permanent solutions are:
- Try to find out what is wrong with your DNS server and fix this.
- Specify IPs instead of hostnames in the MySQL privilege tables.
- Start mysqld with --skip-name-resolve.
- Start mysqld with --skip-host-cache.
- Connect to localhost if you are running the server and the client on the same machine.
- Put the client machine names in /etc/hosts.
If mysql -u root test works but mysql -h your_hostname -u root test results in Access denied, 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 doesn’t work, you don’t have an entry for other_db_name listed in the db table.
If mysql -u user_name db_name works when executed on the server machine, but mysql -u host_name -u user_name db_name doesn’t work when executed on another client machine, you don’t have the client machine listed in the user table or the db table.
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 remove the entry with Host='localhost' and User=".
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 there are one or more corresponding entries in the host table specifying which hosts the db table entry applies to.
If you get the error when using the SQL commands SELECT ... INTO OUTFILE or LOAD DATA INFILE, your entry in the user table probably doesn’t have the file privilege enabled.
Remember that client programs will use connection parameters specified in configuration files or environment variables. See Appendix E. If a client seems to be sending the wrong default connection parameters when you don’t specify them on the command-line, check your environment and the .my.cnf file in your home directory. You might also check the system-wide MySQL configuration files, though it is far less likely that client connection parameters will be specified there. See Section 4.1.2. If you get Access denied when you run a client without any options, make sure you haven’t specified an old password in any of your option files! See Section 4.1.2.
If you make changes to the grant tables directly (using an INSERT or UPDATE statement) and your changes seem to be ignored, remember that you must issue a FLUSH PRIVILEGES statement or execute 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 set the root password with an UPDATE command, you won’t need to specify it until after you flush the privileges because the server won’t know you’ve changed the password yet!
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, there is a problem with your program and not with the access privileges. (Note that 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 daemon 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. Note: 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 bringing it down and restarting it.
If everything else fails, start the mysqld daemon 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 Section D.1.2.
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 Section 1.6.2.3. In some cases you may need to restart mysqld with --skip-grant-tables to run mysqldump.

Get MySQL Reference Manual now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

MySQL Reference Manual by Michael Widenius, David Axmark, Kaj Arno