Frequently we are asked about the recovery of data that may have been deleted in an accounting software or other system running under the TAS runtime engine and using the Btrieve/Pervasive database record manager. Issues related to understanding how information might be deleted are in turn related to security issues

Background

The information contained in this technical support memo applies to most database systems and is not specific to issues relating to the TAS development system nor the TAS runtime engine. Few issues discussed are specific/unique to the Btrieve record manager (hereafter the term "Btrieve" will be used to identify any of the various releases of the Btrieve record manager including Btrieve 5.10a, the Btrieve 6.15 Microkernel as well as the various releases to date of Pervasive.SQL). TAS has used Btrieve as its native record manager in versions 3.0 thru 5.1 and initially in the 6.0 release.

In the discussion that follows, a data file means the entire physical file. A data file corresponds to a file descriptor (synonyms include schema, file layout or file definition). A file descriptor may be thought of as a "table" as used in some other non-Btrieve environments. In a TAS environment, information in a data file NEVER corresponds to more than one file descriptor. File descriptors themselves are unique and are defined by programmers. Different physical data files may use the same file descriptor. An example of this is in Advanced Accounting (and DBA) where the pending sales order "header" file is BKARINV.BXX (where XX is company extension, if any) and the posted/invoiced orders are contained in the BKARHINV.BXX file. Both of these data files use the same file descriptor which coincidentally has the layout name of BKARINV.

Inside each data file are individual records. Records are in turn made up of one or more fields as defined in the file descriptor. A BKARINV.BXX file for pending sales order might at any point in time have any number of records corresponding to the number of pending sales orders. Each record will have the exact same number of fields (currently in Advanced Accounting, there are 54 fields per record - that number could change in the future as additional fields may be added, but each record in a data file will always have the exact same records accordingly to the latest file descriptor installed). A field of information within a record cannot be changed or updated without updating (for example saving) the entire record which includes the entire collection of fields belonging to that record. (This is incidentally why the record must be locked in a multi-user environmen before the record can be saved as otherwise two or more users could be accessing the record at the same time and one could overwrite changes made by the other absent a locking mechanism of some kind preventing that.)

How information loss can occur

Here are the primary ways then that records can be deleted:

(1) Deletion of an entire data file. This could happen at a command line/DOS prompt or via the Windows file explorer/navigator. In this situation all records in the file would be lost.

Recovery: Potentially possible using an "undelete" program (e.g. Norton utilities) depending on how quickly action is taken after the deletion occurs and what operating system is involved and what software was installed on the drive where the data file was located prior to deletion.

Prevention: Frequent back-ups, user education, software restrictions/security, desktop/network user rights and policies (note that you cannot remove a user’s ability to delete a file entirely in the application directory because of temporary files that both the Btrieve record manager and the TAS software itself need to be able to create and delete without the user’s knowledge; users should be denied access to delete files however from a command line prompt as well as from explorer and should also be denied access to options within the software that could allow them to issue commands to delete files)

(2) Overwriting the entire data file with another file. This could happen by inadvertently dragging and dropping or by inadvertenent or intentional misuse of COPY, XCOPY or similar operating system commands or by the unintentional restoring of data from a back-up system.

Recovery: Usually none on most systems (potentially possible thru the use of "go back" software previously installed or if a redundant real-time/parallel server is in place - neither of these situations are usually the case)

Prevention: Same as for deletion of an entire data file (on the subject of user education, users should be instructed when sending reports to "disk" which is a potentially dangerous operation to always user their name and a TXT extension or an appropriate extension that is never used by the installed TAS software to avoid conflict within an existing data or program file or even better, have users save anything "printed to disk" in a pre-established folder set-up for that purpose on their local drive or in a network folder)

(3) Deleting an individual record. Deletion of a record within a given file could take place via normal program operation (either something that is an option given to the user or which the program does automatically, for example, when deleting records out of one file after placing them into another during a posting process) or thru a utility provided in the program (for example a purge or archive program or a program such as "maintain database" which allows raw access to the data and the ability to change or delete records). Less commonly deletion of individual records could occur thru use of an outside program or protocol that has the ability to understand Btrieve files.

Recovery: Generally none. Some database management systems simply flag a record that has been marked as deleted so that until it is rebuilt (thru a "pack" or reindex) it is potentially possible to recover the data by individuals who have the requisite expertise or special software tools. Jim Kyle, author of Btrieve Complete, notes that when Btrieve deletes a record, "it overwrites the entire record with binary zeroes in the interest of keeping sensitive information secure" (personal communication, 10/16/01). So in a Btrieve system, once a record has been deleted, the information is gone regardless of whether a reindex has or has not occurred.

Prevention: Similar to (1) and (2) however the ability to delete a record is mostly a user rights issue within the application (TAS) software itself so particular attention needs to be paid to assigning appropriate rights in the software and eliminating programs that users, even users with the highest level of access, might inadvertently use resulting in the loss of data; for highly sensitive information, add audit controls to track deleted records

(4) Overwriting an existing record. Normally this may occur thru normal program operation when simply updating information however it could also occur thru unintentional or misuse of data maintenance utilities.

Recovery: Generally none.

Prevention: Similar to the others with emphasis on user education and training; audit controls to track changed records can be used to track what information may have been changed

More issues with respect to information loss and data file security

Information loss related to all of these items can occur or be triggered by several other general conditions that warrant further discussion: remote access, employee dishonesty and file corruption.

Increasingly users are accessing their data remotely by remote control software (most commonly PcAnywhere) or via more multi-user robust options such as by Citrix or Microsoft’s Terminal Server. There are Advanced Accounting users accessing their internal accounting systems using wireless laptops. Further the increasing available of inexpensive, high-speed connections (DSL), means that PC’s with static IP addresses are potentially accessible whenever they are up and connected to the network. While the advantages of this relatively new kind of remote access and connectivity generally outweigh the disadvantages, they pose many new problems for controlling data deletion and theft.

Current/former employee dishonesty combined with the fact that access can occur to the system from an unsupervised office or remote location and fall into the hands of users who have not been given access spells additional trouble. Even if no remote access is possible, today’s employees are much more knowledgeable and are more prone to poke around and probe for weaknesses. Most of these employees are simply curious however some have other intentions.

Small businesses that have absentee owners or owners who lack a minimum level of computer savvy and accounting knowledge as well as businesses that lack appropriate internal controls are especially vulnerable.

In addition to eternal vigilance, controls can be added to the application software to determine, in the case of record deletion or records that are being changed to track exactly what was deleted or changed and by who. In the specific context of various Advanced Accounting program functions (and the same would be true of DBA systems released to date), we have implemented custom controls to specifically keep track of, by user log-on and date/time, records that may have been deleted from, for example, customer and sales order files. Further even in a open program such as "maintain database" that allows for raw access to the data, we can add controls that indicate exactly what field in a record was changed, when and by who.

Finally, record loss can also be triggered thru file corruption. File corruption can occur due to a great number of reasons which are beyond the control of the application software or the record manager or database engine. Typically file corruption results thru some sort of hardware failure but can also be caused by operating system settings, software conflicts and viruses that attack the integrity of a hard drive. With Btrieve files, both index and data are stored within the same physical file. Index information often occupies a greater portion of the total data file than the data ("fields") itself. When something goes wrong with a data more commonly an index is impacted rather than the data. The process of reindexing a Btrieve file more often than not will resolve the problem (although reindexing is not a normal/routine housekeeping function and should normally only be done when there is a specific reason to do so). Sometimes when reindexing a file that has become corrupted for some external reason, data loss can result because of previously existing problems within the data itself. We have seen a number of cases where a data file was being used normally on a day to day basis but contained a section within it involving one or more records that was corrupt to the point that a reindex will stop when it hits that section and no error message will necessarily result. All information after or before the area of corruption may be lost. For this reason it is essential that a complete copy be made of any file that is about to be reindexed and further that close attention is paid while the file is being reindexed to ensure that no record loss occurs. Assuming that the file can be read prior to reindexing (with some Btrieve errors, for example an 02, that might not be possible) it is a good idea to get a record count before and then one after to make sure that a loss has not occurred in the course of a reindex.

\homepage\techmemo\ad102001.htm