{"id":933,"date":"2009-03-29T08:34:38","date_gmt":"2009-03-29T13:34:38","guid":{"rendered":"http:\/\/www.coresecuritypatterns.com\/blogs\/?p=933"},"modified":"2020-08-08T04:01:37","modified_gmt":"2020-08-08T04:01:37","slug":"demystifying-mysql-security-for-web-20-part-1","status":"publish","type":"post","link":"https:\/\/websecuritypatterns.com\/blogs\/2009\/03\/29\/demystifying-mysql-security-for-web-20-part-1\/","title":{"rendered":"Demystifying MySQL Security for Web 2.0: Part 1"},"content":{"rendered":"

Web 2.0 applications are proliferating and it has become widely popular for delivering dynamic user-generated content, information collaboration, data mashups, social networking and Web services. Building security for Web 2.0 applications pose several daunting challenges<\/a> to Web 2.0 developers as these applications are publicly accessible and it blindly opens door to several intentional\/unintentional abuses and malicious practices including data interception and manipulation by cyber-criminals.  <\/span>Unfortunately, Web 2.0 has no silver bullet or one-size fits all security solution ! Interestingly, the most common Web 2.0 security threats pertain to the inherent flaws with the application design, deployment architecture and its failure to proactively identify the potential application-level risks  and mitigate them with appropriate countermeasures. <\/span><\/p>\n

 <\/p>\n

Lately Web 2.0 application databases have become an easier target for cyber criminals – as it is transparent to user with rich-client applications and draws close proximity to the network perimeter ignoring the traditional logical-tiers of insulation considered with multi-tier architectures (such as Java EE). If we explore the existing Web 2.0 attack patterns and attempt to identify the potential security threats and exploits of Web 2.0 databases, we will find the most common vulnerabilities pertain to the following issues:<\/span><\/p>\n

\u00b7<\/span>         Eavesdropping database connections<\/span><\/span><\/span><\/span><\/p>\n

\u00b7<\/span>         <\/span><\/span><\/span>Un-trusted application clients<\/span><\/p>\n

\u00b7<\/span>         <\/span><\/span><\/span>Insufficient authentication controls<\/span><\/span><\/p>\n

\u00b7<\/span>         <\/span><\/span><\/span>Insecure database access execution privileges<\/span><\/p>\n

\u00b7<\/span>         <\/span><\/span><\/span>Unauthorized disclosure of user account credentials<\/span><\/p>\n

\u00b7<\/span>         <\/span><\/span><\/span>Unauthorized access to application data tables<\/span><\/p>\n

\u00b7<\/span>         <\/span><\/span><\/span>SQL injection or Arbitrary code execution<\/span><\/p>\n

\u00b7<\/span>         <\/span><\/span><\/span>Lack of auditing controls<\/span><\/p>\n

 <\/p>\n

Thus it becomes extremely critical to proactively address the known database security issues by deploying appropriate countermeasures and ensuring confidentiality and integrity of the database including user accounts and stored data.<\/span><\/p>\n

\"\"MySQL is the most popular open-source database and widely popular in Web 2.0 application environments.  <\/span>MySQL is certainly not my forte! Recently, I had my first experiences with MySQL on couple of projects so I ended up digging deeper into MySQL security and churned up  <\/span>the features for use in Web 2.0 applications.  <\/span>Here is my first installment of my hitchhiker\u2019s view on MySQL security and its relevance to Web 2.0 applications.<\/span><\/p>\n

 <\/span><\/p>\n

Securing MySQL database connections<\/h2>\n

 <\/span><\/p>\n

Enforcing confidentiality and integrity of database communications is critical for thwarting eavesdropping and untrusted client connections.  Enabling MySQL connections with SSL\/TLS protocol guarantees transport-layer security and assures that the database communciation is not accessible for unauthorized access and the data exchanged is not modified or altered during transit.<\/span><\/p>\n

 <\/span><\/p>\n

To secure communication between the client and the database server,  <\/span>MySQL supports the use of SSL for ensuring transport-level security using encrypted communication.  <\/span>Since MySQL 5.0.x, MySQL bundles yaSSL  <\/span>(compatible with OpenSSL ) to support SSL and related cryptographic requirements.<\/span><\/p>\n

 <\/span><\/p>\n

Configuring MySQL with SSL\/TLS communication<\/span><\/h3>\n

 <\/span><\/p>\n

If you are using binary versions of MySQL to verify the existence of yaSSL<\/em><\/strong> support, login to the mysql client and try the following:<\/span><\/p>\n

 <\/span><\/p>\n

mysql> SHOW VARIABLES LIKE ‘have_ssl’;<\/span><\/span><\/em><\/p>\n

+—————+——-+<\/span><\/span><\/em><\/p>\n

| Variable_name | Value  <\/span><\/span><\/em>|<\/span><\/span><\/em><\/p>\n

+—————+——-+<\/span><\/span><\/em><\/p>\n

| have_ssl      <\/span>| YES   <\/span>|<\/span><\/span><\/em><\/p>\n

+—————+——-+<\/span><\/span><\/em><\/p>\n

 <\/span><\/span><\/em><\/p>\n

 <\/span><\/p>\n

Incase of using MySQL source distribution and if you want to choose OpenSSL  <\/span>(I strongly recommend OpenSSL as it is FIPS-140 certified) as your SSL provider, you may choose to recompile your MySQL server using  <\/em><\/strong><\/span>\u2013with-openssl<\/em><\/strong><\/span>   <\/span>as configure switches.<\/span><\/span><\/p>\n

 <\/span><\/p>\n

# .\/configure –with-openssl<\/span><\/span><\/em><\/p>\n

 <\/span><\/p>\n

To verify the configuration with OpenSSL<\/span><\/p>\n

 <\/span><\/p>\n

mysql> SHOW VARIABLES LIKE ‘have_openssl’;<\/span><\/span><\/em><\/p>\n

+—————+——-+<\/span><\/span><\/em><\/p>\n

| Variable_name | Value |<\/span><\/span><\/em><\/p>\n

+—————+——-+<\/span><\/span><\/em><\/p>\n

| have_openssl  <\/span>| YES   <\/span>|<\/span><\/span><\/em><\/p>\n

+—————+——-+<\/span><\/span><\/em><\/p>\n

 <\/span><\/p>\n

To establish an SSL communication, you must obtain the SSL certificates from a certificate authority (CA) (recommended) or alternatively you would able generate the certificates using  <\/span>Solaris Key Management Framework (pktool utility) or OpenSSL<\/em><\/strong> (refer my earlier post on <\/span>OpenSSL as CA\/SSL Test Kit: Cheat Sheet<\/span><\/a>) .  <\/span>To enable SSL, MySQL requires the following three certificate files for both server and client (if required):<\/span><\/p>\n

 <\/p>\n

a)<\/span>      <\/span><\/span>CA certificate<\/span><\/span><\/p>\n

b)<\/span>      <\/span><\/span>Server certificate<\/span><\/span><\/p>\n

c)<\/span>      <\/span><\/span>Client certificate<\/span><\/span><\/p>\n

 <\/p>\n

You may append the location of these certificate files in the <\/span>[mysqld]<\/span><\/em> and <\/span>[client]<\/span><\/em> section of the MySQL server configuration file  <\/span><\/span>my.cnf<\/strong><\/span><\/em>.  <\/span>For example:<\/span><\/span><\/p>\n

 <\/span><\/p>\n

\u2026<\/span><\/span><\/em><\/p>\n

[client]<\/span><\/span><\/em><\/p>\n

ssl-ca=\/client-certs\/cacert.pem<\/span><\/span><\/em><\/p>\n

ssl-cert=\/client-certs\/my-new-client-cert.pem<\/span><\/span><\/em><\/p>\n

ssl-key=\/client-certs\/my-new-client-key.pem<\/span><\/span><\/em><\/p>\n

     <\/span>[mysqld]<\/span><\/span><\/em><\/p>\n

ssl-ca=\/certs\/cacert.pem<\/span><\/span><\/em><\/p>\n

ssl-cert=\/certs\/my-new-server-cert.pem<\/span><\/span><\/em><\/p>\n

ssl-key=\/certs\/my-new-server-key.pem<\/span><\/span><\/em><\/p>\n

     <\/span>\u2026<\/span><\/span><\/em><\/p>\n

 <\/span><\/p>\n

Alternatively, you can specify each certificate as a command-line argument to mysqld (server) and mysql (client) environments.  <\/span><\/span><\/span><\/p>\n

 <\/span><\/p>\n

To start the MySQL server daemon with the SSL configuration:<\/span><\/p>\n

 <\/span><\/p>\n

mysqld <\/span><\/span><\/em><\/p>\n

 <\/span>    <\/span>   <\/span>–ssl-ca=cacert.pem <\/span><\/span><\/em><\/p>\n

 <\/span>    <\/span>         <\/span>–ssl-cert=my-new-server-cert.pem <\/span><\/span><\/em><\/p>\n

                    <\/span>-ssl-key=my-new-server-key.pem<\/span><\/span><\/em><\/p>\n

 <\/span><\/p>\n

To start the MySQL client to use SSL, assuming the connecting user has no client certificate authentication requirements:<\/span><\/p>\n

 <\/span><\/p>\n

mysql –ssl-ca=cacert.pem <\/span><\/span><\/em><\/p>\n

 <\/span><\/p>\n

To start the MySQL client, assuming the connecting user is required to provide a client certificate for SSL authentication:<\/span><\/p>\n

 <\/span><\/p>\n

mysql <\/span><\/span><\/em><\/p>\n

   <\/span>  <\/span> <\/span>–ssl-ca=cacert.pem <\/span><\/span><\/em><\/p>\n

          <\/span>–ssl-cert=my-new-client-cert.pem <\/span><\/span><\/em><\/p>\n

                    <\/span>-ssl-key=my-new-client-key.pem<\/span><\/span><\/em><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

To verify configuration and to ensure that the MySQL server uses SSL connection, the status can be checked from the MySQL client using <\/span>ssl_cipher<\/strong><\/span><\/em> status variable.<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

mysql> SHOW STATUS LIKE ‘ssl_cipher’;<\/span><\/span><\/p>\n

+—————+——————–+<\/span><\/span><\/p>\n

| Variable_name | Value              <\/span>|<\/span><\/span><\/p>\n

+—————+——————–+<\/span><\/span><\/p>\n

| Ssl_cipher    <\/span>| DHE-RSA-AES256-SHA |<\/span><\/span><\/p>\n

+—————+——————–+<\/span><\/span><\/p>\n

 <\/span><\/p>\n

Test-driving MySQL SSL connections using JDBC<\/span><\/h3>\n

 To test-drive SSL communication using JDBC, MySQL Connector\/J  (MySQL JDBC driver) supports using SSL communication as long as the MySQL database server and Java client is configured with SSL certificates. To enable JDBC communication with SSL, it requires setting JDBC property requireSSL=true<\/em><\/strong> and useSSL=true<\/em><\/strong>.  In case, if you want to validate the Server certificate you may choose to use verifyServerCertificate=true<\/em><\/strong>.<\/span><\/p>\n

 <\/p>\n

Here is the example code, I tried out :<\/span><\/p>\n

 <\/p>\n

import java.sql.*;<\/em><\/span><\/p>\n

public class TestJDBCoverSSL    {<\/em><\/span><\/p>\n

public static void main(String args[])   { <\/em><\/span><\/p>\n

      Connection mySQLconnection = null;<\/em><\/span><\/p>\n

          try {<\/em><\/span><\/p>\n

                \/\/Register the JDBC driver for MySQL.
\n                 Class.forName(“com.mysql.jdbc.Driver”);<\/em><\/span><\/p>\n

                 String url =  “jdbc:mysql:\/\/localhost:3306\/mysql?&verifyServerCertificate=false&useSSL=true&requireSSL=true”;
\n                 mySQLconnection =  DriverManager.getConnection(url,  “jdbcuser”, “password”);<\/em><\/span><\/p>\n

                 \/\/Print URL and connection information
\n                  System.out.println(“URL: ” + url);
\n                  System.out.println(“Connection: ” + mySQLconnection); <\/em><\/span><\/p>\n


\n<\/em>                 \/\/ Close the connection
\n                    mySQLconnection.close();<\/em><\/p>\n

             }    catch(Exception ex)  {
\n                     ex.printStackTrace();
\n                  }
\n                }
\n     }<\/em><\/span><\/p>\n

More importantly, You need to import the SSL certificates in the Java keystore and then provide the Java keystore location properties where the SSL certificates are stored. You may provide these values as -D Java runtime options on the command line as follows: <\/span><\/p>\n

-Djavax.net.ssl.keyStore=path_to_Java_keystore_file<\/em><\/pre>\n
-Djavax.net.ssl.keyStorePassword=JavaKeyStore_password\n<\/em><\/pre>\n
-Djavax.net.ssl.trustStore=path_to_Java_truststore_file<\/em><\/pre>\n
-<\/em>Djavax.net.ssl.trustStorePassword=JavaTruststore_password<\/em><\/pre>\n
Alternatively, you may incorporate the Java keystore and truststore properties in the JDBC application:<\/p>\n
System.setProperty(\"javax.net.ssl.keyStore\",\"path_to_Java_keystore_file\");\n<\/em><\/pre>\n
System.setProperty(\"javax.net.ssl.keyStorePassword\",\"JavaKeyStore_password\");\n<\/em><\/pre>\n
System.setProperty(\"javax.net.ssl.trustStore\",\"path_to_Java_truststore_file\");\n<\/em><\/pre>\n
System.setProperty(\"javax.net.ssl.trustStorePassword\",\"JavaTruststore_password\");<\/em><\/pre>\n
To summarize, enabling SSL\/TLS based MySQL connections ensure trusted communication between MySQL clients and the database server. It helps thwarting attacks related to eavesdropping MySQL communications, Man-in-the-Middle (MITM), Forged requests and so forth.  In my next post, I will discuss how to secure user accounts and enable stronger authentication controls in MySQL.<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Web 2.0 applications are proliferating and it has become widely popular for delivering dynamic user-generated content, information collaboration, data mashups, social networking and Web services. Building security for Web 2.0 applications pose several daunting challenges to Web 2.0 developers as these applications are publicly accessible and it blindly opens door to several intentional\/unintentional abuses and malicious practices including data interception and manipulation by… Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[17,20,6,9],"tags":[37,48,50,57,62,73],"class_list":["post-933","post","type-post","status-publish","format-standard","hentry","category-database-security","category-java-ee","category-main","category-security","tag-j2ee","tag-mysql","tag-openssl","tag-pki-main","tag-security","tag-web2-0"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts\/933","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/comments?post=933"}],"version-history":[{"count":1,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts\/933\/revisions"}],"predecessor-version":[{"id":2831,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/posts\/933\/revisions\/2831"}],"wp:attachment":[{"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/media?parent=933"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/categories?post=933"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/websecuritypatterns.com\/blogs\/wp-json\/wp\/v2\/tags?post=933"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}