Locking single records to prevent further modification

We have a number of data structures that we protect from modification. We do it two ways one is to have an audit trail so we can follow the changes. The other completely locks the data so it can't be modified unless you log in with full priviledge and remove the lock. I think you are looking for the second, I will elaborate a bit and you tell me if you want me to provide the approach we use:

We create two layouts for each entry screen, one that is made up of the actual fields purely for admin access and debugging and the other that uses only globals. We place a submit button on the screen which fires a script that decides if it can save new data over the old data. We creat our own navigation buttons that move between records and copy the actual data to their global counterparts so that they can be displayed on the screen. If the user has the authority to modify the data they get sent to another screen "the edit screen" where they again make entries into globals and if they have the appropriate authority to run the submit script then the data can be updated by copying all the global entries to the actual data table.

Because we are creating secure systems that have electronic signatures, we embed a checksum into the audit trail that assures us that the person being recorded as making the transaction is in fact who they say they are. We also retain the transaction for purposes of audititing changes.

This is alot to say here, I just wanted to give you a taste of the process. Let me know if I can help further.

HIPPA compliance is similar but a subset of Title 21 Partt 11 which is what we are meeting. So I think I can give you some guidance but this is not just a question. I think you have a number of steps to go through:

1. How do I input data without modifying the current data and then pass an authentication test that verifies rights? - This can be done by creating a screen based on globals with a submit button that you can add to the screen to run a script that would copy the global values to the record values. This is a good time to add an audit trail so that you can record the activity.

2. How do I create the same functionality on your global screen as what exists in FM navigation tools? you need to add navigation tools that copy the information into the globals screen as you move through the records. You also need to build a find routine that finds in the real data, then copies the data to the globals screen to eliminate the risk of changes

3. How do I create an authenticated record that I know has not been changed? This is a tougher question, we have developed a public domain MD5 script with custom functions that produces this result. I think our company (Alliance Medical Products) would provide to you with the proper license agreement (no cost)

Now that you have an approach defined that will get you to HIPPA requirements you need to let me know what specifically you need help with. If you need an example I would prefer to work on your database. You can create a clone and put some fake info in it so I can see what you are trying to do...

Upload the Clone file by going to

http://idisk.mac.com/juanv-Public

The user name is = public
Password = hippa07

I will close the site as soon as you upload, I will need a day or so to look at the file. Could you enter some mock data so I can see what your intensions are?

lesouef,

I sent you and email to the email you have in your profile, I am surprised I didn't hear back from you,

my email jvaldes[at]amp-us[dot]com

Dr. Jana,

I looked at the clone got to the same location above to download the basic database with the needed functionality.... Use the same username and password

After reviewing your database,  I have a couple of questions, There are three Hippa requirements that I think you desire to meet.

Number one is access control, I noticed that you are attempting to manage that in your database. I recommend that you use the Filemaker Privileges  to manage that. Create the user accounts in File=>Manage=>Accounts and Privileges, Then create the proper account types, ex. Minimal privileges may not allow a person to enter any data but they could view it (Read-only) on the other hand who ever is responsible for managing the database would have full access. If you want to disallow certain users from seeing specific data, then use a script to send you to the layout instead of the Go to Layout command and give access to only those that you want to allow access to that screen. This method combines with menu controls will prevent unauthorized access to the data.

Number two; Audit  trail of who has made changes to the data , when, and the reason for the change. I have an example of how to do this that should provide the insight you seek.

1)      Create a global field to match every field in your database; ex. LastName would have a global twin g_LastName, etc.
2)      Copy the Input screen layout and call it the Input screen
3)      Replace all the fields in that screen with the Global storage equivalents
4)      Add a submit button to the bottom of the screen, that will execute a script to copy the information from the globals to the actual data fields.

To navigate in this new world of globals you need to recreate the navigation tools that are part of filemaker, for example you need to develop a script that will change the current global information to match the next or previous record depending on the script you are running. By doing this you are shielding the user from the data, so that they cant arbitrarily modify the data. Take a look at the exmple.

In order to record who did what to the data, you should create a database called Audit trail that has the following fields: as auto enters

Time Stamp  Autoenter field
Date Stamp  Autoenter field
Username  Autoenter Calculation get(username) if you are using an external server use get(accountname)
Transaction Description  This should be calculated by the Submit script and put in a global variable ($$transDesc) then autoentered as a calculation =$$TransDesc
Authentication String  Autoenter Calculation as well, I will discuss this in section 3 but it would be reading a different global variable, lets say $$AuthString

Every time you submit new data through your submit script you will compute the data that goes in the variables and create a new record in the audit trail database and that will create a completed record for you.

So when you create a script to delete a record, or change a record you only need to populate the global variables with your data and create a new record in the Audit trail database and wa la you have an audit trail.

And last how do you ascertain that the information is indeed authentic and that in fact the person who is listed as the transaction owner is actually who did it. I have added my authentication routine which is not for public use as it is copyrighted (you have a perpetual license to use it, if you provide you personal data via email to me, and I return a notice of perpetual license to you.(which I will)

The authentication script that I have developed will return a string of data  that  when saved with the audit record will guarantee that the data is authentic, with less than 1 in 64 billion chance that it has been changed and the change is not detectable.

Remember the use of Filemaker security is very broad and it would not be in your best interest to rewrite that.

Let me know if I can help further.

Login to the file with account admin password = admin
For unrestricted access
Login to the file with account test password = test
To see access control implemented, you will see that you can go to and from the audit trail on this account but you cant submit new data. That can only be done in the admin account&

Note how I took away access to the script that submits the data for group of users or a specific user. The granularity of security is so fine that I think you are much better off using this approach than writing your own.

If you have a domain or LDAP controller you can manage people by rights on the domain controller rather than in the application. This adds flexibility that you should consider if you intend to serve this application under a filemaker server product&.

I like the dummy table idea, I will implement it in the future, but the audit trail that you recommend will not address electronic signature (compliant with 21CFR part 11 of the regs. Treaceability has to be to the action not the field... Also the audit trail log must be unchangeable by all levels of authority, so it needs a lock that can't be overcome. I have created on based on the MD5 checksum.