Handling Erroneous or Invalid Transactions in an OLTP System

C.

In an online transaction processing system (OLTP) all transactions are recorded as they occur.

When erroneous or invalid transactions are detected the transaction can be recovered by reviewing the logs.

As explained in the ISC2 OIG: OLTP is designed to record all of the business transactions of an organization as they occur.

It is a data processing system facilitating and managing transaction- oriented applications.

These are characterized as a system used by many concurrent users who are actively adding and modifying data to effectively change real- time data.

OLTP environments are frequently found in the finance, telecommunications, insurance, retail, transportation, and travel industries.

For example, airline ticket agents enter data in the database in real-time by creating and modifying travel reservations, and these are increasingly joined by users directly making their own reservations and purchasing tickets through airline company Web sites as well as discount travel Web site portals.

Therefore, millions of people may be accessing the same flight database every day, and dozens of people may be looking at a specific flight at the same time.

The security concerns for OLTP systems are concurrency and atomicity.

Concurrency controls ensure that two users cannot simultaneously change the same data, or that one user cannot make changes before another user is finished with it.

In an airline ticket system, it is critical for an agent processing a reservation to complete the transaction, especially if it is the last seat available on the plane.

Atomicity ensures that all of the steps involved in the transaction complete successfully.

If one step should fail, then the other steps should not be able to complete.

Again, in an airline ticketing system, if the agent does not enter a name into the name data field correctly, the transaction should not be able to complete.

OLTP systems should act as a monitoring system and detect when individual processes abort, automatically restart an aborted process, back out of a transaction if necessary, allow distribution of multiple copies of application servers across machines, and perform dynamic load balancing.

A security feature uses transaction logs to record information on a transaction before it is processed, and then mark it as processed after it is done.

If the system fails during the transaction, the transaction can be recovered by reviewing the transaction logs.

Checkpoint restart is the process of using the transaction logs to restart the machine by running through the log to the last checkpoint or good transaction.

All transactions following the last checkpoint are applied before allowing users to access the data again.

Wikipedia has nice coverage on what is OLTP: Online transaction processing, or OLTP, refers to a class of systems that facilitate and manage transaction-oriented applications, typically for data entry and retrieval transaction processing.The term is somewhat ambiguous; some understand a "transaction" in the context of computer or database transactions, while others (such as the Transaction Processing Performance Council) define it in terms of business or commercial transactions.

OLTP has also been used to refer to processing in which the system responds immediately to user requests.

An automatic teller machine (ATM) for a bank is an example of a commercial transaction processing application.

The technology is used in a number of industries, including banking, airlines, mailorder, supermarkets, and manufacturing.

Applications include electronic banking, order processing, employee time clock systems, e-commerce, and eTrading.

There are two security concerns for OLTP system:Concurrency and Atomicity ATOMICITY - In database systems, atomicity (or atomicness) is one of the ACID transaction properties.

In an atomic transaction, a series of database operations either all occur, or nothing occurs.

A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright.

The etymology of the phrase originates in the Classical Greek concept of a fundamental and indivisible component; see atom.

An example of atomicity is ordering an airline ticket where two actions are required: payment, and a seat reservation.

The potential passenger must either: both pay for and reserve a seat; OR neither pay for nor reserve a seat.

The booking system does not consider it acceptable for a customer to pay for a ticket without securing the seat, nor to reserve the seat without payment succeeding.

CONCURRENCY - Database concurrency controls ensure that transactions occur in an ordered fashion.

The main job of these controls is to protect transactions issued by different users/applications from the effects of each other.

They must preserve the four characteristics of database transactions ACID test: Atomicity, Consistency, Isolation, and Durability.Read http://en.wikipedia.org/wiki/ACID for more details on the ACID test.

Thus concurrency control is an essential element for correctness in any system where two database transactions or more, executed with time overlap, can access the same data, e.g., virtually in any general-purpose database system.

A well established concurrency control theory exists for database systems: serializability theory, which allows to effectively design and analyze concurrency control methods and mechanisms.

Concurrency is not an issue in itself, it is the lack of proper concurrency controls that makes it a serious issue.

The following answers are incorrect: The transactions should be dropped from processing.

Is incorrect because the transactions are processed and when erroneous or invalid transactions are detected the transaction can be recovered by reviewing the logs.

The transactions should be processed after the program makes adjustments.

Is incorrect because the transactions are processed and when erroneous or invalid transactions are detected the transaction can be recovered by reviewing the logs.

The transactions should be corrected and reprocessed.

Is incorrect because the transactions are processed and when erroneous or invalid transactions are detected the transaction can be recovered by reviewing the logs.

References: Hernandez CISSP, Steven (2012-12-21)

Official (ISC)2 Guide to the CISSP CBK, Third Edition ((ISC)2 Press) (Kindle Locations 12749-12768)

Auerbach Publications.

Kindle Edition.

and http://en.wikipedia.org/wiki/Online_transaction_processing and http://databases.about.com/od/administration/g/concurrency.htm.

In an online transaction processing (OLTP) system, erroneous or invalid transactions are bound to occur. It is essential to have a proper plan in place to handle such transactions to ensure the integrity of the system.

Option A - The transactions should be dropped from processing: This option is suitable in situations where the transaction is entirely invalid or unauthorized. Dropping such transactions would prevent the system from processing them, and the user would be notified of the error.

Option B - The transactions should be processed after the program makes adjustments: This option can be suitable for transactions that contain some errors, but the system can make adjustments to the data and process the transaction.

Option C - The transactions should be written to a report and reviewed: This option can be suitable when the system is not sure if the transaction is erroneous or not. Writing such transactions to a report for review allows for further investigation and analysis.

Option D - The transactions should be corrected and reprocessed: This option is suitable for transactions that contain errors that can be corrected. The user is notified of the error and is allowed to correct the transaction before resubmitting it.

Therefore, option A, B, C, and D all have their use cases depending on the type and severity of the error. It is up to the system's design and the security administrator's policies to determine the best course of action.