Custom User Permissions: Part 1

When developing a full scale database application for Microsoft Access, one of the hurdles you're bound to run into sooner or later is the need for user permissions.  Managing who can see which forms, edit which records, run which reports, etc can seem like a daunting task for developers.  This series is focused on provided the knowledge and resources necessary in preparation for developing a user permissions management solution in your own database applications.

Introduction

The "Creating Login Security using Access VBA" article in the UtterAccess Wiki serves as a good introduction to custom user permissions and for this series.

The UA wiki article covers table design and hard code methods of managing form access, record add/edit/delete permissions, and record filtering in a single-to-many/hierarchical permissions system.  I recommend giving it a look as it lays foundations for a few of the methods discussed in this series.

Part 1

In the first part of our series on custom user permissions, we will begin by designing the user and permissions tables and relationships.  The example in this series will be designed to allow users to be members of multiple permissions groups giving administrators increased permissions management flexibility.

First, create tblUsers as shown below.

Next, create tblPermissions.

Now, create tblUserPermissions to serve as a junction table between tblUsers and tblPermissions.  This will allow us to establish a many-to-many relationship.

Notice that both the UserID and PermissionID are set as the primary key for the junction table.

Lastly, establish the relationships between the tables.

Part 1 Summary

In this post we created tables for users, permissions groups, and a junction table to allow each user to relate to multiple permissions groups and each permissions group to relate to multiple users.

Posted in: Access Tables