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 database
unless 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.
Our programming team has 15 members containing a wide variety of technical database knowledge and programming experience. We are kind of like Allstate Insurance - 'we have seen it before'.
Did you know you don't need to move to SQL/Server to run and manage your Access database on the net. There are many alternative solutions. Among them are Amazon's AWS cloud services and various hosting services in nearly every state.
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
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
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
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
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
Next we'll show how the form looks when a regular user
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.
record level security example now.