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.
18 years ago
