Using Form As User/Password Validation? 37

[Spyder757]

Basically I’d like to have a table named “Users”. This table would contain two columns. USER ID and PASSWORD.

A form with two unbound columns would be used to by a user to enter their user ID and password would be validated against the USERS table after a command button was pressed. If they matched they would be allowed into the next form in the database, if not an error message would be displayed and they would still be stuck on the main forum.

Is this possible?

I seem to recall a similar thread some months ago but a lengthy search didn’t turn up anything.

If anyone has a functional example of this please let me know.

Thank you.

Spyder757

 

[Terpsfan]

Ok, what I do is create a table on the backend for usernames, passwords, and security levels. Then I use a local table to copy the user's login info for later use... I use a DCount function to look up the username and password in the backend, if it matches they go to the next form...I also put in an ability for the user to change their password. If you want to see that one, I can provide.

Private Sub cmdAuthenticate_Click()
If DLookup("[Password]", "N_tblPassword", _
"[USERID] = [Forms]![N_frmLogon]![txtUSERID]&quot = [Forms]![N_frmLogon]![txtPassword] Then

MsgBox "Your password has been authenticated!"
DoCmd.OpenForm "frmStartup"
DoCmd.OpenQuery "N_qappLocalUser"
DoCmd.Close acForm, "N_frmLogon"


ElseIf IsNull(txtPassword) Then
MsgBox "The password field cannot be blank. Please type your password."
DoCmd.GoToControl "txtPassword"
Else
MsgBox "Your password is wrong"
DoCmd.GoToControl "txtPassword"
txtPassword.Text = ""
End If
End Sub
Private Sub cmdNewPassword_Click()
On Error GoTo Err_cmdNewPassword_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "N_frmNewPassword"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_cmdNewPassword_Click:
Exit Sub

Err_cmdNewPassword_Click:
MsgBox Err.Description
Resume Exit_cmdNewPassword_Click

End Sub

Private Sub Form_Open(Cancel As Integer)



'Deletes the user in tblLocalUser in order to start afresh
DoCmd.OpenQuery "N_qdelLocalUser"
txtPassword.Enabled = False

End Sub

Private Sub txtUSERID_BeforeUpdate(Cancel As Integer)
If DLookup("[USERID]", "N_tblPassword", _
"[USERID] = [Forms]![N_frmLogon]![txtUSERID]&quot = [Forms]![N_frmLogon]![txtUSERID] Then
txtPassword.Enabled = True

ElseIf IsNull(txtUSERID) Then
MsgBox "The USERID field cannot be blank. Please type a valid USERID."
txtPassword.Enabled = False
Else
MsgBox "You entered an invalid USERID. Please type a valid USERID."
txtPassword.Enabled = False

End If
End Sub

Private Sub txtUSERID_LostFocus()

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

You can also grab a download here at:

http://www23.brinkster.com/vbprogrammer38/SmartSolutions.htm

 

[JeremyNYC]

I very strongly recommend that none of you use the solution recommended here. Access security is breakable, but it is better than anything you can write in access. Writing your own security means rewriting functionality that already exists in Access and throwing away the collective knowledge of the Access community regarding Access user level security.

Check out the Access security faq either at the MS site or on my web site.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[JTregear]

One alternative not mentioned in the discussion so far, is for the Access Application to look up the authenticated user ID that the user has already logged on as.

This avoids the need for having a second level of authentication, and means that all the issues about setting password policy, forcing users to change their password, etc, can be handled by the system.

For an example of the sort of thing I mean, please see:

http://www.gj.thorpe.btinternet.co.uk/username.html

Regards

JTregear

 

[GessTechie]

I asked this question a few days ago....

Here is how I solved it...

Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

Dim stDocName As String
Dim stLinkCriteria As String
Static UserId
Dim Text11, Text13, Text19


If IsNull(Text11 = DLookup("[Text11]", "User table", "[User ID] = Text11&quot) Then
MsgBox "You MUST select a valid User ID. Please try again!"
Exit Sub
End If

If IsNull(Text13 = DLookup("[Text13]", "User table", "[Password] = Text13&quot) Then
MsgBox "You have entered an invalid password. Please try again!"
Exit Sub
End If

stDocName = "Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub

 

[JeremyNYC]

H Jessen,

Security is a difficult thing to get going. But don't be run off just because it doesn't work the first couple of times you try to set it up. If you are trying to become proficient in using access databases it does not make sense to shy away from such an important feature. It _is_ difficult. But programming is not always easy. Stick with the process of learning security and you will get it. And if you do so with the built-in security of Access you will then have the benefit of working with a system used by hundreds of thousands of other people, instead of a community made up of only the people who happen to use exactly the same model you get from one thread in a support forum.

Omega's method may very well be great. I assume it is, judging by the experience he seems to have. But it is still one person's method, and if you need help with it, there will be far fewer people to help you out.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[BillPower]

Hi Omega36 ,

Just a note to say that your solution for Spyder757 is 100% the right one. I think Spyder757 might have been referring to a thread I was involved in, back in November last year, thread181-417795 with Rmcta.

I had exactly the same problem as you, suggesting a different approach to Microsoft’s security sparked off voices of dissent from FancyPrairie, JeremyNYC and Dilettante. None of them actually gave a worthwhile reason as to why my solution wasn’t as good or if not indeed better than Microsoft’s other than to say why re-invent the wheel.

In my opinion Access’s in-built security is a joke and if we didn’t strive to re-invent the wheel we’d still be back in the dark ages, keep advocating your security system, you’v got my backing all the way.

Bill

 

[ClydeData]

for some data base I do the table other users have suggested

But on others I make the confidential bit visable via a simple ubbound text box

If Me![StaffPW] = "yourPW" Then
Me![EmployeesList].Visible = True
Me![Employees].Visible = True
Else
MsgBox " Wrong Password", 64, "Staff Edit Screen"
End If
End Sub

 

[drmansoor]

Bill
Hi BillPower and Omega36,
Yes you are absolutely right, everybody has the right to modify any existing thing, but one MUST have the reasons for this, also he must be able to offer at least few new features in his new system which are not available in the existing system, and also it is not very good to call one system as a “JOKE” without assigning any reasons or examples of any holes or flaws in it. Advocating your views without any evidences and proofs is not sufficient, as merely keep saying things again and again is not advocating.
Bill I am a fan of yours and admire greatly your expertise and Omegas expertise is also very nice indeed, but please for the sake of science kindly could you both try to provide a list of comparison of these both security models with all the PLUS an MINUS points, it will be a good service to the forum too.

Best regards

Mansoor

 

[Terpsfan]

Umbro,
I'd suggest two books for you. I'd start out first with Access 2000 VBA by WROX Press - ISBN 1-861001-76-2. I would put this book at the Intermediate level. After that I'd check out the Access 2000 Developer's Handbook Volume 1 by Sybex - ISBN 0-7821-2370-8. I would consider this an advanced book.

With those two books you'll be well on your way to becoming very adept at Access/VBA.

If you just want to know VBA, more for the office suite including excel, etc, check out VBA Handbook by Sybex.

 

[fneily]

First Omega36 and GessTechie codes are basically the same - Omega36 extends it for new/changing passwords.

For novices: After you check both UserID and Password, if either is wrong, setfocus back to the control to make it "user friendly". Also for the password text box, place an input mask of "Password". This will make asterisks appear instead of the actual password.

As for security issues. Omega36's code does not allow for object level security, eg. some users are allowed to edit some tables/forms, some can't. You also cannot assign multiple users to the same permissions. With her code, when someone gets in, they can do anything they want. No differentiality between users.
If this is all one wants, then find.

JeremyNYC is correct in that Microsoft's Access security routines are more robust. They allow for different permissions on different objects and allow to build "Groups".

May I suggest to anyone who thinks Microsoft's security is "too hard" to read only four pages from Course ILT Access 2000 Advanced cousre book. It's actually quite simple compared to mainframe security. One should not be "lazy" in learning Access.

Encryption is hard. Ask anyone from the NSA.

Neil

 

[fneily]

I forgot to mention another possible helpful tip. To "hide" your table with the user ids and pasword, prefix it with Usys. eg. Usysusertable. This will automatically hide this table or any others, for that matter prefixed with Usys. To see it, click Tools - Options and place a check next to System Objects.

Neil

 

[Terpsfan]

Fneily,
My intention when I first responded to Spyder's post was to show how to create a friendly password interface to the database.
My intention was never to "replace" the functionality of Access security, but to provide simple security measures for small databases in which full Access security isn't needed.
The methods I use aren't for everyone or every application. But I think the methods I use accomplish enough security for most Access applications.

1- create an mde file.
2- encrypt the database
3- hide the database window
4- turn off shortcut keys at startup
5- create a password form at logon to prevent unauthorized access
6- controlling user access to tables via menus and forms

Access, unlike SQL Server, etc, does not have object-level permissions. Permissions are granted on a group level.
Because the database window is hidden, users can't view database objects, nor should they.

 

[markphsd]

Hey, I agree with all the comments about how much more robust Access security is, how much more control you have over the whole database and yadaydayda....

However, access security does seem to slow down the mdb somewhat. Are there any sites, or suggestions one of you Access Security advocates might have or know about on how to speed it back up? Mark P.

I write code in cuneiform, what about you?

 

[KornGeek]

billpower,
I would be interested to hear why you feel Access security is a joke. When properly implemented, it is very secure (not 100%, but nothing is). I'm not clear why you seem to have such strong feelings against it. It's true that it is a little confusing, but I feel that it works rather well.

I skimmed through the thread you mentioned, and it appears that your system relies on a table within the database. The problem with this technique is that somebody could simply modify the table through another database and edit their permissions. You may have found a way around this. I'll admit that I didn't read the thread carefully.

I think the main issue is how secure do you need the database. If you want to provide some security for a database used internally in your company, and you don't want to deal with the complexities of Access security, then your method is probably better. However, if your security needs are tighter because you have sensitive data and/or the database will be released outside the company, then it is probably worth the extra effort to implement Access security. Then again, I've seen several posts that say if security is truly a concern, Access may not be the best product.

In short, both types of systems have their merits, and I don't feel that either method should be put down. That's just my 2 cents. Take it or leave it.

 

[JTregear]

This message is for BLBurden

If you have set up the database as suggested in my earlier post so that it can look up the user's validated account name, it is now longer necessary to store the password in your database at all because they have been validated by the network log-on.

Your Access database should still have a table of users, which contains a field which the users account names. As part of the intialising your opening screen should check to see if the account name appears in the list of approved names. If it does display the name with whatever greating you would like. If the account name does not appear in the list, display a message saying that the user is not registered to use this application before quiting the application.

One suggestion for those people, such as Omega, who have been such good contributors to this thread, can you either set up a separate thread or give some hints on how to set up an Access Control mechanism within the application. i.e. once you have authenticated the user, how you go about restricting the forms and reports that particular users can open.

 

[KornGeek]

JTregear,
If you are using your own table to store valid user accounts, simply add permission option fields (data type Yes/No) that determine what permissions are set for each user. When each form or report is opened, verify that the current user has the appropriate permissions set, and if not, exit immediately.

A similar system can be used with access security. Instead of checking the table for permissions, you check to see if they are a member of a selected group.

Good Luck.

 

[krymat]

I have created a low level security object(forms,reports,functions) control switchboard(access 2000)that may work for some of you. If you would like a copy let me know, I will send it to all that request it by Monday morning. I would appreciate your feedback.

(Note: This not a replacment for a properly secured database, only a solution for those who don't need real security (you know who you are). I recommend learning how to properly secure your access database before deciding to use this method)

 

[RobPotts]

Hi,

I would also like to add my thought on Access security. I agree with those that say try learning to use it. The help files are fairly good then the FAQ on the microsoft site are a great help. Its actually not that hard to do and if you persiver you'll get it cracked in no time.

When it is properly implemented its as secure as I think most people need. I to would like to see Billpowers comments on why the in built security is a joke, I have had no security issues with it and its really not that hard to learn.

people feel free to use this code, but give Access security a go.
Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[Terpsfan]

I am going to post this code on a web site probably tomorrow. You can download it from there. I'll post the link. I'm not down on Access security, I just prefer not to use it. If I am going to create a database application with a big security model, I'd just assume use SQL Server or MSDE.