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

 

[GDGarth]

Not to get in the middle of a big debate, but I'd have to say I agree with those who advise you to use Access security. In my opinion, it's both easier and more secure than anything you're likely to come up with on your own.

I've had people tell me they can crack Access security easily, then have to admit to me they can't. I know a clever hacker can probably get through - but what can't they get into?

If you don't implement security, and protect it with VBA written in your application, I think I could crack that in a matter of minutes.

 

[Brightly]

I just wanted to say thanks to Omega36, I downloaded a copy of that example file on the web page referrenced, it was a great help.

Thanks

 

[Terpsfan]

Jdgeorge, I'm going to put an Access 97 version on my web site. Look for it in the next 2 days.

 

[jdegeorge]

Omega36...

Thanks very much!

Jim "Get it right the first time, that's the main thing..."

 

[sadalaw]

JeremyNYC can you send a direct link to Microsoft that provides answers on how to use password protection of forms?

 

[judgehopkins]

http://support.microsoft.com/default.aspx?scid=kb;en-us;179371

HOWTO: ACC: Create a Password Protected Form or Report

Try this!

 

[KornGeek]

sadalaw,
To my knowledge, Access does not support password protection of individual forms. Using Access security (which is what JeremyNYC recommends above) you can have the user login to Access using their password, and then limit the availability of forms to users.

To find answers on how to implement Access security, just do a search in the Knowledge Base of the Microsoft website, or check out JeremyNYC's website. I believe Jeremy's web address has changed from what is posted above. A brief search of Tek-Tips should turn up the new address.

 

[judgehopkins]

Password for opening a form
thread702-281300

You might also want to read this thread!

 

[BakeMan]

Just to add my 2 cents on the whole Access Security debate...

I don't believe that any of the examples listed above are sufficiently secure on their own. That's why I suggest using a hybrid of the systems to secure your databases. I know this maybe overkill and it would be easier to use something like SQL server, but for those of us that do not have this option, this will help keep you data secure. Unfortunately I do not have a copy of this that I can forward to anyone but I can try to explain the concept.

I first created a MDE file that has no security placed on it (we will call this the LoginDB). The LoginDB is simply a program and form to allow access to the MainDB (the reason I don't have a copy of this to send out is that I since converted the LoginDB into a VB app to speed up the login process, but this can all be done from Access DB's). Security to the LoginDB file is handled by only installing it on the PC's of the user's who require access to the database. The LoginDB opens with a login form requesting the user's User Name and Password (I actually pull the user's User Name from their login to our Novel system as an added level of security. You must be logged into the network as a valid user prior to accessing the database). I then have the program wright a temporary text file to the users PC containing some type of changing code (I'll explain this shortly). I use a combination of the user's User Name and the date from the PC for the code. Now set up a second secure database (we will call this the PasswordDB) that contains a log of all users, their current passwords, the date the last time they changed their password, a list of the passwords used in the last six months, and weather or not this user has been locked out (i.e. due to exceeding the maximum number of failed logins). Then have the LoginDB use the PasswordDB to log any user who exceeds the maximum number of failed logins and check if the user is locked out. The LoginDB should then use the user's User Name and password to open the MainDB through Access's built in security. I then have the start-up of the MainDB open a hidden form (I have the PasswordDB do this process as well). This form should first check for the presents of the file the LoginDB wrote to your user's PC. If the file is not present or does not contain the properly encoded information have the code exit Access. This will keep anyone from entering your DBs from anything other than the LoginDB. If the MainDB (or PasswordDB) find the proper file, delete it, close the LoginDB and continue to the main menu. Of course you must have the MainDB and the PasswordDB encrypted, have the shortcut keys turned off, have the database windows hidden, use custom menu's to keep the user from the built in Access tools that will help them gain access to the code, and have the Access security properly set up with groups that have varying access levels. Then create a form from within the MainDB that allows an administrator to add new users, clear passwords, add or remove groups from users, and lock out or restore users rights. All of these functions log the information into the PasswordDB to properly track user writes that the built in security cannot (i.e. password aging, locking out of users after failed login attempts, and keeping the user from repeating passwords). I would also suggest making the MainDB a combination MDB and MDE. This will help speed up the application and help protect your code.

I have founds that this combination of a separate login MDE (or VB app) with Access's built in security gives me all of the values of each approach listed above with few of their draw backs.

I hope this at least gives some of you some ideas about other ways of approaching Access security.

BAKEMAN

 

[ApplePirate]

Omega36...

Please dont take this as hassle...
but have you managed to sort out the Access97 version of the security db yet Never ever, bloody anything, ever

 

[JeremyNYC]

Wow. I can't believe how big this thread has gotten. I've made absolutley no effort to read it all, and I doubt very much that I will. I did notice that a few people had referenced my site. My URL did, unfortunately, change, but everything can be found at the address below.

Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jmeadows7]

Omega,

Your solution is probably the more robust one. It's the one I typically use. I'm sure you've expanded it beyond the discussion here. Suggestions for other people in this thread utilizing the method are:

Count the # of times the incorrect password is typed and lock the account after X number of tries (recorded on the user record)

Set a password expiry date on the user record to force changing of passwords

Record log in / log out times for audit purposes

See who's logged in from any station

Set up several tables that relate users to groups and windows to groups and objects to windows. Then set up a table to store enabled/visible etc. against the group/window/object. The program can read in the user security and when a window is opened - loop through the controls on the window - turning security on/off based upon the user's group.

You can also write a simple algorithm to encrypt/decrypt a user's password to pass the audit muster.

Code for Checking Security on Each Window Open:

Sub Set_Form_Security(frm As Form)
'***************************************************************
'* Name: Set Form Security
'* Author: JMM
'* Date: 11/7/01
'* Description:
'* This subroutine sets the security for any calling form by
'* looping through all of the controls on a form and determining
'* if security has been set for this control for this user by
'* looping through the list box on the main form. The main form
'* user security list box has the control_id and enabled/visible
'* flags for each control If the control is found in the list,
'* set the two properties for the control.
'* The columns in the security list box are as follows:
'* window_id
'* object_id
'* enabled_fl
'* visible_fl
'*
'* Revision History:
'*
'***************************************************************

Dim ctl As Control
Dim i As Integer

' Enumerate Controls collection.
For Each ctl In frm.Controls
'Check to see if item in user security
For i = 0 To Forms![frm000_Main]![lstw000_Security].ListCount - 1
If ctl.Name = Forms![frm000_Main]![lstw000_Security].Column(1, i) Then
ctl.Enabled = Forms![frm000_Main]![lstw000_Security].Column(2, i)
ctl.Visible = Forms![frm000_Main]![lstw000_Security].Column(3, i)
End If
Next i
Next ctl
End Sub

 

[idd]

As we are on the topic,

I would like to know , is there a way of stopping someone linking to a database secured using the methods mentioned by Omega36, BillPower and others.

I want to secure my database and have tried many times to use the Microsoft technique but find all sorts of difficulties.

I have tried the method Billpower and others have mentioned.

Bill
I worked through the whole of you response to RMCTA's problem on security. I even downloaded the file.

Omega I worked through you r solution too. and downloaded the file.

But How do I stop someone linking to my tables and getting to the information.

I think your ideas are intuitive, and a good workaround for the tough Access solution, which definately is not easy.

Idd

 

[jebry]

Hi Idd!

If you make the tables hidden (right click-properties) then the user will not be able to see them when trying to link but they will still be usable to the database. Assuming that you have a FE/BE set up then you can secure the back end by calling the following function from the autoexec macro:

Public Function CloseAccess()

If CurrentUser <> &quot;YourLANName&quot; Then
Application.Quit
End If

End Function

If CurrentUser is not available in you set up then you can substitute the following API call and function:

Private Declare Function apiGetUserName Lib &quot;advapi32.dll&quot; Alias _
&quot;GetUserNameA&quot; (ByVal lpBuffer As String, nSize As Long) As Long

Public Function fOSUserName() As String
' Returns the network login name

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = &quot;&quot;
End If

End Function

Can't remember where I got this function or I would credit it. If anyone recognizes it please chime in.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[idd]

Jeff,

I was using that code earlier on to today to create my login form. I got the code from a site run by a tek-tips member, he too has posted on this thread. The web address is

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

his name is graham thorpe, but He wrote the following on the web page with the code

' Created by: Unknown (Found on Dev Ashish web site

http://home.att.net/~dashish/api)

' This code has not been altered in anyway.
' Added to database: 27 dec 1999
' Added by: Richard Rensel

I tried getting to the dev ashish web site but it wasn't there.

Anyway

I have read your response, thanks for giving your time and thought to the question, however, I still think that a user who wants to be able to get to the database tables can still get to them. I dont think hiding them does much as they are still accessible.

Any other solutions ? I dont think I really require my present systems to be that secure but, Its just a thought about security which sort of is worrying.

Idd

 

[jebry]

Hi Idd!

You asked for a way to keep people from linking to your tables. If you mark them as hidden then the link and import wizards will not display them and will not link to them or import them. Yes, someone could unhide them, but then they would have direct access to the tables anyway and would not need to link or import. That was the idea behind the public function in the BE database. Calling the function CloseAccess from the autoexec macro will make the BE unopenable by anyone but yourself and that would make them unaccessable by any direct method. Thanks for the info on the function, I probably got it from Dev Ashish as well since I have visited the site many times. I hate posting code I didn't write without giving proper credit.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[GingerR]

Idd: use Access security. users can link, but they can't open them.

 

[RickSpr]

Jebry:
My experience is that if you go into Tools|Options and click the Hidden Objects check box, the Link and Import wizards will show the hidden tables.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein