Record Level Security for Access Database

Row-Level Security without Work Group Administrator

Record Level Security Alternative: Row level data security and tracking can be a daunting task in a Microsoft Access databaseunless you know our simple tricks to simplify row-level security. Many of us have been asked to implement a database where a normal user can only see records which they have entered. At the same time the requirement may also state that an administrator must be able to see all records.Some of you may have gone so far as to create different tables to store different person’s data records.  Perhaps you have created specific data entry/review forms for different person’s records.

Our simple approach, often used by larger DBMS systems, is to create a view of the data (a query) that dynamically filters records based on which username is logged into the database.

The simple example has the following database objects:

The basic database requirements are as follows:

1) Store to do list records for users containing a description, date, status, and other data fields

2) Provide a login function and segregate records into two levels: Staff and Admin

3) When a staff level person is logged in only show To Do entries that have been either entered or assigned to them.

4) An administrator level username may see all To Dos and may assign/reassign any record to any person.

Here’s the contents of the data table.  There are no tricks to this simple table (note in the image Access Level User is equivalent to Staff Level in the discussion):

The other simple table hold the list of To Dos for all users:

Next is the data entry/review form.  Note that for this example we have placed all the required fields onto one form.  Normally you might have the login function on a different form.  The storage fields for the user_id and access_level fields needs to be on a form that is always opened.  You could also use global variables to hold these values but global variables can be lost if you encounter an unhandled error in the database.

The main form of the example in design view follows:

Note that the user_id and user_level holding fields would normally be invisible when the form is running.  However, we show them for the purposes of this example.

The key to implementing is creation of the query that supplies data to the to do list subform.  Here’s the design of the query:

See the where clause.  This where clause restricts records based on the user logged in to looks at the hidden hold_user_id field on the form.  The OR clause is key to allowing users with high-level access to see and update all records.

Now that you have reviewed all the components of our row-level data security method you can see the form in run view here:

Key points:  The default value of the user_id holder field is set to -1 so that no records show when the form initially opens.  The user level (access level) field is set to null.

Next we’ll show how the form looks when a regular user logs in:

The code behind the login button makes sure the username and password are correct and then loads the user_id in the holder view and the access_level into the other holder field.  Then the subform and User_ID combo box are requeried.  You can see that Joe can only see his records and can only assign records to himself.

Now let’s see how the form works with an high-level user logged in:

Billy is an Administrator level user.  Note that Billy sees all users’ records and that the User dropdown lists all users so that Billy can add an entry or reassign a record to any username.  Billy is a powerful guy.

What you haven’t seen is the code behind the Login button and the code used for the User_ID combo box.  To see this you’ll need to run the actual example.

Download the record level security example now.

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016