Have you ever received a really ugly data file that you needed to import into FileMaker Pro? As flexible and forgiving as FileMaker Pro can be in terms of what it can import, there are some files that even it can't deal with.
A few years ago, I was doing some consulting work for a company that brokered leads for the financial services industry. We'd get hundreds, sometimes thousands, of leads every day from dozens of different sources. Needless to say, the format of the data that we received was very inconsistent (as were the delivery methods). Some leads would be received as Excel spreadsheets via email. Others would be come in as Word documents. And some of our more sophisticated suppliers would provide us with nicely structured XML documents (in real time, no less!).
One supplier in particular would send us extremely large data files with hundreds of thousands of leads in them. These were Unix files, and they were a mess. Not only were the files huge, but there were all kinds of characters in them that would cause the tools that I was using at the time (SQL Server 2000's Data Transformation Services (DTS) and FileMaker Pro) to "choke" on them during import.
To deal with these types of files, and get them into a format and condition that FileMaker could deal with, I used a product called TextPipe Pro. This is an amazing program that can work magic on any text file. I used it to replace hidden, problematic characters that were in the files. I used it to reformat data from fixed width formats into delimited formats. I used it to merge multiple files. It quickly became something of a swiss army knife to me. With TextPipe Pro, I could get almost any file -- no matter how large or bizarre it was -- into FileMaker. And once data is in FileMaker, the sky's the limit.
This is the first time that I've reviewed and endorsed a product on Filemaker Addict. I really feel that strongly about TextPipe Pro. It's an awesome program, and a great addition to any database developer's toolbox.
TextPipe Pro is developed by DataMystic, a Melbourne, Australia-based company. Information about TextPipe Pro can be found at http://www.datamystic.com/textpipe.html. Be sure to check out some of the other applications that they provide, including EmailPipe (which you can use to convert large batches of emails into text files for later processing), DataPipe (which you can use to perform very sophisticated search and replace functions on fields in just about any database -- including FileMaker Pro).
Of course, there is a "gotcha." In this case, it's that DataMystic's products run only on the Windows platform. But if you're running Parallels, you're in luck.
So the next time you run into a data file that just won't import, check out TextPipe Pro. I think you'll be glad you did.
-- Tim
P.S. As I mentioned above, this is the first product that I've reviewed on FileMaker Addict. But it certainly won't be the last. I'm planning to review everything from utilities (like TextPipe Pro) to plug-ins and even books. If there are products that you'd like me to review, please drop me a line and let me know. I look forward to hearing from you!
FileMaker Addict Has Moved!
Tuesday, May 29, 2007
Tuesday, May 22, 2007
Improving the Security of Your FileMaker Databases
To effectively secure your FileMaker databases, you need to do so on multiple levels. This means not only securing the databases themselves (which is a good start), but also securing the servers that they are running on, the network that the servers are hosted on, and so on. A good strategy is to provide many different layers of security, so that if a single layer is compromised, the other layers will continue to protect your data. This strategy is often referred to as "Security Layering."
What follows is a list of some of the layers that you should think about in order to effectively secure your databases. My goal isn't to tell you exactly how to protect each one, as the "how" will depend on a number of different factors (the platform that you're running, your network, your physical environment, the resources that you have available, etc). Instead, my goal is to get you to think about these layers and what you can do to better secure each one.
Securing Your FileMaker Network
The outermost layer that you need to secure is your local area network. The details for how to secure a network are too involved to cover in-depth here. However, here are a few things that you should consider:
• If you are running a wireless network (and most people are these days), be sure that your router is configured so that anonymous access is disabled and that users need a password in order to use it. Configure your router to use WPA (Wi-Fi Protected Access).
• Change the administrator password of your router so that it is something other than the default password. You'd be amazed at how many people plug in their routers and forget about them -- and the admin password is still set to "admin."
• If you have a hardware-based firewall available, by all means, use it.
• If possible, configure your router to use NAT (network address translation), and configure it so that only the ports that need to be open and forwarded are done so.
• Consider setting up a VPN (virtual private network). They take time and money to setup, but the benefits are well worth it.
• If your business or organization has intrusion detection systems available, use those as well.
Securing Your FileMaker Servers
The next layer to protect are the servers themselves, and this starts with restricting physical access to your servers. If possible, they should be in a secured server room or office -- a location where only those who absolutely need to be able to physically interact with the servers can get to them.
You also need to secure your servers at the operating system level. Make sure that you use strong passwords for the operating system accounts, and also consider limiting the number of accounts that you setup in the first place.
It is also a good practice to remove or disable unnecessary services that are running on the servers. For example, if you do not need FTP access to your server, disable it.
I mentioned hardware-based firewalls above, but software-based firewalls are important as well. Both OSX and Windows now include built-in software-based firewalls. Take the time to configure them.
Apply security-related patches and service packs on a regular basis. Be sure to apply patches released for both the operating system and for FileMaker.
Whenever possible, run services (such as the Web and FTP services) on non-standard ports. It can be difficult -- and very inconvenient at times -- to do so. However, the additional level of security that this provides makes the trouble worth it.
And finally, be sure that any logging or auditing functionality that you have available to you is enabled, and that you review the logs regularly. Doing so might reveal attempts to access your server. The logs are also very helpful when it comes to troubleshooting some database problems.
Securing Your FileMaker Databases
The inner-most security layer is FileMaker itself. Make use FileMaker's built-in security model to protect the databases themselves. Use strong passwords (meaning that they are very difficult to guess), and change the passwords frequently. Unless you absolutely need it, disable the "guest" account. Setup and assign privilege sets so that users only have access to the data, layouts, and scripts that they absolutely need.
It is also important to consider the security of any development and test databases that you might be using. If you are using copies of your production data in your development or test databases, consider scrambling the data in some way.
Securing Your FileMaker Backups
It's one thing to prevent unauthorized access to the production databases that are being hosted with FileMaker, but have you considered the security of your backups? What valuable data would be compromised if someone simply walked off with that shiny new hard drive that you've been backing up to?
If possible, store your backup drives in a safe location -- or even in a safe if you have one. The same goes for any CDs or DVDs that you may have burned backups to. Also, consider encrypting your backups if possible.
One last backup-related topic worth mentioning: Storing backups in an off-site location (meaning, in a location other than where the server itself is) is an important part of all disaster recovery plans. Just be sure that the off-site location is secure. (I'll cover disaster recovery plans -- another very important topic that is often overlooked in the FileMaker community -- in the future).
Securing FileMaker Databases on Notebooks
Think of your company's notebooks as a special security layer -- a layer that is always on the move. As I mentioned in a previous post, a recent report from the IT Policy Compliance Group indicated that more than 1 in 10 "data loss events" are caused by the loss or theft of notebook computers. If you've got FileMaker databases running on your company's notebooks, then you're asking for trouble. You should seriously consider prohibiting sensitive data from being stored in databases on those notebooks.
Final Thoughts
As I mentioned earlier, the best approach to securing FileMaker databases is to do so on multiple levels. In this article, I listed several different levels and gave some general advice on how to secure each one. The goal wasn't to provide detailed instructions on how to secure each level. Instead, the goal was to get you to think about what you can do at each level.
Some of the things that I've suggested take very little time or expertise to do -- such as changing your router's password, turning off services that you don't need, and so on. Others take a little more time, and based on your technical skills, you might want to bring in a consultant to help with them. In any case, please take the time to consider some of what I've suggested here. The security of your FileMaker databases is not something to be overlooked.
What follows is a list of some of the layers that you should think about in order to effectively secure your databases. My goal isn't to tell you exactly how to protect each one, as the "how" will depend on a number of different factors (the platform that you're running, your network, your physical environment, the resources that you have available, etc). Instead, my goal is to get you to think about these layers and what you can do to better secure each one.
Securing Your FileMaker Network
The outermost layer that you need to secure is your local area network. The details for how to secure a network are too involved to cover in-depth here. However, here are a few things that you should consider:
• If you are running a wireless network (and most people are these days), be sure that your router is configured so that anonymous access is disabled and that users need a password in order to use it. Configure your router to use WPA (Wi-Fi Protected Access).
• Change the administrator password of your router so that it is something other than the default password. You'd be amazed at how many people plug in their routers and forget about them -- and the admin password is still set to "admin."
• If you have a hardware-based firewall available, by all means, use it.
• If possible, configure your router to use NAT (network address translation), and configure it so that only the ports that need to be open and forwarded are done so.
• Consider setting up a VPN (virtual private network). They take time and money to setup, but the benefits are well worth it.
• If your business or organization has intrusion detection systems available, use those as well.
Securing Your FileMaker Servers
The next layer to protect are the servers themselves, and this starts with restricting physical access to your servers. If possible, they should be in a secured server room or office -- a location where only those who absolutely need to be able to physically interact with the servers can get to them.
You also need to secure your servers at the operating system level. Make sure that you use strong passwords for the operating system accounts, and also consider limiting the number of accounts that you setup in the first place.
It is also a good practice to remove or disable unnecessary services that are running on the servers. For example, if you do not need FTP access to your server, disable it.
I mentioned hardware-based firewalls above, but software-based firewalls are important as well. Both OSX and Windows now include built-in software-based firewalls. Take the time to configure them.
Apply security-related patches and service packs on a regular basis. Be sure to apply patches released for both the operating system and for FileMaker.
Whenever possible, run services (such as the Web and FTP services) on non-standard ports. It can be difficult -- and very inconvenient at times -- to do so. However, the additional level of security that this provides makes the trouble worth it.
And finally, be sure that any logging or auditing functionality that you have available to you is enabled, and that you review the logs regularly. Doing so might reveal attempts to access your server. The logs are also very helpful when it comes to troubleshooting some database problems.
Securing Your FileMaker Databases
The inner-most security layer is FileMaker itself. Make use FileMaker's built-in security model to protect the databases themselves. Use strong passwords (meaning that they are very difficult to guess), and change the passwords frequently. Unless you absolutely need it, disable the "guest" account. Setup and assign privilege sets so that users only have access to the data, layouts, and scripts that they absolutely need.
It is also important to consider the security of any development and test databases that you might be using. If you are using copies of your production data in your development or test databases, consider scrambling the data in some way.
Securing Your FileMaker Backups
It's one thing to prevent unauthorized access to the production databases that are being hosted with FileMaker, but have you considered the security of your backups? What valuable data would be compromised if someone simply walked off with that shiny new hard drive that you've been backing up to?
If possible, store your backup drives in a safe location -- or even in a safe if you have one. The same goes for any CDs or DVDs that you may have burned backups to. Also, consider encrypting your backups if possible.
One last backup-related topic worth mentioning: Storing backups in an off-site location (meaning, in a location other than where the server itself is) is an important part of all disaster recovery plans. Just be sure that the off-site location is secure. (I'll cover disaster recovery plans -- another very important topic that is often overlooked in the FileMaker community -- in the future).
Securing FileMaker Databases on Notebooks
Think of your company's notebooks as a special security layer -- a layer that is always on the move. As I mentioned in a previous post, a recent report from the IT Policy Compliance Group indicated that more than 1 in 10 "data loss events" are caused by the loss or theft of notebook computers. If you've got FileMaker databases running on your company's notebooks, then you're asking for trouble. You should seriously consider prohibiting sensitive data from being stored in databases on those notebooks.
Final Thoughts
As I mentioned earlier, the best approach to securing FileMaker databases is to do so on multiple levels. In this article, I listed several different levels and gave some general advice on how to secure each one. The goal wasn't to provide detailed instructions on how to secure each level. Instead, the goal was to get you to think about what you can do at each level.
Some of the things that I've suggested take very little time or expertise to do -- such as changing your router's password, turning off services that you don't need, and so on. Others take a little more time, and based on your technical skills, you might want to bring in a consultant to help with them. In any case, please take the time to consider some of what I've suggested here. The security of your FileMaker databases is not something to be overlooked.
Monday, May 14, 2007
Protecting FileMaker Databases
The IT Policy Compliance Group recently released a very interesting report titled "Taking Action to Protect Sensitive Data". Here's a snippet from the report...
I sometimes think that FileMaker users tend to take the data in their databases for granted. In the many years that I've been using FileMaker, I've seen data lost in a number of different ways: stolen laptops, lost hard drives, and of course, good ol' user error. ("Oops! I deleted a record" and "I didn't think that Delete All Records meant that ALL of my records would be deleted.")
As FileMaker developers and consultants, there's a lot that we can do to help our clients and users to protect the valuable data that is stored in their FileMaker databases. Here's a list of some of the things that you can easily do to help keep FileMaker data safe...
Accidental Deletion of Records
In my experience, the accidental deletion of records is one of the primary ways that data is lost. Now that we can easily replace FileMaker's standard menus with our own custom menu sets (and replace standard functions with scripts), it is very easy to enforce business rules with regards to what can be deleted, when it can be deleted, and by whom. And one of the things that I like doing is not really deleting records immediately. Instead, flag records as being "inactive." That way, if a user does accidentally mark a record as inactive, you can "undelete it."
Don't Store Databases on Laptops
One of the findings by the IT Policy Compliance Group that was a real eye opener to me was how frequently and prevalent the theft and loss of laptop computers really is. More than 1 in 10 data loss event is caused by the loss or theft of a company laptop. So we need to be extra careful with regards to what we store on our laptops. Whenever possible, serve up your databases using a server -- don't run around with your senstistive data in your laptops. And if you need to run your databases directly on your laptop, consider using encryption and password protection. That way, if your laptop is lost or stolen, it will be more difficult for the "finder" (or thief, as the case may very well be) to gain access to your data.
Backup Your Data
Do we really need to discuss why you should be doing this? I sure hope not. There's really no excuse -- especially if your databases are hosted using FileMaker Server. Take the time to setup a comprehensive, automated backup plan. Be sure that your data is being backed up not just on the server itself, but also to an external drive or to tape. And please try to keep some of those backups in an alternate location.
Password Protect Your Databases
Require that your users use passwords that are hard to guess -- and make them change their passwords frequently. Disable the Guest account in your databases. Take advantage of all that FileMaker's priviledge sets have to offer in terms of what your users can see and do.
There's a lot more that you and your users should be doing to protect your FileMaker databases -- everything from using SSL certificates for Web-enabled databases to protecting the networks that your databases are on and so on. Perhaps the best advice I can give is this: Take the time to regularly review your security at all levels, as well as your backup strategy. When it comes to the security of your databases, it is always better to be safe than sorry.
In one form or another, human error is the overwhelming cause of sensitive data loss, responsible for 75 percent of all occurrences. User error is directly responsible for one in every two cases (50 percent) while violations of policy - intended, accidental and inadvertent - is responsible for one in every four cases (25 percent). Malicious activity in the form of Internet-based threats, attacks and hacks is responsible for one in every five occurrence.
I sometimes think that FileMaker users tend to take the data in their databases for granted. In the many years that I've been using FileMaker, I've seen data lost in a number of different ways: stolen laptops, lost hard drives, and of course, good ol' user error. ("Oops! I deleted a record" and "I didn't think that Delete All Records meant that ALL of my records would be deleted.")
As FileMaker developers and consultants, there's a lot that we can do to help our clients and users to protect the valuable data that is stored in their FileMaker databases. Here's a list of some of the things that you can easily do to help keep FileMaker data safe...
Accidental Deletion of Records
In my experience, the accidental deletion of records is one of the primary ways that data is lost. Now that we can easily replace FileMaker's standard menus with our own custom menu sets (and replace standard functions with scripts), it is very easy to enforce business rules with regards to what can be deleted, when it can be deleted, and by whom. And one of the things that I like doing is not really deleting records immediately. Instead, flag records as being "inactive." That way, if a user does accidentally mark a record as inactive, you can "undelete it."
Don't Store Databases on Laptops
One of the findings by the IT Policy Compliance Group that was a real eye opener to me was how frequently and prevalent the theft and loss of laptop computers really is. More than 1 in 10 data loss event is caused by the loss or theft of a company laptop. So we need to be extra careful with regards to what we store on our laptops. Whenever possible, serve up your databases using a server -- don't run around with your senstistive data in your laptops. And if you need to run your databases directly on your laptop, consider using encryption and password protection. That way, if your laptop is lost or stolen, it will be more difficult for the "finder" (or thief, as the case may very well be) to gain access to your data.
Backup Your Data
Do we really need to discuss why you should be doing this? I sure hope not. There's really no excuse -- especially if your databases are hosted using FileMaker Server. Take the time to setup a comprehensive, automated backup plan. Be sure that your data is being backed up not just on the server itself, but also to an external drive or to tape. And please try to keep some of those backups in an alternate location.
Password Protect Your Databases
Require that your users use passwords that are hard to guess -- and make them change their passwords frequently. Disable the Guest account in your databases. Take advantage of all that FileMaker's priviledge sets have to offer in terms of what your users can see and do.
There's a lot more that you and your users should be doing to protect your FileMaker databases -- everything from using SSL certificates for Web-enabled databases to protecting the networks that your databases are on and so on. Perhaps the best advice I can give is this: Take the time to regularly review your security at all levels, as well as your backup strategy. When it comes to the security of your databases, it is always better to be safe than sorry.
Subscribe to:
Posts (Atom)
