| |
| |
|
Access
Split Database Seminar
Set up a secure database using Windows File & Folder Security
|
| |
|
In this seminar, you will learn how to
set up a database using Microsoft Access with multiple back-end (table)
files: one that every database user can access, and another for
sensitive information, such as financial data, that only a small,
select group of users can access. You will then learn how to use Windows
file and folder sharing to secure the sensitive back-end database
file so that only the users you select can read that data.
This seminar stands on its own,
however you will benefit the most if you take this seminar after
warching my
Access Security Seminar. That seminar focuses mainly on locking
down and securing your FRONT-end file (forms, reports, queries,
VBA code, etc.) whereas this one focuses on hiding and securing your
back-end data files.
Watch this video to learn more
about this seminar:
|
| |
|
Seminars
- Access Split Database Security |
|
Description: |
Set up a secure Access database using multiple back-end tables
and Windows file/folder security with users and groups. |
|
Versions: |
I will use Access 2010,
however the lessons are valid for all versions of Access back to
2000 with some minor cosmetic differences. |
|
Pre-Requisites: |
This course stands alone, however you would benefit greatly from
having first taken our
Access
Security Seminar. You should have completed at least our
Beginner-level Access courses before taking this seminar. |
|
More Info: |
Full disclosure: if you've already taken the
Security Seminar,
about half of the material covered in this seminar will be a review for
you. It was necessary to include some of the same material so that this
seminar stands alone. The other half of the material, especially the
seconds on the Linked Table Manager, setting up Shared Folders,
and Windows File Sharing are new. |
|
Running Time: |
1 Hour, 8 minutes |
|
Cost: |
$39 - Order
multiple courses to receive a discount up to 50% off |
|
|
|
This
seminar will cover:
1.
Set up multiple customer tables, one for normal data, one for
sensitive data
2.
Build two customer forms; the sensitive form requiring a
password to open
3.
Split your database into 3 files: front end and two back-end
files for tables
4.
Create shared folders on your server or Windows PC
5.
Use Windows-level file & folder security to prevent access to the
sensitive data
6.
Move the back-end files to the server and relink with the
Linked Table Manager
7.
Encrypt your front-end database and create an ACCDE file
In Lesson 1, we'll talk about WHY you should split your database
and what security benefits it provides. In our example, we'll build a
customer database. Most of the customer information has to be accessible
by all of the users of our database (name, address, phone number, etc.)
however some of the information needs to be kept secure and ONLY
visible to a select few (credit card numbers, balances, credit limits,
etc.)
In Lesson 2, we'll set up a customer
table, a secure customer table, and then we'll set up a
one-to-one relationship between them.
In Lesson 3, we'll build a customer form
and a secure customer form. We'll set up a command button to open
the secure form but require a password via an inputbox.
Users will need the password to open the secure form. Then we'll hide
the navigation pane on database startup so that users can't just
browse to your secure form and open it themselves.
In Lesson 4, we'll split the database
into three files: one for our front-end objects (queries, forms,
reports, modules, etc.) and then two back-end files: one for the normal
customer data, and another for the sensitive, secure data.
In Lesson 5, you'll see how to set up
shared folders on your network file server. I'll show Windows 2000
Server, but most versions of Windows Server work the same. I'll
also show an example using just peer-to-peer file sharing on
Windows 7 machines. We'll then move the back-end database files to these
shared folders and use the Linked Table Manager to tell the
front-end database where they went.
By using the techniques in JUST
this seminar, you can reasonably secure your data against
unauthorized access from users on your network. Coupled with the
techniques shown in my
Security Seminar
and Data
Encryption Seminar, you can really create a locked-down database
without the need for a Database Server program. Of
course, if you have any questions about whether or not this seminar is
for you, please
contact me.
Access Split Database Security Seminar - Outline
00. Intro (5:01)
01. Why Split Your Database (5:07)
02. Customer Tables (10:53)
CustomerT
CustomerSecureT
One-To-One Relationship
Referential Integrity
Cascade Deletes
Cascade Updates
03. Customer Forms (11:36)
CustomerF
CustomerSecureF
Button to Open Secure Form
Without Security
VBA DoCmd.OpenForm
Add a Password
InputBox
Hide Navigation Pane
04. Splitting the Database (5:57)
Database Tools
Move Data > Access Database
Database Splitter
Copy and Rename Backend Files
05. Set up Shared Folders (14:21)
Windows Server
Sharing
Share This Folder
Share Permissions
Full Control
Users & Groups
File Sharing in Windows 7
Linked Table Manager
Refresh Links
06. Encrypted Front End (8:43)
ACCDE File
Distribution Copy
Save your ACCDB file!
07. Review (6:01)
|
|
| |
| |
Student Interaction:
Access Split Database Seminar
|
Richard on 6/30/2012:
In this seminar, you will learn how to set up a database using Microsoft Access with multiple back-end (table) files: one that every database user can access, and another for sensitive information, such as financial data, that only a small, select group of users can access. You will then learn how to use Windows file and folder sharing to secure the sensitive back-end database file so that only the users you select can read that data. This seminar will cover:
1. Set up multiple customer tables, one for normal data, one for sensitive data
2. Build two customer forms; the sensitive form requiring a password to open
3. Split your database into 3 files: front end and two back-end files for tables
4. Create shared folders on your server or Windows PC
5. Use Windows-level file & folder security to prevent access to the sensitive data
6. Move the back-end files to the server and relink with the Linked Table Manager
7. Encrypt your front-end database and create an ACCDE file
You can learn more about this seminar here: Microsoft Access Split Database Security Seminar |
MUBEEZI M on 7/3/2012: Thank you Richard! I really enjoyed it!
For some strange reason, i can't find the back slash on my keyboard! Usually, i have to open Excel, type =CHAR(92) in a cell, then copy the resultant back slash into the VBA editor.
Is there something missing on my keyboard? Or is there a key combination that i have not yet learnt to utilise?
MICAH
Reply from Richard Rost:
Wow. I've never heard of that. Unless you have a foreign-language keyboard I don't know what the problem is. The backslash on ALL of my keyboards is on the same key as the PIPE symbol: |
Can you snap a picture of your keyboard and email it to me? amicron@gmail.com.
|
Jerry on 7/7/2012: At 6:57. You opened the CustomerSecureF showing all secure information. You did this without using a password. I thought the purpose of this course was to show how to protect secure information? Please don't tell me that I can take another course. This one failled in its' stated objective. What gives?
Reply from Richard Rost:
Jerry, if your users have access to the NAVIGATION PANE they can open up any forms they want. You can HIDE the Navigation Pane on database startup (which is in Access Options > Current Database) so they can't. The password only pops up if you use the button to open the form. You COULD put the inputbox code in the OnLoad event of the secure form instead of the button, and then it wouldn't matter how you opened the form.
REGARDLESS of whether they can open the FORM or not, if they don't have security rights to the SECURED BACK-END TABLE database (which you set up at the WINDOWS level), then they won't see any data in the form or will get an error message. *I* have access to that back-end table, so the form opened.
Objective SUCCEEDED. :)
|
Mubeezi Micah on 7/8/2012: Dear Richard,
Actually, the backslash is together with the pipe symbol as you mention above.
When i press this key, i get # and if press the key together with the SHIFT key, i get ~.
On number 3 of my key board is the # key. When i press SHIFT+3, i get £.
I bought this computer from the Netherlands and i remember configuring Windows to English- United Kingdom.
Are there some additional settings that i may have to adjust?
MICAH
Reply from Richard Rost:
Try setting Windows to English-US.
|
Ricardo Tolliver on 3/25/2013: Q- After I've split my database, if the fornt and backend are on different computers how do I deliver changes for forms, reports and tables especially if the (Front End) can be on several different computers?
Reply from Richard Rost:
Well, you CAN keep a working copy of the Front End database on the SERVER for people to use. If your database isn't that big (a few MB perhaps) and your network is fast, it's OK to just have multiple clients running that file directly.
However, if you have the Front End distributed to lots of different workstations, then it's really up to you to distribute those changes. You can make it as simple as creating shortcuts to their database folders and they copying and pasting the new FE file yourself. Or you can email them a link and say "here, download this." I even went so far with one client to program an automated solution that would:
1. Check for a new version (number stored in a table)
2. If it was new, quit the database and launch a BATch file
3. Batch file copies new ACCDB file from server to local workstation
4. Batch file then re-launches database
It's completely up to you.
Richard
P.S. If enough people want to see how I created that automated solution, let me know. If I get a handful of people interested, I'll make a mini seminar out of it.
|
Deon Riley on 3/26/2013: Hi Richard,
Yea bring it on. I'll definately go for this as a mini seminar. You know me by now - anything Access.
|
Brent on 3/27/2013: I'm interested for sure.
|
Nick P on 3/28/2013: I'd like to see that.
|
Brauna Rosen on 5/17/2013: Hi,
I just need to confirm something with you: the back end resides on my computer but not on the shared file and the front end is on my computer's shared file and on the users shared file. Is that correct?
Reply from Richard Rost:
If you have two computers (PC1 and PC2) you can set up a SHARED FOLDER on PC1. In that folder, put your BACK-END database (tables). It doesn't matter where the FRONT-END files go. You can put them on the Desktops of PC1 and PC2 if you like. Each computer should have its own copy. Now, just link to the tables in your SHARED FOLDER, and you're all set.
|
|
|
| |
|
You may want to read these articles from the 599CD News: |
|
|
|
