Approaching Security

Security is a primary concern for all database developers and a significant factor in an organization's requirements for both the internal workings of a database system and the technology used to build it. IT departments in particular pay close attention to security issues and often have specific needs that go beyond those of the users of your database solution.

FileMaker's security architecture was completely overhauled in the FileMaker 7 product line, and it offers a robust set of features for managing security. Although security models in versions before 7 were (at best) cumbersome, we're pleased to say that FileMaker 8 offers a complete set of security capabilities and serves as a "good citizen" for IT organizations: It meets the common standards for security and account administration most IT organizations require of modern server-based technologies.

Regardless of how you plan to deploy a solution (you may not even have an IT department), we strongly urge all developers to learn about security and choose appropriate levels of safeguards for their FileMaker solutions. This may be as simple as locking down the capability to modify the database schema or as complex as deploying your solution on a network with ties to an external authentication server.

Whatever your specific needs for security are, there are three primary concerns that bear consideration:

• Physical File Access The first issue for security is making sure that you protect the database file itself. No matter how robust a security architecture is (for any kind of software or server application), you will face risks if a malevolent person gets direct access to your database file or server.
• Network Access The second area for security is the network traffic between a FileMaker hosting computer and the client computers connected to it. If you are working on an open network, you might want to consider encrypting the data stream between FileMaker Server and its clients.
• Internal Data-Level Security The third area for security has to do with the internal logic of your specific database solution when someone is legitimately logged in. Who has rights to delete records, who can make programming changes to the database, and who can view various layouts in the system? These details are internal to the workings of a FileMaker solution and deal with ensuring that your data remains both secure and reliable.

Every database solution should address these three areas. They may be addressed by the fact that your database will never leave your personal hard drive nor be available to the network at large, but if the data in your solution is particularly sensitive, what might happen if your computer was stolen or if a colleague sat down at it while you were away from your desk? We encourage all developers to consider security issues and make deliberate choices that are appropriate to the sensitivity of their information and the consequences they might face if it were compromised.

Identifying Risks

Security concerns are not all targeted at the clichéd image of a sophisticated hacker sitting in a dark room somewhere surrounded by Mountain Dew cans and pizza boxes. Most FileMaker systems will never be exposed to that level of threat. If you have a reasonably secure network and keep access to your server (or hosting computer) controlled, you will have addressed many of the concerns an extreme case like hacking represents.

The biggest security threat a database system faces is actually from the legitimate users of the system itself, and often has most to do with data integrity. We'll use an example to illustrate: Consider a system for managing invoices that a company depends on for reporting monthly revenue. If every user of the system (including perhaps a temporary employee there to answer phones for a few days) has the capability to delete records, the chances that someone would inadvertently delete invoice records are quite high. Or take a less clear-cut example: What if someone duplicated a record, intending to use the new record to create a similar invoice, but mis-keyed the command and duplicated it twice? In these situations, the database could not be reliably depended on to deliver accurate revenue totals. Although these sorts of issues are not the result of intended harm to a database, they are a risk to the system and need to be addressed by its security architecture and data validation mechanisms.

The second general threat developers face is data sensitivity: In the examples given previously, would it be appropriate for everyone in the system to be able to run the monthly invoice summary report and see the financial performance of the organization? Or in the case of a database that tracks, say, human resources information, which users should have the ability to view the layouts on which people's salary history appears? Security plans need to include an assessment of what data users can access (see and manipulate) in a given solution in addition to what they can do to that data.

We find it useful to work with two general categories of risks to data within a database:

• Data Sensitivity Define the degree to which information should be visible and accessible after a user has legitimately logged in to a system. Risks include inappropriate access to private and proprietary information.
• Data Integrity Define the actions various users can perform on the data in your solution. Often revolving around the creation and deletion of records, risks can also include the capability to edit certain fields or run specific scripted routines.

When you're approaching security for a given solution, it is important to identify the risks the organization faces in terms of both of these areas. We advocate the creation of a risks document in project planning that identifies these issues and the planned means of addressing them.

Planning Security

When you're approaching a new system, it's important to identify the security issues you face and include early in your development process a plan for your security architecture. For example, you will need to plan ahead if some users of your system should not be allowed to view or work with some set of fields, records, or layouts. Security, like reporting, is often left until last when building a system, and as with reporting, this tends to be a mistake. You will need to interweave access issues throughout your database solution (taking security into account when placing objects on layouts, writing scripts, and so on), and it is best to have this mapped out before building a solution.

Using a Security Matrix

To make sense of the myriad security issues many systems face, we recommend the use of a security matrix. A simple example is shown in Table 12.1.

Table 12.1. Security Matrix Example

	Developer	It Admin	Manager	Sales	Finance	Admin
Server Administration
Access to server	Limited[1]	Full	None	None	None	None
Access to backup directory	Limited[1]	Full	None	None	None	None
Access to server admin tool	Limited[1]	Full	None	None	None	None
User Accounts
New account	Full	Full	None	None	None	None
Delete account	Full	Full	None	None	None	None
Change password	Full	Full	None	None	None	None
Data Tables
Customer view	Full	None	Full	Limited[2]	Full	Full
Customer new	Full	None	Full	Limited[2]	Full	None
Customer delete	Full	None	Full	None	None	None
Customer edit	Full	None	Full	Limited[2]	Full	Full
Invoice view	Full	None	Full	Limited[2]	Full	Full
Invoice new	Full	None	Full	Limited[2]	Full	None
Invoice delete	Full	None	Full	None	Full	None
Invoice edit	Full	None	Full	Limited[2]	Full	None
Product view	Full	None	Full	Full	Full	Full
Product new	Full	None	Full	Full	None	Full
Product delete	Full	None	Full	None	None	None
Product edit	Full	None	Full	Full	None	Full
Script Routines
Monthly Revenue Report	Full	None	Full	None	None	None
Regional Revenue Report	Full	None	Full	Limited[3]	None	None
Layouts
Customer List	Full	None	Full	Full	Full	Full
Customer Detail	Full	None	Full	Full	Full	Full
Invoice List	Full	None	Full	Full	Full	None
Invoice Detail	Full	None	Full	Full	Full	None
Product List	Full	None	Full	Full	Full	Full
Product Detail	Full	None	Full	Full	Full	Full

[1] Database developer will have full access to server during testing, but after deployment passwords will be changed.

[2] Salespeople will be able only to create, view, and edit customer and invoice records for customers and invoices in their region only.

[3] Salespeople will be able to run the regional revenue report, but it will report only on the region to which a salesperson belongs.

Note in Table 12.1 that Managers have full access to create and delete data records, that Salespeople have limited access to do so for customer records, and that people in the Admin role cannot make any changes to invoices (however, they can view invoice information).

An additional distinction to note is that although people in the Admin role can view invoice information, they do not have access to the Invoice List or Invoice Detail layouts. This suggests that invoice information may be displayed on other layouts, perhaps as related fields or within a portal. It is important to consider both the capability to view data globally throughout a system and the capability to make use of specific layouts. In most cases, it is not enough to simply limit access to specific layouts; you need to also limit access to the data itself.

Security grids such as the example in Table 12.1 need to be as detailed as they need to be: In other words, they depend on the circumstances you face. If you don't have six different roles in your organization, clearly you won't need the distinctions made in the example. If you want to grant some development privileges to people other than developers (say, the capability to modify certain layouts), you'd need to add a subsection for that. This table should be taken as an instructional example and is not a comprehensive representation of a real-world system.

Finally, be sure to grasp the use of the phrase "to view" (both in this book and within FileMaker itself). In this context we mean the ability to consume the data in various ways; a user can see the data onscreen, can choose to print (if printing is enabled for the user's account), can export that data (if exporting is enabled for the user's account), and can email data.

Planning Implementation

Implementing security is done largely in the Define Accounts & Privileges dialog, but before walking through the mechanics of setting up security, you will need to plan where and how to implement it from an overall perspective.

A significant part of your planning will need to include user interface considerations. If a user shouldn't have access to run a script, for example, she should be presented with a graceful message to that effect if she inadvertently attempts to do so (as opposed to the script simply not doing anything). Likewise, if someone doesn't have access to a layout, your navigation system should reliably prevent him from ever being left on that layout, or at least you should provide a way to get back to the part of the system to which he does have access.

Another consideration is the aesthetics of seeing displayed in various places throughout the system. FileMaker will display when a user isn't allowed to view field data, record data, or a layout. If you do not want to remind your users of their own limited privileges, you may choose to hide away restricted areas by controlling navigation or window access.

FileMaker 8 introduced the Custom Menus feature, allowing you to deal with many security considerations by simply disabling access to certain menu items. For example, if you want to restrict users from being able to delete all records, you can choose to disable that menu item. It is critical to note, however, that this is simply a user interface mechanism. If users have some other means of deleting records (say, through a custom script you've written or some other aspect of FileMaker's interface), the only way to ensure that they cannot perform the restricted action is to control their ability to perform the fundamental action in their security settings. User interface issues should really be considered only for aesthetic and usability reasons.

For more detail on custom menus, see "Working with Custom Menus," p. 373.

Here's another example of how security plays a role in your planning: If you want to prevent people from having to see fields to which they have no access, you can choose to place them on their own layout. In FileMaker 8, you can control access to specific layouts; however, you cannot prevent users from accessing a tab pane if they have access to the layout on which it sits. Given this, you may choose to create separate layouts where a Tab Control object might have served had you not considered security issues.

Your solution's scripts are another area where you will want to plan for different levels of access. If a user has a means of running a restricted script (say, by clicking a button that is omnipresent on all layouts), you will need to present him with a message that he is not permitted to use that function. A more subtle issue is what to do with scripts that are internal to the database operations; for example, you might write a script that allows users to choose different printer settings. If you restrict access to this script for some users, but then reference the script from all your reporting and printing routines, you will need to address that conflicting dependency. Likewise, if a script takes the system to a layout tied to a data table in order to establish context but the current user doesn't have access to that layout, your script may deliver unexpected results.

However you choose to approach security in your system, thinking through the user experience will be an important part of the overall plan. You should note in your layout designs and scripting where security considerations need to be taken into account.