Using Groups in Access Security

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

Using Groups in Access Security

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

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

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.

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.

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.