%dim crumb crumb="Row Level Security in Access" Crumb2="Row Level Security" crumb1="Simple User Security Download" %>
Open Email Using SendObject Access Download
User Inactivity Logout VBA Code
Disable Shift Key Demo
Send Email Microsoft Access Tutorial Download (Advanced)
Programming MS Access Security Alternative
Single User Inventory Calculations Download
How To Perform Multi-User Inventory Calculations
Make Dependent Combo Box Code MS Access Tutorial
Union Query (Advanced) Access Tutorial
How To Fill Fields From Combo Box
<% Gnum="9028376671" %>
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.
MS Access 2003
<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>
MS Access 2013, Access 2010 & Microsoft Office 2007