MySQL Reference Manual

MySQL User Account Management

GRANT and REVOKE Syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY 'password'] ...]
    [REQUIRE
            [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR=#]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the GRANT statement does nothing.

The GRANT and REVOKE commands allow system administrators to create users and grant and revoke rights to MySQL users at four privilege levels:

Global level

Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table.

Database level

Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables.

Table level

Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table.

Column level

Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table.

If you give a grant for a user that doesn’t exist, that user is created. For examples of how GRANT works, see Section 4.3.5.

For the GRANT and REVOKE statements, priv_type may be specified as any of the following:

ALL PRIVILEGES      FILE                RELOAD
ALTER               INDEX               SELECT
CREATE              INSERT              SHUTDOWN
DELETE              PROCESS             UPDATE
DROP                REFERENCES          USAGE

ALL is a synonym for ALL PRIVILEGES. REFERENCES is not yet implemented. USAGE is currently a synonym for “no privileges.” It can be used when you want to create a user that has no privileges.

To revoke the grant privilege from a user, use a priv_type value of GRANT OPTION:

mysql> REVOKE GRANT OPTION ON ... FROM ...;

The only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, INDEX, and ALTER.

The only priv_type values you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.

You can set global privileges by using ON *.* syntax. You can set database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set the privileges for that database. (Warning: if you specify ON * and you don’t have a current database, you will affect the global privileges!)

In order to accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user_name value in the form user@host. If you want to specify a user string containing special characters (such as -), or a host string containing special characters or wildcard characters (such as %), you can quote the user or hostname (for example, 'test-user'@'test-hostname').

You can specify wildcards in the hostname. For example, user@"%.loc.gov" applies to user for any host in the loc.gov domain, and user@"144.155.166.%" applies to user for any host in the 144.155.166 class C subnet.

The simple form user is a synonym for user@"%".

MySQL doesn’t support wildcards in usernames. Anonymous users are defined by inserting entries with User=" into the mysql.user table or creating an user with an empty name with the GRANT command.

Note: if you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user@localhost because otherwise the anonymous user entry for the local host in the mysql.user table will be used when the user tries to log into the MySQL server from the local machine!

You can verify if this applies to you by executing this query:

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

For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A username can be up to 16 characters.

The privileges for a table or column are formed from the logical OR of the privileges at each of the four privilege levels. For example, if the mysql.user table specifies that a user has a global select privilege, this can’t be denied by an entry at the database, table, or column level.

The privileges for a column can be calculated as follows:

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

In most cases, you grant rights to a user at only one of the privilege levels, so life isn’t normally as complicated as the previous list would indicate. The details of the privilege-checking procedure are presented in Section 4.2.

If you grant privileges for a user/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE command. In other words, GRANT may create user table entries, but REVOKE will not remove them; you must do that explicitly using DELETE.

In MySQL Version 3.22.12 or later, if a new user is created or if you have global grant privileges, the user’s password will be set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.

If you don’t want to send the password in clear text you can use the PASSWORD option followed by a scrambled password from SQL function PASSWORD( ) or the C API function make_scrambled_password(char *to, const char *password).

Warning: if you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is insecure.

Passwords can also be set with the SET PASSWORD command. See Section 5.5.6.

If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted.

If a user doesn’t have any privileges on a table, the table is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement).

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the grant privilege, as two users with different privileges may be able to join privileges!

MAX_QUERIES_PER_HOUR=# limits the number of queries the user can make during one hour. If # is 0, this means that there is no limit of the number of queries. This works by MySQL resetting a user-specific query counter to 0, after it has gone more than one hour since the counter started incrementing.

You cannot grant another user a privilege you don’t have yourself; the grant privilege allows you to give away only those privileges you possess.

Be aware that when you grant a user the grant privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. Suppose you grant a user the insert privilege on a database. If you then grant the select privilege on the database and specify WITH GRANT OPTION, the user can give away not only the select privilege, but also insert. If you then grant the update privilege to the user on the database, the user can give away the insert, select, and update.

You should not grant alter privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!

Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this will slow down MySQL a bit.

When mysqld starts, all privileges are read into memory. Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. See Section 4.3.3.

The biggest differences between the ANSI SQL and MySQL versions of GRANT are:

• In MySQL privileges are given for a username + hostname combination and not only for a username.

• ANSI SQL doesn’t have global or database-level privileges, and ANSI SQL doesn’t support all privilege types that MySQL supports. MySQL doesn’t support the ANSI SQL TRIGGER, EXECUTE, or UNDER privileges.

• ANSI SQL privileges are structured in a hierarchical manner. If you remove a user, all privileges the user has granted are revoked. In MySQL the granted privileges are not automatically revoked. You have to revoke these yourself if needed.

• In MySQL, if you have the insert privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don’t have the insert privilege will be set to their default values. ANSI SQL requires you to have the insert privilege on all columns.

• When you drop a table in ANSI SQL, all privileges for the table are revoked. If you revoke a privilege in ANSI SQL, all privileges that were granted based on this privilege are also revoked. In MySQL, privileges can be dropped only with explicit REVOKE commands or by manipulating the MySQL grant tables.

For a description of using REQUIRE, see Section 4.3.8.

MySQL Usernames and Passwords

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

• Usernames, as used by MySQL for authentication purposes, have nothing to do with Unix usernames (login names) or Windows usernames. Most MySQL clients by default try to log in using the current Unix user name as the MySQL username, 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 usernames 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 usernames can be up to 16 characters long; Unix usernames 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 6.3.6.2. Note that even if the password is stored ’scrambled', and knowing your ’scrambled’ password is enough to be able to connect to the MySQL server!

MySQL users and their privileges are normally created with the GRANT command. See Section 4.3.1.

When you log in to a MySQL server with a command-line client you should specify the password with --password=your-password. See Section 4.2.8.

mysql --user=monty --password=guess database_name

If you want the client to prompt for a password, you should use --password without any argument:

mysql --user=monty --password database_name

or the short form:

mysql -u monty -p database_name

Note that in the last example the password is not ‘database_name’.

If you want to use the -p option to supply a password you should do so like this:

mysql -u monty -pguess database_name

On some systems, the library call that MySQL uses to prompt for a password will automatically cut the password to 8 characters. Internally MySQL doesn’t have any limit for the length of the password.

When Privilege Changes Take Effect

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

Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD are noticed by the server immediately.

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise, your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don’t seem to make any difference!

When the server notices that the grant tables have been changed, existing client connections are affected as follows:

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

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

• Global privilege changes and password changes take effect the next time the client connects.

Setting Up the Initial MySQL Privileges

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. See Section 2.3.1. The mysql_install_db script starts up the mysqld server, then initialises the grant tables to contain the following set of privileges:

• The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host.

  Note: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges.

• An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user.

• Other privileges are denied. For example, normal users can’t use mysqladmin shutdown or mysqladmin processlist.

Note: the default privileges are different for Windows. See Section 2.6.2.3.

Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD( ) function):

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

If you know what you are doing, you can also directly manipulate the privilege tables:

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->     WHERE user='root';
mysql> FLUSH PRIVILEGES;

Another way to set the password is by using the mysqladmin command:

shell> mysqladmin -u root password new_password

Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the preceding commands or with SET PASSWORD=PASSWORD('new password').

Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES) because the change will go unnoticed otherwise.

Once the root password has been set, thereafter you must supply that password when you connect to the server as root.

You may wish to leave the root password blank so that you don’t need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.

See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.

If you want the initial privileges to be different than those just described, you can modify mysql_install_db before you run it.

To re-create the grant tables completely, remove all the .frm, .MYI, and .MYD files in the directory containing the mysql database. (This is the directory named mysql under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.

Note: for MySQL versions older than Version 3.22.10, you should not delete the .frm files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.

Adding New Users to MySQL

You can add users in two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements because they are more concise and less error-prone. See Section 4.3.1.

There are also a lot of contributed programs like phpmyadmin that can be used to create and administrate users. See Section 1.6.1.

The following examples show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the following mysql commands.

You can add new users by issuing GRANT statements:

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

These GRANT statements set up three new users:

monty

A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don’t add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host because it has a more specific Host field value and thus comes earlier in the user table sort order.

admin

A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist. No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)

dummy

A user who can connect without a password, but only from the local host. The global privileges are all set to 'N'—the usage privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later.

You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:

shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
    ->          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
    ->          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
    ->           Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
    ->                  VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

Depending on your MySQL version, you may have to use a different number of 'Y' values (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.

The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.

The following example adds a user custom who can connect from hosts localhost, server.domain, and whitehouse.gov. He wants to access the bankaccount database only from localhost, the expenses database only from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

To set up this user’s privileges using GRANT statements, run these commands:

shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO custom@localhost
    ->     IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO [e-mail]
    ->     IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO custom@'%'
    ->     IDENTIFIED BY 'stupid';

We do this to grant statements for the user ‘custom’ because we want the give the user access to MySQL both from the local machine with Unix sockets and from the remote machine ‘whitehouse.gov’ over TCP/IP.

To set up the user’s privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):

shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES
    -> ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES
    -> ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:

mysql> GRANT ...
    ->     ON *.*
    ->     TO myusername@"%.mydomainname.com"
    ->     IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
    ->             PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;

You can also use xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the grant tables. You can find these utilities in the Contrib directory of the MySQL web site (http://www.mysql.com/Downloads/Contrib/).

Setting Up Passwords

In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users. See Section 4.3.1.

The examples in the preceding sections illustrate an important principle: when you store a non-empty password using INSERT or UPDATE statements, you must use the PASSWORD( ) function to encrypt it. This is because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to attempt to set passwords like this:

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;

The result is that the plaintext value 'biscuit' is stored as the password in the user table. When the user jeffrey attempts to connect to the server using this password, the mysql client encrypts it with PASSWORD( ), generates an authentication vector based on the encrypted password and a random number, obtained from the server, and sends the result to the server. The server uses the password value in the user table (that is not the encrypted value 'biscuit') to perform the same calculations, and compares results. The comparison fails and the server rejects the connection:

shell> mysql -u jeffrey -pbiscuit test
Access denied

Passwords must be encrypted when they are inserted in the user table, so the INSERT statement should have been specified like this instead:

mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey',PASSWORD('biscuit'));

You must also use the PASSWORD( ) function when you use SET PASSWORD statements:

mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');

If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD( ) function is unnecessary. They both take care of encrypting the password for you, so you would specify a password of 'biscuit' like this:

mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';

or

shell> mysqladmin -u jeffrey password biscuit

Note: PASSWORD( ) does not perform password encryption in the same way that Unix passwords are encrypted. You should not assume that if your Unix password and your MySQL password are the same, that PASSWORD( ) will result in the same encrypted value as is stored in the Unix password file. See Section 4.3.2.

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 next, along with an assessment of the risks of each method:

• Never give a normal user access to the mysql.user table. Knowing the encrypted password for a user makes it possible to log in as this user. The passwords are only scrambled so that one shouldn’t be able to see the real password you used (if you happen to use a similar password with your other applications).

• Use a -pyour_pass or --password=your_pass option on the command line. This is convenient but insecure because 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 initialisation 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 * characters represent your password.

  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. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password!

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

  Section 4.1.2.

• 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. See Appendix E.

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.

Using Secure Connections

Basics

MySQL has support for SSL-encrypted connections. To understand how MySQL uses SSL, we need to explain some basics about SSL and X509. People who are already aware of it can skip this part.

By default, MySQL uses unencrypted connections between client and server. This means that someone could watch all your traffic and look at the data being sent/received. Actually, they could even change the data while it is in transit between client and server. Sometimes you need to move really secret data over public networks and in such a case using an unencrypted connection is unacceptable.

SSL is a protocol which uses different encryption algorithms to ensure that data which comes from public networks can be trusted. It has mechanisms to detect any change, loss, or replay of data. SSL also incorporates algorithms to recognise and provide identity verification using the X509 standard.

Encryption is the way to make any kind of data unreadable. In fact, today’s practice requires many additional security elements from encryption algorithms. They should resist many kinds of known attacks like just messing with the order of encrypted messages or replaying data twice.

X509 is a standard that makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. In basic terms, there should be some company called “Certificate Authority” which assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys—public and secret. A certificate owner can prove his identity by showing his certificate to another party. A certificate consists of his owner’s public key. Any data encrypted with this public key can only be decrypted using the corresponding secret key, which is held by the owner of the certificate.

MySQL doesn’t use encrypted connections by default because this would make the client/server protocol much slower. Any kind of additional functionality requires the computer to do additional work, and encrypting data is a CPU-intensive operation that requires time and can delay MySQL main tasks. By default MySQL is tuned to be fast as possible.

If you need more information about SSL/X509/encryption, you should use your favourite Internet search engine and search for keywords you are interested in.

Requirements

To get secure connections to work with MySQL you must do the following:

1. Install the openssl library. We have tested MySQL with openssl 0.9.6., which you can download from http://www.openssl.org/.

2. Configure MySQL with --with-vio --with-openssl.

3. If you are using an old MySQL installation, you have to update your mysql.user table with some new columns. You can do this by running the mysql_fix_privilege_tables.sh script.

4. You can check if a running mysqld server supports openssl by examining if SHOW VARIABLES LIKE 'have_openssl' returns YES.

GRANT options

MySQL can check X509 certificate attributes in addition to the normal username/password scheme. All the usual options are still required (username, password, IP address mask, database/table name).

There are different possibilities to limit connections:

• Without any SSL/X509 options, all kinds of encrypted/unencrypted connections are allowed if the username and password are valid.

• REQUIRE SSL option limits the server to allow only SSL-encrypted connections. Note that this option can be omitted if there are any ACL records that allow non-SSL connections.

  mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
      -> IDENTIFIED BY "goodsecret" REQUIRE SSL;

• REQUIRE X509 means that the client should have a valid certificate but we do not care about the exact certificate, issuer, or subject. The only restriction is that it should be possible to verify its signature with one of the CA certificates.

  mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
      -> IDENTIFIED BY "goodsecret" REQUIRE X509;

• REQUIRE ISSUER issuer makes the connection more restrictive: now the client must present a valid X509 certificate issued by CA “issuer”. Using X509 certificates always implies encryption, so the option “SSL” is not neccessary anymore.

  mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
      -> IDENTIFIED BY "goodsecret"
      -> REQUIRE ISSUER "C=FI, ST=Some-State, L=Helsinki,
      "> O=MySQL Finland AB, CN=Tonu Samuel/Email=[e-mail]";

• REQUIRE SUBJECT subject requires clients to have a valid X509 certificate with the subject “subject” on it. If the client has a valid certificate but a different “subject,” the connection is still not allowed.

  mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
      -> IDENTIFIED BY "goodsecret"
      -> REQUIRE SUBJECT "C=EE, ST=Some-State, L=Tallinn,
      "> O=MySQL demo client certificate,
      "> CN=Tonu Samuel/Email=[e-mail]";

• REQUIRE CIPHER cipher is needed to ensure that enough strong ciphers and keylengths will be used. SSL itself can be weak if old algorithms with short encryption keys are used. Using this option, we can ask for some exact cipher method to allow a connection.

  mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
      -> IDENTIFIED BY "goodsecret"
      -> REQUIRE CIPHER "EDH-RSA-DES-CBC3-SHA";

  You also can combine these options with each other like this:

  mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
      -> IDENTIFIED BY "goodsecret"
      -> REQUIRE SUBJECT "C=EE, ST=Some-State, L=Tallinn,
      "> O=MySQL demo client certificate,
      "> CN=Tonu Samuel/Email=[e-mail]"
      -> AND ISSUER "C=FI, ST=Some-State, L=Helsinki,
      "> O=MySQL Finland AB, CN=Tonu Samuel/Email=[e-mail]"
      -> AND CIPHER "EDH-RSA-DES-CBC3-SHA";

  But you cannot use any option twice. Only different options can be mixed.