4.3.9.4 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 or X509 options, all kind 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 which 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" places a restriction on connection attempts: The client must present a valid X509 certificate issued by CA "issuer". Using X509 certificates always implies encryption, so the SSL option is unneccessary.

  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=tonu@mysql.com";
  

• REQUIRE SUBJECT "subject" requires clients to have valid X509 certificate with subject "subject" on it. If the client presents a certificate that is valid but has a different "subject", the connection is disallowed.

  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=tonu@mysql.com";
  

• REQUIRE CIPHER "cipher" is needed to assure 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";
  

  The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause 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=tonu@mysql.com"
      -> AND ISSUER "C=FI, ST=Some-State, L=Helsinki,
      "> O=MySQL Finland AB, CN=Tonu Samuel/Email=tonu@mysql.com"
      -> AND CIPHER "EDH-RSA-DES-CBC3-SHA";
  

  Starting from MySQL 4.0.4 the AND keyword is optional between REQUIRE options. The order of the options does not matter, but no option can be specified twice.

User Comments

It should be noted that this does not apply to the 3.23 series at all.

If the above does not work for 3.23, how does one enable SSL for 3.23.55 and before - will instructions be made available, as I can only find them for version 4.

Regards,
Adam

The docs seem to be wrong. Although "openssl x509 -text -in mycert.pem" write e.g. the Subject as "C=DE, ST=NRW, L=Aachen...", MySQL needs the format that is used be "openssl x509 -in mycert -subject":
/C=DE/ST=NRW/L=Aachen

Instructions will not be made available for using SSL in 3.23.* because versions earlier than 4.0 don't support SSL. If you want SSL you'll have to upgrade to 4.0 (and you'll also have to compile MySQL yourself, because the distributed binaries aren't compiled with SSL support).

Add your own comment.