General discussion

Locked

Securing a database

By Mackem ·
Not sure if I am in the right forum for this question but here goes.
I have a database built with Access 97.
It has ran for three years without any problems, but now we need to be able to validate some of the data which we input.
We run NT 4.0 sp6a servers with nt4.0 and 9x workstations.
What we need to do is validate when someone puts his/her name to a field, that they are authorised to do so. Their name must be validated with a password, to ensure they are who they say they are.
I don't want to use Access security as this dosen't go far enough.
NT security would not go far enough as either.
I also have vb6, but I have not done too much with this lately.
Any help or information as to where I might find the help would be greastly appreciated.
Thanks in advance for your help
JV

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Securing a database

by Bob Sellman In reply to Securing a database

Here's a solution I've used to provide some access control without using the Access security, which I personally don't like.

The basic concept I follow is that you make the user log in when the database is opened. You save the user's ID and any place necessary you can check the ID and modify properties of specific controls as necessary (visible, allow edit, etc.). You can use the group ID to avoid coding for each person (and to handle changing users).

1. In a module, set the public string variables strUserID and strGroupID.

2. Set up a (hidden) Groups table with GroupID, group names, etc.

3. Set up a (hidden) Users table with necessary user information including the userID, password, and groupID. (You should also set up aform to administer this and the group data.)

4. Set up a form which is always called when the database is opened that asks for the userID and password. If the password fails after x tries, close the database. If login is OK, set strUserID = UserID and strGroupID = GroupID (obtained from the Users table). [Note I've set them to strings, you might use integers.)

Within your database you do the necessary coding where you want to control access to certain data.

I realize that the coding to control the fields can be involved, but this method gives you a lot of flexibility and avoids requiring users to use the right workgroup file. I've used this method with a lot of success for a few different Access 97 applications, although not as extensively within each application as I suspect you need to do.

Collapse -

Securing a database

by Bob Sellman In reply to Securing a database

This will give you the login and access to the UserID and UserGroup throughout your application:

Make a new module that contains this code:
Public strUserID As String
Public strUserGroup As String

Create table UserList (see below for columns)

Make a login form with control for the UserID and another for the Password. On AfterUpdate property of password:
Dim intX As Integer ' for counting
Dim strPassword As String
Dim dbsCurrent As DATABASE
Dim rst As Recordset
Dim Response As Integer
Dim strSQL As String
Set dbsCurrent = CurrentDb
strUserID = Me.txtUserID
strPassword = Me.txtPassword
Set rst = dbsCurrent.OpenRecordset("SELECT UserId, UserPassword,IsActive,UserGroup FROM UserList", dbOpenSnapshot)
rst.MoveLast
rst.MoveFirst
intUserCount = rst.RecordCount
rst.MoveFirst
With rst
'testing to match userid entered
For intX = 1 To intUserCount
If !UserID = strUserID Then
If !IsActive = False Then
MsgBox "You cannot log in. Inactive account.", vbExclamation
DoCmd.Quit
Else
If !UserPassword = strPassword Then
If IsNull(!UserGroup) Then
strUserGroup = ""
Else
strUserGroup = !UserGroup
End If
Response = MsgBox("Do you want to change your password?", 260, "Change password option") '6=yes, 7=no, 260 sets default to no
If Response = 6 Then
Me.txtNewPassword.Visible = True
Me.txtRetypePassword.Visible = True
lngTries = 0
Me.txtNewPassword.SetFocus
Exit Sub
End If
strSQL = "UPDATE [UserList] SET [LastLogin] = #" & Date & "#, [CurrentlyLoggedIn]=True WHERE UserID = '" & strUserID & "';"
DoCmd.RunSQL strSQL
DoCmd.Close
DoCmd.OpenForm "fmnuMainMenu"
Exit Sub
End If
End If
End If
rst.MoveNext
Next intX
End With

Collapse -

Securing a database

by Bob Sellman In reply to Securing a database

Here's some more code I use at the end of the login code I already posted:

' if password or User ID not correct, give user a total of 4 tries to get it right, then quit application
lngTries = lngTries + 1
If lngTries = 4 Then DoCmd.Quit
Else
Me.txtPassword = ""
Me.txtUserID = ""
MsgBox "Invalid log in. Try again"
Me.txtUserID.SetFocus
Exit Sub
End If


Make a macro called Autoexec that calls the login form to always start the login.

On your main menu form open property, add this code:

if strUserID < "0" then
msgbox "You did't log in!"
docmd.Quit
end if

In the same place on any forms as appropriate you can, for example, only display buttons to certain group(s). For example, a button cmdAdmin on the main menu is only for members of the admin group. Put in this code as part of OnOpen form property:

if strUserGroup = "Admin" then
me.cmdAdmin.visible = True
else
me.cmdAdmin.visible = False
end if

You should set the visible property of those types of controls on forms, etc., to false when you design the form, so it normally is not visible.

Once you've got the login working well, doing the rest really isn't very complicated.Just keep the number of groups down to a small number. You could even include in the table listing the groups some columns that indicate various areas of access that you could just look up for a form, etc., and act appropriately. Using that concept you could have, for example, 10 different areas of concern and the mix of rights could be different but overlapping for the different groups. If you need that complexity, that would be the simplest solution to allow yourself real control while limiting the code necessary in the forms.

Good luck.

Collapse -

Securing a database

by Mackem In reply to Securing a database

Thanks for your help. I am not sure if this is going to work, but as you are the only one to answer and you have gone to so much trouble, you get the points.
PS. Sorry it has taken so long to post this but TR does not let you know when someone addsto their answer.
Thanks again
JV

Collapse -

Securing a database

by Mackem In reply to Securing a database

Answer one.
You are on the right lines, unfortunately my coding is a bit rusty, since it is three years since I built this Db. What I am realy looking for is a link to some sample code. Thanks for your help.
ps I haven't rejected your answer as you may still help and I don't want to dicount the points from you.
cheers
JV

Collapse -

Securing a database

by Mackem In reply to Securing a database

This question was closed by the author

Back to Networks Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums