MySQL Auditing

I got a task of auditing user activities for some sensitive database in one of our MySQL database servers. Auditing user activity was a tough task with earlier version of MySQL. We had to go through the slow query log or general log and find out our required data from these two files by scanning through lots of data. Which is obviously not a trivial task. But MySQL started supporting plugin API since MySQL 5.1 version and that changed the game. That leads to the arrival of MySQL AUDIT Plugin, which is a MySQL plugin from McAfee and this plugin provides audit capabilities for MySQL.

So in this example I will show you how to audit user activities like update,delete,drop,truncate for a particular database say okcl_sets_app. For this example I have used Ubuntu 12.04 with MySQL 5.5.24.

First I will show you how to install the plugin and then auditing some user activity without restarting the MySQL server.

Download the MySQL Audit plugin for your version of MySQL from the links provided in the page https://github.com/mcafee/mysql-audit/downloads

We have to copy the plugin file into MySQL’s plugin directory. To find the location of the plugin directory we can use the following command:

The plugin file is available in the zip binary distribution. Extract the zip file

# unzip audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip

The actual plugin file is inside of the lib folder of the extracted zip folder. Copy the plugin file (libaudit_plugin.so) into MySQL’s plugin directory.

# cp ./audit-plugin-mysql-5.5/lib/libaudit_plugin.so /usr/lib/mysql/plugin

Once the plugin file is copied, we can install the plugin using the following command:

Note: The above command requires INSERT privilege for the mysql.plugin table.

The INSTALL PLUGIN command loads and initializes the plugin and makes the plugin available for use. So there is no need to restart the MySQL server.

We may also install this plugin by inserting the following line

plugin-load=AUDIT=libaudit_plugin.so

in the [mysqld] section of MySQL configuration file. But this will require MySQL server restart to load the plugin.

To check whether the plugin has been installed and loaded successfully, we can use the SHOW PLUGINS command and check the line for AUDIT plugin.

We can find the version of our loaded audit plugin using the following command:

Our audit plugin is installed and loaded successfully, now we can see the default values for the configuration system variables of the audit plugin:

Audit plugin writes the auditing activities in JSON format. It supports writing auditing activities directly to a file, or to a unix socket.

Now I will enable JSON file auditing using the dynamic system variable.
audit_json_file: json log file Enable|Disable (1|0)

By default the plugin creates mysql-audit.json file inside MySQL datadir and writes audit trail to this file. We can change the file name and location by changing the audit_json_log_file system variable.

After enabling auditing, the plugin starts auditing all the user activities on the server, which will be large amount of data. We may restrict auditing data by specifying the commands that are to be audited and also we can specify the database/table that we need to audit.

As per my requirement I have to audit update,delete,drop,truncate for the database  okcl_sets_app

For that I will first specify the commands that are to be audited by changing the audit_record_cmds system variable.

Next I will specify the object(s) that I need to audit by changing the audit_record_objs system variables.

All done, we can check the audit settings whether everything is changed as per our requirement

Note: To make our audit configurations to persist across MySQL restart, add the required audit plugin system variables into the [mysqld] section of MySQL configuration file.

All the Audit Plugin’s system variables are available in this page: https://github.com/mcafee/mysql-audit/wiki/Configuration

Now I will update the table named TableName1 in okcl_sets_app database and lets see what we get in the Audit log file

Wow, wealth of information that we can collect transparently .

Diary of a dengue patient

Last few days I had gone through some horrific experience of having dengue fever (which is also called breakbone fever). It was during Diwali season and I was alone in my flat in Kharghar, Mumbai as my wife was away in my hometown. I had just shifted to this flat in Kharghar on Saturday, 26th October 2013 from my previous accommodation in Koparkhairane. Things were very hectic and very stressful because of the shifting and rearranging all the stuffs in the new flat. I was busy with fixing all the things in the new flat as well as work in my office. Suddenly dengue virus came into my blood and every plan and appointments were scattered.

 

Day 1 29th October 2013, Tuesday:

On 29th October, Tuesday after having my supper, I felt like feverish and felt a bit of pain in my body. So that night I decided to take early sleep. I just thought may be because of heavy workload and stress in past 4-5 days, I may be not feeling well. I could not sleep well that night (without knowing that the deadly dengue virus had entered in my body and my immunity system was fighting hard to kick it out of my body) because of the body pain and headache.

 

Day 2  30th October 2013, Wednesday:

After I woke up in the morning, I had full-fledged fever and pains in my back, thigh and hip. Also the headache was also at its best . I mailed to my boss, I am not coming to office as I am not feeling well. The body temperature was 102F, I thought may be I am having some kind of flu as at that time because of changing weather many people were having flu. I thought I should have a paracetamol tablet as the body temperature was not coming down from 102F. I took the paracetamol tablet but it did not give any relieve, my temperature did not come down. Rather the temperature went up to 103F. At the evening I called up my doctor and he asked me to visit his clinic immediately. I walked to his clinic (it was around half a kilometer from my place) and he inspected everything and found that my temperature went up to 104F.
He asked me “how you came to the clinic? anybody accompanied you?”
I said “I walked and I am alone”
He said “ you should be very careful as you are having very high temperature”

He gave me some medicines and asked me to start taking those medicines immediately and if my condition does not improve in next 24 hours then he asked me to do some blood tests. Also he advised me to take lots of fluid like coconut water, Enerzal Energy Drink, juices and glucose water for not getting dehydrated.

I came home and started taking the medicines. I took the paracetamol the doctor gave me, it was around 8PM and I was waiting for the body temperature to come down, but it was not. Around 9.30PM I checked my body temperature and I was touching 105F and in my whole life I never saw my body temperature reaching 105F. I was getting anxious, what should I do? Then I thought, lets wait for 1/2 hour, if temperature does not come down, I will call up my doctor. Fortunately after some time I was sweating and the temperature came down to 101F and the muscle pains and headache also reduced a bit. After that I could take a good sleep.

The whole day, I was not feeling like to eat anything. In the morning I took some wheat flakes, milk and banana, I did not feel like having anything during lunch. Evening I took coconut water, few biscuits and one banana before I took medicines. 

I was badly feeling the need of some company, alas my wife was not with me.

 

Day 3 31st October, 2013, Thursday:

Woke up with pains in my body and headache, checked body temperature and it was around 101F. Had some muesli, milk and one banana. Whole day I was lying on the bed and taking rest. The body pain, headache and temperature was not going away. Around 4PM I called my doctor and told him my condition, he said don’t waste time and do the blood tests immediately. I went to lab and gave blood samples for malaria, jaundice, typhoid, complete blood profile and also for dengue. Around 7PM I got all the reports, except the dengue report. The lab did not have dengue testing machine, so they said it will take 24 hours to get the dengue test report. All the other tests were –ve (that means I am not having malaria, jaundice, typhoid), but my platelet count was on the lower side and it was 1,50,000 (range 1,50,000 – 4,50,000). So seeing my symptoms/condition doctor said may be you are having dengue. So he asked me to repeat the platelet count test next day also. Also he gave stress on having more fluids intake.

My appetite was gone, I was not feeling like to eat something and I was not feeling hungry. I had a glass of watermelon juice, coconut water, some papaya, banana etc. Also I was taking the Enerzal Energy Drink, and glucose water drink.

In the night the body temperature went upto 102F, also I had body pain, headache so I took one paracetamol table. Also I had little pain in my left ear, it was not continuous pain, but it was like pinching me in regular intervals. After taking the paracetamol tablet, I could sleep, but the sleep was not good, many times I woke up in the night and the my back was paining.

As I was alone, so I had to walk to the testing lab to give samples, collecting reports, going to doctor’s clinic etc. I was still feeling that I am not having dengue its normal flu or viral fever I am having.

It was festive mood everywhere and I was fighting with the virus .

 

Day 4 1st November, 2013, Friday:

It was same story in the morning, body pain was there but the temperature was bit down, it was in the range of 100F-101F. In the breakfast had banana, milk and some biscuits. Whole day spent on bed and around 4PM went out to check my platelet count. Evening got the dengue report and it was +ve

DENGUE NS1 ANTIGEN ELISA 42.7

Range:
< 9 Panbio Units -Negative
9 – 11 Panbio Units - Equivocal
>11 Panbio Units - Positive

My platelet count was again 1,50,000 same as previous day.

In the evening I went to my doctor, he advised me to take proper rest and have lots of fluid. Also he asked me to monitor my platelet count daily. He said if platelet count comes down then I may have to get admitted in to the hospital. He asked me not to take tension and stay relaxed.

In the night again I had pains in my ear (it was like pinching in regular interval of say 30 secs) I was sitting in the bed and hoping the pinching going away. After some time the pinching moved from my left ear to left head. I felt like restless, could not sleep, I was around 2AM in the night, I started walking inside my flat, trying to get the pains out of my mind. Somehow around 3AM I could get some sleep.

I was feeling helpless but my morals were not down and decided to fight it out myself.   

            

Day 5 2nd November, 2013, Saturday:

It was 1st day of Diwali festival, and we celebrate it as Goddess Kali Puja. There was a Puja ceremony in the temple of my native home. I was lying on the bed and missing the festival badly and everywhere there were lights and sound of crackers. My body temperature was below 100F and also the body pain reduced quite a bit. As food I had milk, little papaya, one apple, banana etc. also the fluids (coconut water, enerzal, glucose, watermelon juice etc.)   

I tested my platelet count and it was 1,30,000 that day. It was 20,000 less than previous day, I was bit tensed, went to my doctor and he said not much to worry and my condition is much better. He also kept options open for hospitalization, and said as you are alone and nobody is there to look after you, so you may get admitted to the hospital. But problem with the hospitals nearby my place is that, they do no provide patient's food, as I was alone supplying food for me in hospital would have been a problem. Also he strictly asked me not to take hotel spicy food for some days. He said there are two Hospitals in Vashi where I can get food also, but these two hospitals were very expensive. Also Vashi is around 15-20KMs from my place and also I have to go through the mediclaim tensions. So I had decided not to get admitted to the hospital till the time I have strength and can manage myself. 

Some of my relatives and friends suggested that I should take papaya leaf juice, this will increase platelet count. Also few suggested that I should take Giloy (Tinospora cordifolia) juice for increasing platelet count. But for me it was like impossible to find papaya tree in Mumbai, fortunately one of my friend and colleague Mr. Sammeer Rane called me to know about my health, he asked me if I need anything. I told him I need papaya leaf and giloy juice. He was kind and helpful enough to find these two items for me and delivered at my home. I made some juice from papaya leaf and eat 2 teaspoons (it was very very bitter in taste) and kept the remaining juice in the freezer.  Also I tool 20ml of Giloy juice.

Back home my family was very tensed (as they keep listening news of dengue deaths regularlyin TV). Also as I was alone at that time and that was also a big worry for them. I kept telling them I am fine and my condition was very good.

In the night my temperature went up to 101F again, so I took one paracetamol and after that I could sleep.

 

Day 6 3rd November, 2013, Sunday:

It was main Diwali day, so the lab where I do the platelet count check was closed, there was another lab nearby but they were also about to close. Somehow I could convinced the girl in that lab to get my blood sample and give the report in 15 minutes. Platelet count was 1,27,000, which was only 3,000 less than the previous day (may be previously I was doing the test during afternoon hours and that day I did it in the morning hours). I consulted my doctor and he said my condition is very stable and as I do not have high temperature and my body pain and headache had reduced a lot so nothing to worry much. Enjoyed the Diwali festival lying on the bed and watching Chennai Express in my laptop (which tried to increase my headache like the dengue virus).

I started cooking at night and taking boiled rice/daal and mashed potato/egg in supper.

Also I was taking 2 teaspoons of papaya leaf juice twice daily and 15ml of Giloy juice twice daily.

 

Day 7 4th November, 2013, Monday:

Body pain, headache reduced quite a bit and body temperature was under 100F. I was feeling fine but weakness was increasing. I was taking rest and also taking more fluid diet. Checked platelet count and it came down to 1,04,000 (23,000 less than previous day) and panicking I consulted my doctor and he said, you are not having temperature and pains, you are much stable, platelet count will come down, that is normal, you need not to worry. I was worried and panicked but never let it to overcome myself. I kept myself internally strong and never showed anybody that I was worried that day. I thought lets see one more day, if platelet count comes below 1,00,000 next day, then I will get myself admitted into some hospital. Also as I read that the dengue virus stays for almost 7 days, so in my case it was 7 days, so I felt like it may improve from next day. Internally I was feeling much better but platelet count was the only worrying factor.

 

Day 8 5th November, 2013, Tuesday:

I was feeling fine but weakness was not going, neither my appetite improved. I was laying in the bed, watching some movie and passing my time with my mobile. Towards the afternoon I had itching in my hands and legs, after that I saw those areas became reddish and was like rashes. I was afraid and thought may be my platelet count had gone down drastically, immediately went to check my platelet count, the lab where I do the tests was closed, so I went to some other one. I was bit tensed and thinking about hospitalization if platelet count goes down. But after getting the report I was delighted to see my platelet count had increased by 6,000 and now it was 1,10,000. I was very relieved and thanked god. Immediately I called up my wife and informed her and she was also very happy and relaxed.

 

Day 9 6th November, 2013, Wednesday:

Weakness & loss of appetite was the problem, also some itching in my legs and hands. Otherwise I was fine and there was no sign of headache, body pain and temperature. Checked platelet count and it went up to 1,35,000 (increased by 25,000). I consulted my doctor and he said, your recovery is on the right track. Now you do not need to check your platelet daily, check it after 2-3 days. Also he asked me to be in rest and take proper food.

 

Day 10-11 7th-8th November, 2013, Thursday-Friday:

Rest, rest and rest………

 

Day 12 9th November, 2013, Saturday:

Checked platelet count and it was 2,01,000 well above the minimum level. Stopped taking papaya leaf juice as my platelet count came to normal level. Weakness was there but I felt that dengue recovery is well on course.

I thank god for taking me out of this situation and recovering me. As I was alone during that time, I felt like god was with me who gave me strength and power to overcome the difficulties.  

Hands On: Deploying SSL certificate in Glassfish

In this hands on I will show you how to deploy comodo 90 days trial SSL certificate in Glassfish server.

For this I will mainly use java keytool command. First I will create the private key and a separate KeyStore (we may use the default keystore.jks KeyStore of glassfish) for this example.

The command is:

keytool -genkey -keysize 2048 -genkey -alias pranabtest.co.in -keyalg RSA  -keystore serverkeystore.jks

Suppose my site is pranabtest.co.in and I kept the alias for the private key same as my domain name. The command will ask for the password of this KeyStore, keep it same as the glassfish master password. Enter all the required information and  keep the private key’s password same as the KeyStore password to avoid any future issues.

We can view the keys in the KeyStore using the following command:

keytool -list -keystore serverkeystore.jks

Now I will generate CSR (certificate signing request) and submit it to receive our 90 days trial SSL certificate from comodo (or some other CA).

keytool -certreq -alias pranabtest.co.in -keystore serverkeystore.jks –file pranabtest.csr

The above command will create a file named pranabtest.csr. Copy the content of the file and submit it in the comodo site’s Provide your CSR page.

After finishing some steps, I will receive the SSL certificate, CA root and intermediate certificates in a zip file in my mail (e.g. pranabtest_co_in.zip).

This zip file contains five files: AddTrustExternalCARoot.crt, ComodoUTNSGCCA.crt, EssentialSSLCA_2.crt, UTNAddTrustSGCCA.crt and my site’s certificate pranabtest_co_in.crt

We have to import all the files into our KeyStore:

First I will import the CA root certificate. If this command returns that this certificate is already exists, then select no

keytool -import -alias root -keystore serverkeystore.jks -trustcacerts -file AddTrustExternalCARoot.crt

 

Then I will import the three intermediate CA certificates

keytool -import -alias comodo -keystore serverkeystore.jks -trustcacerts -file ComodoUTNSGCCA.crt
Certificate was added to keystore

keytool -import -alias essential -keystore serverkeystore.jks -trustcacerts -file EssentialSSLCA_2.crt
Certificate was added to keystore

keytool -import -alias utn -keystore serverkeystore.jks -trustcacerts -file UTNAddTrustSGCCA.crt
Certificate was added to keystore

Finally I will import the SSL certificate for my site:

Here remember to keep the same name for the alias (as the private key alias we created with the KeyStore), otherwise the validation chain will not get completed.

keytool -import –alias pranabtest.co.in -keystore serverkeystore.jks -trustcacerts –file pranabtest_co_in.crt
Certificate reply was installed in keystore

So our certificate installation is finished, now we have to tell Glassfish to use this certificate.

I changed the SSL settings for my 2nd http listener, in Certificate NickName enter the alias for our certificate (i.e. pranabtest.co.in) and also the Key Store name.

 

*******************************************************************************

2 minutes break story:

Once I made a mistake, I imported my site’s certificate with a different alias. Say I created my private key with alias pranabtest.co.in and imported my site’s certificate with alias pranabtest.public. I used the alias for my certificate pranabtest.public in Certificate NickName field of Glassfish HTTP listener’s SSL configuration. But I started getting the following error in my server.log

SSL support could not be configured!
java.io.IOException: SSL configuration is invalid due to No available certificate or key corresponds to the SSL cipher suites which are enabled.

and

ProtocolChain exception java.lang.NullPointerException

The solution was to delete my site’s certificate from the KeyStore (i.e. delete pranabtest.public) and import it again (with alias pranabtest.co.in).

End of 2 minutes break story

*******************************************************************************

 

Next I will stop the Glassfish domain, and take backup of the domain.xml file. Then replace all occurrence of s1as (certificate nickname) with pranabtest.co.in which is the certificate alias and keystore.jks with my new KeyStore serverkeystore.jks in domain.xml file.

Start Glassfish and open the site using the secured port.

Glassfish Monitor

I have written a Glassfish Monitor (using shell scripting, PHP and MySQL), to help myself to monitor the Glassfish Application server’s resource usage as well as the server resource usage.

The setup of this monitor is very simple, just unzip the file and copy the files to some folder say /root/server_monitor folder.

After copying, run the setup_glassfish_monitor.sh script as root user using the command:

# sh setup_glassfish_monitor.sh

The script will ask for Glassfish admin username and password.

The setup script adds a cron job, which is scheduled to run every 15 minutes and to collect the server statistics and add it to some centralized MySQL database server.

Types of Monitoring:

The tool can do the following types of monitoring:

1) Checking whether Glassfish process is running, and sending email alert if the process is not running.

2) Checking High Memory usage and if memory usage is above a specified threshold, then alert email is sent.

3) Checking High CPU usage and sending email alert if CPU usage is above specified threshold.

4) Checking if Server is swapping and sending alert if the server is swapping


Few more monitoring options I will add in future.

Configurations

We can configure the following parameters in the tool:
1) Change the CPU/RAM utilization % threshold for receiving alerts. Default value is 90%.
2) Change the frequency at which the alert will be sent to the receivers.
Note: The script will run every 5 minutes but if we set $alert_sent_every parameter to 30, then if some issue is detected, then immediately alert will be sent to the receivers but if the issue persists then the next alert will go only after  $alert_sent_every minutes i.e. after 30 minues, not every 5 minutes.

3) Set the alert receivers by editing $mail_ids parameter. Multiple recipients separated by ; (semicolon) .

4) Change the DB server settings in this file, these settings are for storing the server status to the centralized server for reporting/analysis.

 

Latest Monitoring data in web browser

We can monitor the server status from our web browser.

After we open the reporting link for this tool, we have to select the server which we want to monitor, so using this tool we can keep an eye on all the Glassfish servers from a single web page.

Select the server name and click on Save button to load the latest monitoring data for the selected server.

If you select Auto Refresh On, and click save then after every Refresh Interval minutes, the latest monitored data will be loaded in the browser automatically and previous loaded data will be pushed towards the bottom of the browser.

Also we can manually load the latest monitored data for the selected server, using the Refresh button, which is enabled after we select and save a particular server.

In the below screenshot we can see that our auto refreshed data is loaded in the browser, now we can see and compare the latest data with the previously collected data.

 

Quick Overviews

After selecting a server we can see some new links, this links provide quick overview of few monitoring entities:
We can see monitoring data for last 3 hours, (12 monitoring collections, 12x15 Minutes = 3 Hours status)

1) Server CPU Usage :

2) Server’s available free memory

3) Memory Usage by Glassfish process

4) Heap Usage by Glassfish JVM

5) Non-Heap memory used by Glassfish JVM

6) JVM threads created by Glassfish JVM

 

 

 

Reports:

If I click on Menu link, I can see the links for different reports

To create a report you have to

· Select the server

· Select report start date and time

· Select report end date and time

· Select number of records that you want to see in the report page

Click on the Go button to generate the report

1) Free Memory Report:
This report is useful for keeping an eye on memory utilization of the server and finding whether we have sufficient RAM or whether we have over provisioned server RAM.

2) CPU Usage Report
Using this report we can see the CPU usage of the server over a period of time. This will help to estimate the load on the server. Here also we can find the % of CPU used by Glassfish out of the whole server CPU usage.

3) Glassfish Memory Usage Report
Here we can see the memory usages of the Glassfish JVM process.

4) Glassfish Heap Usage Report:
Here we can find the JVM heap usages. Here we can see the free available space in the heap, allocated heap size and usage %. This may help us to size the heap correctly.

5) Glassfish Non-Heap Usage Report:
Here we can monitor the Non-Heap usage of the Glassfish JVM

6) Glassfish Thread Usage Report:
Here we can see the JVM threads statistics.

7) Glassfish Classloading Report
Class Loading statistics can be generated from this report.

This is a small tool but can be very handy.

My next plan is to write a heap sizing advisor (sizing different heap areas Eden Space, two Survivor Spaces and the OldGen space), but all depends on how much free time Almighty will provide me .