List By Client
List By Client Groups
List By DBA
List Briefing Notes
List CR Action Plans
Search for CRs
Clients' Open CRs
My New CRs
Open CRs
Client Hours
Business Issues
Client Groups
Daily Activities
Daily Client
Effort by Client Group
Effort by Comment Group
Effort by Month
Last Comment
DBA Resourcing
Overdue SRRs
Avail Config
Cluster Config
Track Clients
Client Users
Backup Strategy
CR Approval
Do Dailies
Page Acknowledgment
Set SRRs
Client Feedback
Spotcheck
Track Admin
Avail Scheduler
Boardroom
Client Sheets
BoardroomCam
Holiday Schedule
Pythian Wiki
OnCall Schedule
Preferences
Running Total
Webmail
Logged in as: cabral        
Jump to CR 
Total Booked 02:04        
Day Booked 02:04		Day Started  8:20am        
Mins Unbooked  (+0)
Micro CR:     
1mdappul16_midtier.mdsps
Support Track
Log Out   Version 5.03_L
    The Pythian Group    
CR Number: 203865 -- Idology Inc.    Team 13  DBA:Sheeri K. Cabral

Warnings
Wiki link for IDL https://secure.pythian.com/trac/trac.cgi/wiki/ClientPages/IDL
tickets When a developer pages about an emergency, it is possible that they will have created a ticket at ThePlanet:
https://orbit.theplanet.com/
UN: pythian PW: da77as2334

For production issues:
https://my.rackspace.com/portal/auth/portal/default
rackspace account #: 62486 UN: Pythian1 PW: $uP3Rd3@!

secret answer (if you forget password) is Paul
Title
Total Hours
8 Hours 30 Minutes
Created by
Phone
Database
Severity
Status
DBA
Notify List
Related CRs

Comments
 


  
Description
Per Jason email below, please look into what we need to do to convert all our db access to SSL
/Magnus

I'd like pythian to look into converting all of our db access to SSL (particularly on production).
Jason


Swap Log Display Order
04-Mar-2009 at  4:42pm (GMT -05)Sheeri K. Cabral4 MinutesSeverity: 3 - Green
Probably the best thing to do would be to set up a reporting server somewhere that can only replicate using SSL from idology11.

To set this up we will need a restart of idology11, which can happen only during a maintenance window.


16-Feb-2009 at 11:25am (GMT -05)Singer X.J. WangSeverity: 3 - Green
Status Review Reminder: Wednesday, March 18, 2009

waiting for more testing by client


15-Jan-2009 at  1:30pm (GMT -05)Singer X.J. WangSeverity: 3 - Green
Status Review Reminder: Saturday , February 14, 2009

waiting for more testing by client


17-Dec-2008 at  9:47am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Friday , January 16, 2009

waiting for more testing


03-Dec-2008 at 11:27am (GMT -05)Singer X.J. WangSeverity: 3 - Green
Status Review Reminder: Wednesday, December 17, 2008

waiting for more testing


28-Nov-2008 at  1:03pm (GMT -05)Singer X.J. Wang
Severity: 3 - Green
you can also do:

show variables like 'ssl_cipher';


and if you are using SSL there will be something like AES256 in the results, otherwise it will empty..


28-Nov-2008 at 12:49pm (GMT -05)Singer X.J. WangSeverity: 3 - Green
Status Review Reminder: Friday , December 05, 2008

waiting to see if Jason has any more requests


28-Nov-2008 at 11:20am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html
right, you can use the java keytools to create client SSL certs (and all certs if you want), I am familiar with the openssl commandline tools so it was just easier


Looks like I'll need to modify /etc/tomcat6/tomcat6.conf and /etc/tomcat6/server.xml to force an SSL connection from the java side. I'm not familar with tomcat, but we have people that can help you with this if you need it.


28-Nov-2008 at 11:17am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
arg, somehow the last comment got cutout.. here is it:

as requested by Jason, I am documenting both methods of SSL

First, without SSL the \s option shows no Cipher in use. Thus no SSL enabled..

[pythian@idology08 ssl-keys]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

Connection id:          39
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.45-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 31 min 26 sec

Threads: 2  Questions: 2565  Slow queries: 0  Opens: 25  Flush tables: 1  Open tables: 19  Queries per second avg: 1.360
--------------

mysql>


REQUIRE SSL
-----------
This mode is similar to what a browser does on a secure site. It verifies the server's SSL cert against the CA (which signed
the server's SSL Cert). The server does not verify the client's SSL cert. In this mode, the user on the command line must
specify the cert of the CA (and must be able to read it - in the browser scenario this cert is already in the browser). The bold
line shows that we are using SSL.

In the example below, I've created a user called testSSL which requires SSL to login:

mysql>
mysql>
mysql>
mysql> GRANT ALL PRIVILEGES ON *.* TO 'testSSL'@'localhost' IDENTIFIED BY 'test1234' REQUIRE X509;
Query OK, 0 rows affected (0.01 sec)

mysql> Aborted
[pythian@idology08 ssl-keys]$ mysql -utestSSL -p
Enter password:
ERROR 1045 (28000): Access denied for user 'testSSL'@'localhost' (using password: YES)
[pythian@idology08 ssl-keys]$ mysql -utestSSL -p --ssl-ca=ca-cert.pem
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

Connection id:          38
Current database:
Current user:           testSSL@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.45-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 30 min 38 sec

Threads: 2  Questions: 2561  Slow queries: 0  Opens: 25  Flush tables: 1  Open tables: 19  Queries per second avg: 1.393
--------------

mysql>



SSL X509 Mode
-------------
X509 mode is stronger then SSL mode in that the client must also provide a SSL cert to the server. The server then verifies the SSL cert
of the client against its know CA list (specified in /etc/my.cnf). In this mode, the client must give the CA Cert, its Cert, and its Key.
I've created a user called testX509 to demostrate this. Again the bold line in \s shows that we are using SSL. 


mysql>  GRANT ALL PRIVILEGES ON *.* TO 'testX509'@'localhost' IDENTIFIED BY 'test1234' REQUIRE X509;
Query OK, 0 rows affected (0.00 sec)

mysql> Aborted
[pythian@idology08 ssl-keys]$ mysql -utestX509 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'testX509'@'localhost' (using password: YES)
[pythian@idology08 ssl-keys]$ mysql -utestX509 -p --ssl-ca=ca-cert.pem
Enter password:
ERROR 1045 (28000): Access denied for user 'testX509'@'localhost' (using password: YES)
[pythian@idology08 ssl-keys]$ mysql -utestX509 -p --ssl-ca=ca-cert.pem --ssl-cert=idology08-client-cert.pem --ssl-key=idology08-client-key.pem
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

Connection id:          42
Current database:
Current user:           testX509@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.45-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 35 min 47 sec

Threads: 2  Questions: 3059  Slow queries: 0  Opens: 25  Flush tables: 1  Open tables: 19  Queries per second avg: 1.425
--------------

mysql>



for security, I have removed both users testSSL and testX509..


28-Nov-2008 at 11:16am (GMT -05)Singer X.J. Wang
Severity: 3 - Green


28-Nov-2008 at 11:15am (GMT -05)Jason CorleySeverity: 3 - Green
Jason Corley wrote:
---------------------------
Seems like there should be a better way than this:
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html
Looks like I'll need to modify /etc/tomcat6/tomcat6.conf and /etc/tomcat6/server.xml to force an SSL connection from the java side.


28-Nov-2008 at 10:40am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
jason moved the files into the desired directories (and renamed them to use FQDN's).. the config is as below.. we seem to be runnig with SSL now..


-bash-3.2$ ls -l /etc/pki/tls/certs/idology08.idologylive.com-ca-cert.pem
-r--r----- 1 mysql mysql 1468 Nov 28 15:32 /etc/pki/tls/certs/idology08.idologylive.com-ca-cert.pem
-bash-3.2$ ls -l /etc/pki/tls/certs/idology08.idologylive.com-ca-cert.pem
-r--r----- 1 mysql mysql 1468 Nov 28 15:32 /etc/pki/tls/certs/idology08.idologylive.com-ca-cert.pem
-bash-3.2$ ls -l /etc/pki/tls/private/idology08.idologylive.com.key
-r--r----- 1 mysql mysql 1675 Nov 28 15:31 /etc/pki/tls/private/idology08.idologylive.com.key
-bash-3.2$ exit
logout
[pythian@idology08 etc]$ sudo /etc/init.d/mysqld  stop
081128 15:38:24 [Note] /usr/libexec/mysqld: Normal shutdown

081128 15:38:25  InnoDB: Starting shutdown...

Stopping MySQL:                                            [  OK  ]
[pythian@idology08 etc]$
[pythian@idology08 etc]$ 081128 15:38:28  InnoDB: Shutdown completed; log sequence number 0 1114958497
081128 15:38:28 [Note] /usr/libexec/mysqld: Shutdown complete

081128 15:38:28  mysqld ended


[pythian@idology08 etc]$ sudo /etc/init.d/mysqld  start
081128 15:38:37  mysqld started
Starting MySQL:                                            [  OK  ]
[pythian@idology08 etc]$ 081128 15:38:38  InnoDB: Started; log sequence number 0 1114958497
081128 15:38:38 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

[pythian@idology08 etc]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  SHOW VARIABLES LIKE 'have%_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
+---------------+-------+
2 rows in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE '%ssl%';
+---------------+----------------------------------------------------------+
| Variable_name | Value                                                    |
+---------------+----------------------------------------------------------+
| have_openssl  | YES                                                      |
| have_ssl      | YES                                                      |
| ssl_ca        | /etc/pki/tls/certs/idology08.idologylive.com-ca-cert.pem |
| ssl_capath    |                                                          |
| ssl_cert      | /etc/pki/tls/certs/idology08.idologylive.com.pem         |
| ssl_cipher    |                                                          |
| ssl_key       | /etc/pki/tls/private/idology08.idologylive.com.key       |
+---------------+----------------------------------------------------------+
7 rows in set (0.00 sec)

mysql>


28-Nov-2008 at 10:30am (GMT -05)Danil ZburivskySeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Danil Zburivsky Singer X.J. Wang

Singer is working on this.



28-Nov-2008 at 10:26am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
we didn't have a CA made, we created the client and server keys/certs and so I recreated using guide @
http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html

# the CA, will be shared among all Idology
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

# server key
openssl req -newkey rsa:2048 -days 1825 -nodes -keyout idology08-key.pem > idology08-req.pem
openssl rsa -in idology08-key.pem -out idology08-key.pem
openssl x509 -req -in idology08-req.pem -days 1825 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > idology08-cert.pem

# client keys
openssl req -newkey rsa:2048 -days 1825 -nodes -keyout idology08-client-key.pem > idology08-client-req.pem
openssl rsa -in idology08-client-key.pem -out idology08-client-key.pem
openssl x509 -req -in idology08-client-req.pem -days 1825 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > idology08-client-cert.pem


[pythian@idology08 ssl-keys]$ ls -l
total 72
-rw-rw-r-- 1 pythian pythian 1468 Nov 28 15:17 ca-cert.pem
-rw-rw-r-- 1 pythian pythian 1675 Nov 28 15:16 ca-key.pem
-rw-rw-r-- 1 pythian pythian 1168 Nov 28 15:21 idology08-cert.pem
-rw-rw-r-- 1 pythian pythian 1180 Nov 28 15:25 idology08-client-cert.pem
-rw-rw-r-- 1 pythian pythian 1675 Nov 28 15:25 idology08-client-key.pem
-rw-rw-r-- 1 pythian pythian  997 Nov 28 15:24 idology08-client-req.pem
-rw-rw-r-- 1 pythian pythian 1675 Nov 28 15:20 idology08-key.pem
-rw-rw-r-- 1 pythian pythian  989 Nov 28 15:19 idology08-req.pem
drwxrwxr-x 2 pythian pythian 4096 Nov 28 15:14 old
[pythian@idology08 ssl-keys]$


28-Nov-2008 at 10:17am (GMT -05)Danil Zburivsky
Severity: 3 - Green
team work with Singer.


28-Nov-2008 at 10:02am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
talking to jason.. there are some confusion over the CA/Server certs being one..


(9:21:12 AM) Singer Wang (TPG-AIM): hey
(9:21:13 AM) Singer Wang (TPG-AIM): jason
(9:21:16 AM) Singer Wang (TPG-AIM): aer you there?
09:55
(9:56:50 AM) Singer Wang (TPG-AIM): /home/pythian/working/ssl-keys
(9:57:04 AM) Singer Wang (TPG-AIM): ssl-ca=/etc/pki/tls/certs/idology08_cacert.pem
ssl-cert=/etc/pki/tls/certs/idology08_cacert.pem
ssl-key=/etc/pki/tls/private/idology08_key.pem
(9:57:37 AM) corleyjazz2000 is no longer away.
(9:57:37 AM) corleyjazz2000 is no longer idle.
(9:57:55 AM) Singer Wang (TPG-AIM): the keys are in the /home/pythian one
(9:58:21 AM) corleyjazz2000: ok
(9:58:58 AM) Singer Wang (TPG-AIM): and those are the 3 lines:
(9:58:59 AM) Singer Wang (TPG-AIM): ssl-ca=/etc/pki/tls/certs/idology08_cacert.pem
ssl-cert=/etc/pki/tls/certs/idology08_cacert.pem
ssl-key=/etc/pki/tls/private/idology08_key.pem
(9:59:16 AM) Singer Wang (TPG-AIM): also, can you make suer the mysql user can read those files?
(9:59:18 AM) Singer Wang (TPG-AIM): thanks :)
(9:59:29 AM) corleyjazz2000: I thought we were using a different cert for CA and MySQL
(9:59:47 AM) Singer Wang (TPG-AIM): I'd have to talk to danil
(9:59:49 AM) Singer Wang (TPG-AIM): one sec
10:00
(10:00:06 AM) corleyjazz2000: k


28-Nov-2008 at  9:34am (GMT -05)Singer X.J. Wang
Severity: 3 - Green
SSL is disabled..

mysql>  SHOW VARIABLES LIKE 'have%_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
+---------------+----------+
2 rows in set (0.00 sec)

mysql>


we need Jason to copy the keys from /home/pythian/working/ssl-keys to the locations he specified (root only, ensureing MySQL can read them)

and then add to the my.cnf below

ssl-ca=/etc/pki/tls/certs/idology08_cacert.pem
ssl-cert=/etc/pki/tls/certs/idology08_cacert.pem
ssl-key=/etc/pki/tls/private/idology08_key.pem


called and left VM..

(also a few minutes for reading over the CR)..


28-Nov-2008 at  4:30am (GMT -05)Danil Zburivsky
Severity: 3 - Green
Generating ssl keys on idology08:

[pythian@idology08 ssl-keys]$ openssl genrsa -out idology08_key.pem 2048
[pythian@idology08 ssl-keys]$ openssl req -new -x509 -key idology08_key.pem -out idology08_cacert.pem -days 1825
[pythian@idology08 ssl-keys]$ openssl req -newkey rsa:2048 -md5 -days 1825 -nodes -keyout client-key.pem  > client-req.pem
[pythian@idology08 ssl-keys]$ openssl x509 -req -in client-req.pem -days 1825 -md5 -CA idology08_cacert.pem -CAkey idology08_key.pem -set_serial 01 > client-cert.pem


Keys are currently in /home/pythian/working/ssl-keys:

[pythian@idology08 ssl-keys]$ pwd
/home/pythian/working/ssl-keys
[pythian@idology08 ssl-keys]$ ls -lh
total 40K
-rw-rw-r-- 1 pythian pythian 1.1K Nov 28 09:26 client-cert.pem
-rw-rw-r-- 1 pythian pythian 1.7K Nov 28 09:25 client-key.pem
-rw-rw-r-- 1 pythian pythian  960 Nov 28 09:25 client-req.pem
-rw-rw-r-- 1 pythian pythian 1.4K Nov 28 09:22 idology08_cacert.pem
-rw-rw-r-- 1 pythian pythian 1.7K Nov 28 09:20 idology08_key.pem




28-Nov-2008 at  4:11am (GMT -05)Danil ZburivskySeverity: 3 - Green
Status Review Reminder: Friday , November 28, 2008



27-Nov-2008 at  8:31am (GMT -05)Danil ZburivskySeverity: 3 - Green
Status Review Reminder: Thursday , November 27, 2008



26-Nov-2008 at  4:34pm (GMT -05)Sheeri K. Cabral6 MinutesSeverity: 3 - Green
Jason sez:

1) 2048 for key size
2) 5-year for how long to expire
3) certificates for idology are kept in a subversion repository, and live on the filesystem in:
private key: /etc/pki/tls/private/
certificate and ca-cert: /etc/pki/tls/certs

After ssl certificate is made, ask Jason to put it into the correct places. Please set up MySQL to read from these places, though.

4) For now, please make a user called 'ssl_test' with some password you make up, and test ssl connectivity that way. After ssl connectivity is checked, try to make the user able to connect via ssl *only* and test that ssl login works, and that non-ssl login does *not* work.


Questions:
1. [key_size] -- the strength of the SSL key (usually 1024 or 2048) in bits - I'd recomend 2048

2. [days_to_expire] -- how long the certificate will be valid. Year? Two year? Five Years? Anything in between.
Note, that when certificate expires a new one should be issued and provided to MySQL client.

3. A place to keep key and certificate on idology08. I would put keys to /etc/mysql/; If that's okay with you,
we need you to create /etc/mysql and make sure that the directory is writeable by the mysql user.

4. Which user should require ssl to connect? Currently there are the following users on idology08:
mysql> SELECT user, host FROM mysql.user;
+---------+-----------+
| user | host |
+---------+-----------+
| webdb | % |
| webdbro | % |
| root | localhost |
| webdb | localhost |
| webdbro | localhost |
+---------+-----------+
5 rows in set (0.00 sec)


26-Nov-2008 at  9:19am (GMT -05)Danil ZburivskySeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 2 - Work In Progress 3 - Pending


26-Nov-2008 at  9:18am (GMT -05)Danil Zburivsky
Severity: 3 - Green
Converting idlogy08 access to SSL plan:

1. Create server's private key:

openssl genrsa -out idology08_key.pem [key_size]


2. Create server's certificate:

openssl req -new -x509 -key idology08_key.pem -out idology08_cacert.pem -days [days_to_expire]


3. Create client's key:

openssl req -newkey rsa:[key_length] -md5 -days [days_to_expire] -nodes -keyout client-key.pem  > client-req.pem


4. Create client's certificate signed by server's certificate:

openssl x509 -req -in client-req.pem -days [days_to_expire] -md5 -CA idology08_cacert.pem -CAkey idology08_key.pem -set_serial 01 > client-cert.pem


5. Put server's keys to a safe place on idology08, make keys accessible only by mysql user.

6. Move client's keys and server's certificate to client machine.

7. Add the following settings to my.cnf file:

ssl-ca=/path_to_server_keys/idology08_cacert.pem
ssl-cert=/path_to_server_keys/idology08_cacert.pem
ssl-key=/path_to_server_keys/idology08_key.pem


8. Restart MySQL.

9. Update MySQL user to require ssl credentials to connect:

UPDATE mysql.user SET ssl_type='X509' WHERE user=user_name;
FLUSH PRIVILEGES;


10. Test client's key and certificate using mysql command line tool:

mysql -u user_name -p --ssl-ca=idology08_cacert.pem 
--ssl-cer=client-cert.pem --ssl-key=client-key.pem


11. Update application code to use ssl connection. More on using mysql_ssl_set() C API function can be found here -- http://dev.mysql.com/doc/refman/5.0/en/mysql-ssl-set.html

Revert plan
If things go wrong and ssl connection could not be configured in reasonable time here is a revert plan:

1. Comment out the following options from my.cnf file:

ssl-ca=/path_to_server_keys/idology08_cacert.pem
ssl-cert=/path_to_server_keys/idology08_cacert.pem
ssl-key=/path_to_server_keys/idology08_key.pem


2. Restart MySQL.

3. Remove ssl requirements from user:

UPDATE mysql.user SET ssl_type='' WHERE user=user_name;
FLUSH PRIVILEGES;


4. Test connection without ssl.

*********************************************************************************************
There are some parameters we need to know before we start maintenance on Friday.

So I'd like Idology to provide their feedback on this today:
1. [key_size] -- defines the strength of the key. Usual value is 2048
2. [days_to_expire] -- how long the certificate will be valid. Year? Two year? Note, that when
certificate expires a new one should be issued and provided to MySQL client.
3. A place to keep key and certificate on idology08. I would put keys to /etc/mysql/
4. Which user should require ssl to connect? Currently there are the following users on idology08:

mysql> SELECT user, host FROM mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| webdb   | %         | 
| webdbro | %         | 
| root    | localhost | 
| webdb   | localhost | 
| webdbro | localhost | 
+---------+-----------+
5 rows in set (0.00 sec)

We need to know which user's should be converted to ssl.


26-Nov-2008 at  8:31am (GMT -05)Danil Zburivsky
Severity: 3 - Green
Some googling helped to resolve this problem.

There is a opened bug http://bugs.mysql.com/bug.php?id=21287 to add more information to SSL-related error messages. As I understood currently MySQL prints only "SSL connection error" and one of the comments to the bug showed how to generate key and a certificate for client.

Here are these steps.

Generate a private key for client and certificate request:

openssl req -newkey rsa:1024 -md5 -days 1000 -nodes -keyout client-key.pem  > client-req.pem

Some parameters as key length and days to expire can be adjusted.

Then we need to sign client's certificate with server's certificate and key. Like this:


openssl x509 -req -in client-req.pem -days 1000 -md5 -CA server_cert -CAkey server_key -set_serial 01 > client-cert.pem


Then we use client certificate, client key and server certificate to connect:

daniel@daniel-desktop:~/client_keys$ mysql -u test2 -p --ssl-ca=/home/daniel/client_ca.pem --ssl-cer=target-cert.pem --ssl-key=target-key.pem 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA | 
+---------------+--------------------+
1 row in set (0.01 sec)


And it works!


26-Nov-2008 at  7:18am (GMT -05)Danil Zburivsky
Severity: 3 - Green
Trying to set up user with REQUIRE X509 privileger. This will make server require not only server's certificate but client's key and client's certificate. No luck so far:

daniel@daniel-desktop:~/client_keys$ mysql -u test2 -p --ssl-ca=/home/daniel/client_ca.pem --ssl-cer=client_key.pem_cert.pem --ssl-key=client_key.pem
Enter password: 
ERROR 2026 (HY000): SSL connection error


Investigating.


26-Nov-2008 at  7:07am (GMT -05)Danil Zburivsky
Severity: 3 - Green
Some more investigation has shown that the real reason that made things work was not moving certificate file to a different location, but using all 3 options in my.cnf file:

ssl-ca=/home/daniel/mysql_cacert.pem
ssl-cert=/home/daniel/mysql_cacert.pem
ssl-key=/home/daniel/mysql_prkey.pem


Previous times I've skipped ssl-cert option, but in openssl certificates hold public key info as well, so it need to be specified as well.

MySQL started without any error or warnings, though.


26-Nov-2008 at  6:53am (GMT -05)Danil Zburivsky
Severity: 3 - Green
OK, I've found and solved the previous problem. The thing was that I was trying to use the same certification file that mysql process was already using. Copying certificate to another file helped:

daniel@daniel-desktop:/var/lib/mysql$ mysql -u test_user -p --ssl-ca=/home/daniel/client_ca.pem
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA | 
+---------------+--------------------+
1 row in set (0.00 sec)

mysql> exit


Will start with the converting to SSL shortly.


26-Nov-2008 at  6:02am (GMT -05)Danil Zburivsky
Severity: 3 - Green
Reading docs at http://www.openssl.org/

Testing establishing SSL connenction on my local machine.

Currently getting non-descriptive error:

daniel@daniel-desktop:/var/lib/mysql$ mysql -u test_user -p --ssl-ca=/home/daniel/mysql_cacert.pem 
Enter password: 
ERROR 2026 (HY000): SSL connection error


Will post more details soon, interrupted by pager.


26-Nov-2008 at  4:52am (GMT -05)Danil ZburivskySeverity: 3 - Green
CR Modified
Variable Old value New value
Title Convert DB Access to SSL Convert idology08 access to SSL


25-Nov-2008 at 11:26am (GMT -05)Danil ZburivskySeverity: 3 - Green
Status Review Reminder: Wednesday, November 26, 2008



24-Nov-2008 at 10:05am (GMT -05)Danil ZburivskySeverity: 3 - Green
Status Review Reminder: Tuesday , November 25, 2008



24-Nov-2008 at  5:23am (GMT -05)Danil Zburivsky
Severity: 3 - Green
Going through previous comments and this document -- http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html


23-Nov-2008 at 10:45pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Sheeri K. Cabral Danil Zburivsky

Danil, can you start with a plan on how to do this for idology08?



23-Nov-2008 at 10:44pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Monday , November 24, 2008



21-Nov-2008 at 11:57am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Friday , November 21, 2008



19-Nov-2008 at  5:22pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 3 - Pending 2 - Work In Progress

Set this up for Friday 28 Nov on idology08 = devweb after the innodb_file_per_table change.



21-Oct-2008 at  9:45am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Thursday , November 20, 2008



20-Oct-2008 at  9:00am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Monday , October 20, 2008



03-Oct-2008 at 11:08am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Santhinesh Kumar Sheeri K. Cabral


19-Sep-2008 at 11:29am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Sunday , October 19, 2008



12-Sep-2008 at  4:01am (GMT -05)Santhinesh KumarSeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 1 - New 3 - Pending


03-Sep-2008 at  2:10am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Thursday , September 18, 2008



26-Aug-2008 at  9:17am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Tuesday , September 02, 2008



18-Aug-2008 at  6:23am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Monday , August 25, 2008



12-Aug-2008 at  8:38am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Thursday , August 14, 2008



11-Aug-2008 at  3:18am (GMT -05)Santhinesh Kumar
Severity: 3 - Green

Created a test certificate in idology07 .

[pythian@idology07 newcerts]$ openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
..+++
...........................................................................+++
e is 65537 (0x10001)
[pythian@idology07 newcerts]$ openssl req -new -x509 -nodes -days 1000 \
>          -key ca-key.pem > ca-cert.pem
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) [GB]:.
State or Province Name (full name) [Berkshire]:.
Locality Name (eg, city) [Newbury]:.
Organization Name (eg, company) [My Company Ltd]:.
Organizational Unit Name (eg, section) []:.
Common Name (eg, your name or your server's hostname) []:idology07
Email Address []:.
[pythian@idology07 newcerts]$ openssl req -newkey rsa:2048 -days 1000 \
>          -nodes -keyout server-key.pem > server-req.pem
Generating a 2048 bit RSA private key
.....................................................................................................+++
........................................................+++
writing new private key to 'server-key.pem'
-----
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) [GB]:.
State or Province Name (full name) [Berkshire]:.
Locality Name (eg, city) [Newbury]:.
Organization Name (eg, company) [My Company Ltd]:.
Organizational Unit Name (eg, section) []:.
Common Name (eg, your name or your server's hostname) []:idology07
Email Address []:.

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:pythian
An optional company name []:pythian
[pythian@idology07 newcerts]$ openssl x509 -req -in server-req.pem -days 1000 \
>          -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Signature ok
subject=/CN=idology07
Getting CA Private Key
[pythian@idology07 newcerts]$ openssl req -newkey rsa:2048 -days 1000 \
>          -nodes -keyout client-key.pem > client-req.pem
Generating a 2048 bit RSA private key
............................+++
....................+++
writing new private key to 'client-key.pem'
-----
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) [GB]:.
State or Province Name (full name) [Berkshire]:.
Locality Name (eg, city) [Newbury]:.
Organization Name (eg, company) [My Company Ltd]:.
Organizational Unit Name (eg, section) []:.
Common Name (eg, your name or your server's hostname) []:.
Email Address []:.
error, no objects specified in config file
problems making Certificate Request
[pythian@idology07 newcerts]$ openssl req -newkey rsa:2048 -days 1000          -nodes -keyout client-key.pem > client-req.pem
Generating a 2048 bit RSA private key
....................................................................................................................+++
.......................................+++
writing new private key to 'client-key.pem'
-----
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) [GB]:.
State or Province Name (full name) [Berkshire]:.
Locality Name (eg, city) [Newbury]:.
Organization Name (eg, company) [My Company Ltd]:.
Organizational Unit Name (eg, section) []:.
Common Name (eg, your name or your server's hostname) []:client
Email Address []:.

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:client
An optional company name []:.
[pythian@idology07 newcerts]$ ls -ltrh
total 56K
-rw-rw-r-- 1 pythian pythian 1.7K Aug 11 06:55 ca-key.pem
-rw-rw-r-- 1 pythian pythian 1.2K Aug 11 06:57 ca-cert.pem
-rw-rw-r-- 1 pythian pythian  956 Aug 11 06:58 server-req.pem
-rw-rw-r-- 1 pythian pythian 1.7K Aug 11 06:58 server-key.pem
-rw-rw-r-- 1 pythian pythian  964 Aug 11 06:59 server-cert.pem
-rw-rw-r-- 1 pythian pythian  920 Aug 11 07:10 client-req.pem
-rw-rw-r-- 1 pythian pythian 1.7K Aug 11 07:10 client-key.pem
[pythian@idology07 newcerts]$



06-Aug-2008 at  4:47pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 2 - Work In Progress 1 - New

make that "coordinate with Jason" on the restarts of idology 08 and idology07. Note that Danil is currently tasked with setting up idology07 so you can coordinate with him about restarting the server.



06-Aug-2008 at  4:45pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 3 - Pending 2 - Work In Progress

Let's use ssl access for idology07 and idology08. Let's coordinate with Magnus when we're ready to convert the webdb user to ssl only. We'll need Jason's help to edit the my.cnf as well.



04-Aug-2008 at  4:11am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Monday , August 11, 2008

Pending responses from IDL


25-Jul-2008 at  1:11am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Monday , August 04, 2008



14-Jul-2008 at  5:48am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Thursday , July 24, 2008



07-Jul-2008 at  9:14am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Monday , July 14, 2008



03-Jul-2008 at  8:08am (GMT -05)Santhinesh Kumar
Severity: 3 - Green
Can we start working on configuring SSL access in idology08 ? The ssl is not enabled here we need to start it with --ssl option. We have installed the mysql binary version so it should be there built-in . We just need to test after starting it with ssl option. 

mysql> show global variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

mysql>

Gone through the http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html document for sometime to create sample keys and certificates.

[pythian@idology08 santhinesh]$ openssl genrsa 2048 > test-key.pem
Generating RSA private key, 2048 bit long modulus
...........................................................................+++
..................................+++
e is 65537 (0x10001)
[pythian@idology08 santhinesh]$ openssl req -new -x509 -nodes -days 1000 \
>          -key test-key.pem > test-key.pem
unable to load Private Key
12990:error:0906D06C:PEM routines:PEM_read_bio:no start line:pem_lib.c:644:Expecting: ANY PRIVATE KEY
[pythian@idology08 santhinesh]$ openssl genrsa 2048 > test-key.pem
Generating RSA private key, 2048 bit long modulus
.......................................................................................+++
.............+++
e is 65537 (0x10001)
[pythian@idology08 santhinesh]$ openssl req -new -x509 -nodes -days 1000          -key test-key.pem > test-cert.pem
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) [GB]:IN
State or Province Name (full name) [Berkshire]:Andra Pradesh
Locality Name (eg, city) [Newbury]:Hyderabad
Organization Name (eg, company) [My Company Ltd]:PYTHIAN
Organizational Unit Name (eg, section) []:india
Common Name (eg, your name or your server's hostname) []:pythian
Email Address []:pythian@pythian.com
[pythian@idology08 santhinesh]$
[pythian@idology08 santhinesh]$
[pythian@idology08 santhinesh]$
[pythian@idology08 santhinesh]$
[pythian@idology08 santhinesh]$ ls -ltrh
total 40K
drwxrwxr-x 2 pythian pythian 4.0K May 16 00:44 migration
-rw-rw-r-- 1 pythian pythian 1.6K May 16 03:04 tables.txt
-rw-rw-r-- 1 pythian pythian 1.5K May 16 03:04 tables1.txt
-rw-rw-r-- 1 pythian pythian 1.7K Jul  3 11:46 test-key.pem
-rw-rw-r-- 1 pythian pythian 1.7K Jul  3 11:48 test-cert.pem



24-Jun-2008 at  9:39am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Friday , July 04, 2008



06-Jun-2008 at  7:24am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Tuesday , June 24, 2008



03-Jun-2008 at 12:36am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Tuesday , June 10, 2008



12-May-2008 at 11:20am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Monday , June 02, 2008



08-May-2008 at  2:53pm (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Friday , May 23, 2008



05-May-2008 at  5:32pm (GMT -05)Santhinesh Kumar
Severity: 3 - Green
Gone through http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html for ssl connection variables


05-May-2008 at  9:18am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Thursday , May 08, 2008



28-Apr-2008 at 10:36am (GMT -05)Sheeri K. Cabral1 MinutesSeverity: 3 - Green
spoke with Magnus just now, he'd like to start with idology08 (but this is a lower priority ticket).


23-Apr-2008 at 11:44am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Saturday , May 03, 2008



16-Apr-2008 at  2:31pm (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Wednesday, April 23, 2008



09-Apr-2008 at  6:21am (GMT -05)Santhinesh KumarSeverity: 3 - Green
CR Modified
Variable Old value New value
CR Status 1 - New 3 - Pending

pending IDL letting us know which db to start with



01-Apr-2008 at  6:07am (GMT -05)Santhinesh KumarSeverity: 3 - Green
Status Review Reminder: Wednesday, April 16, 2008



31-Mar-2008 at 12:47pm (GMT -05)Santhinesh Kumar
Severity: 3 - Green
Going through the http://dev.mysql.com/doc/refman/4.1/en/secure-using-ssl.html....


31-Mar-2008 at 11:47am (GMT -05)Santhinesh Kumar
Severity: 3 - Green
Gone through the CR...


31-Mar-2008 at 11:37am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
CR Modified
Variable Old value New value
DBA Sheeri K. Cabral Santhinesh Kumar


31-Mar-2008 at  9:22am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Monday , March 31, 2008

pending IDL letting us know which db to start with


24-Mar-2008 at 11:27am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Sunday , March 30, 2008

pending IDL letting us know which db to start with.


24-Mar-2008 at 10:18am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Monday , March 24, 2008



20-Mar-2008 at 11:19am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Sunday , March 23, 2008



19-Mar-2008 at 12:36pm (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Wednesday, March 19, 2008



18-Mar-2008 at  9:38am (GMT -05)Sheeri K. Cabral2 MinutesSeverity: 3 - Green
Magnus,

We'll need to restart the databases once we get things configured.

Which database can we do first, that will be least impacting? Our records show:

idology03 production master
idology04 production slave
idology06 staging
idology07 qa
idology08 new staging

I'd guess "qa" might be the least impacting....but please let us know. Also please let us know what coordination, if any, we'll need on this first database we're working on, as we will likely need to restart the mysql instance.


18-Mar-2008 at  9:17am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Tuesday , March 18, 2008



14-Mar-2008 at 11:25am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Monday , March 17, 2008



13-Mar-2008 at  4:51pm (GMT -05)Sheeri K. Cabral2 MinutesSeverity: 3 - Green
Which database can we do first, that will be least impacting? Our records show:

idology03 production master
idology04 production slave
idology06 staging
idology07 qa
idology08 new staging

I'd guess "qa" might be the least impacting....but please let us know. Also please let us know what coordination, if any, we'll need on this first database we're working on, as we will likely need to restart the mysql instance.


13-Mar-2008 at  4:49pm (GMT -05)Sheeri K. Cabral13 MinutesSeverity: 3 - Green
Mostly it's just following the guide at:

http://dev.mysql.com/doc/refman/4.1/en/secure-using-ssl.html

The good news is you already have the correct version with ssl compiled in:

[16:01:06] pythian@idology04.idologylive.com -- ** PRODUCTION SLAVE **
/home/pythian> mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11394 to server version: 4.1.20-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%ssl%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
+---------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

So basically it is just some configuration parameter changes and restarts, if it all goes well.


13-Mar-2008 at  9:10am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Thursday , March 13, 2008



12-Mar-2008 at  9:25am (GMT -05)Sheeri K. CabralSeverity: 3 - Green
Status Review Reminder: Wednesday, March 12, 2008



11-Mar-2008 at  4:15pm (GMT -05)Idology General AccountSeverity: 3 - Green
This CR was created by: Idology General Account