4.9. MySQL User Account Management

This section describes how to set up accounts for clients of your MySQL server. It discusses the following topics:

• The meaning of account names and passwords as used in MySQL and how that compares to names and passwords used by your operating system

• How to set up new accounts and remove existing accounts

• How to change passwords

• Guidelines for using passwords securely

• How to use secure connections with SSL

4.9.1. MySQL Usernames and Passwords

A MySQL account is defined in terms of a username and the client host or hosts from which the user can connect to the server. The account also has a password. There are several distinctions between the way usernames and passwords are used by MySQL and the way they are used by your operating system:

• Usernames, as used by MySQL for authentication purposes, have nothing to do with usernames (login names) as used by Windows or Unix. On Unix, most MySQL clients by default try to log in using the current Unix username as the MySQL username, but that is for convenience only. The default can be overridden easily, because client programs allow any username to be specified with a -u or --user option. Because this means that anyone can attempt to connect to the server using any username, you cannot make a database secure in any way unless all MySQL accounts have passwords. Anyone who specifies a username for an account that has no password is able to connect successfully to the server.

• MySQL usernames can be up to a maximum of 16 characters long. This limit is hard-coded in the MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the mysql database does not work.

  Note: You should never alter any of the tables in the mysql database in any manner whatsoever except by means of the procedure prescribed by MySQL AB that is described in Section 4.6.2, "mysql_upgradeCheck Tables for MySQL Upgrade." Attempting to redefine MySQL's system tables in any other fashion results in undefined (and unsupported!) behavior.

  Operating system usernames are completely unrelated to MySQL usernames and may even be of a different maximum length. For example, Unix usernames typically are limited to eight characters.

• MySQL passwords have nothing to do with passwords for logging in to your operating system. There is no necessary connection between the password you use to log in to a Windows or Unix machine and the password you use to access the MySQL server on that machine.

• MySQL encrypts passwords using its own algorithm. This encryption is different from that used during the Unix login process. MySQL password encryption is the same as that implemented by the PASSWORD() SQL function. Unix password encryption is the same as that implemented by the ENCRYPT() SQL function. From version 4.1 on, MySQL employs a stronger authentication method that has better password protection during the connection process than in earlier versions. It is secure even if TCP/IP packets are sniffed or the mysql database is captured. (In earlier versions, even though passwords are stored in encrypted form in the user table, knowledge of the encrypted password value could be used to connect to the MySQL server.)

When you install MySQL, the grant tables are populated with an initial set of accounts. These accounts have names and access privileges that are described in Section 2.9.3, "Securing the Initial MySQL Accounts," which also discusses how to assign passwords to them. Thereafter, you normally set up, modify, and remove MySQL accounts using statements such as GRANT and REVOKE.

When you connect to a MySQL server with a command-line client, you should specify the username and password for the account that you want to use:

shell> mysql --user=monty --password=guess db_name

If you prefer short options, the command looks like this:

shell> mysql -u monty -pguess db_name

There must be no space between the -p option and the following password value. See Section 4.8.4, "Connecting to the MySQL Server."

The preceding commands include the password value on the command line, which can be a security risk. See Section 4.9.6, "Keeping Your Password Secure." To avoid this problem, specify the --password or -p option without any following password value:

shell> mysql --user=monty --password db_name
shell> mysql -u monty -p db_name

When the password option has no password value, the client program prints a prompt and waits for you to enter the password. (In these examples, db_name is not interpreted as a password because it is separated from the preceding password option by a space.)

On some systems, the library routine that MySQL uses to prompt for a password automatically limits the password to eight characters. That is a problem with the system library, not with MySQL. Internally, MySQL doesn't have any limit for the length of the password. To work around the problem, change your MySQL password to a value that is eight or fewer characters long, or put your password in an option file.

4.9.2. Adding New User Accounts to MySQL

You can create MySQL accounts in two ways:

• By using statements intended for creating accounts, such as CREATE USER or GRANT

• By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE

The preferred method is to use account-creation statements because they are more concise and less error-prone. CREATE USER and GRANT are described in the "MySQL Language Reference."

Another option for creating accounts is to use one of several available third-party programs that offer capabilities for MySQL account administration. phpMyAdmin is one such program.

The following examples show how to use the mysql client program to set up new users. These examples assume that privileges are set up according to the defaults described in Section 2.9.3, "Securing the Initial MySQL Accounts." This means that to make changes, you must connect to the MySQL server as the MySQL root user, and the root account must have the INSERT privilege for the mysql database and the RELOAD administrative privilege.

First, use the mysql program to connect to the server as the MySQL root user:

shell> mysql --user=root mysql

If you have assigned a password to the root account, you'll also need to supply a --password or -p option for this mysql command and also for those later in this section.

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

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'  ;

The accounts created by these GRANT statements have the following properties:

• Two of the accounts have a username of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 4.8.5, "Access Control, Stage 1: Connection Verification")

• One account has a username of admin and no password. This account can be used only by connecting from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges allow the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist. No privileges are granted for accessing any databases. You could add such privileges later by issuing additional GRANT statements.

• One account has a username of dummy and no password. This account can be used only by connecting from the local host. No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.

As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables using FLUSH PRIVILEGES:

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;

The reason for using FLUSH PRIVILEGES when you create accounts with INSERT is to tell the server to re-read the grant tables. Otherwise, the changes go unnoticed until you restart the server. With GRANT, FLUSH PRIVILEGES is unnecessary.

The reason for using the PASSWORD() function with INSERT is to encrypt the password. The GRANT statement encrypts the password for you, so PASSWORD() is unnecessary.

The 'Y' values enable privileges for the accounts. Depending on your MySQL version, you may have to use a different number of 'Y' values in the first two INSERT statements. For the admin account, you may also employ the more readable extended INSERT syntax using SET.

In the INSERT statement for the dummy account, only the Host, User, and Password columns in the user table row are assigned values. None of the privilege columns are set explicitly, so MySQL assigns them all the default value of 'N'. This is equivalent to what GRANT USAGE does.

Note that to set up a superuser account, it is necessary only to create a user table entry with the privilege columns set to 'Y'. user table privileges are global, so no entries in any of the other grant tables are needed.

The next examples create three accounts and give them access to specific databases. Each of them has a username of custom and password of obscure.

To create the accounts with GRANT, use the following statements:

    shell> mysql --user=root mysql
    mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
        ->    ON bankaccount.*
        ->    TO 'custom '@' localhost'
        ->    IDENTIFIED BY 'obscure';

    mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
        ->     ON expenses.*
        ->     TO 'custom'@' whitehouse.gov'
        ->     IDENTIFIED BY   'obscure';
    mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
        ->     ON customer.*
        ->     TO 'custom'@' server.domain'
        ->     IDENTIFIED BY 'obscure';

The three accounts can be used as follows:

• The first account can access the bankaccount database, but only from the local host.

• The second account can access the expenses database, but only from the host white-house.gov.

• The third account can access the customer database, but only from the host server.domain.

To set up the custom accounts without GRANT, use INSERT statements as follows to modify the grant tables directly:

shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
    ->     VALUES('localhost','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
    ->     VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
    ->     VALUES('server.domain','custom',PASSWORD('obscure'));
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('server.domain', 'customer', 'custom',
    ->     'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES;

The first three INSERT statements add user table entries that allow the user custom to connect from the various hosts with the given password, but grant no global privileges (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 you modify the grant tables directly, you must tell the server to reload them with FLUSH PRIVILEGES so that the privilege changes take effect.

If you want to give a specific user access from all machines in a given domain (for example, mydomain.com), you can issue a GRANT statement that uses the '%' wildcard character in the host part of the account name:

mysql> GRANT ...
    ->     ON *.*
    ->     TO 'myname '@' %.mydomain.com'
    ->     IDENTIFIED BY 'mypass';

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

mysql> INSERT INTO user (Host,User,Password,...)
    ->     VALUES('%.mydomain.com', 'myname', PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;

4.9.3. Removing User Accounts from MySQL

To remove an account, use the DROP USER statement.

4.9.4. Limiting Account Resources

One means of limiting use of MySQL server resources is to set the max_user_connections system variable to a non-zero value. However, this method is strictly global, and does not allow for management of individual accounts. In addition, it limits only the number of simultaneous connections made using a single account, and not what a client can do once connected. Both types of control are interest to many MySQL administrators, particularly those working for Internet service providers.

In MySQL 5.0, you can limit the following server resources for individual accounts:

• The number of queries that an account can issue per hour

• The number of updates that an account can issue per hour

• The number of times an account can connect to the server per hour

Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit.

From MySQL 5.0.3 on, it is also possible to limit the number of simultaneous connections to the server on a per-account basis.

An account in this context is a single row in the user table. Each account is uniquely identified by its User and Host column values.

As a prerequisite for using this feature, the user table in the mysql database must contain the resource-related columns. Resource limits are stored in the max_questions, max_updates, max_connections, and max_user_connections columns. If your user table doesn't have these columns, it must be upgraded; see Section 4.6.2, "mysql_upgradeCheck Tables for MySQL Upgrade."

To set resource limits with a GRANT statement, use a WITH clause that names each resource to be limited and a per-hour count indicating the limit value. For example, to create a new account that can access the customer database, but only in a limited fashion, issue this statement:

mysql> GRANT ALL ON customer.* TO 'francis '@' localhost
    ->     IDENTIFIED BY 'frank'
    ->      WITH MAX_QUERIES_PER_HOUR 20
    ->           MAX_UPDATES_PER_HOUR 10
    ->           MAX_CONNECTIONS_PER_HOUR 5
    ->           MAX_USER_CONNECTIONS 2;

The limit types need not all be named in the WITH clause, but those named can be present in any order. The value for each per-hour limit should be an integer representing a count per hour. If the GRANT statement has no WITH clause, the limits are each set to the default value of zero (that is, no limit). For MAX_USER_CONNECTIONS, the limit is an integer indicating the maximum number of simultaneous connections the account can make at any one time. If the limit is set to the default value of 0, the max_user_connections system variable determines the number of simultaneous connections for the account.

To set or change limits for an existing account, use a GRANT USAGE statement at the global level (ON *.*). The following statement changes the query limit for francis to 100:

mysql> GRANT USAGE ON *.* TO 'francis'@' localhost'
    ->     WITH MAX_QUERIES_PER_HOUR 100;

This statement leaves the account's existing privileges unchanged and modifies only the limit values specified.

To remove an existing limit, set its value to 0. For example, to remove the limit on how many times per hour francis can connect, use this statement:

mysql> GRANT USAGE ON *.* TO 'francis @ localhost
    ->     WITH MAX_CONNECTIONS_PER_HOUR 0;

Resource-use counting takes place when any account has a non-zero limit placed on its use of any of the resources.

As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued.

Resource counting is done per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.

The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:

• To reset the current counts to zero for all accounts, issue a FLUSH USER_RESOURCES statement. The counts also can be reset by reloading the grant tables (for example, with a FLUSH PRIVILEGES statement or a mysqladmin reload command).

• The counts for an individual account can be set to zero by re-granting it any of its limits. To do this, use GRANT USAGE as described earlier and specify a limit value equal to the value that the account currently has.

Counter resets do not affect the MAX_USER_CONNECTIONS limit.

All counts begin at zero when the server starts; counts are not carried over through a restart.

4.9.5. Assigning Account Passwords

Passwords may be assigned from the command line by using the mysqladmin command:

shell> mysqladmin -u user_name -h host_name password "newpwd"

The account for which this command resets the password is the one with a user table row that matches user_name in the User column and the client host from which you connect in the Host column.

Another way to assign a password to an account is to issue a SET PASSWORD statement:

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

Only users such as root that have update access to the mysql database can change the password for other users. If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:

mysql> SET PASSWORD = PASSWORD('biscuit');

You can also use a GRANT USAGE statement at the global level (ON *.*) to assign a password to an account without affecting the account's current privileges:

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

Although it is generally preferable to assign passwords using one of the preceding methods, you can also do so by modifying the user table directly:

• To establish a password when creating a new account, provide a value for the Password column:

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

• To change the password for an existing account, use UPDATE to set the Password column value:

  shell> mysql -u root mysql
  mysql> UPDATE user SET Password = PASSWORD('bagel')
      -> WHERE Host ='%' AND User ='francis';
  mysql> FLUSH PRIVILEGES;
  

When you assign an account a non-empty password using SET PASSWORD, INSERT, or UPDATE, you must use the PASSWORD() function to encrypt it. PASSWORD() is necessary because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely 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 literal value 'biscuit' is stored as the password in the user table, not the encrypted value. When jeffrey attempts to connect to the server using this password, the value is encrypted and compared to the value stored in the user table. However, the stored value is the literal string 'biscuit', so the comparison fails and the server rejects the connection:

shell> mysql -u jeffrey -pbiscuit test
Access denied

If you assign passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, they both take care of encrypting the password for you. In these cases, using PASSWORD() function is unnecessary.

Note: PASSWORD() encryption is different from Unix password encryption. See Section 4.9.1, "MySQL Usernames and Passwords."

4.9.6. Keeping Your Password Secure

On an administrative level, you should never grant access to the user grant table to any non-administrative accounts.

When you run a client program to connect to the MySQL server, 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 here, along with an assessment of the risks of each method:

• Use a -pyour_pass or --password=your_pass option on the command line. For example:

  shell> mysql -u francis -pfrank db_name

  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 password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. On some systems this strategy is ineffective, anyway, and the password remains visible to ps. (System V Unix systems and perhaps others are subject to this problem.)

• Use the -p or --password option with no password value specified. In this case, the client program solicits the password from the terminal:

  shell> mysql -u francis -pdb_name
  Enter password: ********
  

  The '*' characters indicate where you enter your password. The password is not displayed as you enter 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. 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 an option file. For example, on Unix 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 accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600. For example:

  shell> chmod 600 .my.cnf

  Section 3.3.2, "Using Option Files," discusses option files in more detail.

• Store your password in the MYSQL_PWD environment variable. This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. If you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine 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 option file.

4.9.7. Using Secure Connections

MySQL supports secure (encrypted) connections between MySQL clients and the server using the Secure Sockets Layer (SSL) protocol. This section discusses how to use SSL connections. It also describes a way to set up SSH on Windows. For information on requiring users to use SSL connections, see the "MySQL Language Reference."

The standard configuration of MySQL is intended to be as fast as possible, so encrypted connections are not used by default. Doing so would make the client/server protocol much slower. Encrypting data is a CPU-intensive operation that requires the computer to do additional work and can delay other MySQL tasks. For applications that require the security provided by encrypted connections, the extra computation is warranted.

MySQL allows encryption to be enabled on a per-connection basis. You can choose a normal unencrypted connection or a secure encrypted SSL connection according the requirements of individual applications.

4.9.7.1. Basic SSL Concepts

To understand how MySQL uses SSL, it is necessary to explain some basic SSL and X509 concepts. People who are familiar with these can skip this part of the discussion.

By default, MySQL uses unencrypted connections between the client and the server. This means that someone with access to the network could watch all your traffic and look at the data being sent or received. They could even change the data while it is in transit between client and server. To improve security a little, you can compress client/server traffic by using the --compress option when invoking client programs. However, this does not foil a determined attacker.

When you need to move information over a network in a secure fashion, an unencrypted connection is unacceptable. 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 kind of known attacks such as changing the order of encrypted messages or replaying data twice.

SSL is a protocol that uses different encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any data change, loss, or replay. SSL also incorporates algorithms that provide identity verification using the X509 standard.

X509 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 a "Certificate Authority" (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can show the certificate to another party as proof of identity. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.

If you need more information about SSL, X509, or encryption, use your favorite Internet search engine to search for the keywords in which you are interested.

4.9.7.2. Using SSL Connections with OpenSSL

To use SSL connections between the MySQL server and client programs, your system must support either OpenSSL or (as of MySQL 5.0.10) yaSSL. This section covers OpenSSL. To use yaSSL, read Section 4.9.7.3, "Using SSL Connections with yaSSL," instead.

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

shell> ./configure --with-vio --with-openssl

mysql> SHOW VARIABLES LIKE 'have_openssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
+---------------+-------+

4.9.7.3. Using SSL Connections with yaSSL

Using MySQL's built-in yaSSL support makes it easier to use secure connections. You don't have to install OpenSSL and perform the other steps described in Section 4.9.7.2, "Using SSL Connections with OpenSSL." Also, both MySQL and yaSSL employ the same licensing model.

Currently, yaSSL support is available for these platforms:

• Linux/x86-64 Red Hat Enterprise 3.0

• Linux RHAS21 Itanium-2 with gcc, statically linked

• Linux Itanium-2 with gcc

• Windows (all builds)

To enable yaSSL when building MySQL from source, you should configure MySQL like this:

shell> ./configure --with-yassl

Note that yaSSL support on Unix platforms requires that either /dev/urandom or /dev/random be installed to retrieve true random numbers. For additional information (especially regarding yaSSL on Solaris versions prior to 2.8 and HP-UX), see Bug #13164 (http://bugs.mysql.com/13164).

To start the MySQL server with yaSSL support, use the same options as with OpenSSL support and identify the certificates needed to establish a secure connection:

shell> mysqld --ssl-ca=cacert.pem \
       --ssl-cert=server-cert.pem \
       --ssl-key=server-key.pem

• --ssl-ca identifies the Certificate Authority certificate.

• --ssl-cert identifies the server certificate.

• --ssl-key identifies the client certificate.

To establish a secure connection to a MySQL server with yaSSL support, start a client like this:

shell> mysql --ssl-ca=cacert.pem  \
       --ssl-cert=server-cert.pem  \
       --ssl-key=server-key.pem

In other words, the options are the same as for the server, and the Certificate Authority certificate has to be the same.

To establish a secure connection from an application program, use the mysql_ssl_set() API function to set the appropriate certificate options, before calling mysql_real_connect().

4.9.7.4. Setting Up SSL Certificates for MySQL

Here is an example of setting up SSL certificates for MySQL using OpenSSL:

DIR='pwd'/openssl
PRIV=$DIR/private


mkdir $DIR $PRIV $DIR/newcerts
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf


# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)

touch $DIR/index.txt
echo "01" > $DIR/serial

#
# Generation of Certificate Authority(CA)
#


openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
        -config $DIR/openssl.cnf


# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/monty/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:


#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
    $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf


# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++

# writing new private key to '/home/monty/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:


#
# Remove the passphrase from the key (optional)
#


openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem


#
# Sign server cert
#
openssl ca -policy policy_anything -out $DIR/server-cert.pem \
    -config $DIR/openssl.cnf -infiles $DIR/server-req.pem


# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName           :PRINTABLE: 'FI'
# organizationName      :PRINTABLE: 'MySQL AB'
# commonName            :PRINTABLE: 'MySQL admin'

# Certificate is to be certified until Sep 13 14:22:46 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated


#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
    $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf


# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to  '/home/monty/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:


#

# Remove a passphrase from the key (optional)
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem


#
# Sign client cert
#


openssl ca -policy policy_anything -out $DIR/client-cert.pem \
    -config $DIR/openssl.cnf -infiles $DIR/client-req.pem


# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName           :PRINTABLE: 'FI'
# organizationName      :PRINTABLE: 'MySQL AB'
# commonName            :PRINTABLE: 'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated


#
# Create a my.cnf file that you can use to test the certificates
#

cnf=""
cnf="$cnf [client]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/client-cert.pem"
cnf="$cnf ssl-key=$DIR/client-key.pem"
cnf="$cnf [mysqld]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf

To test SSL connections, start the server as follows, where $DIR is the pathname to the directory where the sample my.cnf option file is located:

shell> mysqld --defaults-file=$DIR/my.cnf &

Then invoke a client program using the same option file:

shell> mysql --defaults-file=$DIR/my.cnf

If you have a MySQL source distribution, you can also test your setup by modifying the preceding my.cnf file to refer to the demonstration certificate and key files in the SSL directory of the distribution.

4.9.7.5. SSL Command Options

The following list describes options that are used for specifying the use of SSL, certificate files, and key files. They may be given on the command line or in an option file.

• --ssl

  For the server, this option specifies that the server allows SSL connections. For a client program, it allows the client to connect to the server using SSL. This option is not sufficient in itself to cause an SSL connection to be used. You must also specify the --sslca, --ssl-cert, and --ssl-key options.

  This option is more often used in its opposite form to indicate that SSL should not be used. To do this, specify the option as --skip-ssl or --ssl=0.

  Note that use of --ssl does not require an SSL connection. For example, if the server or client is compiled without SSL support, a normal unencrypted connection is used.

  The secure way to ensure that an SSL connection is used is to create an account on the server that includes a REQUIRE SSL clause in the GRANT statement. Then use this account to connect to the server, with both a server and client that have SSL support enabled.

• --ssl-ca=file_name

  The path to a file with a list of trusted SSL CAs.

• --ssl-capath=directory_name

  The path to a directory that contains trusted SSL CA certificates in PEM format.

• --ssl-cert=file_name

  The name of the SSL certificate file to use for establishing a secure connection.

• --ssl-cipher=cipher_list

  A list of allowable ciphers to use for SSL encryption. cipher_list has the same format as the openssl ciphers command.

  Example: --ssl-cipher=ALL:-AES:-EXP

• --ssl-key=file_name

  The name of the SSL key file to use for establishing a secure connection.

4.9.7.6. Connecting to MySQL Remotely from Windows with SSH

Here is a note that describes how to get a secure connection to a remote MySQL server with SSH (by David Carlson dcarlson@mplcomm.com):

At this point, you should have an ODBC connection to MySQL, encrypted using SSH.