Mirrored Database: a Technique to Deal with Super User/ Admin

Case 1: IT System Managed by External

1. One day, I had a conversation with a chief financial officer (CFO) of a SOE’s subsidiary (say Property Corp). One of the main revenue sources of his company is parking fee,generated from its huge parking lot in an internatinal airport area. For managing the park lot, Property Corp has a contract with third party (say Motorpark Corp), with a deal that Property Corp will receive a fix percentage (say 20%) of parking admission. As part of the deal, Motorpark Corp will provide all infrastructure and human resources needed to manage the park, including the Parking IT System, IT Management (maintenance and admin), and the operator of computer.
   Property Corp will receive its cut based on Motorpark Corp’s monthly parking report, which generated (output) from the Parking IT System. At first place, Property Corp conviced that the monthly parking report was true, simply because its generated from the system. But, a few months later, the CFO suspicious that the report was not true/being manipulated, because he felt that the report did not corresponded with his observations on the traffic of the parking lot.


2. The larger case similar with above is parking management employed by PEMDA. The PEMDA imposes a tax per parking ticket issued by Private Companies. The PEMDA obtains tax proceeds from the Private and then audit the annual report produced by IT System, which managed by the Private. It is also suspected that the report is manipulated in order to lower the tax proceed paid by the Private.


3. There is also a case in a theme park company. The entrance fee is managed by external party, including the entrance IT system. In order to control the fee calculation made by the external, the company assigned a counter part from internal posted near the entrance point, to make a manual calculation on the amount of visitors. Every day, there is a dispute between the counter party and the external regarding to the amount of visitors.

Case 2: IT System (off shelf softaware) Managed by Internal

A port  company starts utilizing a high technology into its port activities. Every activities at port now is controlled by IT System, including calculating the production, which then used to calculate revenue. Managements believe that company’s revenue is fully controlled and can not be manipulated (once again, that what the IT vendor told). However, in order to avoid service interruption in case of the system can not capture any deviation occured at the field, the company assigned a super user at each of its divisions. The super user has a role as a user and a system admin as well. The risk of this policy is that the super user has ability to change or manipulated the database in order to get money in return from the consumer.

General Problem

When a company is utilizing a high technology system, one of the common attitude expressed by managements is that they presumed that the output of the system can not be manipulated nor compromised. So, when the system says that the last month production was 1,000 units and generated USD1,000,000 income, then the management believe that is the “true fact”. When asked why he/she so confidence with the system, they responded that the IT provider said so. The management never thought that the system database can be compromised by internal party in order  to lower the invoice amount.

Solution

The question arise then how should we do to ensure that our database can not be compromised or manipulated?
Before we try answer that question, one thing we should consider is that many officers/ managers (and also auditors) do not have high competencies at IT area. Moreover, it is the management who hold the responsibility to control the integrity of the database.
By considering its cost, I suggest the following solution:

a.    Perform IT Audit

In a normal situation, IT audit is absolutely a good solution. IT Audit provide an assurance on the integrity of the IT system, including its database/information. But, IT audit may not be effective if there is a super admin/ super user who can change database without leaving any trace. This super admin/ user might able to turnoff/ remove database log, or even change database from backdoor (not via the application).

b.    Utilize database logging.

Database log (transaction log) records all changes made to a database. This log also provide an audit trail to management and auditor. In our case, by analyzing this log, management can identify the activity (who, when, where, and what) occured on a database. 
Different softwares have different platforms/ languages, which make it is not realistic for management (auditor) to call a log by creating a coding. So, when a company acquire/ develop a software, make sure that the software  include a feature to call transaction/ database log, such the following picture.

Transaction Log

The problem arise when a super admin/ user turnoff / remove the database log.

c.    Build a mirrored database

Actualy, mirrored database is a prosedure for backup the database. However, this technique can be used to control the integrity of the database. Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible.
Since the mirror server is located at different place from principal server, then the redo action need an internet connection. Redoing can be set at real time, so the superadmin/ user does not have a chance to manipulate the database. However, realtime mirroring will require a high bandwidth and storage capacity, which means a high cost. To reduce the cost of invesment, a company may set redoing at batch (hourly, or daily). However, this delay increase a risk that the superadmin/user to compromise the database.
The diagram of database mirroring as follows:

Transaction Log

One of SOE has applied this technique (real time mirroring) and found that several staffs in billing division had colluded to manipulate the database, so reduced invoices value billed to customer. The fraud has costed the company at amount of billions rupiah.

Semoga Bermanfaat ...........
PS: Mohon dipersorry kalo english nya ugly, maklum masih belajar, he he ...