PCI-DSS is a standard that brings to mind the famous sayings of Jimmy Dugan, the coach of an all-girls baseball team in the movie A League of their own (Played by Tom Hanks):
“It’s supposed to be hard. If it wasn’t hard, everyone would do it. The hard… is what makes it great.”
Well, at least the first part. Whether the banter of it making it ‘great’ is a different story. Most PCI-DSS sufferers will add the word ‘pain’ after the word ‘great’. And, one of the main pains for PCI-DSS is logging and monitoring. That’s requirement 10 for you. So much so that PCI-DSS recently released a document specifically addressing this issue here. So you will be faced with myriads of issues – from the simple to the hard: no we cannot centralise log anything, we do not have logging function in our application, we do not know how to do daily monitoring of our logs, we do not know what to log or how to log, we are all running on DEC VAX from 1974. So many reasons.
One of the challenge we recently faced with the client was that they were using MySQL community version. The challenge was how they can log administrator actions and security INSERTS, UPDATES etc in mysql community version? Logging is totally available in Enterprise, but not the free one – or at least not in its limited form.
Enter Maria-DB Plugin. Now before we go into semantics, MariaDB is an opensource database created by guys who created MySQL. It’s a fork, because MySQL was acquired by Oracle some time back and everyone was afraid that Larry Ellison might gobble MySQL up the way Galactus ate planets. The cutest story here is that MySQL was named after the founder’s daughter – My. And yes, MariaDB is named after his other daughter! But the first daughter’s name is “My”…so it’s like, “Yeah, this is My, My Daughter.”
Anyway. So what we are talking about here is not for them to install MariaDB, but to use it’s ‘plugin’ for MySQL. Make sure the QSA doesn’t get confused on this because ours did and we entered into the twilight zone of communications for a while where nothing made sense.
The Advantages of using MARIA DB AUDIT PLUGINS are:
- Open Source: can be downloaded from: https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/
- Constantly Being Upgraded with the latest version being1.4.0
- Easy to install and use compared to McAfee MySQL plugin or other similar add ons out there
- Support MYSQL and Percona Server
- It has a cute name
So this article, we are going to explain on how we install the plugins in MySQL version 5.6.35 that is based on CentOS 7.
- Download the latest plugin from the links given above and you should see the download directory as below. Choose the latest version. We used server_audit-1.4.0.tar.gz. in centOS. We can use the wget command that is:
wget https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/server_audit-1.4.0.tar.gz
- Extract the tar file by using the command
tar -xvzf <file name>
- Login into MySQL and locate the Plugin Directory of MY SQL using the command below
SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
- Copy the plugin to plugin directory in MySQL based on your linux server (64 bit/32 bit).
-
cp server_audit-1.4.0/linux-x86-64/server_audit.so /usr/lib64/mysql/plugin/
-
chown -R mysql.mysql /usr/lib64/mysql/plugin/server_audit.so
-
- Install the MariaDB Audit Plugin into the MySQL Server by this command inside MySQL
-
INSTALL PLUGIN ‘plugin name’ SONAME ‘filename.so’;
-
- Once Installation is complete, we’ll start the daemon with the following command in the command line:
sudo systemctl start mariadb
- The command systemctl doesn’t display the outcome of all service management commands, so to be sure we succeed, we’ll use the following command:
sudo systemctl status mariadb
If MariaDB has successfully started, the output should contain “Active: active (running)”
-
Next, let's take a moment to ensure that MariaDB starts at boot, using the systemctl enable command, which will create the necessary symlinks: sudo systemctl enable mariadb
- Next, we’ll turn our attention in configuring the syslog FormatSet the Type of Action that will be log (within MySQL)
- Connect: connecting and disconnecting to/from the server will be added to the log. An unsuccessful connect will be logged as a failed connect including the error code.
- Query: full statement including the values will be logged
- Table: Any operation on a table triggered by query will result in an event the MariaDB Audit Plugin can catch to log it directly
SET GLOBAL server_audit_events='CONNECT, QUERY,TABLE';
You need to have root privilege to be able to change the Audit Plugin variables. With this changed we are ready to enable the auditing, which we now will do by using the following command within MySQL:
SET GLOBAL server_audit_logging=ON;
The full set of variables is found on this page: https://mariadb.com/kb/en/mariadb/server_audit-system-variables/
To make the changes to the configuration of the MariaDB Audit Plugin permanent, we now need to add these settings to my.cnf. This ensures that the same configuration will be used after server restart.
Under [mysqld] in my.cnf, add in
server_audit_events=CONNECT, QUERY, TABLE
server_audit_logging=On
There you go, now your MySQL is ready to face the scrutiny of the QSAs in your PCI-DSS compliance program!
Email us at avantedge@pkfmalaysia.com for any enquiries regarding this plugin or PCI-DSS in general and we will get back to you as soon as we can.
