We were working with a customer recently on a custom Claris FileMaker project, and during a demo of progress to date where we were showing a simple notes feature, our customer asked “Can you make it so that the therapist notes aren’t visible to other staff members?”. Thinking ‘record level access privileges’, we said “Of course, no problem”. But then she continued, “And can you make it so that other staff members can see that the therapist has written a note, but not see the contents of the note?”. Again, thinking ‘calculated field level access’, we said “absolutely!”.
(TL;DR – It’s not that simple – we did use record level access, but the use case has some subtleties worth exploring. Read on for details.)
It wasn’t until we started working on this feature that we realized that this wasn’t going to be as easy as we originally thought. While Claris FileMaker brings an incredibly robust set of security management tools to the table, there is no way to directly control user access to a specific field in a table (in our case, the Note field in the Notes table) using a calculation. We were sure we could do some layout-based trickery to obscure these sensitive notes, but not wanting to do an end run around the FileMaker security model, we searched for an alternate solution.
First, let’s state the goal in the form of a couple user stories:
As a Therapist, I’d like to be assured that my session notes are not visible to non-therapist staff members (like patient coordinators) because they often contain sensitive Personal Health Information that I shouldn’t share with my non-therapist colleagues.
As a Patient Coordinator, I need to know that our Therapists have completed their session notes, but understand that I shouldn’t be able to see the contents of those notes.
These are the requirements for the feature as expressed by the customer, but let’s add one more user story from the developer perspective:
As a Claris FileMaker Developer, I know that it’s important to achieve my system security goals using FileMaker privilege sets rather than some other ersatz method, since the former is undoubtedly more secure (New Paradigms In FileMaker Platform Security, 2015, Steven Blackwell).
We want to avoid using any type of security that relies on obscuring objects on layouts – which really is an interface technique, and not proper control of personal health information. Techniques like hiding the notes field when a certain condition exists, or creating a calculation to display the note only if the user is associated with a specific privilege set should be avoided.
We might initially think that something like this should work nicely in this case, but what if a Staff user is granted privileges to edit a layout? If they have layout access (either now, or in the future) they could place the actual notes field on a layout and gain unauthorized access to data. It’s important to appreciate in Claris FileMaker the difference between manipulating objects on a layout – the interface layer – versus controlling access to data at the database layer. This is especially important if you’re considering building a HIPAA-compliant Claris FileMaker solution; while we won’t address HIPPA explicitly in this post, the techniques described here are absolutely relevant to that type of initiative.
So how do we proceed? Let’s take a look at the current architecture of the system, or at least the architecture of the relevant parts in this simplified example. We’ve have a People table and a Notes table with a simple primary key to foreign key relationship.
We might think that we can leverage the isPrivate field in a calculation to determine whether or not a member of a specific privilege set can see the Notes field, but FileMaker doesn’t give us the ability to define conditional access to a specific field in a table. When we look at the Custom Record Privileges for our Staff privilege set, and drill down to Custom Field Privileges, we find that we can fine tune field access on a field by field basis, but can specify that a particular field is Modifiable, View Only, or No Access only. There is no option to define a calculation to determine the level of access on a specific field.
So we don’t have calculated privileges at the field level, but we do get calculated privileges at the View, Edit and Delete levels of the Custom Record Privileges dialog. Here is the “Aha!” moment; if we modify the architecture of our database, we stand a chance at being able to modify these record level privileges to get the security behavior that we desire.
Put the text of the note in another NotesText table, while the other attributes of the note stay in the Notes table, and we should be able to define some record level view privileges to achieve our goals.
This technique isn’t without its pitfalls, (which we will discuss in a bit) but should do the trick. Now we can define our user access to the NotesText::Note field with a calculation, independent of the access that we define at the Notes level. In the definition of the Staff privilege set, we specify Custom Privileges for Records, and choose View > Limited, where we can define the following calculation.
The logic here specifies that as a member of the Staff privilege set, I can only see the NotesText records when the record is either created by me, or isPrivate evaluates to False (in other words, the record is NOT private). It’s worth noting that the Edit and Delete calculations are a little simpler since we only have to worry about records that we can view. Those calculations are simply set to:
CreatedBy = Get ( AccountName )
The above calculation then limits users associated with the Staff privilege set to editing and deleting only those record that they created themselves (that’s a requirement that the customer never asked for, but it seemed logical to us, and as it turns out, it’s what they meant anyway).
So that’s it, our security is defined and works as expected. If you look at the example file, authenticate as Eliza and navigate to the NotesText layout, you’ll see the behavior that we expect.
The rest of this exercise is all about building a great user interface when we have View, Edit, and Delete constraints like this, and ensuring data integrity with this new architecture.
One thing to be aware of is that we intend the relationship between Notes and NotesText to be a one to one relationship. We want to be sure that we have one (and only one) NotesText record for every Notes record. Our first thought was to just “Allow creation of records in this table…” when defining the relationship between Notes and NotesText, but that causes a problem when a user attempts to edit the NotesText::Note field from a Notes record where they do not have access to the NotesText record. The way that FileMaker behaves in this situation is as if the NotesText record doesn’t even exist, and FileMaker will happily create another NotesText record. It’s worth noting that FileMaker isn’t behaving poorly in this situation. One could easily argue that this is precisely how FileMaker should behave, it’s just that this is decidedly NOT the behavior we are looking for, so we will need to block this behavior somehow.
The other thing to consider is how we create the NotesText record in the first place. Our first thought was to define the relationship from People to Notes and from Notes to NotesText to “Allow creation of records in this table…”, and place the related NotesText::Note field in the portal. Even though we had record creation enabled on the relationships between People and Notes, and Notes and NotesText, we couldn’t get FileMaker to create the NotesText record by clicking into the NotesText::Note field. Rather than troubleshoot that blocker (sometimes deadlines get in the way!) we pivoted and pursued scripted record creation, and we’ll leave it to you to investigate that script in the demo file (spoiler alert: it’s not terribly exciting code).
Once our data model was secure, and our interface smoothly allowed record creation, we turned our attention to the details of the user experience. Specifically, what should we show the user when they don’t have access to a note? When displaying the NotesText::Note field in a portal, FileMaker won’t display the standard <No Access> because we don’t have access to the record, and FileMaker makes it appear as though the related record doesn’t actually exist (this is the same issue we encountered above when talking about data integrity). If we do nothing, there will just be a big blank space where the record note should be. Now we will turn to the “Hide object when” feature to display our own <Private Note> text when the user doesn’t have access to the related record. While we’re at it, let’s apply the same treatment to the Private text and checkbox. It’s important to understand that we have already established security at the privilege set level, and we are only relying on these interface elements to improve the user experience.
One last little nicety that we can add is to conditionally display the delete record icon (the trashcan) in our portal rows. We naturally would not want to show the icon in cases where a user doesn’t have permission to delete something. This is a slightly different calculation that checks to see if the related record was created by the user or if the user is a [Full Access] user. This could certainly get more complicated if there were multiple privilege sets in the file that allowed delete of other user’s records. Also, this is the only feature we have coded that specifically refers to a privilege set by name. It’s nice to avoid referring to an object that can be deleted or renamed like a privilege set, but in our simple case, we’re only referring to the [Full Access] privilege set which can’t be renamed or deleted.
There’s certainly more that can be done to fine tune the user experience with this feature. For example, we didn’t specifically address the issue where a user might attempt to edit a note that they didn’t create. If a user tries to do this, they’ll get the default “Your access privileges do not allow you to perform this action.” message. While that’s not an ambiguous dialog, as a user it can be an intimidating or at least a jarring interaction. It’d be an improvement if this UI deficiency was addressed.
While we don’t have calculated field level access privileges in Claris FileMaker, you can still achieve this type of behavior if you’re willing to explore some modifications to your data architecture. Record level access privileges have been around since Claris FileMaker 7, and using this feature effectively is key to implementing any moderately complex security model.
Want to learn more Security tips? Learn more about Claris FileMaker’s field-level encryption functions here.
Charlie is well known in the Claris community; he has deep experience in technical leadership with a major focus in the vertical space. On the personal side, Charlie is the president of his local cycling club, a competitive sailor, a ski instructor, and recently completed all 46 4000+ foot peaks in the Adirondacks.