First lets create a small Camel database with couple of tables on our server.mydomain.com host using the following script:<\/p>\n
\r\ncreate database camel;\r\ngrant usage on camel.* to 'camel'@'%' identified by '[camel-password]';\r\ngrant all privileges on camel.* to 'camel'@'%' identified by '[camel-password]';\r\nCREATE TABLE camel.aggregation (\r\n id varchar(255) NOT NULL,\r\n exchange longblob NOT NULL,\r\n constraint aggregation_pk PRIMARY KEY (id)\r\n);\r\nCREATE TABLE camel.aggregation_completed (\r\n id varchar(255) NOT NULL,\r\n exchange longblob NOT NULL,\r\n constraint aggregation_completed_pk PRIMARY KEY (id)\r\n);\r\nflush privileges;\r\n<\/pre>\nNow we need to enable SSL in our MySQL server so we can have encrypted communication with the clients, which for example are not in the same VPC as the server. At the moment we have:<\/p>\n
\r\nmysql> show variables like '%ssl%';\r\n+---------------+------------------------------------------------+\r\n| Variable_name | Value |\r\n+---------------+------------------------------------------------+\r\n| have_openssl | DISABLED |\r\n| have_ssl | DISABLED |\r\n+---------------+------------------------------------------------+\r\n2 rows in set (0.00 sec)\r\n<\/pre>\nWe setup our SSL certificates under
\/etc\/mysql\/ssl\/<\/code> directory and we set in the config file\/etc\/mysql\/conf.d\/encompass.cnf<\/code>:<\/p>\n\r\n[mysqld]\r\n#\r\n# SSL\r\n#\r\nssl=1\r\nssl-ca=\/etc\/mysql\/ssl\/Encompass_CA.pem\r\nssl-cert=\/etc\/mysql\/ssl\/camel.pem\r\nssl-key=\/etc\/mysql\/ssl\/camel.key\r\nssl-cipher=ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-SHA\r\n...\r\n<\/pre>\nand after we restart the server we can see:<\/p>\n
\r\nmysql> show variables like '%ssl%';\r\n+---------------+------------------------------------------------+\r\n| Variable_name | Value |\r\n+---------------+------------------------------------------------+\r\n| have_openssl | YES |\r\n| have_ssl | YES |\r\n| ssl_ca | \/etc\/mysql\/ssl\/Encompass_CA.pem |\r\n| ssl_capath | |\r\n| ssl_cert | \/etc\/mysql\/ssl\/camel.pem |\r\n| ssl_cipher | ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-SHA |\r\n| ssl_crl | |\r\n| ssl_crlpath | |\r\n| ssl_key | \/etc\/mysql\/ssl\/camel.key |\r\n+---------------+------------------------------------------------+\r\n9 rows in set (0.02 sec)\r\n<\/pre>\nwhich means we can start using SSL in our client connections.<\/p>\n
On the client side we first need to set the CA certificate, here I have copied over the same CA:<\/p>\n
\r\nigorc@client:~$ sudo mkdir -p \/etc\/mysql\/ssl\r\nigorc@client:~$ sudo vi \/etc\/mysql\/ssl\/Encompass_CA.pem\r\n<\/pre>\nand to connect via ssl we need to use –ssl-ca<\/cite> option pointing to the CA cert:<\/p>\n
\r\nigorc@client:~$ mysql --silent --ssl-ca=\/etc\/mysql\/ssl\/Encompass_CA.pem -h server.encompasshost.com -P 3306 -u camel -p[camel-password] camel\r\nmysql> status;\r\n--------------\r\nmysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3\r\n\r\nConnection id:\t\t14058\r\nCurrent database:\tcamel\r\nCurrent user:\t\tcamel@ip-10.18.239.45.ap-southeast-2.compute.internal\r\nSSL:\t\t\tCipher in use is DHE-RSA-AES256-SHA\r\nCurrent pager:\t\tstdout\r\nUsing outfile:\t\t''\r\nUsing delimiter:\t;\r\nServer version:\t\t5.6.21-log MySQL Community Server (GPL)\r\nProtocol version:\t10\r\nConnection:\t\tserver.encompasshost.com via TCP\/IP\r\nServer characterset:\tlatin1\r\nDb characterset:\tlatin1\r\nClient characterset:\tutf8\r\nConn. characterset:\tutf8\r\nTCP port:\t\t33306\r\nUptime:\t\t\t4 hours 10 min 19 sec\r\n\r\nThreads: 4 Questions: 41640 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 65 Queries per second avg: 2.772\r\n--------------\r\n\r\nmysql> show tables;\r\nTables_in_camel\r\naggregation\r\naggregation_completed\r\nmysql> exit\r\n<\/pre>\nThe status line SSL: Cipher in use is DHE-RSA-AES256-SHA<\/cite> confirms that our connection is via SSL.<\/p>\n
Further more, if we wish to restrict access only from some hosts we can do that using client side SSL certificates. In that case on the client we setup the client cert and key under
\/etc\/mysql\/ssl\/<\/code> and change the config accordingly in\/etc\/mysql\/my.cnf<\/code> file:<\/p>\n\r\n[mysql]\r\nssl=1\r\nssl-ca=\/etc\/mysql\/ssl\/Encompass_CA.pem\r\nssl-cert=\/etc\/mysql\/ssl\/mysql-client.pem\r\nssl-key=\/etc\/mysql\/ssl\/mysql-client.key\r\n<\/pre>\nthen on the server side we set the certificate parameters we want to check for the particular client and user by modifying the
\nuser’s GRANT:<\/p>\n\r\nmysql> GRANT ALL PRIVILEGES ON camel.* to 'camel'@'%' IDENTIFIED BY '[camel-password]' REQUIRE X509;\r\n<\/pre>\nwhich means that the client must have a valid certificate but that the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. Further limitations might be applied via one of the following:<\/p>\n
\r\nREQUIRE ISSUER 'issuer'\r\nREQUIRE SUBJECT 'subject'\r\nREQUIRE CIPHER 'cipher'\r\n<\/pre>\nExample:<\/p>\n
\r\nmysql> GRANT ALL PRIVILEGES ON camel.* to 'camel'@'%' IDENTIFIED BY '' REQUIRE SUBJECT '\/C=AU\/ST=New South Wales\/L=Sydney\/O=Encompass Corporation Ltd.\/OU=DevOps\/CN=mysql-client';\r\n<\/camel><\/pre>\n If REQUIRE SSL<\/cite> is used, all connections from that particular user must come via SSL only.<\/p>\n
To manually test the connection before we put all this in the config file we run:<\/p>\n
\r\nigorc@client:~$ mysql --silent --ssl-ca=\/etc\/mysql\/ssl\/Encompass_CA.pem --ssl-cert=\/etc\/mysql\/ssl\/mysql-client.pem --ssl-key=\/etc\/mysql\/ssl\/mysql-client.key -h server.encompasshost.com -P 3306 -u camel -pcamel\r\nmysql> \\s;\r\n--------------\r\nmysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.2\r\n\r\nConnection id:\t\t75548\r\nCurrent database:\tcamel\r\nCurrent user:\t\tcamel@ip-10.18.239.45.ap-southeast-2.compute.internal\r\nSSL:\t\t\tCipher in use is DHE-RSA-AES256-SHA\r\nCurrent pager:\t\tstdout\r\nUsing outfile:\t\t''\r\nUsing delimiter:\t;\r\nServer version:\t\t5.6.21-log MySQL Community Server (GPL)\r\nProtocol version:\t10\r\nConnection:\t\tserver.encompasshost.com via TCP\/IP\r\nServer characterset:\tlatin1\r\nDb characterset:\tlatin1\r\nClient characterset:\tutf8\r\nConn. characterset:\tutf8\r\nTCP port:\t\t33306\r\nUptime:\t\t\t21 hours 28 min 14 sec\r\n\r\nThreads: 5 Questions: 226218 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 65 Queries per second avg: 2.926\r\n--------------\r\n\r\nmysql> show tables;\r\nTables_in_camel\r\naggregation\r\naggregation_completed\r\nmysql> exit\r\n<\/camel><\/pre>\n This means client certificate authentication is working so we go and make the above changes in the client config file. The client certs used have been of course issued and signed by the same CA as are the ones on the server side.<\/p>\n","protected":false},"excerpt":{"rendered":"
First lets create a small Camel database with couple of tables on our server.mydomain.com host using the following script: create database camel; grant usage on camel.* to ‘camel’@’%’ identified by ‘[camel-password]’; grant all privileges on camel.* to ‘camel’@’%’ identified by…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-67","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/67","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=67"}],"version-history":[{"count":10,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/67\/revisions"}],"predecessor-version":[{"id":77,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/67\/revisions\/77"}],"wp:attachment":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=67"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=67"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=67"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}