by Clint Roberts <clint@dbpublishing.com>
RATING: Intermediate
VERSION: FileMaker 5.5/6
PLATFORM: Macintosh, Windows
TECHNIQUE FILES: RLS.fp5
In the past, FileMaker Pro developers had to spend hours developing tremendous routines to protect important data from either prying eyes or errant fingers. Not surprisingly, Record Level Security (RLS) was one of the most requested features in FileMaker Pro, and FileMaker, Inc. delivered a native version with FileMaker Pro 5.5.
This article will eradicate some of the misconceptions and ambiguities surrounding Record Level Security, demonstrate Record Level Security's power, instruct on how to implement RLS, and implement a simple routine to protect your database or database system from non-RLS aware versions of FileMaker.
First and foremost, Record Level Security is no different than the other security features included in previous versions of FileMaker Pro. With some basic understanding, RLS is not difficult to understand or set-up:
1. What ISN'T Record Level Security
2. What IS Record Level Security
3. A Simple Record Level Security Sample
4. How to Setup Record Level Security
5. How to Protect Your Database from Non-RLS Aware Versions of FileMaker
What ISN'T Record Level Security?
The name Record Level Security may imply that Records are either granted access or not granted access based on a password. This is not Record Level Security. That type of security has been available in FileMaker Pro for quite some time.
Previous versions of FileMaker Pro provided the following security measures:
1. File-level Password Protection -- Access granted to a FileMaker file based on a password entered upon opening the file.
2. Layout-level Password Protection -- Access granted to view a layout of a FileMaker file based on a password entered upon opening the file.
3. Field-level Password Protection -- Access granted to view, edit, or modify the data in a field of a FileMaker file based on a password entered upon opening the file.
Based on the Password entered by the User upon launching a FileMaker file, and the Access Group that Password was assigned to, a User was granted access to either view certain layouts, and view and/or edit and/or fields. These settings were specified under the File > Access Privileges > Passwords... and File > Access Privileges > Groups... menus.
When properly implemented, the Password Protection features of FileMaker were quite powerful -- but not comprehensive. Record Level Security helps to fill in some of the gaps in FileMaker's Password Protection feature set.
What is Record Level Security
Record Level Security is a dynamic-based security feature. Records are protected based on the value or values of a record or series of records.
While the password entered upon opening a file is involved, it is not the sole determining factor.
A user is granted access to either browse, edit and/or delete records based on the value contained within a field. This field may be either a Text, Number, Date, Time, Calculation, or Global (Text, Number, Date, or Time).
More is described in the the following section.
Based on your logic prowess, the Record Level Security system that you implement can be very complete and amazingly savvy.
Just A Sample
For demonstration purposes, the example that I provide is quite simple. Users who enter the technique file named, "RLS.fp5" with the password, "user" may delete a record that they created only within the first two minutes of creating it, and if they were the creator of the record.
I accomplish this by utilizing the following calculation:
Date Created = Status(CurrentDate) and
Person Created = Status(CurrentUserName) and
(Time Created > (Status(CurrentTime) - 120))
In this calculation, I grant delete privileges to a user who is attempting to delete a record that meets the following criteria:
1. Date Created = Status(CurrentDate) -- Date Created is a Date field that is set to Auto-Enter the Creation Date, disallowing modification of the value. The function Status(CurrentDate) returns the date set on the computer clock at the time when the function is called. If the date the record was created equals the date the record deletion attempt is made, the first criterion is met.
2. Person Created = Status(CurrentUserName) -- Person Created is a Text field that is set to Auto-Enter the Creator Name, disallowing modification of the value. Upon creation of a new record, the Person Created field is populated with the User Name set in FileMaker preferences. On Windows and Mac OS 9, you may set this value by selecting Edit > Preferences > Application... On Mac OS X, you may set this value by selecting FileMaker Pro > Preferences > Application... The function Status(CurrentUserName) returns the User Name set in FileMaker preferences. On Windows and Mac OS 9, you may set this value by selecting Edit > Preferences > Application... On Mac OS X, you may set this value by selecting FileMaker Pro > Preferences > Application... If the person who created the record equals the person who is attempting to delete the record, the second criterion is met.
3. Time Created > (Status(CurrentTime) - 120 -- Time Created is a Time field that is set to Auto-Enter the Creation Time, disallowing modification of the value. The function Status(CurrentTime) returns the time set on the computer clock at the time when the function is called. If the time when the record was created is within 120 seconds (2 minutes) of when the attempt to delete the record is made, the third criterion is met.
The record is deleted only if all three criterion are met.
Record Level Security uses a boolean result to determine whether an action may be performed. Boolean results are either True (any non-zero value), or False (a zero or empty value). True values allow the action to be performed. False values deny access to the action.
Up until this point in this article, I have discussed what Record Level Security is not, what Record Level Security is, and a sample calculation to be used in Record Level Security.
I will now discuss how to setup Record Level Security utilizing the sample calculation discussed above.
How to Setup Record Level Security
In the technique file, "RLS.fp5", I have already set up Record Level Security to follow the rules discussed in the "Just A Sample" section above. Upon launching RLS.fp5, enter the password, "user" to test the Record Level Security setup. To see behind the scenes, launch RLS.fp5 and enter the password, "master".
Choose File > Access Privileges > Passwords... In the left-hand pane on the Define Passwords window, click on the "user" password. Notice the fourth item down on the right-hand side of the Define Passwords window. The "Delete records:" item has the label "Limited..." in the pop-up menu. Click on the pop-up menu and choose "Limited...". Notice the calculation in the Specify Calculation window is the same calculation as discussed in the previous section.
To exit the Specify Calculation window, click the cancel button. To exit the Define Passwords window, click the Done button. If you made any changes while in either of the previous windows, you will be prompted to enter a master password. Enter, "master".
In showing how to setup Record Level Security, I will provide a step-by-step guide to recreating the entire functionality of RLS.fp5.
Step 1: Create a new database file by selecting File > New Database... from within FileMaker Pro. Choose where you would like to save the file. I have chosen to save my file on the desktop. Choose which name you would like to give the file. I have named my file, "Sample.fp5".
Step 2: In the Define Fields dialog that automatically displayed, create the following fields:
a. A Date field named, "Date Created". Click the Create button. Click the Options... button. Under the Auto-Entry tab (the first tab of the Options dialog), click the first checkbox and ensure that "Creation Date" is displayed in the pop-up menu. Click the last checkbox labeled, "Prohibit modification of value". Click the OK button.
b. A Time field named, "Time Created". Click the Create button. Click the Options... button. Under the Auto-Entry tab (the first tab of the Options dialog), click the first checkbox and ensure that "Creation Time" is displayed in the pop-up menu. Click the last checkbox labeled, "Prohibit modification of value". Click the OK button.
c. A Text field named, "Person Created". Click the Create button. Click the Options... button. Under the Auto-Entry tab (the first tab of the Options dialog), click the first checkbox and ensure that "Creator Name" is displayed in the pop-up menu. Click the last checkbox labeled, "Prohibit modification of value". Click the OK button.
Step 3: Click the Done button to exit the Define Fields dialog.
Step 4: Choose File > Access Privileges > Passwords...
Step 5: Type "master" in the password field at the bottom right-hand corner of the Define Passwords window. Leave all options in their default position on the left-hand portion of the window. Click the Create button.
Step 6: Type "user" in the password field at the bottom right-hand corner of the Define Passwords window (you will have to type over the previous password, "master" which is still displayed in that field. Click the Create button.
Step 7: Choose "Limited..." in the "Delete records:" pop-up menu (fourth item from the top).
Step 8: In the Specify Calculation window, type, "Date Created = Status(CurrentDate) and Person Created = Status(CurrentUserName) and (Time Created > (Status(CurrentTime) - 120))".
Step 9: Click the OK button to exit the Specify Calculation button.
Step 10: Click the Done button to exit the Define Passwords window.
Step 11: If prompted to save changes to "user", click the Yes button.
Step 12: When prompted to Confirm Password, type, "master".
You have now successfully created a database utilizing Record Level Security that will only allow users who log into the database using the password "user" to delete a record by the creator within two minutes of creation.
How to Protect Your Database from Non-Record Level Security Aware Version of FileMaker Pro
FileMaker Pro 5, FileMaker Pro 5 Unlimited, FileMaker Developer 5, FileMaker Pro 5.5, FileMaker Pro 5.5 Unlimited, FileMaker Developer 5.5, FileMaker Pro 6, FileMaker Pro 6 Unlimited, and FileMaker Developer 6 all are able to open files created by one another.
Whereas Record Level Security was introduced in FileMaker Pro 5.5, FileMaker Pro 5.0 is not Record Level Security aware. As you might imagine, this may present a problem -- especially if your database/database solution relies heavily on Record Level Security.
While FileMaker, Inc. has done an incredible job in protecting your data when opening a database relying on Record Level Security in a version of FileMaker that does not support it.
For instance, using FileMaker Pro 5.0v3, I opened three separate databases utilizing Record Level Security. Here is what I discovered:
1. Opening a database with a password that restricts delete privileges on records based on Record Level Security disables deleting records entirely.
2. Opening a database with a password that restricts viewing privileges on records based on Record Level Security is impossible. The user is given the message, "This password does not enable you to perform this operation."
3. Opening a database with a password that restricts editing privileges on records based on Record Level Security disables editing fields entirely.
While it is comforting to know that your data is still protected, a file that relies on Record Level Security that is opened in a version of FileMaker 5 (if it is opened at all), is functionally useless.
I recommend that developers trap for versions of FileMaker Pro 5 and warn users upon launch of a file that utilizes Record Level Security of potential problems.
To do this, utilize the Status(CurrentAppVersion) function. The data type returned by this function call is text representing the current application version (such as, "Pro 5.0v3", "Pro 5.5v2 Dev", or "Pro 6.0v2").
Whereas it is irrelevant whether the product being used is client, Unlimited, or Developer, and the data type returned by the function call is text, we can parse out the necessary version number. We are mainly interested in characters in position 5, 6, and 7 in the returned result.
Thus, a calculation such as:
Middle(Status(CurrentAppVersion), 5, 3)
will return either "5.0", "5.5", or "6.0", based on the version of FileMaker being used.
Set your file to run an startup script that checks for the version of FileMaker opening the file. If a version of FileMaker 5.0 is detected, display a dialog that lets the user know that the database was designed to work with FileMaker Pro 5.5 or higher and close the file.
Conclusion
Record Level Security is an extremely powerful feature introduced in FileMaker Pro 5.5. Experiment with the ways in which Record Level Security can add security to your database solutions.
Happy FileMaking!
Clint Roberts is Editor-in-Chief of ISO FileMaker Magazine. Email him at clint@dbpublishing.com.