<%dim crumb crumb="Row Level Security in Access" Crumb2="Row Level Security" crumb1="Simple User Security Download" %> Row Level Security in Access
Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

<%if len(crumb2)>2 then response.write crumb2 else response.write ">

Open Email Using SendObject Access Download

User Inactivity Logout VBA Code

Disable Shift Key Demo

Send Email Microsoft Access Tutorial Download (Advanced)

Sequential Counter in Query

Multi Select List Box Query Parameters

Row Level Data Security

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 Example (Simple)

Union Query (Advanced) Access Tutorial

How To Fill Fields From Combo Box

Use Global Variables as Parameters

Continuous Form Dependent Combo Box

How To Program Continuous-Continuous Master/Detail Forms

Access Bar Chart / Bar Graph

Programming Crosstab Query Example

TransferText & OutputTo Microsoft Access

DoCmd.OpenForm & OpenArgs VBA Example

Running Sum Query Method

Choose Command Dynamic SQL Order By

Access Conditional Format

Access Report Banding

Popup Form Control Method

<% 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:
Row level data security

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:
user level record security tracking

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.

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016
<% dim rnum Randomize rnum = Int(Rnd * 10) + 1 response.write " Updated On " & now-rnum %>

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>

MS Access 2013, Access 2010 & Microsoft Office 2007