MySQL project activities

The STAR MySQL for GRID project is an effort to integrate MySQL database in the GRID infrastructure. This means providing tools to help management of networks of replicated database and providing GSI authentication for MySQL connection.

The current subprojects are:


Slides of the presentation given at LBL. They give a summary of the scope and the aims of this project.
Two version are posted: PowerPoint and HTML


Gabriele Carcassi - Richard Casella

GSI Enabled MySQL

Grid Security Infrastructure (GSI) is the mechanism used by the Globus Toolkit for enabling secure authentication and communication for a Grid over an open network. GSI provides a number of useful services for Grids, including mutual authentication and single sign-on. For detailed information regarding GSI you can read the GSI overview from Globus. Enabling MySQL to use GSI security and authentication will enable Grid users with grid proxy certificates to securely communicate with MySQL daemons on the grid without having to do further authentication. Processes that have been scheduled and initiated on the grid by an authenticated user will be able to communicate with MySQL daemons as well without further authentication.

GSI

GSI uses X.509 certificates and SSL providing:
  • secure communication
  • security across organizational boundaries
  • single sign-on for users of the Grid

MySQL

As of version 4.0.0, MySQL is both SSL and X.509 enabled.

By default, MySQL is not SSL enabled, since using encrypted connections to access the database would slow down transactions and MySQL is, by default, optimized fo speed. Read the MySQL documentation on Using Secure Connections for details on how to set up MySQL for SSL, including how to create and set up the user certificates and grant the proper privleges for a user to authenticate.

The current implementation requires that the Certificate Authority (CA) certificate which signs the user and server certificates be available for the SSL/X.509 configuration to work. This is fine for applications which do not work with GSI enabled applications. It does not, howerver fit with the GSI model for authentication. The CA only need sign user and service certificates to use GSI. An example of a successful implementation of GSI using SSL on legacy software is the GSI Enabled OpenSSH.


Testing


Presentations

  • PPDG Collaboration Meeting presentation, June 10, 2003 - HTML - PPT

Richard A. Casella

GSI Enabled MySQL - Testing

To Grid-enable MySQL is to allow client authentication using X509 certificates as used in the Globus Toolkit. Using the X509 certificates issued by the Globus Toolkit CA will alleviate the need for the client to authenticate separately after issuing the "grid-proxy-init" command. To do this in MySQL, one needs to connect over an SSL encrypted channel. This document will outline the steps needed to prepare MySQL for such connections, demonstrate a simple Perl DBI script which accomplishes the connection, and discuss future plans for testing and implementation.


Setup

  • MySQL
  • For a more in-depth explanation of the why's and how's, see the MySQL documentation. What is included here are exerpts and observations from that documentation.
    • Build MySQL with SSL enabled. The following conditions apply to MySQL 4.0.0 or greater. If you are running an older version, you should definitely check the documentation mentioned above.
      1. Install OpenSSL Library >= OpenSSL 0.9.6
      2. Configure and build with options --with-vio --with-openssl
      3. Check that your server supports OpenSSL by examining if SHOW VARIABLES LIKE 'have_openssl' returns YES
    • X509 Certificates
    • Check documentation for more detailed explanation of key creation.
      • Setup. First create a directory for the keys, copy and modify openssl.cnf
      • DIR=~/openssl
        PRIV=$DIR/private
        mkdir $DIR $PRIV $DIR/newcerts
        cp /usr/share/openssl.cnf $DIR/openssl.cnf
        replace .demoCA $DIR -- $DIR/openssl.cnf
      • Certificate Authority
      • openssl req -new -keyout cakey.pem -out $PRIV/cacert.pem -config $DIR/openssl.cnf
      • Server Request and Key
      • openssl req -new -keyout $DIR/server-key.pem -out $DIR/server-req.pem \
        -days 3600 -config $DIR/openssl.cnf
        openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
        openssl ca -policy policy_anything -out $DIR/server-cert.pem \
        -config $DIR/openssl.cnf -infiles $DIR/server-req.pem
      • Client Request and Key
      • openssl req -new -keyout $DIR/client-key.pem -out $DIR/client-req.pem \
        -days 3600 -config $DIR/openssl.cnf
        openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
        openssl ca -policy policy_anything -out $DIR/client-cert.pem \
        -config $DIR/openssl.cnf -infiles $DIR/client-req.pem
      • Init Files
      • MySQLd needs to be made aware of the certificates at start-up time. MySQLd reads /etc/my.cnf at start-up time. Add the following lines (be sure to replace $DIR with the actuaal location) to /etc/my.cnf
        [server]
        ssl-ca=$DIR/cacert.pem
        ssl-cert=$DIR/server-cert.pem
        ssl-key=$DIR/server-key.pem
        Add the following lines (be sure to replace $DIR with the actuaal location) to ~/.my.cnf
        [client]
        ssl-ca=$DIR/cacert.pem
        ssl-cert=$DIR/client-cert.pem
        ssl-key=$DIR/client-key.pem
    • Grant Options
    • Again, the MySQL documentation should be consulted, but basically, the following options are added to the grant options in the user table of the mysql database. Not all of the following options have been tested at this time, but they will be before all is said and done. These options are added as needed in the following manner...
      	  mysql> GRANT ALL PRIVILEGES ON test.* to username@localhost
      -> IDENTIFIED BY "secretpass" REQUIRE SSL;
      • REQUIRE SSL limits the server to allow only SSL connections
      • REQUIRE X509 "issuer" means that the client should have a valid certificate, but we do not care about the exact certificate, issuer or subject
      • REQUIRE ISSUER means the client must present a valid X509 certificate issued by issuer "issuer".
      • REQUIRE SUBJECT "subject" requires clients to have a valid X509 certificate with the subject "subject" on it.
      • REQUIRE CIPHER "cipher" is needed to ensure strong ciphers and keylengths will be used. (ie. REQUIRE CIPHER "EDH-RSA-DES-CBC3-SHA")
  • Perl DBI/DBD
  • Perl DBI needs to connect over an SSL encrypted connection. SSL needs to be enabled. You must configure DBD::mysql with -ssl, then build and install it on the machine where you will be running your Perl code.

Testing


**Richard A. Casella -