User Level Security 3
By Richard Rost
3 years ago
User Level Security in Microsoft Access, Part 3
In this Microsoft Access tutorial I'm going to teach you how to make it so that users can only view and edit the records that they themselves have created. This is handy for example to make it so that sales reps can only see their own customers and not everyone else's.
Landon from Wausau, Wisconsin (a Platinum Member) writes: II have 3 sales reps who use my database. Is there any way to keep it so that they can only see their own customers and orders and not everyone in the system? I don't want to have to make them each their own database because I like to run reports based on everyone.
Disclaimer
This tutorial will show you security that is "good enough" to keep most users out of your database. However an experienced Access developer will be able to bypass this logon routine. To learn the best possible way to secure your database I recommend my Microsoft Access Security Seminar.
Members
Members will learn how to have it so that all users can view customer data but users can only edit or delete their own customer data. When it comes to deleting a customer we will first check to see if that customer has any orders and if so deny the deletion. We will then check to see if they have any contacts in the contact history table and if so will prompt the user again to make sure they're sure they want to delete him. And if the deletion is approved then we will run the SQL statements to delete the customer and all of his contacts.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Pre-Requisites
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Complex delete routine for customer, check contacts, check orders, dont allow deletion if customer has orders, prompt if customer has contacts, allow filters, tempvars
Intro In this video, you will learn how to set up user-level security in Microsoft Access so that each user can only view and edit the records they created. I will show you how to track which user owns each record, filter forms so users only see their own data, prevent users from turning off these filters, and automatically assign new records to the logged-in user. This is part 3.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's video is part three of my user-level security series. I am going to teach you how to allow users to only view and edit the records that they themselves created in the database.
This is the question that kicked it off, Landen from Wausau, Wisconsin. I hope I pronounced that right. One of my Platinum members, Landen says, "I've got three sales reps who use my database. Is there any way to keep it so they can only see their own customers and orders and not everyone in the system? I don't want to have to make them each their own database because I like to run reports based on everyone."
Of course, Landen, in order to do this, you have to go through parts one and two of this series. You have to set up the user-level logons, like we do in part one. In part two, you can control who can get into what. As long as they can get into all the forms and reports, that's fine. You want to prevent them from seeing other users' data.
We have to track which users own which particular records in whatever tables you want to control. It goes without saying: go watch part one first, where we set up the simple user-level security. In part two, we control what users can open up which forms and reports. In today's class, we'll set it up so that users can only view and edit their own records.
Once again, as a disclaimer, this tutorial will show you security that is good enough to keep most users out of your database or out of the things that they're not supposed to see. However, an experienced Access developer can bypass this logon routine. To learn the best possible way to secure your database, I recommend my full Microsoft Access Security Seminar. More information on that at the end of the video.
So we've got our database all set up from parts one and two. I'm going to log on: Richard and 599, 599 CD. I'm logged on. The first thing I'm going to do actually is remove that security that we put in the customer list form before. I think we put it in the form itself. Let's see what we've got. That's fine.
In the customer list, just to keep things easy for class, I'm going to go in here and just pull this out. This form open report or event, we don't need it. Usually, if you're controlling what records the users can see, generally you don't need to worry about what forms and reports they get into because they're only going to see their own records, the stuff that they created.
Now for whichever tables you want to control what they can see, you have to put a user ID field in that table. So let's just do customers: right-click, design view. In here, put a user ID. That's going to be a number of type long integer. That's a foreign key pointing to the user table, which is right here. You're going to put the user ID that this record belongs to in each customer, for example. You can do the same thing with orders, order details, contacts, whatever you want. I'm just going to do customers. You get the point.
Let's open up the customers and come in here. We've got, what, 29 customers? Let's just assign all these existing ones. You've got to assign them to the users manually the first time. If you've got a bunch of people in here, you're just going to have to go through and do it by hand unless you've got the sales rep's name in there, and you might be able to run an update query, whatever. I'm just going to assign the first, what, eight customers to me, and then we'll just make these two, and then three and four. Come on. You get the point.
So I've got the first eight customers. Close it.
Now, when the user logs on in the logon form, we get their username in a tempvar. So we can hang on to it and use it later on. Let's also grab their user ID, which we looked up right here. We know what that is because the user ID is how we can track what records they can view.
So, very similarly, we're going to say tempvars user ID. We're creating a new tempvar called user ID equal. And I forgot to mention Adam in the last tempvars video because he loves tempvars. The full tempvars video is coming soon. Relax.
Tempvars user ID equals ID. That's just a value. It's a long integer, so you can just set that right in the tempvars. When you're doing text boxes, you have to remember to say dot value. Save that. So it's a long integer. I've got the username and the user ID in memory. I can use that anywhere in the database, pretty much.
Now what you have to do is anywhere in the database where the user can open up a form or report that's going to have lots of stuff in it, like the customer list. Right now, I can see all 29 customers. So I need to change this button so it applies a where condition, or a filter - doesn't really matter much.
Right here, in fact, I'm going to get rid of this on error resume next. Right here, we're going to go comma, comma, comma, and a where condition of where the user ID of that customer equals tempvar tempvars user ID.
Now, I don't have a user ID yet in tempvars because I logged on before we added that code. Save everything, close it, log back in: Richard, 599 CD. Now the tempvar has been set in memory. If you wanted to display that on this form somewhere, you can. Doesn't matter.
Now watch this, when I open up customer list. Look at that. I'm only seeing my eight customers because the button said only show it where the user ID equals, in my case, one.
The user ID field does not necessarily need to be on this form as long as it's in the underlying table or query. One problem you'll notice is right down there - see the filtered thingy? Any user can just come in here and click and unfilter those results. To prevent that, go to design view, open the form's properties, find Allow Filters and set that to No. Save it. Close it.
Now when they open the form, look at that. They cannot turn the filter off. They can't use other filters on their own, like filtering by state or whatever. But it's a trade off.
Are there other ways you can do this? Yes, there are ways in code. You can change the entire SQL statement behind the form. That's a lot more advanced. Again, this is simple user-level security. I cover more advanced methods in my seminar. This is good enough for most people.
Now you also have to do that in any other forms where you can open up a customer. For example, here I can open up James Kirk by double-clicking there, but I can also turn off the filter in this form. So, for any of these forms underneath, you also have to set Allow Filters to No. Once you do that, then the user will not be able to open up anybody else's customers. They can't turn that off.
I've got number one here, record one of one, which is Minteo, which is one of my customers. You've got to do it in this button too, because that button opens up all the records in the form. So, same thing, I'm going to steal the code out of this button. Right here, we can just put that there. And do whatever other forms and tables and stuff you have.
Now, the only way I can get to orders in my database is by going through the customer form. So if I go to customer forms and I go to orders, I can unfilter that. So you have to make sure that you control this one too. Set Allow Filters to No. Once you do that, you shouldn't have to modify the button at all, because that button only opens up this customer's orders. You can't unfilter that.
Same thing with contacts. I've got everybody's contacts. Right-click, design view. Allow Filters is No.
Any questions? One more thing, and that's adding new records.
If you want to add a new record, come in here, hit the Add New button. We need a way at this point of knowing who's adding this record. So we have to add user ID to the form, but I don't want to see it, so I'm going to make it invisible. Just copy one of these other fields, copy, paste, delete the label. I'm going to slide this over here. This is going to be the user ID field and name, and it's not a currency. Its default value is going to be what? This is the nice thing about tempvars. You can use them here, in queries, in reports - you can use them everywhere, almost everywhere. Equals tempvars user ID, just like that.
I'll zoom in so you can see it better. Ctrl F2. Equals tempvars user ID. Now, I like to make these red, too, when they're hidden. That way I can see when I go to design view. I know that's a hidden field. How do we hide it? Go to Format, Visible: No. Save. Close it. Close it. Open it.
Let's add a new record... Harry Mudd. Close it. Now, if I open up my customer list, there's Harry Mudd. If I go check the customer table, at the bottom there he is. Let's make sure that ID is set, and, yep, ID 1.
There you go. Do the same thing for contacts, orders, whatever else you want to track for each customer. Now, when your users log in, they can only see and edit their records.
If you want to learn more, in the extended cut for the members, here's what we're going to do. Instead of making it so they can only see and edit their own records, we're going to make it so that all the sales reps can see everyone - they can see all the customers, see all the orders, whatever, but they can only edit their own customers.
That way, if a customer calls in and a different sales rep takes the call, they can still help the person and look up the order, but they just can't change things. Some offices work differently.
Then we're going to go through and manage being able to edit or not. That's not a big deal. Deleting can be complicated, so we're going to check. First of all, only a sales rep can delete their own customer. You can't delete a customer if they have orders. If that customer has got orders in the system, you can't delete them. It creates an accounting problem.
Then we'll ask, "Are you sure you want to delete this person?" if they have no orders. Next, we'll check to see if they've got contacts in the system. If they've got contacts in their contact history, we can still delete them, but we just want to double-check and say, "Are you sure?" If the user still says yes, and they have no orders, and they said yes twice, then go ahead and we'll issue the SQL commands to delete all that stuff, and everybody's happy.
That's covered in the extended cuts. Silver Members and up get access to all of my extended cut videos. There are hundreds of them now. Gold Members get to download free copies of these databases for themselves. So check it out.
And again, don't forget to check out my security seminar if you want to learn more about properly securing your database and locking it down.
And that, my friends, is going to be your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.Quiz Q1. What is the primary goal of the technique demonstrated in this video?
A. Allow users to delete any record in the database
B. Allow users to view and edit only the records they have created
C. Prevent users from logging into the database
D. Allow users to export the database to Excel
Q2. Before implementing user-level record visibility, what must be set up first?
A. Custom encryption for the database table
B. User-level logons and basic form/report access security
C. Email notifications for new records
D. Automatic backup of user records
Q3. How is user ownership of records tracked in the database?
A. By using the user's name in a text field
B. By linking records to a UserID field that acts as a foreign key
C. By tracking the date the record was created
D. With a randomly generated string for each record
Q4. Why is it important to assign existing records to users when implementing this security?
A. To prevent the records from being deleted
B. To enable accurate filter functionality per user
C. To make sure records can be copied between users
D. To allow bulk editing by any user
Q5. What is the purpose of using TempVars in this solution?
A. To store and recall the current user's name and user ID during their session
B. To encrypt sensitive data before saving
C. To send emails to the current user
D. To back up records automatically
Q6. Which form property setting disables the ability for a user to remove a filter?
A. Allow Additions: No
B. Allow Edits: No
C. Allow Deletions: No
D. Allow Filters: No
Q7. When adding a new customer record, how is the user ID assigned to it automatically?
A. From a lookup wizard in the form
B. By assigning the value of TempVars("UserID") to the UserID control's default value
C. By requiring the user to type in their user ID
D. By using the system date
Q8. Why is the UserID field made invisible on data entry forms?
A. To prevent accidental edits to the user ID
B. To save space on the form
C. Because the users need to select their own ID
D. Because Access does not allow visible foreign keys
Q9. What potential weakness does the presenter note about this user-level security approach?
A. It can easily be bypassed by anyone using Excel
B. It can be bypassed by experienced Access developers
C. It causes data loss when enabling filters
D. It makes reports inaccessible to users
Q10. What adjustment must be made to ALL forms where user-level visibility is required?
A. Set Allow Additions to Yes
B. Ensure Allow Filters is set to No
C. Add a password field to each form
D. Lock all records by default
Q11. What alternative approach does the presenter mention for more complex scenarios?
A. Deleting the user table altogether
B. Modifying the SQL statement behind forms for data visibility control
C. Creating an Excel export for each user
D. Allowing users to email their records
Q12. How does the extended cut for members expand the functionality?
A. Users can delete anyone's records
B. Users can view all records but only edit their own
C. Users can filter by any field, even if not allowed
D. Users can print a list of all user names
Q13. Why might you want to prevent deletion of a customer with existing orders?
A. To compress the database size
B. To prevent accounting issues from lost data
C. To speed up query processing
D. To allow sales reps to transfer customers
Q14. When adding code to restrict record visibility, what should be applied to forms opened from other forms (such as order or contact forms opened from a customer form)?
A. They require no change since they are linked
B. They should also be filtered and have Allow Filters set to No
C. They should enable user selection in a combo box
D. They should remove all navigation controls
Q15. What is the chief benefit of using this user-level security structure for a sales team, according to the original student query?
A. It lets each sales rep see only their own customers while management can run reports for all
B. It allows customers to edit their orders directly
C. It disables all reporting features
D. It prevents customers from being assigned to sales reps
Answers: 1-B; 2-B; 3-B; 4-B; 5-A; 6-D; 7-B; 8-A; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will show you how to restrict users in Microsoft Access so they can only view and edit the records that they themselves have entered into the database. This is part three in my series on user-level security.
The need for this kind of security comes from users who share a database among several people, such as sales reps, but do not want each user to have access to every record in the system. For example, one user might want each sales rep to see and work with just their customers and orders while still allowing management to generate reports based on all sales activity.
To implement this in Access, you must first complete the setup described in parts one and two of this series. In the first part, you create user logins to control access. The second part lets you determine which users can open specific forms and reports. Now, in part three, we will ensure that users can only view and edit records they own.
This approach to security is adequate for most purposes, but keep in mind that an advanced Access developer could potentially bypass it. For the most robust protection, you should look into my full Microsoft Access Security Seminar, which I mention at the end of this article.
Assuming you have user-level logins set up, the next step is to make sure the tables you want to restrict contain a user ID field. For example, in the Customers table, you need to add a field called UserID, set as a long integer. This acts as a link to your Users table, recording which user owns each customer record. You can apply the same idea to other tables such as Orders or Contacts. For existing data, you'll need to manually assign each record to a user the first time—unless you already have some indicator to automate the process with an update query.
When a user logs into the database, store their user ID in a TempVar so it is available throughout their session. This makes it easy to compare the logged-in user's ID to the UserID field in your tables or forms.
To limit the records shown on a form, such as a customer list, set up the button or event that opens the form so it uses a filter. The filter should allow only records where the UserID matches the TempVar with the current user ID. After making these changes, closing and reopening the form will show each user only the customers that belong to them.
It's important to note that users might still be able to remove filters on forms, allowing them to see all records. To prevent this, set the form's Allow Filters property to No in design view. Now, the user cannot simply turn off the filter and access someone else's data.
Make sure to repeat this process for any other forms where users might access customer data, such as detail or edit forms that pop up when a record is double-clicked. Each of these forms should have Allow Filters set to No and be controlled by a filtered open event or button.
When users add new records, you must ensure those records are linked to the correct user automatically. Add the UserID field to your form, but make it invisible so users do not change or even see it. Set its default value to the current user's ID using the TempVar you set up at login. This way, every new customer, order, or contact record the user adds will be assigned to them without additional input.
Once configured, users will only be able to see and modify the records they own. You can repeat this same process for any table or form where you want to apply this kind of security.
If you are interested in going further, today's Extended Cut for members demonstrates how to allow sales reps to view all records in the system but only edit their own. This method can be useful if your business model requires letting users find and help with each other's customers, but not make changes to them. I cover more complex scenarios here, including handling record deletion where, for example, a customer cannot be deleted if they have orders, and adding confirmation dialogs for other deletions. These advanced techniques are only available to Silver Members and higher, who can also access my library of extended cut videos. Gold Members may download sample databases as well.
For those seeking stronger security measures, consider my security seminar, which is designed to show you how to better lock down your database.
You can watch a complete video tutorial with detailed, step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Adding a UserID field to tables for record ownership
Assigning existing records to users
Storing logged-in UserID in a TempVar
Filtering forms by current user's records
Applying a where condition to form open actions
Preventing users from removing filters
Setting Allow Filters property to No on forms
Hiding the UserID field on data entry forms
Assigning default UserID to new records using TempVar
Restricting record visibility and editing to record owners |
