Technique: FileMaker 5.5 - ISO FileMaker Magazine

FileMaker Pro 5.5: Record-Level Security & oAzium Events 3.0
by Clint Roberts <clint@filemakermagazine.com>

RATING: Intermediate
PLATFORM: Macintosh & Windows
VERSION: FileMaker 5.5 Only
TECHNIQUE FILES: Orders.FP5, Security.FP5, Test Delete.FP5
WEB DOWNLOAD: <http://www.wmotion.com/oazium/events/>

The newest, and perhaps most exciting feature of FileMaker Pro 5.5 is the ability to validate a record by calculation upon record viewing, editing, and deletion. Based on the password entered at startup, you can set different validation calculations that are triggered when a record is browsed, edited, or deleted.

Record-level security works by attaching a calculation to these three actions in the Define Passwords dialog box. The calculation result must be Boolean; that is, either true or false. If the result is true (a "0" or ""), that event will be allowed. If it is false (""), the user will be prompted with "These remaining 1 record(s) could not be deleted because they were in use by other users or your password does not allow you to delete them." The event, then, would be disallowed.

Let's test to see record-level security in action. The following steps will lead you through creating a database, setting up two passwords (Master and User), entering a validation calculation for the Delete event, and testing to see the validation in action.

1. Create a new, empty database called "Test Delete.FP5".

2. Create one text field called "Delete It".

3. Choose the "Access Privileges ---> Password..." command under the File menu.

4. Create a password called "Master".

5. Enable the "Access the entire file" checkbox if it is not already enabled.

6. Click the "Change" button.

7. Create a password called "User".

8. Choose "Limited..." in the pop-up menu next to the "Delete records:" label.

9. In the calculation window that is presented, type in "Delete It = "Yes"".

10. Click the "Change" button.

11. Click the "Done" button.

12. When prompted to enter in a master password, enter "Master".

13. Close the newly created database.

14. Open "Test Delete.FP5".

15. When prompted for a password, enter "User".

16. In the existing record, enter a value of "No" into the "Delete It" field.

17. Create a new record and enter a value of "Yes" into the "Delete It" field.

18. Go back to the record that holds a value of "No" in the "Delete It" field.

19. Either choose "Delete Record" from the Records menu, or select Command-E (Mac OS) or select CTRL-D (Windows).

20. Choose to delete this record.

21. Notice the validation error that appears. You are unable to delete this record!

22. Go to the record that holds a value of "Yes" in the "Delete It" field.

23. Either choose "Delete Record" from the Records menu, or select Command-E (Mac OS) or select CTRL-D (Windows).

24. Choose to delete this record.

25. Notice that the record is deleted.

Enabling record-level security for deletion protection in your FileMaker Pro 5.5 database solution is as simple as taking the above steps.

Author's Note: Whereas record-level security is tied to a password, entering "Test Delete.FP5" with the password "Master" will allow you to delete records regardless of the value contained in the "Delete It" field. I have included a pre-created "Test Delete.FP5" file so that you skip creating the file, and simply begin at Step 18 above. You can use the pre-created file in the event that you encounter difficulty creating your own file.

Although FileMaker developers and enthusiasts have cried for record-level security features and FileMaker, Inc. has delivered this in FileMaker Pro 5.5, there are two noticeably missing features: the ability to validate a record upon record creation and the ability to run a script upon record browsing, editing, deleting or creating.

The Ability to Validate a Record Upon Record Creation.

Perhaps this might not seem important as a new record has no data to be validated. Therefore, it does not seem as important as the ability to validate upon viewing, editing and deleting a file. FileMaker, Inc.'s logic appears to be: If you do not want a user to create a file, simply disable the "Create records" privilege for that password by:

a. Choosing the "Access Privileges ---> Password..." command under the File menu.

b. Clicking on the password that correlates to the user that you do not wish to have the ability to create a record.

c. Unchecking the "Create records" privilege for that password.

d. Clicking the "Change" button.

e. Clicking the "Done" button.

However, I can foresee a scenario that needs the ability to validate a record upon creation.

Image a database system with 20 or more files that are accessed by 15 different types of users. Now imagine needing to update those passwords periodically as new types of users are added to the company, or as employees leave. It would become a nightmare to go into each of those 20 or more files to adjust passwords and/or access privileges.

For this reason, a number of savvy developers only use a master password and one general user password. They control which users have access to which functions by a login name and password which correlates to the user's security preferences. For instance, accessing the Purchase Order system could be limited to certain users (like members of the Accounting Department) by entering a value of "Yes" into that user's Purchase Order security preference field.

With this type of login system, it becomes possible to make on-the-fly changes on a per incident basis without having to bring the entire database system down to change a setting or two in the "Define Passwords" dialog box.

I can foresee another scenario that begs for the ability to validate a record upon creation.

Imagine you have a solution where some database systems contain highly sensitive data and it is necessary to keep a log of all actions taken in the database- for example, an Audit Trail. It would be nice to trap for a record creation event and log that date, time, and user who created a record.

The Ability to Run a Script Upon Record Browsing, Editing, Deleting or Creating

The second noticeably missing feature that I see is the ability to trigger a script based on record-level security. Currently only a validation calculation is possible with the boxed FileMaker Pro 5.5 product.

Fortunately, Waves in Motion comes to the rescue with oAzium Events 3.0. Using oAzium Events 3.0 external function script steps, oAzium Events 3.0 brings the power of triggering a script upon record creation, viewing, editing and deletion.

In order to see the power of combining oAzium Events 3.0 with FileMaker Pro 5.5's record-level security features, you will need to download and install the oAzium Events 3.0 plugin from <http://www.wmotion.com/oazium/events/>.

Three different techniques are used to accomplish this task. The first technique is used for creating records, the second technique is used for deleting records, and the third technique is used for browsing and editing records.

Technique 1: Creating Records

Since FileMaker Pro 5.5 does not contain a validation method upon a create record event, a little trick is used. A calculation field of type "Text" is created that contains the following calculation:

Whereas calculation fields are automatically evaluated upon record creation, the oAzium Event is triggered. In this case, if this user has the Create Record security preference set to "No", the oAzium Event plugin is called. It is instructed to create an event named "Create" that runs a script named "Create Record" in the current database ("status(currentfilename)") which is a non-recurring event ("N"). Since no date and time is specified, the event is run immediately.

The "Create Record" script is rather simple. The user is warned that they do not have the access privilege to create a record, and the created record is deleted, as follows:

Show Message [ Buttons: "OK", "", ""; Data: "You do not have permission to Create Records!" ]
Delete Record/Request
[ No dialog ]

If the user has the Create Record security preference set to anything excluding "No", no event is called and a record is created, as indicated by the final "1". Remember that any result of the calculation other than "0" or "" allows an event to occur.

Technique 2: Deleting Records

This technique involves a simple calculation, similar to the calculation used in the "Test Delete.FP5" file that you created earlier:

Security by gUser ID to User ID::Delete Records in Security Database = "Yes"

If the user's Delete security preference is set to "Yes", the calculation result is "0" and the event is allowed to occur. However, if the user's Delete security preference is set to anything but "Yes", a calculation result of "1" is returned. The user is then asked if he or she is sure that the record should be deleted. When the user chooses to delete the record, he or she is prompted with, "These remaining 1 record(s) could not be deleted because they were in use by other users or your password does not allow you to delete them."

Author's Note: Please review the "Test Delete.FP5" setup instructions in the first part of this article for step-by-step setup.

Technique 3: Browsing and Editing Records

This technique involves a calculation that calls an oAzium Event that triggers a script, similar to Technique 1, above. I consider this a different technique because it utilizes FileMaker Pro 5.5's built-in record-level security mechanism to call an oAzium Event.

Instead of the following calculation residing in a calculation field of type text, it is inserted in the Define Password dialog box. Again, please review the "Test Delete.FP5" setup instructions in the first part of this article for step-by-step setup.

Our calculation is almost identical to that used in Technique 1, with the exception of which script is called when a Browse or Edit Record event is triggered. The following calculation is used to validate whether a user should be able to browse records:

In this case, if this user has the Browse Record security preference set to "No", the oAzium Event plugin is called. It is instructed to create an event named "Browse" that runs a script named "Browse Record" in the current database ("status(currentfilename)") which is a non-recurring event ("N"). Since no date and time is specified, the event is run immediately.

The following calculation is used to validate whether a user should be able to edit records:

In this case, if this user has the Edit Record security preference set to "No", the oAzium Event plugin is called. It is instructed to create an event named "Edit" that runs a script named "Edit Record" in the current database ("status(currentfilename)") which is a non-recurring event ("N"). Since no date and time is specified, the event is run immediately.

Enough chatter and theory, dive into my technique files "Security.FP5" and "Orders.FP5" to see everything in action.

The master password (record-level validation disabled) for the file is "Master". The user password (record-level validation enabled) for the file is "User".

I suggest that you use "Master" the first time that you log into Security.FP5 and Orders.FP5. Then login with the user name "Clint" and user password "happy". This way you can create multiple users that have various security preferences and test the results.

Conclusion

FileMaker Pro 5.5's record-level validation feature is a welcome feature addition for the FileMaker developer community. Kudos to Waves in Motion for enabling this powerful feature with oAzium Events to run powerful validation/action scripts. Let me know what experiences and thoughts you have on this power duo.

Happy FileMaking!

Clint Roberts is the Senior Technical Editor for ISO FileMaker Magazine. Email him at <clint@filemakermagazine.com>.

ISO FileMaker Magazine

Site navigation

User menu

You love new FileMaker information right?