Multiple user login system design issues

[Olby]

Hi Guys,

I've been designing an application that supports multiple users at the same time (local network based) and I would appreciate your input on the greatest challenge am facing. My system is based on SQLite database file that holds user data (Name, Password, State, ..).

Procedure:
1. User inputs name and password.
2. If name exists and password is correct application proceeds to check state of the user.
3. If state=1 then someone has already logged in using this name (end).
4. If state=0 then application grants access and sets state=1 (proceed).

Now the problem is when the application crashes (database related error, power surge, termination, etc.) user state is left unchanged. And next time someone tries to log in using the same name application denies the access (since state=1). I have created a .bat file that executes my app with certain command line parameters and resets user state but this is rather ugly and extremely unsafe workaround. I was looking for something more advanced like database lock that automatically resets back to 0 when application unexpectedly terminates or some temp file (WinAPI) that tracks user status.

Can anyone comment on this issue because I cant really think of anything else. Any help is much appreciated.

Thank you!

A.

[Trond]

Why do you even have this state? Can't you simply allow multiple logins?

And why do you have this state in the database? It just doesn't make sense to me.

[Olby]

Because each user has its own stats (timing, keystrokes, errors, files finished, ..) and we just cant allow everyone to access and update this highly personal information due to the fact that salary is based on user's achievements. That's why I'm looking into this because we have never designed a multi-user software before and I need some advice.

[TomS]

So you don't have one account per user?
If you do, how could someone log in at another station with the name of another user?
And how would it be a problem if somebody logs in at another station. The user should automatically be logged out at the machine he was at.
If you don't, just do^^
You're sureley not limited in terms of a maximum amount of user accounts since you're writing the software yourself.

[Olby]

Since I design this software for a small company located in a different country I don't have hands on access to their work stations. There is another problem of supervisors using very basic (identical) passwords for different users and there have been situations where one user knows/uses other users name and password which in turn means discrepancies in our statistics. I would like to prevent this from happening on a software level rather than somehow restricting our people.

[TomS]

You don't need to have hands on access.
Just tell them to install your software.
Since you programmed the client, you can tell the client to shut the user out by sending a specific command from the other client to the server and from there to all the other clients.

I hate it when people say "Make me a secure system. But I still want to be able to use "1234" as my password".
That's just not possible.
You put security measurements in your system all you want.
If the user is too dumb to select a proper password, it's all for nothing.

If you want to stay on that path, use "keep alives".

Instead of saving the log-on state (1/0), save the current timestamp (timestamp.i = Date()) on log-on.
Then overwrite that timestamp with the current value, every 5 minutes or so (AddWindowTimer()).
Now when someone tries to logon at another station, you check if the current timestamp is greater than the timestamp in the database + 300 (300 seconds = 5 minutes).

If that's not the case the other machine either crashed or the user logged of, in which case you write "0" into the database.
Otherwise let the user log in.


Downside: If the program/machine should crash, your legitimate user has to wait up to 5 minutes before he can login again.
But you can also use 1 minute instead.


You may have to write your own timestamp routine. I don't know if the timestamp from Date() is timezone dependent. That would be a problem if you have clients in different timezones.

EDIT: This doesn't prevent anybody from using another user's account when they're not at work for some reason, of just not logged in (coffee break)

[Olby]

It's not essentially a security issue because all employees are aware of who is doing what, it's just sometimes they tend to create a mess with job stats. It would have been a real problem if we did not have any other way to track stats but we do. On the other hand I fully agree with what you've said and I share your thoughts. Small budget and under-qualified employees make my job a real night mare because each advanced feat you implement will present a real challenge for them to get used to.

Thank you for the time stamp tip! I use similar functionality for other parts of my software but I never tried using it with login system. I'll look into this.

Thanks!

[TomS]

Given the "security" at your clients company, I hope what you meant by "I use similar functionality for other parts of my software" was:
Users get kicked out of the software after a certain amount of time without input activity

Keep Alives are a good idea for any kind of server-client application.
Usually at a higher frequency than 5 minutes, though.

[JustinJack]

hmm... I have an idea. If you're using a database and not a client/server app, you could add another column to your log-in-state table called ClientAck of type "DateTime". Then have a timer on one of your windows or another thread update that table (from the client) every X minutes to tell the Database it's active. Then, on log-in, if the client is still logged in, but the last time stamp is outside the limit of acknowledgement, you can reset the state and allow access.

To protect the integrity of everything, you might want to have the client self log-off after a period of inactivity.

[TomS]

Thank you for repeating my posts in your own words.
This would be a good exercise for en english test at school

[Olby]

Given the "security" at your clients company, I hope what you meant by "I use similar functionality for other parts of my software" was:
Users get kicked out of the software after a certain amount of time without input activity

Keep Alives are a good idea for any kind of server-client application.
Usually at a higher frequency than 5 minutes, though.

I have automatic-pause which halts the client application and requires a password to continue if last input from user was >10 mins ago.

[Foz]

We have a similar problem at work where it's possible for multiple people to edit the same record, and in some cases it's a one way edit (i.e. picking stock).
In this case we applied a locking system in the same way you are talking about, and yes we get locks. And to eliminate cockups, we have a program that lists the lock, lock type and user. And to manage them... well that is what IT is for.

Realistically, we very rarely have to clear locks and when we do, we have to roll back certain bits of data, or they have to redo the work, dependant on the work done.

Even so, I prefer a personal hand in these because it has flagged up odd bugs (some times as simple a a timeout which then becomes an sql optimisation lesson) which we then fix. And a bug free program is always a good thing!

[JustinJack]

No prob TomS..no charge this time, I usually have a nominal fee for interpretation services.

Ha! I just read your post. Good thinking, how'd you know what I was going to post before I responded? You Germans are tricky like that!

I know..I know..and us Texans are crass...

[TomS]

...multiple people to edit the same record...

There's a new solution for that kind of problem. I think it's awesome, but right now it's only available as an AJAX / Java Servlet solution.
A PB Client/Server application would be really great.

Check out: http://piratepad.net (don't be scared away by the name, there are other projects based on the same code: http://etherpad.com )

The idea behind this project is that every single keystroke is
1. saved in a changelog
2. submitted to all other users that are currently logged in, in realtime

It's fun to play around with, and I already used it on several occasions (discussing a topic via ICQ and writing the results to the pad instead on a post-it note. That way everybody has an up2date version)

[RichAlgeni]

Are you using a web front end?

With a browser you could send a cookie to their workstation, and it will be sent back to you each time. I have a process that associates a user login, IP address, date and time with a cookie. You can do this even if it's not a browser, send a file to the client in the background, the client could send it back to you each time. If this file or cookie expired (time limit you set), they have to log back in.

Browsers are 'stateless' connections, that means they connect, send and receive, and disconnect each time.