Row-Level Security for Access Database

Row Level Security Alternative: Row level data security and tracking can be challenging requirement for an Access database developer.  In this example we demonstrate how to simply implement a solution to row and record level datea security.

A frequent requirement in multi-users databases is to restrict visibility or editability of records depending upon the owner or creator of the data records. Some of you may have gone so far as to create different tables, or even different databases, to store different user's data.  Perhaps you have created specific data entry/review forms for different users rather than employing a single intelligent data review form.

Our approach is to create a view of the data (a query) that dynamically filters records based on which user is logged into the database to enforce record-level data security.  This simple data security requirement may be expanded to more complex requirements including locking records after a time delay, restricting viewing of records based on location of user.

The downloadable Access database described here implements the following specifications:

1) Store activity lists for users containing a description, date, status, and user fields

2) Provide a user login function and segregate users into two levels: User and Admin

3) When a user-class person is logged in then only show entries which have been either entered by them or assigned to them.

4) An admin-level user may see all records and may assign/reassign any record to any user.

If you haven't already seen our detailed explanation of this data security example then go to our tutorial on record-level security.

When a regular user logs in the data review/entry form looks as follows:

User login validation is performed using visual basic code tied to the login button.  The queries use the hidden fields stored on the form to restrict view of data records depending on which user is logged in.

Here is how the form responds to an Admin user-level user logging in:

Billy is an Admin level user.  The Billy sees all user's records and that the User dropdown lists all users so that Billy can add an entry or reassign a record to any user.

Download the row level security example now.