Why Mcafee Audit Plugin On Ubuntu
Nowadays security is a major concern with each and everything. Here I am a explaining a method by which we can secure and audit our MySQL database much closer using Mcafee Audit Plugin on Ubuntu. Since lot of attacks and intruders are targeting our database it will be key thing to secure your database .
How to Install
Download the Audit Plugin from the following link.
- https://github.com/mcafee/mysql-audit/downloads.
The downloaded file is actually a MySQL plugin file which allows you to perform advanced level auditing with your databases. Please download the Mcafee Audit Plugin on Ubuntu package which suits the architecture of your machine. use command uname -m on your terminal to identify your architecture. I tried it on ubuntu 12.04 with mysql 5.46. If you are using same os and MySQL 5.5. You can download the file from the following location.
After download the plugin copy the libaudit_plugin.so to MySQL plugin dir. To identify your plugin directory got to your mysql prompt and run the following command.
SHOW VARIABLES LIKE 'plugin_dir'; +---------------+---------------------------------+ | Variable_name | Value | +---------------+---------------------------------+ | plugin_dir | /usr/lib/mysql/plugin/ | +---------------+---------------------------------+ 1 row in set (0.00 sec)
copy the plug in file to the corresponding location and gave necessary permissions for that.
In order to work, MySQL Audit Plug-in needs to extract some offsets from MySQL server, some of them are build-in the the Audit code, but some aren’t. It will changes with respect to the mysqld binary. If you are using same version of mine you can give the offsets i retrieved. If you are using an entire different version you have to generate the offset for yours.
Now you have to make necessary changes to MySQL configuration file my.cnf and restart the MySQL.
Add the following lines under the [mysqld] section in your configuration file.
plugin-load=AUDIT=libaudit_plugin.so audit_offsets=6120, 6168, 3792, 4288, 88, 2592, 96, 0, 32, 104 audit_json_file=1
Monitor the Mysqld log while restarting the MYSQL if you met with any checksum error you can add following line to avoid it.
audit_validate_checksum=OFF
f the plugin installation is successful or not the MYSQL will get started. So check the mysqld log very closely. MySQL will get started with or without if you find the following errors on your mysql error log
Audit Plugin: Offsets set didn’t pass validation. audit_offsets: 3816, 3844, 2368, 2700, 44, 1656 .150807 13:02:19 [ERROR] Plugin ‘AUDIT’ init function returned error.
it indicates that the offsets you specified in my.cnf is not matching with your binary. So you have to generate it.
If it start with out any error go to mysql prompt and make sure whether it installed or not. Check the plugins first.
show plugins; You can find the audit plug in at the end of the list.
+--------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| AUDIT | ACTIVE | DAEMON | libaudit_plugin.so | GPL |
+--------------------------+----------+--------------------+--------------------+---------+
24 rows in set (0.00 sec)
show global status like 'AUDIT_version'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | Audit_version | 1.0.8-527 | +---------------+-----------+
by default, a file named mysql-audit.json is created on MySQL datadir, from now one, you can parse the file to extract the information that you think is relevant to you, see bellow some examples:
Get failed attempts to connect to MySQL server:
# grep ‘\”cmd\”:\”Failed Login\”‘ /var/lib/mysql/mysql-audit.json
Get successful attempts to connect to MySQL server:
# grep ‘\”cmd\”:\”Connect\”‘ /var/lib/mysql/mysql-audit.json
Get all activities from a specific host:
# grep ‘\”ip\”:\”192.168.1.32 \”‘ /var/lib/mysql/mysql-audit.json #192.168.1.32 is the ip of host you want to monitor.
Offset generation
If the the given offset are not working for you you have to regenerate it using the offset generation script. You can download the offset generation script from the following link.
chmod +x offset-extract.sh
./offset-extract.sh <path to mysqld>
~# ./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.1.41-community)
{"5.1.41-community","6ccf4357688d8e46bfcb4443966970b0", **6120, 6168, 3792, 4288, 88, 2592, 96, 0, 32, 104**},Then try installing the AUDIT plugin either via plugin-load configuration option (restart) or by issuing the INSTALL PLUGIN statement.
> INSTALL PLUGIN AUDIT SONAME ‘libaudit_plugin.so’;
Note: Some distributions (such as Percona Server) contain the mysqld debug symbols as a separate binary, which requires installing an additional package (rpm/deb). For example for Percona Server on RedHat you will need to install: Percona-Server-55-debuginfo-5.5.30-rel30.1.465.rhel6.x86_64.rpm. Once installed pass to the offset-extract.sh script also the path to the mysqld symbols file. Symbols file must be the same version as the target mysqld. For example:
./offset-extract.sh /usr/sbin/mysqld /usr/lib/debug/usr/sbin/mysqld.debug
Notice that the mysqld debug symbols may be named also: /usr/lib/debug/usr/sbin/mysqld (no .debug). Make sure not to confuse this with mysqld-debug, which is a separate executable. HIRE REMOTE LINUX ENGINEER at affordable rates
Offsets on Debian distributions: On Debian (and Ubuntu) distributions debug symbols are not shipped with the mysql server distribution. There is need to compile from source in order to extract the offsets. Example steps for compiling from source with debug symbols:
- Download source code by running:
apt-get source mysql-server - Extract source:
dpkg-source -x mysql-<version>.dsc - Change into extract dir:
mysql-<version>
Modify debian/rules file to add “-g” flag to CFLAGS and CXXFLAGS passed toDifference before and after editing.
#diff debian/rules debian/rules.org
93c93
< CFLAGS=${MYSQL_BUILD_CFLAGS:-"-g -O2 -DBIG_JOINS=1 ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \
---
> CFLAGS=${MYSQL_BUILD_CFLAGS:-"-O2 -DBIG_JOINS=1 ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \
95c95
< CXXFLAGS=${MYSQL_BUILD_CXXFLAGS:-"-g -O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \
---
> CXXFLAGS=${MYSQL_BUILD_CXXFLAGS:-"-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \
- Build mysql with “nostrip” option: DEB_BUILD_OPTIONS=”nostrip” dpkg-buildpackage -b -uc
- Resulting mysqld file available at: builddir/sql/mysqld now contains debug symbols and you can extract the offsets from this binary.
Use this binary with the offset script and generate the offsets for the respective version.
