Security is in the forefront of everyone’s minds, especially when it comes to data storage and use. Databases are one component of a secure environment which has its own implementation of security.
Using a largely open source RDMS, such as MySQL, provides its own certification challenges. There is a myriad of criteria out there from Best Practices and PHI/PII Compliance, to full blown HIPPA and FISMA Compliance, and everything in between.
Types of Compliance
There are a number of acts that require various types of security for your data implementation, and they apply to specific types of data and organizations. For Best Practices, we at Datavail look at the most rigorous requirements for all of them, and use that as our checklist.
|Act||What it regulates|
|Sarbanes Oxley Act||Retention of financial records for 7 years|
|FISMA||All federal agencies|
|GLBA||Financial Institution client records|
|PCI-DSS||Credit Card record|
Types of Implementation
For MySQL, prior to an audit, you need to make sure you are using compliant practices.
The version of MySQL you use must be secure, including settings and permissions. It must reside on a secure operating system, and on secure hardware. The host and database permissions should always be locked down as much as possible, while still allowing for proper functioning. For example, don’t allow read/write when only read is required.
Communication with the RDMS must be via secure methods. Communication with the host (server) must be via secure methods.
Data must be stored securely. This includes “live” data as well as “data at rest.”
Permission and access must be closely controlled. This includes using group permissions (e.g. AD/LDAP/PAM) for host (server) access, as well as database access.
To be fully compliant, all access must be logged, including activity.
You must monitor your implementation for security breaches and threats.
Compliant MySQL Implementations
When talking about the environment for your database, you must ensure that each part is secure. Typically, a VPN is used which is not directly accessible to the public. Access to this VPN is severely limited, monitored, and audited. In general, to easily achieve compliance with the applicable requirement (FISMA, PHI, etc.), or your business requirements, you have three primary choices for MySQL: MariaDB, Percona MySQL, and Oracle MySQL Enterprise.
These forks of MySQL allow you to enable plugins for PAM (Active Directory) authentication and Auditing. Additionally, they can use encryption plugins to keep live data safe. Replication and MySQL connections should be using SSL. Regardless of the build of MySQL, full disk encryption should be implemented prior to installing MySQL to fulfill the “encryption at rest” component.
All access to the host and MySQL instance, where possible, should be done using roles and accounts established from your centralized user system (e.g. Active Directory)
When we design a MySQL database environment, we take into account the level of security, the auditing required, data retention requirements, as well as performance, growth, extensibility, and cost. Our number one goal is to fulfill your security requirements while at the same time, maximizing performance and usability, as well as ensuring the environment will grow with you.
When we encounter an existing system that should be compliant to a particular security act, or if the system owners just want to be able to say they are secure, we perform an audit on the database environment. We have an automated audit that will roll through your MySQL database implementation, flagging exceptions, and noting checklist items that pass. Once we have the results of this audit, we review what is lacking, and provide a plan on how to bring the environment more into compliance with the least amount of impact to live systems.
Table 1 – Sample Checklist Results
|Place Databases on Non-System Partitions (Scored)||YES|
|Use Dedicated Least Privileged Account for MySQL Daemon/Service (Scored)||YES|
|Disable MySQL Command History (Scored)||NO|
|Verify That the MYSQL_PWD Environment Variables Is Not In Use (Scored)||YES|
|Disable Interactive Login (Scored)||NO|
|Verify That ‘MYSQL_PWD’ Is Not Set In Users’ Profiles (Scored)||MANUAL CHECK|
|Ensure ‘datadir’ Has Appropriate Permissions (Scored)||YES|
|Ensure ‘log_bin_basename’ Files Have Appropriate Permissions (Scored)||NO|
|Ensure ‘log_error’ Has Appropriate Permissions (Scored)||YES|
|Ensure ‘slow_query_log’ Has Appropriate Permissions (Scored)||N/A|
|Ensure ‘relay_log_basename’ Files Have Appropriate Permissions (Scored)||N/A|
|Ensure ‘general_log_file’ Has Appropriate Permissions (Scored)||N/A|
|Ensure SSL Key Files Have Appropriate Permissions (Scored)||MANUAL CHECK|
|Ensure Plugin Directory Has Appropriate Permissions (Scored)||YES|
|Ensure ‘local_infile’ Is Disabled||YES|
|Ensure ‘mysqld’ Is Not Started with ‘–skip-grant-tables’ (Scored)||N/A|
|Ensure ‘–skip-symbolic-links’ Is Enabled (Scored)||N/A|
|Ensure the ‘daemon_memcached’ Plugin Is Disabled (Scored)||YES|
|Ensure ‘secure_file_priv’ Is Not Empty (Scored)||N/A|
|Ensure ‘sql_mode’ Contains ‘STRICT_ALL_TABLES’ (Scored)||N/A|
|Ensure ‘file_priv’ Is Not Set to ‘Y’ for Non-Administrative Users (Scored)||YES|
|Ensure ‘process_priv’ Is Not Set to ‘Y’ for Non-Administrative Users (Scored)||NO|
|Ensure ‘super_priv’ Is Not Set to ‘Y’ for Non-Administrative Users (Scored)||VALIDATE:|
|Ensure ‘shutdown_priv’ Is Not Set to ‘Y’ for Non-Administrative Users (Scored)||YES|
|Ensure ‘create_user_priv’ Is Not Set to ‘Y’ for Non-Administrative Users (Scored)||YES|
|Ensure ‘grant_priv’ Is Not Set to ‘Y’ for Non-Administrative Users (Scored)||YES|
|Ensure ‘repl_slave_priv’ Is Not Set to ‘Y’ for Non-Slave Users (Scored)||VALIDATE: root@localhost|
|Ensure DML/DDL Grants Are Limited to Specific Databases and Users (Scored)||MANUAL CHECK|
|Ensure ‘log_error’ Is Not Empty (Scored)||YES|
|Ensure Log Files Are Stored on a Non-System Partition (Scored)||MANUAL CHECK|
|Ensure ‘log_error_verbosity’ Is Not Set to ‘1’ (Scored)||N/A|
|Ensure ‘log-raw’ Is Set to ‘OFF’ (Scored)||N/A|
|Ensure ‘sql_mode’ Contains ‘NO_AUTO_CREATE_USER’ (Scored)||N/A|
|Ensure Passwords Are Set for All MySQL Accounts (Scored)||YES|
|Ensure ‘default_password_lifetime’ Is Less Than Or Equal To ’90’ (Scored)||N/A|
|Ensure Password Complexity Is in Place (Scored)||MANUAL CHECK|
|Ensure No Users Have Wildcard Hostnames (Scored)||NO|
|Ensure No Anonymous Accounts Exist (Scored)||YES|
|Ensure ‘have_ssl’ Is Set to ‘YES’ (Scored)||NO|
|Ensure ‘ssl_type’ Is Set to ‘ANY’, ‘X509’, or ‘SPECIFIED’ for All Remote Users (Scored)||NO|
|Ensure ‘MASTER_SSL_VERIFY_SERVER_CERT’ Is Set to ‘YES’ or ‘1’ (Scored)||N/A|
|Ensure ‘master_info_repository’ Is Set to ‘TABLE’ (Scored)||N/A|
|Ensure ‘super_priv’ Is Not Set to ‘Y’ for Replication Users (Scored)||NO|
|Ensure No Replication Users Have Wildcard Hostnames (Scored)||YES|
Read This Next
Data integrity is of paramount importance to ensure the leadership of an organization can make good business decisions based on insights pulled from accurate data.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.