Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cmspot
Starting Member
44 Posts |
 Posted - 2007-10-14 : 06:08:39
|
| Hello everybody , I am going to need your help with this problem (Sorry for my english if you find something wrong!)Well i 've made a Complicated (My first) Windows Application Using VB and Sql Server 2005.My problem is that I want to Control Licences for my application.What Do I mean? I want to put a max users limit f.e max 10 users can be together on line and also i want to restrict that only one user with the same user name can be logged in.What i have done.......I ve created a table with two fields (username varchar (20) primary key, online bit default 0)After the login options If my application finds that online field for the given user is 1 throws a login error message.It is also counts the number of fields which are 1 and if it finds that they are equal to maxusers throws a login error message.If all go right my application my application updates the online field of my table for the current user to 1 and moves to the main menu of my application.It is important to inform you that in my application closed event i update the online field to zero.Problems.....What happens if electricity goes down?what happens if my application throws me out?what happens if my pc stucks and i have to make a reset?1) is my way correct for someone who wants to do this?2) Do you have anything else to suggest?AS:I am new in programming so your detailed answer would be really appreciated!Thank You very very much for your valueable timeAlexander GreeceIT & Finance Consultant |
|
|
cmspot
Starting Member
44 Posts |
Posted - 2007-10-14 : 06:27:44
|
| NOTE:The security checks is controlled by Sql serversAll users are opened as logins in sql serverthe check happens like thisBy pressing ok buttondim sconnection as new sqlconnectionsqlconnection.connectionstring = ".................user id = me.usernametextbox.text....password = me.passrdtextbox.text"try sqlconnection.open sqlconnection.close()Catch ex as exception messagebox.show (Wrong.............) End subend try..............................I sell my mother in law.Is anybody interested? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 06:36:18
|
I would separate the UserName and Online data into two separate tables.[User] table stores information about the user - UserID, Name, Address, Password, Telephone, ...Then I would have an [Session] table with:UserID, DateTimeLoggedIn, LastAccessWhen user wants to log in I would do:"OnLine table housekeeping" [see below]Check the COUNT(*) for [Session] table, and if >= 10 then deny the loginotherwiseInsert UserID/Time of user log in into the [Session] tablePeriodically I would UPDATE the LastAccess for that UserID in the [Session] tableIf the UserID cannot be found in the table at that time I would disconnect that user (so application has to switch to Login screen, or something like that)OnLine Table HouskeepingBefore trying to log a user in I would delete all entries where LastAccess earlier than, say, one hour ago. This would get rid of people who had crashed, left their machine logged in over the weekend, etc.You may not actually delete such users, you may just flag them as Offline instead (so you have a bit of a history) and delete if LastAccess more than, say, 24 hours ago - that might help a bit with debugging as you will be able to see Inactive users for 23 hours after they become inactive, before that row is actually deleted.Another way to tackle this is to allow administrators to delete users from the [Session] table. However, the user will still be "logged in" as far as the application is concerned, so you also need a way for the application to discover that their connection is still valid.One way is to include the [Session] table in all your queries:SELECT Col1, Col2, Col3FROM MyTableWHERE Col1 = 'ABC123' AND EXISTS (SELECT * FROM [Session] WHERE UserID = @MyUserID) Kristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-10-14 : 06:44:31
|
| I basically don't know anything about windowsapplications but is it possible for you to grab either the machine name or the ip-adress of the user logging in? If you log this in your login-table then you can throw an error if a user is online and someone from another machine/ip-adress tries to log on. You probably also need to handle login timeouts...what happens to the online-status once a user has been idle for say 30 minutes? In one of my old webapps I actually updated the online-status for every page hit to be able to tell exactly how long a user has been idle. If it exceeded 30 minutes he was forced to log in again. If there is a license limit you also need to have a reliable count of online users...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
cmspot
Starting Member
44 Posts |
Posted - 2007-10-14 : 06:57:37
|
| My friend thank you for your time but using the ip adress or the Pc name i think is not a good idea.The reason is that if i use this, a lot of users can login with the same user name from different pcs.I want to provide unique login for each user name in my application.Do you agree?I sell my mother in law.Is anybody interested? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-10-14 : 07:22:14
|
| You can't force a user to use the same machine/location every time he/she logs on (well...it's possible of course but not very user friendly) but you can make sure that the same username isn't used more than once at the same time from different locations, which is an important difference. Logging the machine name or ip-adress could make this possible (be aware that ip-adresses are often shared inside an office).Also, updating a timestamp in the user table for every hit to the database is pretty resource intensive so you should consider this carefully and see if there are other ways that serve your needs better.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
cmspot
Starting Member
44 Posts |
Posted - 2007-10-14 : 09:50:15
|
| Thank you Kristen , I think your idea is great!Can i ask you something more?What do you think if after the login i keep a connection opened until the application closing ,So i can control users from sql server and master.dbo.processes table?What is the cost of an opened connection?is it correct to keep an opened connection to sql server as long as you are connected to the applicationI think Power builder works like this.It uses a connection string from an INI file and leaves it opened until the application termination.(Maybe I am Wromg.Forgive me if I am!)Your opinion is valueable thank you!Thank you my friend Lumbago too but it sounds a little difficult to use the IP adress for this job.Maybe it is difficult for my experience.I appreciate your time.I sell my mother in law.Is anybody interested? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 10:53:03
|
| I doubt there will be a problem keeping the connection open, provided that you don't have lots and LOTS of users. However, I don;t think t he database will be able to detect that the connection has closed, so you you will only be able to detect an EXplicit Logout, or a Timeout. You will not be able to detect a connection closing from, say, application crash or user left PC running and went home!You can still provide Admin Utility to "force logoff" of course.Kristen |
|
|
|
|
|
|
|
|
Security Issue