The Importance of Maintaining Data Integrity and Availability In FileMaker Pro Databases

[Steven H. Blackwell]

[blurb]

[big]

[color:red]The Importance of Maintaining

Data Integrity and Availability

In FileMaker Pro Databases

[/big]

By:

Steven H. Blackwell

Platinum Member, FileMaker Business Alliance

FileMaker 8 Certified Developer

FileMaker 7 Certified Developer

Comprehensive, real-time maintenance of data integrity and data availability in FileMaker Pro databases is an on-going and significant challenge for business owners, database developers, database administrators (DBA’s), and IS/IT managers in organizations of all types and sizes. Whether you are a government agency in the health care field, an airline maintenance department, a small business providing commercial dish-washing facilities for the food services industry, a business trade association or professional society, or a small dry-cleaning business contractor on a military base, you have to be able to rely on the fact that your data will be available when needed and that those data are accurate and complete.

Like the Dark Force from some science fiction movie, there are a host of elements that ceaselessly conspire to attack the availability and integrity of your data day in and day out. And hovering over many database installations are also a myriad of regulatory requirements such as Sarbanes-Oxley, the European Union’s Basel II, HIPAA, Gramm-Leach-Bliley, the Payment Card Industry Data Security Standard (PCI DSS), and the Buckley Act—to name just a few.

What can go wrong? And given that accidents can happen even in the best-regulated of households, when things do go wrong, how can DBA’s and IS/IT people fix them? And given that in many small businesses the business owner is the DBA and lacks formal DBA or IS/IT training, how does that business owner recover from what could be a disaster?

In this paper I will detail a number of errors and events that can occur. I will also explain some concepts for addressing, mitigating, and even reversing those errors. I will focus particularly on the difficult issues of deletion management, of backup reconciliation, both roll-back and roll-forward, and of regulatory compliance.

[/blurb]

[big]Threats to Data Integrity and Availability[/big]

[float:right]

[small] Picture One. Sometimes data can become overheated. [/small]

[/float]

Business critical data are at risk and are expected to remain at significant risk in the future. Both their availability and their integrity are threatened by a variety of agents. Availability, as the term implies, means that the data actually exist in the tables and files where they are supposed to be and that they are not inadvertently or purposefully deleted in unauthorized fashions. Integrity, as the term implies, means that the data are trustworthy, that they conform to the business rules the database should enforce, and that owners of the data can rely on their not having been altered in unauthorized fashions, even by authorized users of the database system.

Threats to data integrity and availability take many forms. Data can be copied or stolen. Laptops with key data can be damaged or stolen. Drives on servers and workstations can crash, and in some instances, literally burn. (See Picture One). Data on those drives are either irretrievably lost or become prohibitively expensive to recover.

A principal objective of a well-designed FileMaker Pro database should be prevention of errors and possible catastrophic losses. Likewise, a principal reason for deploying FileMaker Server is the prevention and mitigation of data integrity and availability errors.

Business owners and IS/IT managers can often be lulled into complacency about managing these risks by the easy lure of partial solutions. Identity and Access Management through Accounts and passwords as well as regularized and automatic backups of data are solutions. But these solutions are partial answers at best.

Backups, for one example, must be reliable, available, protected, and current. And they must actually function to restore data in the event of damage or loss. Yet many organizations fail to test their backups for restorative capability, and they discover only after an incident occurs, that carefully run backups cannot restore data.

Records that are deleted, either accidentally by authorized users or maliciously by disgruntled ones, frequently are lost forever, particularly if they are not covered in any pre-existing backups. Other times, data can be transposed or written that are incorrect, even though they pass various validation tests. These data must be rolled back to assure data integrity.

Authoritative and immutable logs of writes, edits, and deletes in database system are a frequent requirement both of various regulatory frameworks such as Sarbanes-Oxley, HIPAA, and Gramm-Leach-Bliley, as well as of European Union Privacy (Basel II) and PCI DSS standards. Likewise such logging capabilities are requirements of many corporate security policies, and their presence is an important element for qualification of a particular software’s acceptable use in a corporate environment.

In many organizations with professional IS/IT staff and well-defined corporate software policies, authoritative and immutable auditing and transaction tracking as well as roll-back and roll-forward capabilities frequently are core requirements for initial qualification of a software package for entity-wide use and for on-going re-qualification of that software to remain in use. The lack of intrinsic tracking and roll-back or roll-forward capabilities in FileMaker Pro and FileMaker Server can disqualify them for use in many environments.

FileMaker Pro has some indigenous tracking capabilities at the record level for creation and modification of records through the auto-enter options of field definitions. FileMaker Server has a robust automated backup capability, one of the principal purposes for its use. However, field level modification tracking in FileMaker Pro is cumbersome, and it most times requires either elaborate scripting or an entire series of shadow tracking fields utilizing the auto-enter and replace existing value option, frequently utilizing the Evaluate function as part of its field definition. Moreover, tracking the before and after values of any edited data element requires even more complex systems. Adding any of these to a table causes extensive overhead in the file, potentially affecting performance.

When data are deleted in FileMaker Pro systems, and particularly when entire records are deleted, retrieving their contents is next to impossible to do short of a manual re-entry or a re-importation of data from a prior backup. While many FileMaker Pro professional developers take great care and precaution to prevent inadvertent or malicious deletes of records, situations do occur where records can be deleted by accident. Data integrity and data availability in the system both require that these records be able to be restored. The time between when the action occurs and when it is either noticed or reported to the database administrator can be critical in determining whether and how the data can be retrieved.

While FileMaker Server produces excellent backups, in an operational environment there is no immediate or facile method to reconcile any given backup to the last known state of the database when an anomalous event occurs or when it is discovered. When business owners, IS/IT administrators, or others responsible for maintaining FileMaker Pro databases must restore from the last good backup, they are faced with the challenge of reconciling that backup with actions that have happened in the database since the backup was written. [color:red]How do you update a backup?

[float:left]

Figure One. A roll-forward is required to reconcile the last good backup with the actual

state of the databases when an incident occurs. A log must be used to perform this roll-forward operation.

[/float]

They must perform a roll-forward on the backup copy to synchronize its contents with those of the database at the instance of the anomalous occurrence that has necessitated the restoration. If some accident requires the restoration from a backup, any data entered, deleted or modified since the last backup are lost or desynchronized and their accompanying transactions are not recorded. The lack of this roll-forward capability has significant adverse impact on data availability and data integrity.

[big]More Data Integrity and Availability Issues[/big]

A coherent system for unified field level tracking of changes can assist in preserving data integrity and data availability by determining the identity of users who take authorized actions within the database system, especially and particularly editing and deleting records. Such tracking can monitor the before and the after values of a particular action on a particular field of a particular record. This allows for restoration to the correct state of integrity. FileMaker Pro does not have the ability to perform this type activity natively. It is just as important to track the actions of authorized users as it is to prevent unauthorized persons from accessing the databases or to prevent users from promoting or escalating their privileges. In conjunction with strong Identity and Access management controls, this field level tracking provides for non-repudiation of user actions.

Insiders remain the principal threat to data confidentiality, integrity, and availability. In a recent survey of IT professionals 34% identified the careless employee as the greatest insider threat to data integrity and availability. The top six insider threats:

[float:right]

Figure Two. What are the principal insider threats to data integrity and availability?

[/float]

Careless employees[color:gray]2 34%

Negligent employees[color:gray]3 32%

Temporary employees 29%

Disgruntled employees 21%

Terminated employees 19%

Outside Partners 16%

And when the disgruntled employee enters the situation, the instances of sabotage or fraud caused by the intentional, and frequently undetected, alteration of data become a significant issue.

Simple ignorance of how to use a database system can contribute to data integrity and availability problems. Many systems lack multiple levels of undo or redo for data entry. Careless UI design or careless construction of access privileges for a specific role (or class) of users has frequently led to unexpected data substitution or unexpected data deletion or transposition. When these incidents occur, they frequently cannot easily be reversed, or even reversed at all, even if they are immediately recognized.

Roll-backs of systems to a previous, correct data state may be required when extraneous and incorrect data are entered into the database. When the data meet business rule requirements and other validation options, but are still incorrect, roll-backs provide the only viable method short of a manual re-entry of desired changes into a valid backup, to restore the database system to its correct level of integrity.

Access privileges as defined in the Privilege Set attached to the active Account can materially assist in preserving data availability and data integrity by, for example, preventing the inadvertent deletion of data. There is no method however, short of merging selected data into a restored backup, for retrieving data deleted by a user authorized to take that action. Additionally, field by field change history can be cumbersome to maintain.

[big]A Climate Drenched in Regulatory Requirements[/big]

Many companies and organizations operate within the requirements, confines, and restrictions of various regulatory frameworks at national, state, and international levels. Some principal examples include medical practices, financial institutions, mortgage lenders and brokers, K-12 schools, and universities. And most all publicly traded companies are subject to the Sarbanes-Oxley law regarding the integrity of financial data that are reported in their financial audits and other filings. Additionally, the European Union has a wide range of requirements for protection of personally identifiable data and for transmission of such data.

The wide proliferation of databases and the even wider proliferation of multiple Application Interfaces that can access the data these systems store have opened rather wide the doors by which users can be authorized to access these data and to write, edit, or delete them. When coupled with the dramatically increased business criticality and significance these data assume at all levels of business, both large and small, the need for robust systems to protect these data becomes paramount.

From a regulatory compliance standpoint, companies must frequently demonstrate to independent, outside auditors that they are actively monitoring data changes as well as having evidence of that monitoring.

Assuring effective controls[color:gray]4 over database activity (reads, writes, changes, deletes) is a key component of data integrity and therefore of Sarbanes-Oxley compliance as well as of other regulatory schemes. A prescription for failure of such an audit and for an adverse opinion from the auditor is the failure to have a data transaction auditing system in place or the insecure storage and maintenance of the logs such a system generates.

As a result of Sarbanes-Oxley, or of the HIPAA regulations covering various personally identifiable medical and healthcare information, or of Gramm-Leach-Bliley Act requirements for financial institutions, or of PCI DSS requirements for credit card processing, or of Buckley Act requirements for management of student data, many organizations have adopted extensive security policies governing specific steps that must be taken to assure confidentiality, integrity, and availability of digital assets. Additionally many companies or organizations not covered by various regulatory frameworks have also adopted, as a matter of Best Practices, similar requirements. In the not-for-profit sector this often includes the business leagues of regulated industries (501c6), educational, literary or scientific professional organizations (501c3), and foundations (501c9) supported by various businesses or industries.

As earlier indicated, many IS/IT managers or business owners succumb to the siren call of “…we have backups…” when contemplating these issues. Backups of business critical data must be reliable, available, protected, current, and restorative. Many owners and IT managers either do not know how properly to backup FileMaker Pro systems, or they simply do not make such backups.

[big]Summary[/big]

Presently in FileMaker Pro, providing authoritative and immutable logging of data transactions, providing unified field level tracking, enabling multiple re-do’s or un-do’s, or providing a roll-back or roll-forward capability all entail extensive file overhead with shadow fields for audit tracking, auto-enter calculations, deletion signatures, and scripts. Additionally, the reliability of such calculations across files that reference one another (via file references) is questionable. It can also be difficult to assure the integrity of the logs themselves, an absolute requirement for forensic purposes. Business owners, developers, database administrators, and IS/IT Managers can profit by automation and streamlining of these processes both in terms of ease and speed of development and in terms of database deployment management requirements. Such techniques can also aid materially in fulfilling different regulatory mandates.

#######

[color:gray]

1 The top two threats per respondent. Therefore the total will exceed 100%.

2 Careless employees (aware of security policies but ignores them)

3 Negligent employees (ignorant or unaware of policies)

4 Section 404 of Sarbanes-Oxley requires an external auditor’s opinions of the effectiveness of these internal controls.

DataIntegrity.pdf

[Steven Cappiello]

Thank you for this important analysis.. Being one who never has attempted to implement an audit trail, I'm curious to hear how some might suggest the process be improved.

[Steven H. Blackwell]

Being one who never has attempted to implement an audit trail, I'm curious to hear how some might suggest the process be improved.

I will have some more information on this middle of next week, hopefully, if I can get it done.

Steven

[Steven H. Blackwell]

Take a look here for more information on addressing these issues.

Steven