Document toolboxDocument toolbox

MySQL Secure Login

Owned by Marty Goodrich
Last updated: Oct 13, 2023 by David EngblomVersion comment
1 min read

Notes on Installation:

  1. The DISA MYSQL STIG has syntax errors in the cipher strings they require, which caused the server to not start up with SSL support.
  2. They have the tls_ciphersuites comma separated, where-as they should be colon separated in the configuration.
  3. The database user has to be created without a password, just specifying SSL is required. To do this, you have to temporarily disable password validation since it prevents creation of a user without a password.

MySQL Server


  1. Add the openssl-perl package to support CRL download script, enter:

    yum --setopt=install_weak_deps=False install openssl-perl

  2. Run the steps for dod_crl.sh script: dod_crl.sh script

  3. File System Commands

    mkdir -p /var/lib/mysql/tls
chown -R mysql:mysql /var/lib/mysql/tls/
chmod -R 750 /var/lib/mysql/tls/
chcon -u system_u -R /var/lib/mysql/tls/

  4. Add the server cert, key and CA bundle to /var/lib/mysql/tls/

    1. https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html
    2. https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-rsa-files-using-mysql.html
    3. Note that when installing for GSMO the certificates that were auto created by the MySQL install had to be manually overwritten with the new certs to get the server to use them.

  5. Certs should be owned by mysql:mysql with 640 perms

  6. Key should be owned by mysql:mysql with 600 perms

  7. Add the following to /etc/my.cnf

    ssl_fips_mode = ON
ssl_ca = /var/lib/mysql/tls/jitc-ca-bundle.pem
ssl_cert = /var/lib/mysql/tls/path-to-server.pem
ssl_key = /var/lib/mysql/tls/path-to-server.key
ssl_crlpath = /etc/pki/tls/crl/
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3
ssl_cipher = ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:DH-DSS-AES256-GCM-SHA384:DHE-DSS-AES256-GCM-SHA384:DH-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA256:DH-RSA-AES256-SHA256:DH-DSS-AES256-SHA256:ECDH-RSA-AES256-GCM-SHA384:ECDH-ECDSA-AES256-GCM-SHA384:ECDH-RSA-AES256-SHA384:ECDH-ECDSA-AES256-SHA384:AES256-GCM-SHA384:AES256-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:DH-DSS-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DH-RSA-AES128-GCM-SHA256:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DH-RSA-AES128-SHA256:DH-DSS-AES128-SHA256:ECDH-RSA-AES128-GCM-SHA256:ECDH-ECDSA-AES128-GCM-SHA256:ECDH-RSA-AES128-SHA256:ECDH-ECDSA-AES128-SHA256:AES128-GCM-SHA256:AES128-SHA256
tls_ciphersuites = TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_CCM_SHA256:TLS_AES_128_CCM_8_SHA256

  8. Restart the MySQL server and login as root

    mysql -h localhost -uroot -p

use sys;
-- Add the following event schedule to reload the TLS hourly to make sure -- the CRL cache gets updated when the cron downloader loads new CRLs.
SET GLOBAL event_scheduler = ON;
CREATE EVENT update_crl_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Updates CRL cache by reloading TLS hourly.'
    DO
      ALTER INSTANCE RELOAD TLS;

  9. Please note the following values must be substituted with valid data:

    SERVER_NAME

    From MySQL cmdline as root
    UNINSTALL COMPONENT "file://component_validate_password";

CREATE USER 'pcr360_prod'@'SERVER_NAME' REQUIRE SSL;
CREATE USER 'pcr360_test'@'SERVER_NAME' REQUIRE SSL;

INSTALL COMPONENT "file://component_validate_password";

CREATE DATABASE pcr360_prod CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'SERVER_NAME'; CREATE DATABASE pcr360_prod_archive CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'SERVER_NAME';
CREATE DATABASE pcr360_prod_metadata CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod'@'SERVER_NAME';
GRANT USAGE ON *.* TO 'pcr360_prod'@'SERVER_NAME';

CREATE DATABASE pcr360_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'SERVER_NAME';
CREATE DATABASE pcr360_test_archive CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'SERVER_NAME';
CREATE DATABASE pcr360_test_metadata CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test'@'SERVER_NAME';
GRANT USAGE ON *.* TO 'pcr360_test'@'SERVER_NAME';

MySQL Client

  1. Add the openssl-perl package to support CRL download script, enter:

    yum --setopt=install_weak_deps=False install openssl-perl
  2. Run the steps for dod_crl.sh script. dod_crl.sh script

  3. Example client connection from mysql client:

    mysql -h XXX.XXX.XXX.XXX -u pcr360_prod --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/tls/certs/DoDCA59.pem --ssl-cert=/etc/pki/tls/certs/ucsppcrapp01v230417.pem --ssl-key=/etc/pki/tls/private/ucsppcrapp01v230417.key --ssl-cipher=DHE-RSA-AES256-SHA –ssl-crlpath=/etc/pki/tls/crl

PHP/PDO Connection

$pdo = new PDO('mysql:host=XXX.XXX.XXX.XXX;dbname=pcr360_prod', 'pcr360_usr', '', array(
    PDO::MYSQL_ATTR_SSL_KEY    =>'/etc/pki/tls/private/ucsppcrapp01v230417.key ',
    PDO::MYSQL_ATTR_SSL_CERT=>'/etc/pki/tls/certs/ucsppcrapp01v230417.pem',
    PDO::MYSQL_ATTR_SSL_CA    =>'/etc/pki/tls/certs/DoDCA59.pem ',
    PDO::MYSQL_ATTR_SSL_CIPHER    =>'DHE-RSA-AES256-SHA'
    )
);

PCR-360 Config

  1. Here is how to get the driver_options parameters to use based on the PHP PDO parameters they map to.

    php -r "echo PDO::MYSQL_ATTR_SSL_KEY;"

1007

php -r "echo PDO::MYSQL_ATTR_SSL_CERT;"

1008

php -r "echo PDO::MYSQL_ATTR_SSL_CA;"

1009

php -r "echo PDO::MYSQL_ATTR_SSL_CIPHER;"

1011

  2. Apply the PCR-360 configuration with an empty password, and set the correct options for the SSL Key, Cert and CA plus the Cipher to use.

    [prod : default]

resources.multidb.default.host = "SERVERNAME"
resources.multidb.default.username = "pcr360_prod"
resources.multidb.default.password = ""
resources.multidb.default.dbname = "pcr360_prod"
resources.multidb.default.port = "3306"
resources.multidb.default.driver_options.1007 = "/etc/pki/tls/private/pcr360_prod.key"
resources.multidb.default.driver_options.1008 = "/etc/pki/tls/certs/pcr360_prod.crt"
resources.multidb.default.driver_options.1009 = "/etc/pki/tls/certs/ca-bundle.pem"
resources.multidb.default.driver_options.1011 = "DHE-RSA-AES256-SHA"
resources.multidb.default.encryptPassword = 0

[test : prod]

resources.multidb.default.host = "SERVERNAME"
resources.multidb.default.username = "pcr360_test"
resources.multidb.default.password = ""
resources.multidb.default.dbname = "pcr360_test"
resources.multidb.default.port = "3306"
resources.multidb.default.driver_options.1007 = "/etc/pki/tls/private/pcr360_test.key"
resources.multidb.default.driver_options.1008 = "/etc/pki/tls/certs/pcr360_test.crt"
resources.multidb.default.driver_options.1009 = "/etc/pki/tls/certs/ca-bundle.pem"
resources.multidb.default.driver_options.1011 = "DHE-RSA-AES256-SHA"
resources.multidb.default.encryptPassword = 0

  3. With all of that in place, the PCR360 commands to create the SQL scripts that build the database should work

    1. For the GSMO install, the FAPOLICY was preventing the application from using the certificates. This lead to a MySQL connection error. They had to update the fapolicy to allow this.

      php /var/www/pcr360/prod/cli/zfcli.php -a update.create.download -d -e prod -i /var/www/pcr360/configs/pcr360.ini  --js "{\"schema\":\"default\"}" > pcr360_prod.sql
php /var/www/pcr360/prod/cli/zfcli.php -a update.create.download -d -e prod -i /var/www/pcr360/configs/pcr360.ini  --js "{\"schema\":\"archive\"}" > pcr360_prod_archive.sql
php /var/www/pcr360/prod/cli/zfcli.php -a update.create.download -d -e prod -i /var/www/pcr360/configs/pcr360.ini  --js "{\"schema\":\"metadata\"}" > pcr360_prod_metadata.sql

php /var/www/pcr360/prod/cli/zfcli.php -a update.create.download -d -e prod -i /var/www/pcr360/configs/pcr360.ini  --js "{\"schema\":\"default\"}" > pcr360_test.sql
php /var/www/pcr360/prod/cli/zfcli.php -a update.create.download -d -e prod -i /var/www/pcr360/configs/pcr360.ini  --js "{\"schema\":\"archive\"}" > pcr360_test_archive.sql
php /var/www/pcr360/prod/cli/zfcli.php -a update.create.download -d -e prod -i /var/www/pcr360/configs/pcr360.ini  --js "{\"schema\":\"metadata\"}" > pcr360_test_metadata.sql

Loading the Database Tables:

# Production
mysql -h 192.168.204.241 -u pcr360_prod --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/tls/certs/DoDCA59.pem --ssl-cert=/etc/pki/tls/certs/ucsppcrapp01v230417.pem --ssl-key=/etc/pki/tls/private/ucsppcrapp01v230417.key --ssl-cipher=DHE-RSA-AES256-SHA -D pcr360_prod -f < pcr360_prod.sql

mysql -h 192.168.204.241 -u pcr360_prod --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/tls/certs/DoDCA59.pem --ssl-cert=/etc/pki/tls/certs/ucsppcrapp01v230417.pem --ssl-key=/etc/pki/tls/private/ucsppcrapp01v230417.key --ssl-cipher=DHE-RSA-AES256-SHA -D pcr360_prod_archive -f < pcr360_prod_archive.sql

mysql -h 192.168.204.241 -u pcr360_prod --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/tls/certs/DoDCA59.pem --ssl-cert=/etc/pki/tls/certs/ucsppcrapp01v230417.pem --ssl-key=/etc/pki/tls/private/ucsppcrapp01v230417.key --ssl-cipher=DHE-RSA-AES256-SHA -D pcr360_prod_metadata -f < pcr360_prod_metadata.sql

# Test
mysql -h 192.168.204.241 -u pcr360_test --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/tls/certs/DoDCA59.pem --ssl-cert=/etc/pki/tls/certs/ucsppcrtest01v230417.pem --ssl-key=/etc/pki/tls/private/ucsppcrtest01v230417.key --ssl-cipher=DHE-RSA-AES256-SHA -D pcr360_test -f < pcr360_test.sql

mysql -h 192.168.204.241 -u pcr360_test --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/tls/certs/DoDCA59.pem --ssl-cert=/etc/pki/tls/certs/ucsppcrtest01v230417.pem --ssl-key=/etc/pki/tls/private/ucsppcrtest01v230417.key --ssl-cipher=DHE-RSA-AES256-SHA -D pcr360_test_archive -f < pcr360_test_archive.sql

mysql -h 192.168.204.241 -u pcr360_test --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/tls/certs/DoDCA59.pem --ssl-cert=/etc/pki/tls/certs/ucsppcrtest01v230417.pem --ssl-key=/etc/pki/tls/private/ucsppcrtest01v230417.key --ssl-cipher=DHE-RSA-AES256-SHA -D pcr360_test_metadata -f < pcr360_test_metadata.sql

Help Desk Portal - Email: help@pcr.com - Phone: 616.259.9242