Data Separation – Managing User Accounts

Having split my solution into “Data” and “Interface”, I had to give thought to regulating access to each file, and maintaining user accounts.  Here’s what I’ve ended up with for now – it’s roughly the method explained a while ago by Matt Petrowsky on his magazine site , which I heartily recommend.  He did a whole series of articles about data separation which I found invaluable.

  • Central to the whole user access thing is the User Preferences table (UPF) in the Data file, which has a record for each user, including details of his her account name, privilege set, as well as personal details (email, photo, etc.), various indicators to show his/her access to parts of the system, etc.
  • The Interface file is wide open.  In File Options, I’ve set “Log in using…” with a shared username and password, i.e. anybody with access to the server on which the file is hosted can open it.  (There is, of course, one other account, i.e. the admin account, which can be used via shift/option+click.)
  • When the Interface file is opened, the Data file is also opened, as it’s listed as an external data source.  Needless to say, this doesn’t have a generic user account, so at this point the user is prompted for a username and password.
  • When the user provides valid credentials and the file is opened, a calc field is set in the UPF table, i.e. UPF_c_Account, defined as simple “Get ( AccountName )”.  (There’s another calc field, UPF_c_PrivSet, which holds the user’s Privilege Set – more about that later.)   Nothing else happens in Data for now – we don’t open a window, so the Startup script doesn’t run yet.
  • Next, back in the Interface, we go to a “startup” layout, which necessitates opening a window, which in turn kicks off the “startup” script, as defined in File Options.  This is what the script does:
  1. If  this is a Full Access user (i.e. the Interface file has been opened with the Admin user account), install appropriate menu set, show toolbars, etc.  Otherwise (i.e. it’s been “auto-opened” with the generic account), install minimal menus, hide toolbars, etc.
  2. Perform the “Verify user details” script, which does this:
  3. Take UPF_c_Account (which contains the current user’s account), and do a find in the “User Preferences” table – i.e. set UPF_AccountName to the value held in UPF_c_Account, the calc field set when the file opens.  If there’s no match (unlikely, given that they must have entered a valid username and password to get this far), display a “No Access” message and close (see screenshot below).  If there IS a match, then set all the required globals, variables, etc., display the welcome screen, then the main menu.

It all works pretty well, although, as ever, I look forward to being told there’s a better way to skin the cat!


4 Comments on “Data Separation – Managing User Accounts”

  1. fmpsoft says:

    Dave I really like your blog.

    I’m wondering how would you manage user accounts in a solution with more than one data file?
    Obviously, replicating accounts in a multiple files would be tedious job.
    What would be your suggestion regarding managing user accounts in a multi data file solution.



    • Dave says:

      Hello Slobodan,

      Thanks for the feedback. I’m afraid I have no suggestions for security in multiple data files. I’ve never had to deal with the issue, and can’t think of an easy way around it. Anyone else?


  2. lozanobosch says:

    Hi Dave!
    If calculate field can’t be global, this solution could fail if several users enter to the interface file at the same time. I’ve implemented it just open the password protected data file first, saving accountName and privileges in a global field and then open interface file with a generic username and password. I have a preference table with layouts, levels, etc. for each user in the database and retrieve it via SQL with a first open window script.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s