FileMaker Security: What you need to know by Pam Rotella
So, you're programming a new FileMaker database application, and the data is sensitive enough to restrict. FileMaker offers a nice security feature where you can set passwords and group rights, and so you set up a few users and groups, and move onto the next development task.
Not so fast! Every program has security vulnerabilities, and if you're serious about your data's security then you have to learn what those vulnerabilities may be, and what can be done to minimize them.
Answer this question: How secure does that data really need to be? Is it payroll information, for example -- are certain people with a password not supposed to see all of the data in all of the tables, like temporary accounting help seeing management's salaries and addresses? Does your database contain medical records, meaning that HIPAA laws apply? Are there confidential notes that should be restricted to the originating author or a related group of people?
Maybe you need to learn a few security considerations known to most FileMaker developers, but rarely learned by FileMaker's small army of user-programmers, or their usual IT support staff.
The basic password always seems like a good place to start, but there are some issues of complexity around that simple-looking feature. It matters whether you're authenticating with a network password or a password hard-coded and maintained in the FileMaker files. It matters whether you're keeping the files on a FileMaker Server, a regular network server, or a local hard drive (and whether that hard drive is virtual or physical). It matters whether people with a password shouldn't see certain fields. It matters whether you're creating the files with a regular copy of FileMaker, or an "advanced" (developer's) version of FileMaker. So let's start with the best-known security features, and work our way through the list.
Safeguarding against the rare attack
What I'm about to cover may make certain people nervous. After reading about FileMaker's best-known vulnerabilities, managers may think that FileMaker software has big security holes, when in fact FileMaker security is above average. For even minimally-protected databases, bypassing FileMaker security would require a determined and intelligent user -- someone with strong incentive, access, money, skill, and patience. The average employee isn't willing to spend so much time and effort on something that could lose him a job. In general, good network security, trustworthy employees, and a few precautions specific to FileMaker will see you through.
Security for typical threats
Most people have no desire to become hackers, especially the type of hacker who goes to jail. The majority of database damage and data loss is unintentional, a product of human error and lack of computer skills by hard-working people who simply make a mistake.
Whether that mistake was the result of an overworked staffer trying to accomplish too much too fast, or a person who deliberately ignored instructions and shared a password, is a personnel issue. Database developers can try to produce products that minimize human error, and they can warn management to forbid password sharing, but developers can't eliminate the threat entirely. In the end, clients are responsible for ensuring the integrity of their own data. Developers produce the code; their clients decide how to use it.
Password sharing and granting rights beyond a person's job description are the most common database risks in a work environment. If a person temporarily steps into a higher password, he or she does not have the same level of experience as the password's original owner. Deleting records, replacing all values in a field across a found set, even design access may now become available, and the user with the new password doesn't understand what some of those menu items do. When fumbling for the one feature he or she needs, a massive, data-destroying menu option may be launched. Do-it-yourselfers may also alter scripts in ways that make them unusable or worse, or write reports that misrepresent data, or alter screen layouts and unintentionally alter the tab order, making data entry by other users slow and nearly impossible. All of these are common mistakes by good employees who were only trying to do their boss a favor and produce a critically needed report in a hurry.
Failure to restrict rights appropriately can produce similar results, including data compromised by employees with ethical problems. Usually employees who deal with money have their deletion rights restricted and a good audit trail is programmed into the system. In this way, an employer can minimize the risk of embezzlement. Many employers would like to believe that all of their employees are trustworthy, and in my earlier years I'd hoped for the same and occasionally produced a product with few or no end user restrictions. I don't do that anymore. In fact, I have a true story for less experienced developers.
Years ago, when version 5 was the latest and greatest, I programmed a small FileMaker database for a friend, just as a favor, no money involved. She needed something better than index card files and spreadsheets to track memberships at her small business. I didn't set up audit trails, other than a serial number not seen by end users, and of course employees who had to work alone were granted deletion rights. If they deleted a record, all we'd see was a break in serial numbers. They had to be able to void transactions, and I only had a few days of free time to program the system, not a few months. It was better than a spreadsheet where employees could alter absolutely everything, I told myself, and my friend was delighted with the new system.
Well, put a temptation in front of employees, and most will resist, but not all. My friend had an employee who was stealing from her by deleting transactions as though they were mistakes or had been voided, and of course employees like this are eventually caught -- one day my friend the owner was working the front desk and a customer came in with her membership receipt, but it wasn't on the database, and so we checked the serial numbers, my friend checked her surveillance video, and the employee was fired. It turned out the offending employee was the owner's best friend's daughter. Rather than involving law enforcement, my friend took the evidence to the girl's mother, and explained why she was firing her daughter.
That was at a small business in a small town, but I've encountered the same problem at one of the largest corporations in America. The IT department in that company felt that it could trust its own desktop support analysts, and so the FileMaker database controlling help desk tickets and desktop support assignments wasn't locked down at all. Our technicians could correct just about anything on the tickets, even delete them.
One day, a help desk analyst came to me and said that a lady had called to follow-up on a problem she'd reported days earlier, but he checked and couldn't find her ticket number on the system. I checked the number, and it should have been there, but it wasn't. Someone had deleted the ticket, probably because her problem was harder and more time-consuming to solve than most. A new manager had come to the desktop support team recently, and was pressuring his techs to close their tickets faster. After many problems with desktop analysts deleting problem tickets in order to improve their job performance statistics, I locked that database down so tightly that desktop technicians could only change a ticket's status and type in their own notes on their own tickets, in one field meant for that purpose. Otherwise they had to walk over to the help desk and ask a help desk analyst to make any other corrections for them. One day, the desktop technician suspected of being the worst offender for deleting problem tickets walked up to my desk and told me "You finally got me. I can't do ANYTHING in that database!"
It wasn't a mistake that we'd originally allowed our technicians the right to correct tickets as they saw fit. We wanted to trust them and allow them to control the ticketing system out of courtesy and respect for them. We didn't think that any of them would delete a ticket and leave a customer hanging. But a little heat from above, management wanted them to close their tickets faster, and suddenly one of the desktop analysts most popular with users because of his good "desk side manner" decided to make the more difficult tickets disappear. That's a lesson that novice developers learn quickly -- no matter how nice they seem, lock 'em down tight. Closing tickets faster is a management issue; if demands were unreasonable, then the proper course of action was not to destroy the data. Perhaps a discussion on rating tickets for level of difficulty and allowing extra time was appropriate. Making harder tickets go bye-bye was not.
The average employee making mistakes and the slightly unethical employee are usually in the same security category. They're locked out easily. If you set up good security within FileMaker to restrict their access, most aren't ambitious enough to learn the details of database programming and network administration for small and risky rewards. Give them an easy temptation and they may take it; put up a few roadblocks and they're discouraged. Usually they don't try much harder than guessing the occasional password.
FileMaker's built-in security menu -- in version 11, menu items "File," "Manage," and then "Security" -- allows the creation of user groups (called "Privilege sets") and user IDs with passwords. (More on password authentication options and risks later.) Groups or privilege sets can then be restricted on the basis of rights categories, individual layouts, fields, and even scripts. (For the more detailed layout- and field-level restrictions, go to the "privilege sets" tab and create a new privilege set, then under the "Data Access and Design" section, select "Custom privileges" from the drop-down lists.) Very few users, if any, should have design access, for example. In contractual relationships where the developer owns the code, no design access should be granted to anyone but the developer. And remember the part about locking users out on a field-level basis. It helps with users who know how to use your database as a back end to a new file that they program themselves (in other words, anyone willing to read my article on data separation). More on that later.
A more sophisticated solution may use relationships and startup scripts to guide users to different menus and layouts according to their rights levels, thus restricting each user to the smallest set of fields and records needed to accomplish his or her tasks. For users who only need to see their own dataset within certain tables, relationships including users' account IDs (typically linked to a field storing data in a creation or assignment field) can be used to restrict data. The FileMaker application menu can be modified or eliminated entirely (a tool found in "advanced" or developers' versions of FileMaker), making the database application button-driven and even group-sensitive, with the same buttons taking different users to different places depending upon their security classifications.
Another good feature to consider is an audit trail. Typically financial systems require them, but other database applications handling sensitive data may benefit from a good audit log, too. FileMaker has improved its features in this area, but not enough to avoid all extra work for good developers. If a field within a record is the audit trail, that entire history is lost if the user finds a way to delete the record. In systems needing a more reliable audit trail than a lonely field, the log item model is better -- a separate table tracking all changes, even dates and times of logins and logouts. Records in a separate table can also be organized, to group and report on activities related to date, time, table, user accounts, or other variables.
Management tools are a good way to grant database administrators control over user accounts. A button-driven administrative screen can provide the ability set up or modify user IDs and modify script-driven access, all without the need for the DBA to understand FileMaker programming, or the need to grant the DBA full programming access to the database files. It's also important to consult with management on specific restrictions wanted for each job description and/or employee. I find that management styles usually fall into two categories -- the "I can handle it" manager who would rather use tight personnel management than programming restrictions, and the "extreme detail" manager who wants as many business rules and audit trails built into the database as possible.
For those situations where data loss or design damage occurs, due to user error, password sharing, or other problems, the responsible path is to ensure that files are backed up daily by the server. That gives the customer a path to recover most of the lost data and code. Design damage is another good reason for my data separation model -- typically the damage will be in the interface files containing no data, which can simply be replaced with the last known good copy.
FileMaker hosting and networking
Where database files are physically saved and used is where they are "hosted." FileMaker as a PC database allows hosting on servers, network drives, and local computer disks.
In fact, FileMaker has always offered extraordinary hosting and networking features. In its earlier versions, FileMaker offered peer-to-peer networking. The first person who opened the file was its "host," and then a number of people could open the file afterwards and become a "guest" in the file(s). When the host wanted to close the file, FileMaker would notify the guests, who were given time to prepare to disconnect (i.e. finish their immediate work or request more time), and then the guests would automatically exit the file when it was closed by the host. The guests who wanted to continue working could then reopen the database themselves, with one of them becoming its new host.
The problem with this model was that in the early 1990s, when versions 2 and 3 were FileMaker's latest and greatest, computers weren't as stable as they are today. If the first person to open the file had a slower, older machine with less memory than other users, everyone entering the files as a guest of the slower host was limited by the power of the host's machine. And if the host's PC froze up and had to be rebooted with the database open, which seemed to happen a lot, then the database could become corrupted. While FileMaker database files are the most corruption-resistant of any database product I've ever used, and also offer a "recover" menu item that nearly always repairs files too corrupt to open, some databases were corrupted and lost with repeated computer crashes using the old peer-to-peer networking system. FileMaker still offers peer-to-peer networking as an option, but the number of users allowed in the database at one time is restricted to a very small number.
The most respected feature of FileMaker hosting and networking in its earlier versions was its cross-platform networking capability. Hosting a FileMaker solution on either a Mac or PC allows both Mac and PC users to be guests of the file(s) at the same time. In the 1990s, Novell was the networking standard of the day, although Macs and PCs shared FileMaker files across Apple and eventually the Widows networks prevalent today. The Mac/PC compatibility applies to both peer-to-peer networking and FileMaker Server software.
Although FileMaker Server software was available for hosting databases on a dedicated server, earlier versions of the server software weren't very popular. FileMaker marketed FileMaker Server as beneficial to networks with dozens of users or more, with better database speed and reliability than peer-to-peer networking. For those companies with only a few database users in any given database at a time, spending extra money on server software and hardware seemed like an unnecessary expense. Putting a FileMaker database on a dedicated machine -- any machine, server or not, server software or not -- could improve the speed and reliability of peer-to-peer networking. A dedicated computer, one not used actively for other applications and processes other than FileMaker, was the most stable model for FileMaker peer-to-peer networking, as the hosting machine would always open the files first and always be the host. The computer wouldn't lock up from memory errors as often as end-user computers used for other applications simultaneously.
I worked at a major insurance company where we had an older Macintosh computer set up in the back of the help desk area, hosting a FileMaker database application that my boss and I had designed. This was in the mid-1990s, and we were using FileMaker version 3. That database controlled all service ticketing and reporting for the IT department, it produced reams of reports for management on I.T. Department performance every month and at the start of each quarter, it paged technicians when tickets were issued, it even had features like assigning the least busy analyst to tickets in order to even out the workload, and ...let's just say that it tracked pretty much everything. Despite the hosting machine's age and the use of peer-to-peer networking exclusively, database performance and stability were much like that of a server. I don't remember the help desk database application freezing even once, although we did have to reboot the old Mac due to performance issues maybe twice in two years.
In more recent versions, FileMaker restricts the number of users who can share a database simultaneously via peer-to-peer networking to just a few licenses at a time, making server software necessary in all but the smallest multi-user solutions. The more recent versions of FileMaker Server products also have features that everyone wants, such as automatic backups, the convenience of external password authentication, and the ability to restrict who can upload and download files or save copies of the files. Of course, there are other server features making the application more robust, but I mention these three features because they're all relevant to FileMaker security. In fact, when protecting a database from advanced computer users, it's critical to understand the vulnerabilities of these particular features.
Before I cover FileMaker Server, however, I should explain what an advanced user can do with a copy of a FileMaker database.
Password Recovery Key Software
If you've never heard of a "password recovery key," feel free to Google it now. You can combine the term with popular software program names, such as Word, Excel, or FileMaker. Don't click on the links from the search unless you know and trust the site, though -- the type of sites that market password keys are often the same sites found to be dangerous by website virus scanning services.
Password recovery key software can unlock or reset passwords, and is typically written for common software programs like MS-Office products. Password key software does exist for FileMaker, and it works.
For popular software programs, password key software is easily found online, and it isn't expensive. The software can work at least two different ways -- it can reveal user IDs and passwords in a given file, or it can overwrite the passwords and provide you with a new one.
FileMaker (the software manufacturer) has claimed in the past that running FileMaker database files through the advanced version (what was called the developer's license in earlier versions) will protect its files from this type of password-cracking software, but I'm not sure that advanced version utilities can protect databases from every type of password key, especially those that work by overwriting the password area of a file.
Password key programs present an interesting problem for anyone trying to keep a database secure -- namely, if a copy of the database can be obtained, then it can probably be opened by a software recovery key. The key word in that sentence is "if." IF a copy of the database can be obtained.
Because of password recovery key software, and vulnerabilities mentioned in the next section on external password authentication, it is critical to restrict network (or physical) access to databases with sensitive information or code. Anybody can try to unlock a file with a software key program if he or she is able to download that file from a server, or even a network or local drive. Rights to upload/download files and rights to open/edit FileMaker databases are two different things -- users don't need upload/download rights to work in the files. In other words, upload/download access to FileMaker servers, server directories containing FileMaker files, and local drives must be restricted to those who need to have that access as a part of their jobs. Allowing extra users to upload and download files invites password hacking via password recovery key programs.
Network administrators can restrict upload and download access to FileMaker servers to only those users who need that access for their jobs (typically database developers, network administrators, and owners). One feature of FileMaker Server is that it disables the "File"/"Save a Copy As" menu item in its open files, and so users without upload/download access aren't able to gain a copy of the file to unlock with a password recovery key.
Restricting access to databases not on a FileMaker server can be challenging. Physical hard disks can be password restricted, but even so, connecting to a network may expose a local database via peer-to-peer networking access if sharing is enabled, or if other users are granted rights to the hard disk. Network drives with restricted databases would need careful user rights administration at the network level to prevent access by unauthorized users, but again, peer-to-peer networking could expose the files if sharing is enabled. Even if a user can access the files for read/write access, but is restricted from copying the files at the network drive level, the "File"/"Save a Copy As" menu item could be available. The "save" menu item can be disabled with FileMaker Advanced version, but then the menu with that feature disabled must be locked, and rights must be restricted so that advanced users can't add the menu item back. Also, if a file does not need to be shared, the file can be restricted to "single-user" (from the "File"/"Sharing") menu. Even so, if a file can be accessed through the network, someone with read/write access can still open the file as a single user and use the "save" command to gain a copy locally.
There are too many variables with FileMaker databases when networked without FileMaker server, and so if the data or code is sensitive enough to protect, then I don't recommend networking FileMaker databases anywhere but on a FileMaker server. If the database is just an office supply vendors list, it's unlikely to have much value to anyone but the person who orders office supplies, unless somebody has a grudge against the person maintaining the list or somebody on the list. However, if the database contains information of monetary value, for example bank account information, or payments that could become larger for certain employees if someone is able to access the database and manipulate figures, then protecting the data should be a priority.
Sometimes a user may feel that the file and data are too sensitive to host on a server. Although good internal security combined with hosting on FileMaker Server makes it difficult for an outsider to hack, at some companies many people have upload/download access to the FileMaker Server. Perhaps the database controls executive bonuses, and all levels of management and all members of the I.T. department have upload/download access to the server -- possibly hundreds of people. In such cases, the developer can work with network administrators to restrict the file as much as possible on a network or local drive, and to restrict the location of file backups (more on backups to follow).
When hosting on local or network drives, another security consideration is that some "hard disks" are virtual now, meaning that the company's I.T. department has programmed users' computers to appear to have a hard disk, but that storage space is in fact on a server. That allows the I.T. department to regularly backup information on "hard disks" to avoid as many lost files as possible, and also to push software upgrades to users' "machines" more easily. Such automation is admirable, but a disadvantage of this model is less privacy for users, meaning that sensitive databases can become compromised more easily. Anyone with access to the network drive in question can obtain a copy of the database, and the I.T. department may or may not understand the potential consequences of such exposure.
Password recovery key vulnerability is a characteristic of local authentication, meaning that the user IDs and passwords are stored internally, inside of the FileMaker database, typically set up one user at a time by the file's developer or administrator. External authentication has its own vulnerabilities, to be covered in the next section.
External Password Authentication
The convenience of external password authentication is expected in today's office environment. When a user logs onto his or her computer, the same password opens the computer, the network connection, and most other applications used by the same person for that session. It makes life easier to have only one password for most applications, and I.T. help desks don't have as many forgotten passwords to reset. I've worked in companies where approximately 50% of all calls to the I.T. department were related to password resets, and I suspect that statistic is similar for all companies requiring regular password changes.
External password authentication can also make programming slightly easier for the database administrator, as the majority of users can be added and dropped by simple network group maintenance external to the application. However, most developers need to program a good ID and password tool for future DBAs regardless of whether external authentication is used. Database user pools change over the years, and companies don't like to call the developer or deal with FileMaker's security feature every time that a simple add/drop is required.
A word of caution to developers on external authentication -- it can lock users out of a file unintentionally if you don't understand FileMaker's authentication order. (That's when a password recovery key could actually save your job.) FileMaker's security tool allows a mix of users with both external and internal authentication, and FileMaker authenticates according to the order on its user list. If the same ID appears twice, the first ID found on the list is used to log into the database.
This is how the incorrect order on the list can lock you out. If a developer or DBA has a higher password hard-coded into the database, but is also a member of a network group (for example "infotechnology" or "accounting") with lesser rights to the file, placing the group higher on the user list will lock out the advanced ID. Why? Well, let's say that the group has basic read/write access to the database, but not design access. If FileMaker finds the developer's or DBA's user ID in the network group first, then it'll log the advanced user in as a member of that group, and never even get to the ID with design access. Similarly, being a member of two different groups with two different access levels can do the same thing -- the group with minimal rights is accidentally placed higher on the user ID list, and that's the group that FileMaker finds first.
Authentication order is why FileMaker prompts for confirmation before changing the order on a user ID list. It seems like an unnecessary step, until you realize that the wrong order can lock people out of the database.
External authentication has other security vulnerabilities, sharing the same risk with internal authentication of allowing users to obtain a copy of a file. However, the route to unauthorized access via a copy of the file could be more difficult with external authentication, depending upon the tools available to the would-be hacker.
External authentication is usually handled by a network utility, for example Active Directory or Open Directory, although it isn't necessary for the FileMaker programmer to know all details of a network's authentication methods. Databases need to be portable from one network to the next, from one server to the next. FileMaker can't analyze network settings each time a change or upgrade takes place; external authentication needs to start working as soon as it's dropped into a network environment. Yet portability can make external authentication vulnerable to would-be database hackers.
When a accounts are created in FileMaker's File/Manage/Security menu, and external authentication is specified, the individual FileMaker file doesn't know and doesn't care what a network uses for external authentication. It doesn't even ask. Authentication is a function of FileMaker Server, not the individual database file. FileMaker is only concerned with finding the account or group name currently logged on, to compare that name to accounts granted external authentication access to the file.
And there's the security vulnerability. If someone were to take a copy of the file and put it on a FileMaker server in a different environment, for example on a competing company's network or a home network, then the user account or group names could be duplicated on that outside network with different passwords -- passwords created by the person who took the FileMaker file from its original location.
Often at least some user or group names are easy to discover or guess, and so the ability to download or copy a file using external authentication provides easy access to would-be hackers using this technique. For example, it may be well-known at a company that a network group called "Accounting" has access to all FileMaker financial databases, or that the Director of Finance is named Bob Jones and that he was assigned the ID "rjones" -- due to his job title an ID with top access to financial information. If someone were to download the file from the company network and emulate the group "Accounting" or the User ID "rjones" on a network outside of the company, then financial information could be viewed by an unauthorized user.
The active user or group account ("active" in this case meaning currently logged on) may come from the network or the local PC -- FileMaker Server will check both locations before giving up on a match and returning an error message. If a user can log onto a system connected to FileMaker Server with either type of account location, FileMaker Server may open the file.
Again, FileMaker database files need to be portable. It isn't practical for the software to be particular about the way that an account is authenticated. If FileMaker's external authentication utility were sensitive to network settings, then a database developer would need to revise each database on each FileMaker server every time that network settings were changed.
External authentication is a characteristic of FileMaker server, and so a machine with FileMaker Server software installed and running is needed to exploit this type of security vulnerability. That would require a skilled and dedicated computer user, or someone who already has a parallel FileMaker Server environment available. Therefore it's a vulnerability that most employees can't or won't exploit. This type of deliberate hacking is usually limited to people with strong incentive and excellent software skills, with access to FileMaker Server software or the determination to procure a copy outside of normal channels. However, it is important to be aware of this vulnerability, and to take action to prevent it. When incentive is strong enough, people who have the skills and resources may try to exploit it.
It may seem that the only danger of downloading a database copy to a parallel network is the viewing of data by an unauthorized user. That's possible, if the user is able to download but not upload the file. However, gaining both upload and download access would allow an unauthorized user to download the file, take it to a parallel network and change the data, and then return the file to its original location, overwriting the original file with the changed copy. (More on this type of vulnerability in the section on backups.)
Access by an unauthorized user is often devastating to the person whose account is hacked, because any record modification tracking built into the database would attribute changes to the hacked account's original owner. If a good audit trail exists, however, changes made under strange circumstances may be detected -- perhaps a field shows when a record was modified, and after finding a suspicious amount somebody notices that the record was modified at 3 a.m., clearly outside of working hours. If modification tracking is good and the "error" is caught quickly, it may be possible to see that the records could have been altered by somebody other than the account recorded by the file's tracking system. However, many systems are not designed with a good audit trail in place, and so if a person's account can be accessed this way, it is possible that changes will go undetected, or that they will be blamed on an innocent person. It's also possible that a hacker can gain top access to the file and change tracking data to cover his tracks.
Some may think that by keeping files open on the server, that anyone not having administrative access to the FileMaker Server tools would not be able to close a file and therefore would have trouble downloading an uncorrupted copy. This is sometimes the case -- file access may be denied, or the file may become corrupt if copied while open -- but not always. Although FileMaker (the software manufacturer) recommends that backups are made by the FileMaker Server software and then the backups moved from there, some network backup programs can backup a FileMaker server while in use pretty effectively. Even if the file is corrupted as a result, FileMaker is very good at recovering corrupted files, and FileMaker users often don't think it unusual to wait for a few seconds while FileMaker recovers a file that mysteriously went corrupt. Also, often the type of person who has upload/download access to the server has or can request access to open and close the files. The person could also watch for I.T. department maintenance notices, and time unauthorized access around scheduled down time for the files.
Files that do not utilize external authentication in their account security settings won't have this vulnerability. It is also limited to those situations where the would-be hacker can download and perhaps upload files from FileMaker Server (or download from the location of recent backups, and possibly upload to the server later if an upload is necessary -- more on backups later).
As with files using internal password authentication, restricting upload/download access to FileMaker Server to a minimum number of people can limit this type of security risk. Rights to FileMaker Servers should be granted cautiously.
"Front ends" or data separation as a security risk
Restricting access to FileMaker Server helps to minimize the previous two security risks covered here. The next potential problem applies to any FileMaker database where a user has password access, regardless of rights to any server. The database may be on a server, a network drive, or a physical drive.
A "front end" in the database world is usually a user-friendly file that displays data without actually containing data of its own, or at least not much data. FileMaker makes a great "front end" while a plainer database product -- perhaps SQL -- is sometimes used as the "back end" (the data-containing file). Even before more recent versions of FileMaker, which allow direct access to a SQL table simply by dropping it into a relationship graph, plug-ins were available to allow FileMaker to serve as a front end to SQL databases. In fact, SQL databases are used as back ends by many products, and so I'll cover SQL databases as the first example here.
Let's say that a company or corporation has just spent hundreds of thousands of dollars on a new accounting application, one that handles all finances for the company and offers good payroll, invoicing, and reporting capability. It has a SQL back end, which is very common in applications that house a lot of data, and a well-designed user interface or "front end."
The question is, how secure is that accounting data? You won't know simply by following the front end's screens and menus designed especially for each person's job duties.
The answer comes from how much access passwords have to the SQL back end of the application, not the front end that people see during their everyday activities. If a password allows people to view or change most fields in the SQL portion of that application, and they're locked out mostly by the application's front end, in other words, creating their own front end will allow them to bypass most of the security in that application.
Building a front end in FileMaker is easy, as my article on data separation shows. Many power users can do it. Often that's how they make their own print reports when not granted layout design rights to a file -- they create a new FileMaker file locally with full design rights, drop a FileMaker or SQL table into FileMaker's relationship graph in that file, and then start creating their own print reports. Many companies won't budget enough database development time for all of the reports that every user wants or needs, and so advanced do-it-yourselfers find a way around budget shortages.
Access to SQL-based tables through other programs could be interpreted as a good feature or a bad design flaw, depending on a company's goals for its SQL-based application. To create special screens or reports, such flexibility may be a welcome capability. Software manufacturers have to allow some back end access for report writing, after all -- otherwise they'd lose sales because they can't possibly program every accounting report that every customer wants. Allowing SQL-compatible reporting programs to wrap around their SQL tables gives their clients more power to write reports locally, and makes the SQL-based software more versatile.
The question is, does such flexibility create an unacceptable level of risk.
[I apologize to readers, but the full article is much longer than originally expected, and is not yet ready for full publication with diagrams -- please check back again in the next month or two!]
- Pam Rotella is a Senior FileMaker Developer with over 20 years of I.T./PC database programming experience, more than 15 of those years working with FileMaker. She frequently consults with major US corporations and medium-sized companies, and can be reached via e-mail at firstname.lastname@example.org.
DISCLAIMER: This site is not affiliated with FileMaker software company, but rather seeks to provide a forum for credible discussion on software use. Tips provided here are not intended as individual consultation or advice. All papers published here are the opinions of their respective authors, and are not necessarily endorsed by FileMakerPapers.com or Pam Rotella.