Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Access User Level Security
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   13 years ago

A lot of people ask me about User-Level Security in Microsoft Access, and why I haven't covered this in my Access classes yet.

I have mentioned this a couple of times in my Developer-Level Access courses (300+) but I'll discuss it here just so everyone can read it.

I do NOT like User-Level Security (ULS) in Microsoft Access for a couple of reasons. The most important reason is that ULS does not do much to secure the data in your tables, which is what most people are really looking for. Anybody who knows databases can connect to your tables using another database and modify your information. So Access ULS is almost meaningless when it comes to actually SECURING your data.

In order to secure your DATA, you need a database SERVER program like Microsoft SQL Server or Oracle (or even Sharepoint). The database hides and encrypts your data, and without the right authorization password, nobody can get at it. And yes, I will be covering SQL Server in the future.

Now as far as securing the REST of your database, you can easily encrypt and protect your queries, forms, reports, macros, and modules, plus all of your VBA code by simply turning your database into an MDE file (Microsoft Database Encrypted). This essentially "compiles and locks" your database up, and this is what you give to end users or people you don't want modifying your "program." I cover creating an MDE file in Access 307:

/site/courselist/access/access307


So what is Access User-Level Security good for? Well, it does provide you with an easy list of users to maintain, and it can give you a username/password login prompt for your people to actually log in to your database. You can use this to track who's doing what, or even keep people out of certain forms or reports.

But... if these users know ANYTHING about Access, they can easily create their own blank MDB file and then attach to your tables, and import or modify your records. So you can consider Access ULS like a lock on a screen door. It will keep HONEST users (perhaps in a small office or home setting) from going where they're not supposed to go. However any hacker or unscrupulous employee with a little bit of Access knowledge can destroy your data.

In fact, Microsoft knows that ULS isn't very good. They removed it completely from their new Access 2007 database format (ACCDB). If you are using an Access 2003 (or older) database that you are using in Access 2007, you will still have your ULS settings. However if you upgrade to the new Access 2007 ACCDB format, you will lose all of your user account settings. Microsoft realizes ULS wasn't very good... and of course they want you to buy SQL Server.

So that's it. That's why I don't like user account settings and permissions in Access. They're too easy to get around. You can easily do the exact same thing yourself with a simple user table and controls in your VBA code to keep people out of forms and reports (with buttons and such). I show you how to do this in Access 322 with a custom logon form:

/site/courselist/access/access322


Aside from that, I probably WILL cover Access ULS in a future lesson or seminar - JUST to show it to you, but I'm going to keep it brief. You can probably just run through the Wizard yourself and figure it out in about 10 minutes. It's not hard... but that's my point... it's NOT hard (to crack).

UPDATE: In October of 2009, I covered Access security in detail in the Access Database Security Seminar. This seminar covers just about everything you ever wanted to know about securing your Access databases. It covers all versions (up to 2007) and shows you the most effective ways to lock up your databases to prevent unauthorized use.

User Level Securtity Upload Images   Link  
Richard Rost 
12 years ago
Mark, if you're looking for a REAL SECURE database to keep out people who KNOW what they're doing (like me), then Access ULS is not going to work for you. You need a database server like SQL SERVER. If you are looking for "good enough" security on semi-sensitive information - just to keep out Joe from Accounting who isn't necessarily tech-savvy, then ULS is just fine. You're right, there are some extra steps you can take to keep people out of the tables too, but again, it's not 100% secure. It's like a lock on a screen door.
Add a Reply
Access security and auditing of users Upload Images   Link  
Richard Rost 
12 years ago
Bob, I am going to cover security in a future lesson... but Microsoft did away with user-level security in Access 2007, so it may only be a short seminar for 2003 users. Auditing users' changes is something you could do with a recordset, and I show how to create a log in one of the existing lessons... in the 320 series, I believe.
Add a Reply
User Level Securtity Upload Images   Link  
Mark annett 
12 years ago
I'm not sure that I agree with what he has to say, "The most important reason is that ULS does not do much to secure the data in your tables, which is what most people are really looking for. Anybody who knows databases can connect to your tables using another database and modify your information. So Access ULS is almost meaningless when it comes to actually SECURING your data." Read More...
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

12/3/2021Access Beginner 2
12/3/2021Access Beginner 2 Lessons
12/3/2021Access Beginner 2
11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/28/2021Random
11/22/2021Currency Symbols
11/13/2021Access Developer 36
11/13/2021Access Developer 36 Lessons
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access security user level security tips uls  PermaLink