6 The MySQL access privilege system

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

6.1 General security

Anyone using MySQL on a computer connected to the Internet should read this chapter to avoid mistakes people do.

Under "security" we mean that our site, not only MySQL is protected 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.

There are some security logic in MySQL: Access control lists (ACL-s) and SSL encrypted connections but much more is depending on overall usage of MySQL. Also most of this chapter isn't MySQL dependant at all. Same rules apply for most applications.

When you running a site, designing software or just doing something with MySQL then try to follow these rules:

• Try to understand MySQL ACL system. The GRANT/REVOKE commands are for restricting access to MySQL. Do not grant anyone for more than is must. Never grant all hosts to do something. Checklist:
  • Do mysql -u root. If you granted a connection without asking password, then this is bad.
  • Use command SHOW GRANTS and check who is having access and to what.
• Do not keep any plain passwords in tables when possible. When your computer gets compromised, intruder can take full list of passwords and use them in somewhere else place. Instead use MD5() or other one-way hashing function.
• Do not use passwords from dictionaries. There are special programs to break them. Even passwords like: "xfish98" are very bad. Much better is "duag" which is same word "fish" but typed one key left on keyboard. Another method is to use "Mhall" which is taken from first characters of sentence "Mary had a little lamb". Easy to type when you know the system but hard to guess from side.
• Invest in firewall. This protects for at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in demilitarized zone (DMZ). Checklist:
  • Try to scan your ports from Internet. MySQL uses port 3306 by default. This port should be unaccessible for most cases.
• Do not trust any data entered from user. Users can enter special symbols from web-forms, URL-s or your custom application. Are you sure that your application remains secure if user enters something like ; DROP ALL DATABASES ; into form? Checklist:
  • All WWW applications:
    • Try to enter ' and " in all forms on your WWW. If you get any kind of MySQL error, better take your site down.
    • Try to modify any types of dynamic URL-s by adding %22 ("), %23 (#) and %27 (') in your URL.
    • Try to modify datatypes in dynamic URL-s from numeric ones to character ones containing characters from previous example. Your application should be safe against this.
    • Try to enter characters, spaces, special symbols instead of numbers in numeric fields. Application should remove them before passing to MySQL or your application should generate error. Passing wrong things to MySQL is dangerous!
    • Check data sizes before passing them to MySQL.
  • Users of PHP3:
    • Check out the addslashes() function
  • Users of MySQL C API:
    • Check out the mysql_escape() API call.
  • Users of MySQL++:
    • Check out the escape and quote modifiers (?) for query streams.
• Do not transmit plain data over the net. This data is accessible to everyone who have interest to trap this information and reuse it somewhere. If you really need this, use encrypted communications like SSL. MySQL supports internal SSL connections beginning from version 3.23.9.
• Learn to use utilities "tcpdump" and "strings". For most cases you can see unencrypted MySQL data streams issuing command:

  tcpdump -l -i eth0 -w - src or dst port 3306 | strings
  

  (This works under linux and should work with small modifications under another systems). Warning: If you do not see data this doesn't actually always mean that it is encrypted. If you need high security you should consult with security expert.

6.2 How to make MySQL secure against crackers

When you connect to a MySQL server, you should normally use a password. The password is not transmitted in clear text over the connection.

All other information is transferred as text that can be read by anyone that is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL 3.22 and above) to make things much harder. To make things even more secure you should install ssh (see http://www.cs.hut.fi/ssh). With this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL client.

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 user name. 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. mysqld can be run as any user. 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 user name in the user table, because MySQL user names have nothing to do with Unix user names. You can edit the mysql.server script to start mysqld as another Unix user. Normally this is done with the su command. For more details, see section 19.8 How to run MySQL as a normal user.
• If you put a password for the Unix root user in the mysql.server script, make sure this script is readable only by root.
• 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 saves 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 that has this privilege can write a file anywhere in the file system 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 can't 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 principle, the --secure option to mysqld should make hostnames safe. In any case, you should be very careful about using hostname values that contain wildcards!

The following mysqld options affect security:

--secure

IP numbers returned by the gethostbyname() system call are checked to make sure they resolve back to the original hostname. This makes it harder for someone on the outside to get access by simulating another host. This option also adds some sanity checks of hostnames. The option is turned off by default in MySQL 3.21 since it sometimes takes a long time to perform backward resolutions. MySQL 3.22 caches hostnames and has this option enabled by default.

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

6.3 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 select, insert, update and delete privileges on a database.

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.

6.4 MySQL user names and passwords

There are several distinctions between the way user names and passwords are used by MySQL, and the way they are used by Unix or Windows:

• User names, as used by MySQL for authentication purposes, have nothing to do with Unix user names (login names) or Windows user names. Most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only. Client programs allow a different name to be specified with the -u or --user options. This means that you can't make a database secure in any way unless all MySQL user names have passwords. Anyone may attempt to connect to the server using any name, and they will succeed if they specify any name that doesn't have a password.
• MySQL user names can be up to 16 characters long; Unix user names typically are limited to 8 characters.
• MySQL passwords have nothing to do with Unix passwords. There is no necessary connection between the password you use to log in to a Unix machine and the password you use to access a database on that machine.
• MySQL encrypts passwords using a different algorithm than the one used during the Unix login process. See the descriptions of the PASSWORD() and ENCRYPT() functions in section 7.4.12 Miscellaneous functions.

6.5 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 user name 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: Specifing 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.15.4 Option files.

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

• The default hostname is localhost.
• The default user name 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.15.4 Option files.
• You can specify connection parameters using environment values. The host can be specified using MYSQL_HOST. The MySQL user name can be specified using USER (this is for Windows only). The password can be specified using MYSQL_PWD (but this is insecure; see next section).

If connection parameters are specified in multiple ways, values specified on the command line take precedence over values specified in configuration files and environment variables, and values in configuration files take precedence over values in environment variables.

6.6 Keeping your password secure

It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed below, along with an assessment of the risks of each method:

• Use a -pyour_pass or --password=your_pass option on the command line. This is convenient but insecure, since your password becomes visible to system status programs (such as ps) that may be invoked by other users to display command lines. (MySQL clients typically overwrite the command line argument with zeroes during their initialization sequence, but there is still a brief interval during which the value is visible.)
• Use a -p or --password option (with no your_pass value specified). In this case, the client program solicits the password from the terminal:

  shell> mysql -u user_name -p
  Enter password: ********
  

  The client echoes `*' characters to the terminal as you enter your password so that onlookers cannot see it. It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs non-interactively, there is no opportunity to enter the password from the terminal.
• Store your password in a configuration file. For example, you can list your password in the [client] section of the `.my.cnf' file in your home directory:

  [client]
  password=your_pass
  

  If you store your password in `.my.cnf', the file should not be group or world readable or writable. Make sure the file's access mode is 400 or 600. See section 4.15.4 Option files.
• You can store your password in the MYSQL_PWD environment variable, but this method must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes; your password will be in plain sight for all to see if you set MYSQL_PWD. Even on systems without such a version of ps, it is unwise to assume there is no other method to observe process environments.

All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly-protected `.my.cnf' file.

6.7 Privileges provided by MySQL

Privilege information 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 6.11 When privilege changes take effect.

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

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 yourself 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 table below shows which mysqladmin commands each administrative privilege allows you to execute:

The reload command tells the server to reread 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.

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.
• 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. However, with sufficient privileges, that 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.

6.8 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 user name you specify. The system grants privileges according to your identity and what you want to do.

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

MySQL access control involves two stages:

• Stage 1: The server checks whether or not you are even allowed to connect.
• Stage 2: Assuming you can connect, the server checks each request you issue to see whether or not 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 below:

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 below:

Each grant table contains scope fields and privilege fields.

Scope fields determine the scope of each entry in the tables, i.e., 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 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 6.10 Access control, stage 2: Request verification.

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

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:

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, the privilege fields indicate the user's global (superuser) privileges.
• 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 6.10 Access control, stage 2: Request verification.
• 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 level 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 or not 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 6.11 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. For help in diagnosing problems, see section 6.15 Causes of Access denied errors. For advice on security issues, section 6.2 How to make MySQL secure against crackers.

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.

6.9 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 or not 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 user name

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 is equivalent to '%'. Note that these values match any host that can create a connection to your 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 user name, 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 user name 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) and compared to the already-encrypted version stored in the user table. If they match, the password is correct.

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

Since you can use IP wildcard values in the Host field (e.g., '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 host named 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 just shown above. 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 user name matches both the connecting hostname and user name. (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 user name, 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 user name!

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.

6.10 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 7.26 GRANT and REVOKE syntax. (You may find it helpful to refer to section 6.8 How the privilege system works, 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 t