In general, database security requirements boil down to the following:
• Encryption at rest – usually done via TDE or equivalent - Have Finished this one
too
• Encryption in transit – TLS v1.2 ==> Use my SOP for Setting up SSL In MySQL
• Access management – via AGS
• Logging – there’s a nice tool called Database Access Manager (DAM) by McAfee. I
believe we have an enterprise license for it
SOP for Setting up SSL In MySQL:
1. ## Generate or create CA certificates
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3660 -key ca-key.pem -out ca.pem
2. ## Create server certificate: steps
openssl req -newkey rsa:2048 -days 3660 -nodes -keyout mysql-server-key.pem -out
mysql-server-req.pem
openssl rsa -in mysql-server-key.pem -out mysql-server-key.pem
openssl x509 -req -in mysql-server-req.pem -days 3660 -CA ca.pem -CAkey ca-key.pem
-set_serial 01 -out mysql-server-cert.pem
3. ## Create client certificate
openssl req -newkey rsa:2048 -days 3660 -nodes -keyout mysql-client-key.pem -out
mysql-client-req.pem
openssl rsa -in mysql-client-key.pem -out mysql-client-key.pem
openssl x509 -req -in mysql-client-req.pem -days 3660 -CA ca.pem -CAkey ca-key.pem
-set_serial 01 -out mysql-client-cert.pem
openssl verify -CAfile ca.pem mysql-server-cert.pem mysql-client-cert.pem
Edit the Config file:
[client]
ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/mysql-client-cert.pem
ssl-key=/etc/ssl/mysql/mysql-client-key.pem
[mysqld]
ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/mysql-server-cert.pem
ssl-key=/etc/ssl/mysql/mysql-server-key.pem
service mysql restart
## A script to do the Job:
[root@tpahotdb opt]# cat sslgen.sh
#!/bin/sh
# Generate SSL keys
##Create a directory that will house all your keys for mysql
##/etc/ssl/mysql
cd /etc/ssl/
mkdir mysql
cd mysql/
echo "====\nCreating the CA certificate\n===="
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3660 \
-subj
"/C=US/ST=Florida/L=Clearwater/O=Clarity.net/OU=Hotfix/CN=tpahotdb.clarity.net/emai
lAddress=sefange@clarityservices.com" \
-key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3660 \
-subj
"/C=US/ST=Florida/L=Clearwater/O=Clarity.net/OU=Hotfix/CN=tpahotdb/emailAddress=sef
ange@clarityservices.com" \
-nodes -keyout mysql-server-key.pem -out mysql-server-req.pem
openssl rsa -in mysql-server-key.pem -out mysql-server-key.pem
openssl x509 -req -in mysql-server-req.pem -days 3660 -CA ca.pem -CAkey ca-key.pem
-set_serial 01 -out mysql-server-cert.pem
# Create the client and server certificates
for target in client server
do
echo "====\nCreating the $target certificate\n===="
openssl req -newkey rsa:2048 -days 3660 \
-subj
"/C=US/ST=Florida/L=Clearwater/O=Clarity/OU=Hotfix/CN=tpahotdb/emailAddress=sefange
@clarityservices.com" \
-nodes -keyout mysql-client-key.pem -out mysql-client-req.pem
openssl rsa -in mysql-client-key.pem -out mysql-client-key.pem
openssl x509 -req -in mysql-client-req.pem -days 3660 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out mysql-client-cert.pem
openssl verify -CAfile ca.pem mysql-server-cert.pem mysql-client-cert.pem
done
cd ..
chgrp -R mysql mysql/
@@@@@@@@@@@@@@@@@@@@@@@@@ 2nd script just creates a CA certificate called ca-
cert.pem as opposed to ca-pem. Edit your my.cnf based on the names of the files.
[root@tpahotdb opt]# cat sslgen2.sh
#!/bin/sh
# Generate SSL keys
##Create a directory that will house all your keys for mysql
##/etc/ssl/mysql
cd /etc/ssl/
mkdir mysql
cd mysql/
echo "====\nCreating the CA certificate\n===="
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3660 \
-subj
"/C=US/ST=Florida/L=Clearwater/O=Clarity.net/OU=Hotfix/CN=tpahotdb.clarity.net/emai
lAddress=sefange@clarityservices.com" \
-key ca-key.pem -out ca-cert.pem
openssl req -newkey rsa:2048 -days 3660 \
-subj
"/C=US/ST=Florida/L=Clearwater/O=Clarity.net/OU=Hotfix/CN=tpahotdb/emailAddress=sef
ange@clarityservices.com" \
-nodes -keyout mysql-server-key.pem -out mysql-server-req.pem
openssl rsa -in mysql-server-key.pem -out mysql-server-key.pem
openssl x509 -req -in mysql-server-req.pem -days 3660 -CA ca-cert.pem -CAkey ca-
key.pem -set_serial 01 -out mysql-server-cert.pem
# Create the client and server certificates
for target in client server
do
echo "====\nCreating the $target certificate\n===="
openssl req -newkey rsa:2048 -days 3660 \
-subj
"/C=US/ST=Florida/L=Clearwater/O=Clarity/OU=Hotfix/CN=tpahotdb/emailAddress=sefange
@clarityservices.com" \
-nodes -keyout mysql-client-key.pem -out mysql-client-req.pem
openssl rsa -in mysql-client-key.pem -out mysql-client-key.pem
openssl x509 -req -in mysql-client-req.pem -days 3660 \
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out mysql-client-cert.pem
openssl verify -CAfile ca-cert.pem mysql-server-cert.pem mysql-client-cert.pem
done
cd ..
chgrp -R mysql mysql/
