Securing split db with multiple front-ends… (2000 SR-3)

Topic

New Reply

Hi Everyone…

I’m trying to help a colleague complete an Access db application but it’s kinda like the blind leading the blind at the moment… so I’m here for help!

Here’s what we want to do… Secure the database … creating 3 different groups of users… My friend doesn’t like the fact that users with lesser access get bombarded with Access’ messages when they click on things in the interface that they are not allowed to do…. My thought was to secure the db, split it, and then create three different front-ends… all linked to a central back-end db…. Each Front-end can be customized to show only the controls, forms, etc that the current user is allowed to see… I think this will work well… and if workgroup file is attached then if the user happens to find and try to use the other Front-ends on the network (with more access… such as the Developer group front end – full control of everything) they will still only be able to access what their group allows… They’ll get lots of messages when they try to get where they shouldn’t, but it’s still secured…

I hope that makes sense…

My question is that I need to know what the best order to complete this is…. Should we complete the security of the database and then create and join the new workgoup file to the full database BEFORE we try and split it? … or … well… how does it work for split db’s?… Can’t say that I’ve had to do this before… Does he have to secure each front-end and the back-end separately?

Help!

Reply | Quote

Replies

(Edited by HansV to make URL clickable, and to preserve indentation – see Help 19)

I suggest that you split the database immediately and work with the correct workgroup file and groups from the very beginning. Also, you might consider modifying the current frontend to have it change itself dynamically depending on the permissions granted to the current logged-in user by controlly the Visible and Enabled properties of the various command buttons. You will need a function like IsGroupMember() and some IF/Then or Case statements to control the user interface. The trade-off is having just a single frontend to maintain versus more complex programming. You will have to decide for yourself, based on your level of expertise.

Here is the function:

Public Function IsGroupMember(psGroupname As String, Optional psUserName As String) As Boolean

Dim wk As DAO.Workspace
Dim group As DAO.group
Dim user As DAO.user

‘ check for group membership by seeing if an error is raised
‘ default value is FALSE

IsGroupMember = False

If IsMissing(psUserName) Or Len(psUserName) = 0 Then
psUserName = CurrentUser
End If

Set wk = DBEngine.Workspaces(0)

On Error Resume Next
Set user = wk.Users(psUserName)
Select Case Err.Number
Case 0 ‘ nothing – user is defined in this workgroup
Case Else
‘ user is not defined in the workgroup, so cannot be member of any group
GoTo endfunction
End Select

Set group = user.Groups(psGroupname)
Select Case Err.Number
Case 0 ‘is member of group
IsGroupMember = True
Case 3265 ‘ nothing — not a member of group, so allow the false value to stand
Case Else
MsgBox “Error in group membership routine ” & Err.Number
End Select

endfunction:
Set group = Nothing
Set user = Nothing
Set wk = Nothing
On Error GoTo 0
End Function

And it is used like this:

Public Sub test()
If IsGroupMember(“admanadmins”) Then
MsgBox “yes”
Else
MsgBox “no”
End If
End Sub

I have written a paper on Access Security that you might find useful. http://www.geocities.com/jacksonmacd%5B/url%5D

Reply | Quote

Thanks!

That actually was my first thought… However I spent a ton of time on the dynamic updates to the front-end before I realized that I can’t change the start-up options …disable menus and right click and all that (for two of the three groups only) … as the database opens… It won’t work until the NEXT time the database is opened… which is a waste of time in this situation… I tried all kinds of ways around it but no go… Plus my friend has images and labels with hyperlink addresses all over the place so it wasn’t as simple as making things invisible or disabled… We want them to still show (so that there aren’t a lot of blank areas on his main forms) … and since there’s no enabled property for images or labels… that wouldn’t work…

The programming is not an issue… I believe I’m quite competent in that department… I’m trying to make it easier for my friend to maintain and more esthetically pleasing for his users…

Anyway… I guess it doesn’t really matter what order we do this in… since we can use the same workgroup file with all of the separate split parts… I just thought I’d ask in case I was missing something… Thanks again!

BTW… I have read your paper on Security in the past… I’ll look through it again… It was a great help the first time I secured a db… Terrific work Jack!

Reply | Quote

Hi Trudi,
If you want to use a common front-end (deployed to each workstation – recommended), what you typically want to do is disable things to the point where they need to be for the most restricted user, and then based on the user group, turn on the appropriate features. That does mean of course that the start-up has to be set for the most restricted user, but the rest of the changes should be fine, though they do need to be implemented as forms are opened, which complicates the code behind the forms considerably. We usually attack this a bit differently, as we segment forms so that users can only see certain forms, and then we modify which forms are opened by the menu items, and we modify the menus using the security group info. Let us know if you want more details.

Reply | Quote

Hi Trudi,
If you want to use a common front-end (deployed to each workstation – recommended), what you typically want to do is disable things to the point where they need to be for the most restricted user, and then based on the user group, turn on the appropriate features. That does mean of course that the start-up has to be set for the most restricted user, but the rest of the changes should be fine, though they do need to be implemented as forms are opened, which complicates the code behind the forms considerably. We usually attack this a bit differently, as we segment forms so that users can only see certain forms, and then we modify which forms are opened by the menu items, and we modify the menus using the security group info. Let us know if you want more details.

Reply | Quote

Thanks!

That actually was my first thought… However I spent a ton of time on the dynamic updates to the front-end before I realized that I can’t change the start-up options …disable menus and right click and all that (for two of the three groups only) … as the database opens… It won’t work until the NEXT time the database is opened… which is a waste of time in this situation… I tried all kinds of ways around it but no go… Plus my friend has images and labels with hyperlink addresses all over the place so it wasn’t as simple as making things invisible or disabled… We want them to still show (so that there aren’t a lot of blank areas on his main forms) … and since there’s no enabled property for images or labels… that wouldn’t work…

The programming is not an issue… I believe I’m quite competent in that department… I’m trying to make it easier for my friend to maintain and more esthetically pleasing for his users…

Anyway… I guess it doesn’t really matter what order we do this in… since we can use the same workgroup file with all of the separate split parts… I just thought I’d ask in case I was missing something… Thanks again!

BTW… I have read your paper on Security in the past… I’ll look through it again… It was a great help the first time I secured a db… Terrific work Jack!

Reply | Quote

(Edited by HansV to make URL clickable, and to preserve indentation – see Help 19)

I suggest that you split the database immediately and work with the correct workgroup file and groups from the very beginning. Also, you might consider modifying the current frontend to have it change itself dynamically depending on the permissions granted to the current logged-in user by controlly the Visible and Enabled properties of the various command buttons. You will need a function like IsGroupMember() and some IF/Then or Case statements to control the user interface. The trade-off is having just a single frontend to maintain versus more complex programming. You will have to decide for yourself, based on your level of expertise.

Here is the function:

Public Function IsGroupMember(psGroupname As String, Optional psUserName As String) As Boolean

Dim wk As DAO.Workspace
Dim group As DAO.group
Dim user As DAO.user

‘ check for group membership by seeing if an error is raised
‘ default value is FALSE

IsGroupMember = False

If IsMissing(psUserName) Or Len(psUserName) = 0 Then
psUserName = CurrentUser
End If

Set wk = DBEngine.Workspaces(0)

On Error Resume Next
Set user = wk.Users(psUserName)
Select Case Err.Number
Case 0 ‘ nothing – user is defined in this workgroup
Case Else
‘ user is not defined in the workgroup, so cannot be member of any group
GoTo endfunction
End Select

Set group = user.Groups(psGroupname)
Select Case Err.Number
Case 0 ‘is member of group
IsGroupMember = True
Case 3265 ‘ nothing — not a member of group, so allow the false value to stand
Case Else
MsgBox “Error in group membership routine ” & Err.Number
End Select

endfunction:
Set group = Nothing
Set user = Nothing
Set wk = Nothing
On Error GoTo 0
End Function

And it is used like this:

Public Sub test()
If IsGroupMember(“admanadmins”) Then
MsgBox “yes”
Else
MsgBox “no”
End If
End Sub

I have written a paper on Access Security that you might find useful. http://www.geocities.com/jacksonmacd%5B/url%5D

Reply | Quote