Jun 30, 2021 by Ioan Sighiartău

Big DataSecurityBig data Analytics

Big Data Community Challenge #2: Database Administration Challenge

How our tech communities are engaging members while solving complex problems in a fun, technical way

The Cognizant Softvision Big Data community wanted to engage all the community members via a healthy competition, to dive into the most important part of databases: security. 

By taking a look into the transaction log and discovering its secrets with the help of the documented and not-so-well documented functions and queries, each candidate set out to track the mal-intended actions made by a specific user and try to revert them without affecting the changes made by the other innocent users. Lastly, the participants were to come up with a solution that would prevent the same scenario from happening again. 

The contest scenario

Imagine you work for a relatively small company and it is decided that all the information necessary for daily tasks should all be in one place.

That place is an MS SQL database on a local physical server (sql server 2019 developer edition), to which all employees have access and equal rights. The database architecture is a single server with a single database in full recovery mode. A full backup is done once a day and log backups are done several times a day.

There is no other option of high availability or disaster recovery implemented.

One morning, complaints appear that some data had been altered or disappeared suspiciously. By noon, the complaints have multiplied and management decides to detach the database until the problem is identified.

From the discussions with the employees, the theory that the data was altered with malicious intent is circulating.

The management team tries to allocate a budget for a tool that recovers data, but in the meantime it asks the contest participants, as the database experts, to identify what actions user1 has done and if it is possible to recover the data.

The following information is provided:

  • The last Full backup (when the database was fine) – DataMissingDB_Initial.bak
  • Three Log back-ups (Log1, Log2, Log3) and database files as they were before detaching. (DataMissingDB.mdf, DataMissingDB_log.ldf)

Participants were also asked to think of a plan to prevent/limit such an event in the future or to make it easier to identify altered/missing data

At this point, participants begin thinking about changes related to:

  • Security
  • Architecture
  • Custom code
  • Using existing functionalities in the sql server
  • Purchase of software

In the contest scenario, the company management wants a list of pros and cons for each of the above, so that they can make an educated decision.

The Winning Solution

The actions of user1 were to delete a record, to truncate a table and to update two other records. All these actions were mixed together with other similar actions performed by other innocent users.

Out of nine participants, four impeccable solutions emerged. Each of these solutions utilized at core the undocumented functions fn_dblog & fn_dump_dblog, and were able to track the actions of user1 by adventuring into the uncharted waters of the transaction log. However, each of these solutions had a different personal touch. In order to track and revert the malicious actions they restored/attached databases, identified the LSN of each user action, translated it into STOPBEFOREMARK, used different DBCC commands to identify the pages and slots where the data resided and took into consideration all the constraints and table relationships.

The four solutions were implemented by four talented Softvisioners:

Liviu Barbu was the winner of the challenge. Liviu provided a 36-page, well-documented entry that detailed his journey to find out exactly what user1 did, how to revert the changes, how his findings should be communicated to the management team, and several ways to prevent this from happening again.

This Big Data challenge showcased the talents, passion and dedication of our Softvisioners. Discover how our tech communities engaged members while solving complex problems in a fun way through our first challenge – Data Modeling & Analysis.