Row-Level Security for Access Database

Row Level Security Alternative: Row level data security and tracking can be impossible or complicated task in an Access database.

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 user's data.  Perhaps you have created specific data entry/review forms for different users.

Our simple approach, often used by larger systems (Oracle, Ingres, SQL/Server), 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 user security..

The database downloadable example has the following specifications:

1) Store To Do 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 only show To Do entries that have been either entered or assigned to them.

4) An Admin-level user may see all To Dos 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:

The VBA code behind the logon button makes sure the username/password are correct and then loads the user_id in the holder field 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 own records and can only assign records to himself.

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

Billy is an Admin level user.  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.