Transitioning to SSL network encryption on MySQL servers

Transitioning to SSL network encryption on MySQL servers

by Rebecca O'Connell -
Number of replies: 4
Our school is transitioning to SSL network encryption on MySQL servers. We're running Moodle 2.7.8  with PHP 5.4.16, MySQL 5.5.41, and RHEL 6.6.


Our IT department sent us "certificates file(s) in PEM format and PKCS#12 format  to be used by clients". 

They gave us the following instructions:

The php-mysqli library only requires you to set the CA file for the mysqli connection object using the 

mysqli_ssl_set() function.

Example (db_connect_mysqli.php)    (modify the user, password, server, and port)

<?php

$con=mysqli_init();

mysqli_ssl_set($con,NULL,NULL,"[.pem CERT NAME]",NULL,NULL);

if (!mysqli_real_connect($con,"myserver","username","password","database","port"))

  {

  die("Connect Error: " . mysqli_connect_error());

  }

$query = "select * from information_schema.session_status where variable_name = 'ssl_cipher'";

$res = mysqli_query($con ,$query);

print_r(mysqli_fetch_array($res));

mysqli_close($con);

?>


Run: php -f db_connect_mysqli.php

Output:

Array

(

    [0] => SSL_CIPHER

    [VARIABLE_NAME] => SSL_CIPHER

    [1] => DHE-RSA-AES256-SHA

    [VARIABLE_VALUE] => DHE-RSA-AES256-SHA

)


As you might gather from all of the quoting, I'm not entirely clear on what this means in terms of what I need to to do our server/Moodle. There appears to be a certificate I need to install and...some part of the Moodle core to alter?

Any suggestions on where to go from here would be much appreciated.

Rebecca

Average of ratings: -
In reply to Rebecca O'Connell

Re: Transitioning to SSL network encryption on MySQL servers

by Just H -

No need to alter Moodle core, just update your config file (see here).

Installing the certificate on your server will depend on how you normally admin it e.g. do you use WHMS or any other control panel or do you just use the command line.

Average of ratings: -
In reply to Rebecca O'Connell

Re: Transitioning to SSL network encryption on MySQL servers

by Rebecca O'Connell -

Starting with a certificate provided by the server admins, this is what I had to do to solve the problem:


1. Add the following to /etc/my.cnf:
[client]
ssl-ca = path to certificate

2. Add the following to [moodle production folder]/lib/dml/mysqli_native_database.php:
Just after 
$this->mysqli = @new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket);

in the function 
public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null)

(~ line 444)

Add this code:

$this->mysqli = mysqli_init();
$errorno = $this->mysqli->options(MYSQLI_READ_DEFAULT_FILE,'/etc/mysql/my.cnf');
$errorno = $this->mysqli->options(MYSQLI_READ_DEFAULT_GROUP,'client');
$this->mysqli->real_connect($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket, MYSQLI_CLIENT_SSL);


(We also updated the database user to require SSL, to ensure that the connection actually is secure.)


Average of ratings: Useful (1)
In reply to Rebecca O'Connell

Re: Transitioning to SSL network encryption on MySQL servers

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Why on earth would they want to encrypt the MySQL data? Is your database remote (and on the public Internet) from your web server? Or do they just have too much time on their hands? wink

Average of ratings: -
In reply to Howard Miller

Re: Transitioning to SSL network encryption on MySQL servers

by James McLean -

Yeah that's the only thing I can think of too, that all queries are going over an insecure link. If so, I'd think a VPN would be easier to configure and maintain.

Average of ratings: -