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.
downloadable Access database described here implements the following specifications:
activity lists for users
containing a description, date, status, and user fields
Provide a user login function and segregate users into two levels: User and
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
see all records and may assign/reassign any record to any user.
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.
row level security example now.