how to host remotely, script locally, and preserve account information

[jjjjp]

I want to do something very basic, namely to host a database remotely while being able to safely change the UI on a local version, which I would "upload" periodically (i.e., when I am relatively sure there are no bugs) to the host.

I have experimented with the separate-data model but am concerned that it is too complicated to deal with the calculations, which I'd have to include in both UI and data file. So I'm considering moving back to one integrated file and scripting imports for each of my tables. These scripts would delete all records in the local table before importing the host data (i.e., local data would be entirely replaced by host data). I would then replace the host database with the local one, which would now have fully current data.

(1) Does returning to the single-file model make sense given my basic aims?

(2) How do I deal with changes to user accounts (in particular changes to passwords, which the user may have made while I was working away locally)? There's no way of importing account information back into the local file before farming it back to the host, is there? So what do I do?

I haven't been able to find answers to these questions on the Forum. Thanks in advance for any guidance.

Edited July 10, 2010 by Guest

[jamesducker]

An age-old problem.

We've done a lot of work on this one and developed a solution which we call "rollover".

Basically:

1) put a copy of the rollover code in to the live database

2) take a copy of the live database

3) host that copy on your development server

4) do your development work

5) test, test, test again... get the client to connect and test

6) when you are happy with it, run rollover as follows:

a) take the development system offline

: take the live system offline

c) put them both on the same machine

d) rollover extracts all the data from the "live" system...

e) ...deletes all the old / test data from the "development" system

f) ...puts the live data in to the development system

g) ...carefully compares the live and development system to check that the values are not the same

You don't need to define scripts, custom import orders etc etc

Rollover copes with new/renamed/deleted fields, new/renamed/deleted tables etc etc

It took us months to develop but it works. We just use it internally, it is very much a technique not a product but works on pretty much any file and it saves us HOURS of time.

Get in touch if you'd like to use it.

James

[jjjjp]

Thanks very much for that reply. I've been working away at this since my post, and I think I have arrived at something very close to what you're describing, so your response gives me a lot more confidence in what I'm doing. I'm creating an import script for each of my tables (delete all records then add new records) and will add to them as I create new tables. I'm assuming (hoping) the right thing to do is to say yes to "Perform auto-enter options while importing."

The really tricky thing was the the user accounts, privileges, and passwords. I decided the only way to really tackle this problem was to take over from Filemaker the job of managing and storing passwords. I still set up accounts in Filemaker, but all users get an empty password on the "system" and a password that I store in a table. The system always logs users in as Admin automatically, and then they pass through a scripted interface, which re-logs them in after authenticating them, so that they can still be controlled by the system-defined privileges that they are assigned.

This system is admittedly less secure (e.g., the administrator has full access to passwords), but I see no way of relying on the system-supplied system of authentication while still being able to "rollover" development changes without also losing account information.

[Kevin Smith]

Hello James

At times it feels a bit like a religious divide between the "data-separationists" and the "single-filers" :-). I'm a single-filer.

My upgrade script works OK, but requires updating every time I do a new version with extra tables. In addition, there is often the need to run one-off scripts to massage the data stored in a particular field in order to fit adjustments made to the system. As a result upgrades are rather a pain.

I've chosen to stick with the built in password system. This means that the passwords the users have stored for themselves will be lost when the new system is put online. To overcome this they are given an interim password which they need to change after the first login to the upgraded system. I've taken this route in preference to storing user's passwords in a table within the database. The problems with that approach are:

a) As a developer I have access to confidential information that I don't want to know. The passwords are stored in plain text.

: There are many ways for a determined user to access passwords stored in this way. I may not have though of all of them when I develop my system.

c) At some point I may have a client who is especially fussy about security and this setup won't do for them. I'll then need to develop, test and debug a different access rights system for them.

d) The same password stored in the FileMaker system may also be employed by a user on many other systems she needs access to, in both her personal and work life. I would not like to have the responsibility for securing that info.

Regards Kevin Smith

[jjjjp]

Thanks, Kevin, for your response. You raise some important security concerns.

I assume, then, that unless one makes very few changes to user accounts and is willing to replicate those changes manually on both ends, one would still want to keep track of accounts (that is, account names and privileges, but of course not passwords) in a table on the active database and then use that table to replicate accounts on the developer's side when one is ready to import the data into the developer's database and roll out a new version.

[Vaughan]

one would still want to keep track of accounts (that is, account names and privileges, but of course not passwords) in a table on the active database and then use that table to replicate accounts on the developer's side when one is ready to import the data into the developer's database and roll out a new version

Yes.

The trick is to script the account management processes and store the username and privilege set information in a table. Scripts are used to add and delete accounts and the account table records are created and deleted by the scripts as necessary. (Remember that these scripts need to fun with full access privileges.)

When updating the solution, the import script deletes all the existing accounts (except full access) imports the accounts records then uses this data to re-create the real internal accounts in the database. As Kevin said, these will need to have a "temporary" password set because the system does not know what the previous password was, and there is no way to determine it.

Of course, all of this is unnecessary if external authentication is used. I am a huge fan of EA and have implemented for both small and large organisations.

[jjjjp]

I know little about external authentication systems, though clearly they would offer the best of both worlds: security and control. Are there low cost (even free) solutions for more modest projects?

Thanks.

[Kevin Smith]

Hi,

External Authentication exploits the features of your fileserver. Windows server will have "Active Directory" available and on the Mac you'll have "Open Directory". So, if you're in an environment with a fully configured server, you're able to use these features at no extra cost.

regards Kevin

[jjjjp]

Thanks for that, Kevin. I don't have access to a fully configured server now but might at some point in the not-too-distant future. If you yourself have such access (like me, you may not), why would you choose not to go the way of EA, rather than asking users to change passwords each time there's an upgrade. I.e., is there some problem with authentication on a server that's worth knowing about?

[Kevin Smith]

Hi (it would be nice to know your name!)

I guess I've steered away from it as I did not want to be reliant on both the IT Dept. and another computer system for the external authentication to work.

I must also admit to having a mental block, I've been too stuck in following the usual FileMaker login method to think of the advantages of External Authentication; until now!

Regards Kevin

[jjjjp]

Thanks, again, Kevin. Name's Jerry.

As with you, there would be IT-related politics involved in my using my unit's server that would complicate any decision. But good to know that your helping me may indirectly have ended up helping you.

[Vaughan]

External Authentication does NOT require AD or OD. It can work equally well from local users and groups set up on the server machine.

I have set up a couple of clients with EA like this. It's quick and easy and is very cheap for small organisations that don't have IT staff.

[jjjjp]

OK. Good to know that.

-Jerry

[Kevin Smith]

And thanks for putting me straight. I'm learning a lot too.

Regards Kevin