the Manx Man's MS Access Pages
to subscribe - click here

Using Groups in Access Security (035)

NOTE:This article has been converted to our new web site. See it here.

This article assumes that you are familiar with setting up Access security. My favourite tip in this regard is - ALWAYS CREATE A CUSTOM SECURITY FILE (or MDW). If you have not recently read a good article on Access security, here is one. I recommend it. Read it NOW.
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

One of the tips in that article is, never use a user's username to determine some security related permission. Always use groups, as they are easier to maintain. Then assign your user to the appropriate group(s). The user will have the highest level of security available from all and any groups that they belong to.

Let's look at a simple example.  (all my examples assume that you have DAO set as the default data manipulation tool.  If you are not sure what this means, and you are using Access 2000 or higher, click here. )

Let's say that you want all your manager level people to see an additional command button on a form. Or, perhaps the opposite is true. You do not want them to see it!!!

  • Create a group called managers
  • Add that group to all your manager level users
  • Utilize code similar to the following

Determining What Groups a User is in

Public Function GroupScore() As Long

'--- this function assigns a score to a user based on which groups they are in
'
'           assigned group scores are:
'                 DataEntry = 2
'                 Supervisor = 4
'                 Manager = 8
'                 ?? = 16
'                 ?? = 32
'                 ?? = 64
'                 ?? = 128
'                 Admins = 256

Dim wrk As DAO.Workspace
Dim usr As DAO.User
Dim grp As DAO.Group
Dim tmpScore As Long

Set wrk = DBEngine.Workspaces(0)
Set usr = wrk.Users(CurrentUser())
tmpScore = 0

'--- loop through all the groups that the user belongs to
For Each grp In usr.Groups
     Select Case grp.Name
          Case "DataEntry"
               tmpScore = tmpScore + 2
          Case "Supervisor"
               tmpScore = tmpScore + 4
          Case "Manager"
               tmpScore = tmpScore + 8
          Case "Admins"
               tmpScore = tmpScore + 256
     End Select
Next grp

GroupScore = tmpScore

End Function

In Form's Open Event

'--- payroll button only available to managers
if (groupScore() and 8) = 8 then
     cmdPayroll.Visible = True
else
     cmdPayroll.Visible = False
End If

How Does it Work?

The GroupScore function sets up a reference to the current user's user properties and loops through all the groups that that user belongs to. By using powers of 2 to assign a "score" we can come up with a number that allows us to determine at any time whether they belong to a specific group.

This works because of the binary pattern of powers of 2 and the functioning of the "and" operator. For example, if the user belongs to both the Supervisor group and the Manager group, their group score would be 12. Next, we compare their group score of 12 to a Manager's pure group score of 8, (12 and 8). If the result of that "and" operation equals 8 (which it does) then we know they are a manager. Alternatively, (12 and 4) equals 4 so they are also a supervisor.

Further Suggestions

Assign the GroupScore to a global variable so the computer wastes less time each time it needs to check a user's groups within the same session.

Next Issue

What's next? Several more articles about creating a table completely from VBA code, and using an Excel file to define the fields in the table.  See the first 2 parts of that series at tip021 and tip022.

index of tips click here for a
clean laugh		 profit from the web

tip # 035  ||  previous tip  ||  next tip