Select Page

MySQL Security

Charleste King | | June 26, 2018

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
HIPAA Healthcare records
Sarbanes Oxley Act Retention of financial records for 7 years
FISMA All federal agencies
GLBA Financial Institution client records
FERPA Student 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.

RDMS Implementation

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.

Secure Communication

Communication with the RDMS must be via secure methods. Communication with the host (server) must be via secure methods.

Data Storage

Data must be stored securely. This includes “live” data as well as “data at rest.”

Access/Permissions

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.

Auditing

To be fully compliant, all access must be logged, including activity.

Monitoring

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)


Our Approach

Design

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.

Existing Environments

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

Control Set Correctly?
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
nagios@servername.company.local
   
Ensure ‘super_priv’ Is Not Set to ‘Y’ for Non-Administrative Users (Scored) VALIDATE:
  root@localhost
  username@%
  username@localhost
  replication@servername.company.local
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
  username@%
  replication@servername.company.local
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
Check These:  
Log File Location
log_error /var/log/mysqld.log
log_bin_basename  
relay_log_info_file relay-log.info
slow_query_log_file /var/lib/mysql/va-vm-mysql-01-slow.log
general_log_file /var/lib/mysql/va-vm-mysql-01.log
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
app-ro@%
backup@%
bugs@%
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
root@localhost
Ensure No Replication Users Have Wildcard Hostnames (Scored) YES

Read This Next

When the Data Doesn’t Match Up

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017
oracle , aws

Oracle Cloud vs Amazon Cloud – Which is right for you?

Choosing the right cloud computing vendor for your database needs is difficult. This blog post takes you through the pros and cons of Oracle vs Amazon Cloud.

Megan Elphingstone | July 20, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS