University of Vermont

Hosted database services

Enterprise Technology Services manages MySQL database servers that any UVM affiliate can use. We take care of configuration, maintenance, and backups so you can concentrate on querying your data.

MySQL Database Service

Web Publishing with WebDB

Most programming languages have multiple database libraries from which you can choose to work with MySQL. We currently suggest the following:

LanguageDatabase LibraryTutorialDocumentation
PerlDBIMySQL Perl tutorialDBI, at MetaCPAN
PHPPDOPDO Tutorial for MySQL DevelopersPHP Data Objects, at php.net
PythonSQLAlchemyObject Relational TutorialSQLAlchemy.org
RRMySQLRMySQL Tutorial For BeginnersPackage ‘RMySQL’
RubyActive RecordActive Record, at api.rubyonrails.org

Encrypted Database Connections

Although authentication details are always protected, full connections between MySQL clients and the server are not encrypted by default. For many web sites in a controlled environment this is acceptable. However, if your application needs extra security, it is possible to use SSL/TLS encryption for all database communication.

From the command-line

On zoo and the silk hosting service, usage of the mysql command-line tool is already configured to use encryption.

On other systems, you can do the following:

  1. Save a copy of the WebDB certificate authority public key to the computer you will be connecting from.
  2. In either /etc/my.cnf (for system-wide use) or ~/.my.cnf (for your personal use), either find or create a [client] section. Add to it the following:
    [client]
    host = webdb.uvm.edu
    ssl-ca = /path/to/where/you/saved/webdb-cacert.pem
    ssl-verify-server-cert
    

New connections to webdb.uvm.edu should now be encrypted. You can verify this by starting a new session and typing status. You should see something similar to:

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.33-79.0, for Linux (x86_64) using  6.0

Connection id:          783
Current database:
Current user:           myusername@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
…

The "SSL:" line describes the type of encryption in use.

From Perl

If you will also use MySQL from the command-line, follow the directions above. Then tell DBI to load your my.cnf/.my.cnf file from your DSN. For example:

my $dsn = "DBI:mysql:mydbname;host=webdb.uvm.edu;mysql_ssl=1;mysql_read_default_file=/etc/my.cnf";
my $dbh = DBI->connect( $dsn, $username, $password );

Alternately, you can download the WebDB certificate authority public key and describe it directly:

my $dsn = "DBI:mysql:mydbname;host=webdb.uvm.edu;mysql_ssl=1;mysql_ssl_ca_file=/etc/pki/tls/certs/webdb-cacert.pem";
my $dbh = DBI->connect( $dsn, $username, $password );

From PHP

When using PDO for database connections, you can simply give it the location where you downloaded a copy of the WebDB certificate authority public key.

$dbh = new PDO(
    'mysql:host=webdb.uvm.edu;dbname=mydbname',
    $username,
    $password,
    array(
        PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/tls/certs/webdb-cacert.pem',
    )
);

From Python

Download a copy of the WebDB certificate authority public key and use it in your connection options.

ssl_args = {'ssl': {'ca': '/etc/pki/tls/certs/webdb-cacert.pem'}}
db_engine = create_engine(
        'mysql://username:password@webdb.uvm.edu/mydbname',
        connect_args=ssl_args)
Session = sessionmaker(bind=db_engine)
db = Session()

From Drupal

Download a copy of the WebDB certificate authority public key.

Look in your site's settings.php. Add a driver_options section to the database configuration, describing where to find the certificate authority file. For example:

$databases = array (
    'default' => 
     array (
       'default' => 
        array (
          'database' => 'mydbname',
          'username' => $username,
          'password' => $password,
          'host' => 'webdb.uvm.edu',
          'port' => '3306',
          'driver' => 'mysql',
          'prefix' => '',
          'driver options' => array(
              PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/tls/certs/webdb-cacert.pem',
           ),
       ),
    ),
  );

From WordPress

Download a copy of the WebDB certificate authority public key.

By default, WordPress does not yet provide configurable access to SSL/TLS settings (see this enhancement request). For the time being, you can set up WordPress with SSL/TLS by installing the Secure DB Connection plugin and adding the following to wp-config.php:

define( 'MYSQL_SSL_CA', '/etc/pki/tls/certs/webdb-cacert.pem' );
define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );

Note

On zoo and silk, a copy of the certificate authority file can be found and referenced at /etc/pki/tls/certs/webdb-cacert.pem rather than downloaded.

Keeping Database Credentials Safe

To publish web pages on www.uvm.edu, they must be accessible by you (to edit them) and by the web servers (to read them), without also letting other zoo users see your webdb login information. You can keep your database username & password safe by doing the following:

  1. Save your database credentials in a file on zoo named for your web site. For example, the site saved under the NetID kapoodle might have a file named kapoodle.inc:
    <?php
      $DB_INFO = array(
          'db_name' => 'kapoodle_db',
          'reader' => array( 'username' => 'kapoodle_reader', 'password' => 'mypassword' ),
          'writer' => array( 'username' => 'kapoodle_writer', 'password' => 'mypassword' ),
          'admin'  => array( 'username' => 'kapoodle_admin',  'password' => 'mypassword' ),
      );
    
    Make sure to remove permissions from this file so other people cannot access it.
  2. Send a request to the Systems Architecture & Administration group, letting them know where you have put this file. SAA will place this file where it can be accessed by the web servers but not other users.
  3. Include the file from your code:
    <?php
      require_once("/usr/local/uvm-inc/kapoodle.inc");
    ?>