Removing Switchboard Options with Access Security 2

[TomHW]

I have created a database using Access Security. I would like to know what would be the best way to disable options on the main menu form, ie. the Switchboard, that a user does not have access to as opposed to program using a popup to inform the user that they do not have permission to view a form after clicking on the button for the form.

I have considered using DAO or ADOX to determine the current user's groups and graying out the option accordingly, but this seems to defeat some of the purpose of the built-in security being that I will have to somewhat manage security on my own.

Any help would be greatly appreciated,

Tom

 

[dRahme]

Hello, this might help. I personally do not use Access Security but this is how we basically handle what you describe:

Add a column to the switchboard table that is used for identifying security levels for each form.

Add a user's table that identifies their security level.

Add a bit of code to the switchboard the renders switchboard buttons invisible if the form security level is higher than the user's security level.

So, a programmer might have a level of 7,meaning that he can see anything.

An administrator who has a level of 6 can see everything 6 and below and so on.

Here is a snippet that illustrates the use:

mUserDept = Me.txtDeptID
mSecurity = Me.txtSecurity


If (rs.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else

Select Case mUserDept

Case 6 ' Administrater
While (Not (rs.EOF))
Select Case Nz(rs![Security])
Case Is <= mSecurity
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
End Select
rs.MoveNext
Wend
Case 7 ' Programer
While (Not (rs.EOF))
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend


Case Else
While (Not (rs.EOF))
Select Case Nz(rs![DeptID])
Case mUserDept, 15
Select Case Nz(rs![Security])
Case Is <= mSecurity
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]


End Select
End Select
rs.MoveNext
Wend
End Select
End If


This eliminates the need for popup's, etc. The user can only use what he can see.

The reason for this approach is because of the nature of the business I am in where it is not easy to set up maintain traditional Access Security. This allows a 'broad stroke' application, which may or may not be suitable for your needs.

Hope that helps.

dRahme

 

[easyit]

Hi,

For this to work you need to find out the usergroup they are in: With ADO you can use:

Public Function InGroup(strGroupName As String) As Boolean
' This function will return TRUE IF the current user is a member of a security Usergroup with a name matching the string provided in the argument.

Dim conn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim i, k As Integer

On Error GoTo SubError1
InGroup = False
MdwLocation = "Jet OLEDB:System database=" & SysCmd(acSysCmdGetWorkgroupFile)
ConnStr = "data source=" & CurrentDb.name & ";" & MdwLocation & ";user id=db_admin;Password='MyAdminPWD'"
With conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = ConnStr
    .Open
End With
On Error GoTo SubError
Set cat.ActiveConnection = conn
With cat
For i = 0 To (.Users(CurrentUser()).Groups.Count - 1)
'Debug.Print "User: " & CurrentUser() & " belongs to group: " & .Users(CurrentUser()).Groups(i)
    If .Users(CurrentUser()).Groups(i) = strGroupName Then
'        Debug.Print "Username: " & CurrentUser() & " belongs to group: " & .Users(CurrentUser()).Groups(i)
        InGroup = True
    End If
Next i
End With

Exit Function

SubExit:
conn.Close
Set conn = Nothing
Exit Function

SubError1:  ' This exit is used when the connection cannot be openend. Otherwise you'll have a double error

MsgBox Err.Number & " " & Err.Description
Exit Function

SubError:
MsgBox Err.Description & " " & Err.Number
GoTo SubExit

End Function

Ofcourse you need the proper references, and in my situation a user can be member of only one group.


easyit

 

[TomHW]

dRahme

Thanks for the help. I use something very similar for all of my other projects, it is just that I am required to use the built-in Access security for this one and was trying to figure out why I should have to write my own routines to do this kind of thing when I am already using security. I just don't like that it is enforced in such a poor way interface-wise.

easyit

Thanks also, that is more along the lines of what I was looking for. I guess I'll just have to determine what groups they belong to the hard way and disable options accordingly.



My solution involves a general mix of the two solutions above: determine if the user is part of a group with sufficient rights to the form, if not, disable the option.

Tom