Tracking what users are doing on a database that contains secure data is very important. This is not limited to protecting the data, but protecting the systems themselves and their availability.
How many end users have brought a DBMS to a slow crawl with a query that consists of an amalgamation of illogically thought out joins and index avoidances? Accidental data deletions? And various other anomalies that nobody knows what happened, but it is the database’s fault? Maybe I am just lucky, but I have seen it too many times.
The frustrating part of this is when approached the end user denies they did anything. After all, one cannot create an infinite loop with a select statement. That’s just impossible! Data truly does randomly disappear into thin air as well! Happens quite often. These are problems with a solution.
The Linux OS environment makes triaging, validating, and providing absolute root cause to these types of issues fairly easy with a little configuration. This is greatly enhanced when combined with a logging tool like splunk.
The MySQL client creates a history file very much like the .bash_history file aptly named .mysql_history in a users home directory. Just like the bash history file it keeps a history of commands, SQL statements, function calls, and etc.
To complicate things a bit the solution must account for those users that can use sudo and su to change to another user. The solution is to differentiate the users by appending their username to any file that contains their command line history via their profile settings.
This is best completed system wide via a profile.d configuration file by setting the file name in the MYSQL_HISTFILE shell variable. To see a list of all shell variables use the handy Linux command: printenv.
The easiest way to arrive at the end user, arbitrary of any number of su layers is via the LOGNAME shell variable. This can be tested by doing multiple su commands to different users and then using the following statement: echo $(LOGNAME)
The profile.d configuration file is created as /etc/profile.d/<name>.sh for custom configurations and shell settings, where <name> is meaningful for identification of its purpose.
One’s requirements and complexity of their profile configuration file can range broadly, however the following two lines provide a simplified manner to arrive at the desired solution:
As an example, if my username was wcwood, and I change to the root user, every statement that I run after connecting with the MySQL client would be located in the root user’s directory as such:
Depending on someone’s level of access this can certainly be circumvented, which is why employing a tool like splunk, collecting other shell history, and monitoring all for changes is crucial. All layers of the onion.
Many times as a DBA one will be researching anomalies after the fact. After the SQL has ended and the user has disconnected. This makes triaging issues much easier.
Relative War Story:
I remember a particularly nasty instance where someone came to me exclaiming that an entire table of data had miraculously disappeared from a customer’s test environment. The customer had spent many hours painstakingly entering the data and was very upset.
Of course the person delivering this information had no idea what happened and there must be something wrong with the database. I spent many hours restoring multiple RMAN backups. Finally restoring to the end of retention, one more day and it would have all been gone, before finding the data in question. This was a test environment and backup retention was only 30 days.
In this case, not only was I able to find and restore the data, but was also able to determine exactly what happened to it and who did it. The culprit ironically happened to be the same person who came to me with the alarm that there was something wrong with the database, had no idea what happened, and it must be a database issue as data was randomly disappearing.
That person ran a delete statement, minus a where clause, with a commit all at once. Had this person came to me right away I could have recovered it in a few minutes, but instead they chose to sit on the problem, claimed to have no idea what happened, and waited until the customer had gone ballistic. Almost too late!
I then instituted read only access in all customer facing environments moving forward. I should have done it sooner, but hindsight being what it is and trying not to rock the proverbial boat too much as a new manager played a factor.
What could have taken minutes, caused me to lose about 20 hours of my life that I will never get back. All over a test environment.