Proper Security for your Microsoft Access Databases
Welcome
This seminar will teach you how to properly secure your Microsoft Access databases. But that's not all. You will also learn how to build proper security into the workflow of your database so that you can control every aspect of a user's experience, and monitor their work as well in a database log.
You will learn how to properly secure your Access databases to control who can log in, who can edit data, and who can make changes to the database itself. But that's only the beginning. You will learn how to build a database where you can control every step in the "work flow" process of data entry, and create a system log so you can see who is doing what.
Resources
Main Seminar Goals
- Create user and group security inside your database
- Manage the "work flow" of your database
- Control who has access to which database objects
- Send email notices from inside your database
- Create a detailed system log to track users
- Encrypt and secure your database files
Topics Covered
You will begin by creating a user information table (with username, password, and other data) and a secure logon form. This will allow us to control who can log on to the database.
Next we'll set up a Group form so we can assign users to different groups, like SalesRep, ServiceTech, Manager, Admin, etc.
In the next lesson, we'll define a "work flow" for our database. This will control how information is entered by our users. For example, our database will be for service work orders. Our work flow will consist of a sales rep entering a work order, a service technician performing the work, a manager reviewing the details, the sales rep follows up with the customer, and then the manager closes the work order.
You will be able to control which users will be able to enter data into which fields. For example, only service technicians can enter data into the "service performed" field, but not the "service requested" field, which is the job of the sales rep.
We will control each step of the work flow process with buttons that signify that a user is done with his work, and the work order is ready for the next person. Here you can see a "service call completed" button that will then send this work order to the manager for review. This work order will then be locked out so the service tech can't edit it any further.
You will learn how to send Email notices from inside the database when needed. This is great if you have employees that don't check the database all the time, so as to say, "your assistance is required."
The database will log every step in the work flow procedure. You will be able to see who did what, and when.
We will also create a system log table, so we can log everything that a user does: what time he logs on, what data he adds, what records he changes, etc. You name it, we can log it.
You will learn how to build your own internal database security. For example, do you want sales reps to be able to edit other sales reps' customers? Do you want service techs to be able to see customer data if they're not working on that customer's work order? Who can edit, delete, or add new customer records? What information should be required for new customers? Some of these are things you just can't do with standard Access User-Level Security.
You will be able to hide or show buttons and forms based on a user's security level. For example, the menu that a system administrator might see...
...will be completely different from what a simple service technician sees. This will help to control who can do what in your database.
We will spend a lot of time learning how to lock down your database and secure it from intruders. We will split the database into front-end and back-end files, encrypt the database with a password, turn off the Navigation Pane (database window) to keep people from poking around in your objects, set a Startup Form, and disable the Bypass Key so people can't go to the Navigation Pane directly when the database starts up.
You will learn how to dynamically link to tables in your back-end database using VBA code to overcome some security problems.
We will encrypt the front-end database into an ACCDE file (MDE file for older versions of Access). This will effectively secure the form, report, and module design capabilities and prevent people from viewing your database in design mode.
For our Access 2003 users (yes, there are still a bunch of them out there), we will spend some time going over the User Level Security Wizard. This wizard will secure an Access 2003 database and provide "OK" security if all you want is to keep Joe from Accounting from poking around in form design, and seeing data he's not supposed to. It's not hack-proof, however, but it does provide "quick and dirty" security in a pinch. We'll set up users, passwords, groups, and permissions.
This seminar is perfect for anyone who wants to learn how to secure an Access database, prevent someone else from accessing parts of the database they shouldn't, assign permissions so you can control who does what, and create a work flow strategy in your database.
This seminar is long (over seven hours) but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish.
Pre-Requisites
This is a Developer-Level Seminar. There will be a lot of VBA. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video.
Version
I am using Access 2007 in this seminar, however the lessons are perfectly valid for all versions of Access from 2003 and later. It's currently 2022 and I just recently verified that everything in this seminar still works with Access 2021 and Office 365.
Back-End Security
I want to be clear about the type of security this seminar offers before you purchase. This seminar is designed to teach you how to lock down your Access front-end database to prevent people from unauthorized use. You'll learn how to make user logons, set security permissions, and things like that. I show you how to best lock down your database, but keep in mind that if you need real security for your data then your back-end should be a real database server like SQL Server.
Access cannot store back-end data 100% securely. It's just a limitation with the way Access works. This seminar shows you the best way to get as secure as possible with only Access. A determined hacker can still get to your back-end table database and open the tables up directly. I do have another seminar called the Data Encryption seminar which shows you how to scramble the information in your tables to make certain fields unreadable. This way if someone does go directly into your tables, all they'll see is gibberish. There's nothing to prevent them from deleting or editing the data, however.
The only true way to have a 100% secure application using Microsoft Access is to use it for a front-end interface only and store your data in a database server like SQL Server. I don't currently have any lessons for on-premesis (in house) SQL Server installations. One is in the works. However, if you're considering allowing your users to work with your database online, over the Internet, then you may want to consider my SQL Server Online Seminar. I show you how to setup your SQL Server database on the web, and then connect to it via Access.
The lessons taught in this seminar are good enough for 99.9% of situations where you mostly trust your users and aren't worried about a determined hacker stealing your data. However, if you have sensitive financial data or other information that must be absolutely 100% secure, then you should use Access (and the lessons taught in this Seminar) in addition to SQL Server as your back-end.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access security, user level security, group security, administration, work flow process, audit log, audit trail, user activity log, system log, show hide buttons, lock down, encrypt, accde, user level security wizard
