Thoughts/Advice on Creating a Login Database

[RWX]

The database system I'm currently designing (it's in the very early stages) will essentially handle my client's day-to-day business, from tracking customer information to tracking payments to scheduling appointments with customers, etc. The reason I'm posting is that I'd like to get some opinions on how to track user activity and set permissions. I'm considering implementing a very simple login/tracking system that employs a Users file. This file would be opened first and the user would have to "log on" to the system. Once logged in, a global (or set of globals) would contain the current user ID and any other information required. That way, whenever a new record is created in any of the databases, I can relate it back to the user so that management can see who did what and when. I can log activity as well, and set permissions. I plan to disable all menu commands and make the entire system either button driven or use MenuControl to create menus.

The alternative, of course, to this approach is to simply let FileMaker automatically enter the current user name into a field when a record is created, etc. My main concern with this approach is that the data doesn't actually relate back to anything, thus making it more difficult to generate a comprehensive user activity report, for example. Also, if the user name is changed for whatever reason (perhaps someone gets married), every database will need to be updated with the changed information or the newly name changed user will appear as a brand new user in the system.

What are your thoughts on this issue? Security isn't of the utmost concern. Although there will be two distinct groups, Users and Management, there are only a few databases that Users shouldn't see. More than anything, I want to prevent Users from running certain reports. I also want to track their activity and log it. Is a simple login solution a good idea for tracking activity? Has anyone implemented such a solution and, if so, can you offer any suggestions or advice? When implementing such a system, does one use the built-in FileMaker password functionality as well, or would this completely replace it?

Thanks!

-Rob

[John Caballero]

Hi Rob,

You'll probably want a combination of a User db and FM's password/group scheme. It won't do any good to disallow deletion, for example, based on a user ID if the User can still access the menu items.

One important thing you'll need to consider is navigation from file to file. In my system, whenever a user goes from one table to another, I clear the global in the departed from file as the user goes to the next file. This way if they ever get back to a file via some other method than my navigation, they are forced to log back in because every script checks to see if there is a value in the global User ID field in that file before anything else can happen.

It sounds like you'll be needing an activity log db as well. You can capture all sorts of information based on the Status functions that can sometimes be very useful for debugging (current found count, button pushed, etc) in addition to tracking who did what. Every script that you call will call up a "record activity" script that grabs all the info you want and writes that info to a new record in your activity file.

Good luck.

[RWX]

Thanks John!

It sounds like when your users login, you enter their user ID and whatever else into globals in the current file, correct? What about having some sort of master relationship that relates every file back to the Users (ie: Login) database. That way one wouldn't need to copy/insert the logged in user information into each file, but could simple check the Users file via a relationship. It seems like such an approach would also eliminate the need to clear the local globals and re-login when jumping around the system. Any thoughts? Clearly you didn't do it this way. Why? Is it a bad idea? -)

Another approach I thought of was to use Waves In Motion's Variables plugin. I'm considering the plugin for a number of things and figured it could work for storing current login information. Any thoughts on that?

Thanks again for your help and advice!

-Rob

[John Caballero]

The reason I don't have a relationship back my User file in any of the main files is for security reasons. My Login db has a relationship back to the User file that is based on a concantenation of their user ID and password. A successful log in sets their user ID and Name into a couple of globals in the Login record and then passes control (and the globals) over to a Main menu file. Once I've grabbed my user ID and Name from the User file during the login process, I shut down the User and Login files.

And even if you did have a relationship back to the User file, you'd still be setting a global with their user ID once you navigated to the next file, otherwise the relationship would be useless when the user arrived at the next file.

As for any thoughs on the plug-in...I've never used it. Sorry.

[RWX]

So, once the user has logged in and the Users and Login databases are shut down, what happens when the user moves to another database? Do they login again? Or is their information somehow passed? Are your users jumping around between a lot of databases or are they primarily working in one or two?

[John Caballero]

The information is passed from one file to the next via a constant-to-global relationship. FM will whine when you create this relationship, but it will still let you do it, and since you're passing data via globals, the relationship can be invalid by FM's standards. This is faster than a constant-to-constant relationship because FM doesn't have to evaluate all the records in the related file (special thanks to former workmate Mariano Peterson for that little gem).

OK, so a user logs in successfully. Their User ID and Name is passed to the Main menu (Main.fp5) via a constant-to-global relationship where there are a couple of global fields just waiting to receive this data and Main.fp5 is brought to the front. Users.fp5 and Login.fp5 both shut down - I'm done with them for this session. On the Main menu I have buttons that take the user to the various files that they'll be interacting with. If they click on a button, their User ID and Name data is passed onto the global User ID and Name fields in the target file via a constant-to-global relationship (see a pattern yet?). The global fields in Main.fp5 are then cleared out and the target file is then brought to the front. To get back to Main, the same process happens in reverse (I set the globals in Main then clear the globals in the current file and then bring Main to the front). If the user winds up in the Main file without having navigated there and then tries to execute a script, their global fields will be empty (because they were cleared out when he left) and they will be prompted to log in again because all my scripts first call a script that checks to see if there is data in the required globals before they execute. If the user doesn't elect to log in (the choices are log in and quit) then all the files close down.

[Leb i Sol]

Hi John!

I, too, have been following your line of thought and attempted to recreate simillar system.....but didn't get far

Please, Could you take a look at the files to see if I am even on the right track?

Thank you very much for the comments and advise!

login.zip

[Lee Smith]

Hi Leb i Sol,

I modified your files a little. I think this is what you had in mind.

HTH

Lee

1085082205-For_Leb_i_Sol_r.zip

[Leb i Sol]

Hi Lee!

Thank you very much for helping out and taking your time!

I have learned a few more tricks ...next step it passing the global (session variables) througout the files!

Many thanx!