Specialized Auditing Matters - Database Concurrency Controls in a Distributed Environment

Databases stored at multiple sites are deemed to be in a distributed environment. In one configuration, a replicated database copy is stored at all sites, and in another configuration, pieces of a database can be stored in different partitions with each partition stored at one site. Data concurrency and deadlocking problems are usually addressed by a two-step process. First step, before a transaction can read data, it must establish a read-lock on the data item. In like fashion, before a transaction can write to a data item, it must establish a write-lock on the data item. Second step, different transactions are not allowed to establish conflicting locks simultaneously. Essentially, this two-step rule means that two transactions can own read-locks on the same data item, but a read-lock and a write-lock or two write-locks are not permitted at the same time. Until a transaction releases the lock, it cannot establish additional locks. Releasing a lock provides another transaction — the opportunity to obtain control over the data item. For this reason, a transaction must commit its database changes before releasing its locks to avoid inconsistent results.

Database concurrency and deadlock problems can become serious threats to distributed database integrity unless the database management system has appropriate control levels. With replicated and distributed databases, the system must ensure that all accessible database versions are kept in a consistent state. There are some replicated database procedures that require that all data items are locked before update operations proceed. Auditors must determine the locking and updating protocols that ensure data integrity is established and maintained in a distributed environment. Further, it is important that auditors ascertain the procedures by which database administrators handle data error and conflict reports.

Audit Trail Controls
Audit trails or logs are electronic records reflecting the chronology of events occurring in the database or the database definition. Most systems require a complete set of events to be recorded such as, creation, deletions, modifications, and specific records accessed. If audit trails do not exist, it is be impossible to determine how the database arrived at its current state, who retrieved a record or who executed a specific transaction.

There are several important characteristics of audit trails. All transactions must have a unique time stamp confirming that a transaction was directed to the database definition or the database itself. Time stamps identify the unique time that the transaction caused a series of events to take place so a documented history is created. It is important to note that audit trails must record not only the time and the transaction, but also the user account from which the transaction occurred. Auditors must be mindful of the length of time that audit trails must be retained. In many cases, laws and regulations applicable to the specific industry or type of data strictly mandate how long an audit trail will be retained.

Object Reuse
It is important for auditors to address issues concerning object reuse in the database management system and operating system. Operating systems are responsible for deallocating system resources, such as files used to store tables. In order to maintain confidentiality and integrity, data stored in these resources and objects must be zeroed or replaced with random information before being reassigned.

Database Existence Controls
Existence controls in the database subsystem must be able to restore the database in the event of loss or corruption. All backup procedures involving the maintenance of a previous version of the database and corresponding audit trails. Recovery procedures generally take two forms. The first is the current state of the database must be restored if the entire database or a portion of the database is lost or corrupted. This activity involves a "roll-forward operation" where a prior correct version of the database is restored along with the log of transactions or changes that have occurred to the database since its last backup copy was made.

The "roll-back operation" is where the current invalid state and the updates are rolled back undoing the updates that caused the database to be corrupted. The log of database changes is used to restore the database to the prior valid state. Auditors must carefully examine the possibility of fraudulent behavior in rolling back database operations, making changes to the database; allowing the database to process the data, then rolling back the database to its prior state, without error report generation and audit trail recording.