Skip to content

Oracle has done a great technical work with MySQL. Specifically a nice job has been done around security. There is one useful feature that exists in Oracle MySQL and that currently does not exist in MariaDB.
Oracle MySQL offers the possibility from within the server to generate asymetric key pairs. It is then possible use them from within the MySQL server to encrypt, decrypt or sign data without exiting the MySQL server. This is a great feature. This is defined as a set of UDF (User Defined Function : CREATE FUNCTION asymmetric_decrypt, asymmetric_encrypt, asymmetric_pub_key … SONAME 'openssl_udf.so';).
Keep on reading!

Oracle MySQL 8.0 has been declared GA but a critical piece is missing …
MySQL 8 is a fantastic release embedding the work of brilliant Oracle engineering.
I will not detail all the great features of MySQL 8 as there are a lot of great presentations around it. https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available/
Keep on reading!

MariaDB 10.1 introduced Data at Rest Encryption. By default we provide a file_key_management plugin. This is a basic plugin storing keys in a file that can be itself encrypted. This file can come from a usb stick removed once keys have been brought into memory. But this remains a basic solution not suitable for security compliance rules.
Keep on reading!

MaxScale is a Proxy for the MySQL protocol built with a modular architecture.
The underlying concept of modules allows to extend the MaxScale proxy services. The current version implements Read Write splitting and Connection Load Balancing. Internally MySQL queries go through a SQL parsing phase. This gives MaxScale great capabilities regarding queries routing.
Keep on reading!

When full auditing is activated with the the MariaDB Audit Plugin a large volume of audit data is generated and it can put an extra burden on the server. The MariaDB audit plugin offers the two following setup variables that allow to restrict what data will be logged.

MariaDB [test]> show variables like '%audit%users';
+-----------------------------+-----------------------+
| Variable_name               | Value                 |
+-----------------------------+-----------------------+
| server_audit_excl_users     |                       |
| server_audit_incl_users     |                       |
+-----------------------------+-----------------------+
14 rows in set (0.00 sec)

These variables allow to restrict the data that is going to be pushed to the audit log.
You can say: I only want to log data from these users

MariaDB [test]> set global server_audit_syslog_incl_users='proxy';

Only activities coming from the user 'proxy' will be logged.

Or you can say : I want to log data except for these users.

MariaDB [test]> set global server_audit_excl_users='user2';

No activities coming from the user 'user2' will be logged.

What happens if a user is both included and excluded ? If a user is both included and excluded database activities for that user will be logged. Include has priority over exclude. It is important to notice that the audit plugin logs data only based on the username. This username is different from the MariaDB and MySQL user definition. For them a user is combination of the username and hostname ('user'@'host').

This capability to filter audit data is crucial to avoid too much burden on the audited server and to avoid generating too much volume of audit data.

By going to the download section of  SkySQL website  some users have noticed "MariaDB Audit Plugin". This auditing feature for MySQL has been requested by more and more customers. Legal constraints make it mandatory for more and more companies to keep logging information about database access and activity.

It is very important for the MySQL community to have an open source audit plugin available. MariaDB team has always stick to the principle of keeping MySQL 100% open source and has developed the MariaDB Audit Plugin according to these principles. The MariaDB Audit Plugin has been developed using the standard MySQL Audit Plugin API. Being based on standard API makes it run both with MariaDB and with Oracle MySQL Server. The MariaDB Audit Plugin also has some unique features only available for MariaDB.

To develop this plugin we have sticked to the principle of listening to our big customer to define the specifications. This is the first version and some more improvements will come. We will be pleased to hear your feedbacks. All bug reports and critics are welcomed. The MariaDB Audit Plugin already covers the main requests that have been expressed. But we have more to come and your improvement requests are welcomed.

The purpose of the MariaDB Audit Plugin is to log the server's activity. Who connected to the server, what queries ran and what tables were touched is stored to a rotating log file. The MariaDB Audit Plugin also supports output to the widely used syslogd system. It offers the option to include/exclude users from auditing.

Oracle has released an audit plugin available through its MySQL Enterprise Server version. This make it mandatory to have an active subscription. The version you run is then a fully closed source server under a commercial license. In that case you cannot rely on third party support like SkySQL or Percona.

The MariaDB Audit Plugin is delivered as a very light shared library that can be very easily downloaded and installed on any MariaDB/MySQL server.

Where to download the MariaDB Audit Plugin ?
http://www.skysql.com/downloads/mariadb-audit-plugin-beta

Source Code of the MariaDB Audit Plugin :
https://code.launchpad.net/~maria-captains/maria/5.5-noga-hf

Pointers :
MariaDB Audit Plugin Getting started guide
MariaDB Audit Plugin documentation

My blog post on other auditing techniques
Oracle MySQL Audit Plugin
Oracle MySQL Audit Plugin doc
Macfee Audit Plugin
Macfee Audit Plugin doc

 

MySQL 5.6 has introduced a set of new features related to security (default randomised root password at install, password Validation Plugin ...). MySQL now also provides a method for storing authentication credentials securely in an option file named .mylogin.cnf.
This new feature has been introduced to avoid exposing password when running MySQL scripts. Its behaviour and limitations have been well described here :
http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/

In all previous version and also in 5.6 if you run a MySQL script without interactively asking for password  the password is exposed on the  commands line and it can be accessed though a "ps" command or through history files.
With MySQL 5.6 it is now possible to transform a command like this :

mysql -hlocalhost -uroot -pmypass

into :

mysql --login-path=safelogin

To create this login-path containing the credential

mysql_config_editor set --login-path=safelogin --host=localhost --user=root --password

The login file must be readable and writeable to the current user, and inaccessible to other users. Otherwise, mysql_config_editor ignores it, and the file is not used by client programs, either.
-rw-------.  1 sfrezefo sfrezefo        152 15 févr. 21:25 .mylogin.cnf

To list the content of this login file you can run mysql_config_editor and through this command you will not see the password :

[sfrezefo@serge ~]$ mysql_config_editor  print --all
[local]
user = localuser
password = *****
host = localhost
[safelogin]
user = root
password = *****
host = localhost

So one of the principal benefits of mysql_config_editor is ease-of-use. But users must realize that even though it uses AES for encrypting the credentials security is not the main point. In fact the key is left on the door you just have to turn it to enter.
I have developed a very basic program "mysql_showconfigpwd" that expose the credentials. This could be useful if you have forgotten your encrypted credentials.

[sfrezefo@serge ~]$ mysql_showconfigpwd
File exists.
file_buff [local]
user = localuser
password = manager1
host = localhost
[safelogin]
user = root
password = manager1
host = localhost

The way mysql_config_editor works is quite simple : All the information associated with a login path is stored encrypted through AES. A random key is generated which is used to encrypt all the login path information (password included).
All these information are stored in a file. But the key used to encrypt the login path is store in clear.
The OS protection is thus the only thing that protect the content of the file. This is the same issue you get with ssh keys except in that case you have the idea of key pair.

This is even more critical because beside the operating system the database itself could get access to the login file.
A database user with the FILE privilege can do a LOAD DATA INFILE and then get access to all the encrypted password. This problem is not an easy one and we always fall back to the egg and hen problem 🙂 .All mechanism based on private key encryption meet these issue of where to sore the key. How does it work with other databases. Oracle 11G has the same concept "Secure External Password Store". The only difference is that being closed source  it create a false feeling of security  🙂

Here is the code of "mysql_showconfigpwd.cc" :

#include "my_config.h"
#include "my_aes.h"
#include "client_priv.h"
#include "my_default.h"
#include "my_default_priv.h"
 
#define MY_LINE_MAX 4096
#define MY_LOGIN_HEADER_LEN (4 + LOGIN_KEY_LEN)
static int g_fd;
static size_t file_size;
static char my_login_file[FN_REFLEN];
static char my_key[LOGIN_KEY_LEN];
 
int main(int argc, char *argv[]) {
 
DYNAMIC_STRING file_buf, path_buf;
char cipher[MY_LINE_MAX], plain[MY_LINE_MAX];
uchar len_buf[MAX_CIPHER_STORE_LEN];
int cipher_len = 0, dec_len = 0, total_len = 0;
MY_STAT stat_info;
const int access_flag = (O_RDWR | O_BINARY);
 
init_dynamic_string(&path_buf, "", MY_LINE_MAX, MY_LINE_MAX);
init_dynamic_string(&file_buf, "", file_size, 3 * MY_LINE_MAX);
 
if (!my_default_get_login_file(my_login_file, sizeof(my_login_file))) {
  fprintf(stderr, "Error! Failed to set login file name.\n");
  goto error;
}
 
if (my_stat(my_login_file, &stat_info, MYF(0))) {
  fprintf(stderr, "File exists.\n");
  file_size = stat_info.st_size;
  if ((g_fd = my_open(my_login_file, access_flag, MYF(MY_WME))) == -1) {
    fprintf(stderr, "Error! Couldn't open the file.\n");
    goto error;
  }
}
 
if (my_seek(g_fd, 4, SEEK_SET, MYF(MY_WME)) != 4) {
  fprintf(stderr, "Error! Couldn't seek 4.\n");
  goto error;
}
 
if (my_read(g_fd, (uchar *) my_key, LOGIN_KEY_LEN, MYF(MY_WME)) != LOGIN_KEY_LEN) {
  fprintf(stderr, "Failed to read login key.\n");
  goto error;
}
 
if (file_size) {
  while (my_read(g_fd, len_buf, MAX_CIPHER_STORE_LEN, MYF(MY_WME)) == MAX_CIPHER_STORE_LEN) {
    cipher_len = sint4korr(len_buf);
 
    if (cipher_len > MY_LINE_MAX) {
      fprintf(stderr, "Error!cipher_len > MY_LINE_MAX.\n");
      goto error;
    }
    if ((int) my_read(g_fd, (uchar *) cipher, cipher_len, MYF(MY_WME)) == cipher_len) {
      if ((dec_len = my_aes_decrypt(cipher, cipher_len,(char *) plain, my_key, LOGIN_KEY_LEN)) < 0) {
        fprintf(stderr, "Error! failed to decrypt the file.\n");
        goto error;
      }
      total_len += dec_len;
      plain[dec_len] = 0;
      dynstr_append(&file_buf, plain);
    }
  }
  fprintf(stderr, "file_buff %s \n", file_buf.str);
}
 
error:
dynstr_trunc(&file_buf, 0);
dynstr_trunc(&path_buf, 0);
my_close(g_fd, MYF(MY_WME));
dynstr_free(&file_buf);
dynstr_free(&path_buf);
 
exit(0);
}

Tu use it install this file in the MySQL source tree (mysql-5.6.8-rc/client in my case).

Add these 2 lines to the CMakeLists.txt in this folder.

MYSQL_ADD_EXECUTABLE(mysql_showconfigpwd mysql_showconfigpwd.cc)
TARGET_LINK_LIBRARIES(mysql_showconfigpwd mysqlclient)

Go to the root of your MySQL source tree and do :

rm CMakeCache.txt
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql56
make -j 8
sudo make install

ezNcrypt offers a table level transparent data encryption solution for MySQL. This technology is purely declarative which mean you declare tables or database you want to encrypt. You then have nohing more to care about. What is nice with ezncrypt is that only the mysqld process can encrypt/decrypt the data. A set o UDF functions have been added to handle that. The key management allows to store the key locally or remotely.

Critotech trough ezNcrypt offers a table level transparent data encryption solution for MySQL.

This linux technology is purely declarative which mean you declare tables or database you want to encrypt.
You then have nothing more to care about.

What is nice with ezncrypt is that only the mysqld process can encrypt/decrypt the data. A set o UDF functions have been added to handle that. The key management allows to store the key locally or remotely.
Using table level transparent data encryption can be an interesting solution for many websites. Encrypting a few tables is often enough and less costly than encrypting the whole database. Most of the time partial encryption makes the whole data totally unusable. Many websites are hosted and data asset protection is a key worry.

Of course there exists many other solutions like file system level encryption or trigger based encryption.
File system level encryption put more burden on the CPU as all data is encrypted. A trigger based encryption solution is quite intrusive and does not solve all security issues.
When implementing encryption it is mandatory to also care about encryption of innodb recovery logs and and mysql binary logs.
It is also necessary to be careful about temporary tables pushed to disk. Swap space encryption should also be taken into account.

Having tested ezncrypt I must confess it is quite simple to setup. It still needs some improvement to be more flexible when dealing with complex configuration : multiple BASEDIR and DATADIR for people having multiple versions and instances of MySQL on the same platform.

Example of database encryption : encrypting the eztest database

[root@serge mysql]# db_encrypt eztest
ezncrypt | Checking system dependencies
mysql | getting information about database file location
...........|> using /var/lib/mysql
...........| Please provide a MySQL username & password
...........| Enter username: root
...........| Enter password:
...........| Looking if database exists
...........| done!
keymgr | Retrieving passphrase from KSS
...........|> Encryption password retrieved from KSS
...........| generating keys
...........| done!
ezncrypt | checking database encryption status
...........| done!
ezncrypt | checking disk space
...........| done!
...........| WARNING: MySQL will be stopped while encrypting data. Continue? (Y/n)
backup | backing up data
...........| This can take a while. Please be patient
...........| Executing /opt/mysql/bin/mysqlhotcopy to backup 'eztest'
...........|> /backup/2009-12-23/eztest
...........| done!
mysql | stopping mysql service
...........| done!
ezncrypt | begin database protection
...........| moving data to encryption directory
...........| This can take a while. Please be patient
...........|> /var/lib/ezncrypt/ezncrypted/mysql/eztest
...........| done!
mysql | starting mysql service
...........| done!
mysql | adding key to mysql process
...........| done!
ezncrypt | congratulations. you have a database ready for ez-encryption!
...........Log Information: /var/log/ezncrypt/db_encrypt.log

ezNcrypth is based on very standard linux technology like ecryptfs