FileMaker Encryption

Security is obviously a big concern for everyone in the technology world, and we often are asked to secure FileMaker solutions for clients. In one recent project, we were asked how we could secure our client’s data even from us.

As experienced FileMaker developers know, [Full Access] privileges give a user the ability to make all changes to a FileMaker file, including to data. By definition, Full Access is, well, full access.

Our client tracks data that is protected under the HIPAA Privacy Rule. They work with personal and medical information. In this particular situation, the client’s requirement is that an employee (with access) may see any of the data in their application at any time, but the data needs to be secured from anyone outside the company.

In this particular case, they need the team at Codence to be able to make changes, add features, and so on—all without the ability to see any of the private data involved.

We’ve detailed our approach using FileMaker’s CryptEncrypt function and created an example file for you to download. We hope you find it useful and of interest.

Ideas we explored

When approaching the use case, we considered a few general approaches:

Two-file Solution

Here we’d build interface and most fields (the non-private ones) in a main file, and secure data in a second file. We’d not allow developers outside the client organization Full Access to that file. This didn’t fully meet our client’s needs: managing a separate data file was beyond their technical comfort, and it still wouldn’t meet the primary use case where even with [Full Access], they needed the data locked down.

Limited “Developer” Privilege Set

We also considered creating a non-Full Access privilege set, and through Custom Menus and FileMaker functions allowing work in the Manage Database dialog, Script Workspace, and so on. While this approach would certainly work—and may well represent a “more elegant” approach—it still didn’t cover the scenario where someone has access to the [Full Access] credentials.

CryptEncrypt Functions

Due to the sensitivity of the data, this is ultimately what we chose — with these functions in place, our clients can fully encrypt their data, even from a [Full Access] account. Without the key to decrypt, the information is secure.

Implementing CryptEncrypt Functions

The attached example demonstrates one method of implementing encryption in an app using the CryptEncrypt function. Rather than hard coding which fields to encrypt, it leverages FileMaker Design functions to give users a way to choose fields for themselves and some abstraction to apply encryption. The idea is to provide an easy way for our client to be able to secure data over time should they add new tables or fields. Given the requirements, we found that encrypting the data was only part of the solution, and that managing access to that data for Full Access accounts would require some additional attention.

As we looked at all the ways to secure the data, a first step was to ensure that the file itself has encryption at rest (EAR) in place which helps protect the data in the event that the physical file itself is ever taken. This can be applied using the Developer Utilities. Since it is not possible to create limited Full Access accounts using FileMaker Manage Security (though wouldn’t that be nice?), we decided the best approach was a combination of a workflow and programming in the application. Additionally, encrypting the data meant that the app was not really usable for work purposes; so we proposed the following overall process to help manage developer access:

1. Require all users to quit FileMaker
2. Encrypt the data
3. Open Manage Security and make the developer’s Full Access account active
4. Allow the developer to do their work independently (no other users in the system) until complete
5. When the developer is finished, open Manage Security and make the developer’s Full Access account inactive
6. Decrypt the data
7. Allow users back in

CryptEncryptBase64 vs CryptEncrypt

The difference between these two functions is how they store encrypted data. CryptEncryptBase64 stores the encrypted data as text. While it may be helpful for display purposes, storing encrypted data as text means that it can be altered if a user has access to the encrypted text, whereas CryptEncrypt stores the encrypted data as a binary file which requires a container field for storage. I chose to use the CryptEncrypt function in the example because it offers an additional layer of protection since the encrypted data cannot be manipulated. Our use case is about securing the data for Full Access users, so there is no way to prevent access whether it is text or container data without using a separate file which we did not do in this case. In both cases, where you choose to store the encrypted data is important.

Considerations and Limitations

There are many aspects of encrypting to be aware of and it is important to understand them.

1. IMPORTANT: FileMaker CryptEncrypt and CryptEncryptBase64 functions only encrypt/decrypt text or container data. Data stored in Number, Date, Time, and Timestamp fields cannot be encrypted/decrypted directly. However, the attached example demonstrates a work-around by passing all data through a global text field as a part of the encryption / decryption process.
2. There is no way to decrypt data without the key. It is important that any user with the ability to apply encryption in their solution know that they need to protect that key in some fashion.
3. Encryption is applied or not. It is not possible to apply encryption for some users, and not for others without data replication and complex schema to support it.
4. It is possible to encrypt data that is already encrypted. Doing so will require that each encryption layer be decrypted with its own key (if they are not the same for each layer).
5. If you encrypt data using the CryptEncryptBase64 function, which creates an encrypted text string, and store it in the same field, or another field that a user can access, the encryption can be broken if a user modifies the text either accidentally, or on purpose. No matter how encryption is applied, it is important to ensure that sufficient backup strategies and data access policies are in place.
6. It is important to test a decrypt key to confirm it is correct, before trying to decrypt your data. A failed decryption attempt will return a “?” and a potential loss of the encrypted data when using CryptDecryptBase64 if you are not careful about where you store the encrypted data.
7. No matter how you implement encryption, it is critical that it is approached as an all-or-nothing process. Data is not secure if any record (or field) is “missed” during the encryption process, unless your workflow is meant to only address a record at a time. In the attached example, the process is meant to encrypt the desired fields for all records in all associated tables. The only way to ensure this is successful is to have a process that complements the actual encryption.
8. If users are allowed into the system after data is encrypted, their associated privilege set should prevent access to fields that are a part of the encryption process.
9. QA does become an issue depending on what the developer is focusing on in the primary application. It is possible for a developer to create new records and add data as needed, but the mixing of test data and production data should be avoided if possible. The approach we have demonstrated is best used for tackling issues that require an immediate fix. Other issues or enhancements should use a structured development process; handling major development in an offline copy, testing, and migrating to production after testing is complete.

Notes on the attached example

1. The example uses the CryptDecrypt function to store encrypted data as a binary file in a container field. As a result, every field included in the encryption process requires a container field in which to store the data. The standard we adopted is to name the container field the same as the original field while adding a “_Encrypt” to the end of the field name (ex. MyField and MyField_Encrypt). The Secure Data script will account for these automatically.
2. Any table you want made available to the encryption process requires a layout with a name matching an associated table occurrence before it will appear when adding fields to be encrypted.
3. To make a field available to the encryption process, simply add it to the base table layout. In the attached example, List View layouts (similar to what a user would see) are used.
4. The encryption / decryption process in the example is fairly flexible. If an error is encountered, a warning is displayed, but if a process fails before completing, data could be left in a mixed state; meaning some is encrypted and some not. If the process failed while encrypting the data, this means that it is possible to correct an issue, and re-encrypt again. Data that is already encrypted will be skipped and only the missed data will be encrypted, however it is important to use the same key so the decryption process can decrypt all data in one pass. If a different key is used, the decryption process will need to be run for each key. It is recommended that a clear process of how to apply encryption be in place, along with a clear process for handling errors.
5. Placeholder text is used to give users a visual clue that a particular field is encrypted. Placeholder labels do not display in Table View. However, if you select Table View you will see the other fields used in the process and can have a peek at what is happening behind the scenes.

Conclusion

FileMaker’s field-level encryption functions go a long way to helping secure your data. We were reminded along the way that, as with any development process, it was important to invest time learning the details of these functions and to test various approaches. While our example may serve as a good launching point, it is not meant to be foolproof nor a “perfect” way to approach encryption. Rather, it is one idea that we hope will provide some insight as you do your own exploration.

The most secure approach is to combine data access policies and procedures that are supported with programming. We hope this post offers some insight. Explore more security posts, like Managing Security Changes.