How to create a scalable, object-level permission scheme in your Microsoft Access application
Hello again. I have had several people on Experts Exchange asking about object
level permissions in access. The built in access user management is pretty worthless if you ask me, so I am going to
share the solution I use to manage user privleges in the app I am currently developing.
For any of this to make sense, you will need to download The Working Example
First, lets start out with the table structure. There are three tables: tbl_Users, tbl_UserPermissions and tbl_Permissions.
tbl_Users needs no explanation so I will move on to tbl_Permissions. You will notice four columns here. PID (Unique ID),
Indx (we'll get to that in a second), BinVal (Binary Weight) and PermissionName. BinVal is the binary weight of the
permission and is calculated by (2^Bit) / 2 (I divide by two because we start out with bit 1 instead of 0). If you don't
know what I mean by this, I suggest that you look for a good binary tutorial online, I am assuming most of you will already
have at least a conceptual understanding of binary. This leads into the reason for the Indx field. If you know VB, you will
know that the largest integer variable is the Long type which is a 32 bit signed integer. Since the sign bit takes up one,
we are going to use 30 bits out of each Long variable we use for permission flags (we could use 31, but 30 is simpler). If
you have less than 30 permissions to assign, you don't need the Indx field and your life is way easier. My method is far
more complicated, but very scalable. The index field in tbl_Permissions corresponds to which permission set each permission
is stored in. When you look in tbl_UserPermissions you will notice that each user has three permissions records: one for
each different value for Indx in tbl_Permissions. Our overall goal is to be able to write a function which will ask
"Does user have PermissionX?" The way we do this is: - Get the index and weight of PermissionX from
tbl_Permissions
- Get the permissions record for the current user and specified index (from permission record) from tbl_UserPermissions
- Then if Weight (binary AND) Permissions = Weight, the user has the privlege
Simple enough? Good. Lets move on to the functions.
There are three functions in mod_Functions that are used to check permissions. These are:
- CheckPermByNum(PermissionID As Integer) As Boolean
- CheckPermByName(PermissionName As String)
- Digit_Weight(ByVal Digit As Integer) As Long
CheckPermByNum is the main permission checking function. Lets go through it. First we check to
make sure the Permission ID is under 30 (if it is over 30 we need a higher index).
Now, we pull the user's record from tbl_UserPermissions for the calculated index. After that we
simply logically AND the user's permissions from the record we grabbed with the permission we are checking.
If the result of this equals the permission we are checking, the user has the permission.
This is a perfect example of how properly structured tables can make coding very simple.
CheckPermByName is a convienent function for checking a permission by name instead of its ID.
The main reason this would be used is for form or report level permissions. This type of permission
is demonstrated in Form3. The form or report level permission line of code obviously requires the name of
the permission to be the same as the name of the form (if you want to use the caption, you would use
me.caption instead of me.name). The advantage of this approach is that you can copy and paste the same
couple lines of code into each form or report (although you will still have to manually add the permissions
to the permissions table).
The last function is very simple. It calculates the binary weight of the specified digit beginning with
one instead of zero. This is used to convert the permission id into its binary counterpart.
I have included two other functions which convert a string of binary 1's and 0's to a long integer and vice versa.
They are not used in this example, but they are helpful for this sort of thing so I left them in.
There is another function in mod_Functions for setup. I am not going to go into detail as this is just an example
and you should populate your tables according to your application's needs. It can be executed by the Reset Database macro.
Now on to the sample implementation. While we are still on the topic of mod_Functions I will explain the last two
elements of it. These are the intCurUser global variable and the GetCurUser function. These both relate to the login form.
It is important to note that these are for example purposes only, this is a super flimsy login scheme. The login form gets
the UserID based on the username and password and stores it in the global variable intCurUser. This variable is used in the
CheckPermByNum function (through GetCurUser) to identify the user to pull permissions for (the currently logged in user).
The GetCurUser function is used in place of intCurUser so that in case intCurUser becomes zero (which it does, stupid M$)
it will open the login form instead of freaking out. In the application I actually use this permission scheme in I have the
equivalent of intCurUser stored in a field on a hidden form as well as in a global variable for this exact reason. I did not
include the hidden form because it is beyond the scope of the article.
Finally, there is the most complicated part of the system. This is frm_Permissions. This form is used to select the permissions that a user has or has not. In the case that there are many permissions, it is prudent to give each type of user base permissions when the user is created. This is because the list boxes in the permission form do not save the position when a permission is added or removed. I will not go into any more depth about the innerworkings of this form since it is again only here for demonstration. The code is pretty self explanatory.
There is one last comment I must get in before I finally wrap this up. It is crucial to the proper function of the system that there is an entry in tbl_UserPermissions for each user and each possible index. The way to do this is to have a sub which checks tbl_UserPermissions and is run every time a new user or new permission is added. Make sure you take care of this before using it in your application.
I hope you have enjoyed this article. Just remember that it is meant only to demonstrate a principle, not to just cut paste and go in your application. You should be able to make it fit pretty simply if you are familiar with access and vba.
Until Next Time,
-Joe Korzeniewski
|
