|
This
seminar has several main goals. You will learn how to:
1.
Create user and group security inside your database
2.
Manage the "work flow" of your database
3. Control who has access to which database
objects
4. Send email notices from inside your database
5. Create a detailed system log to track users
6. Encrypt and secure your database files
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, 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.
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.
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.
Click here for
another video showing my step-by-step lesson plan guide
to what's covered in each lesson of this seminar in
detail.
Click
to Play - Step-By-Step Lesson Plan
Click here for
a video
walk-thru of exactly what this database does, and see
how the flow of information going into the database can be tightly
controlled from sales rep to service tech to manager.
Click
to Play - Database Walkthru
All
of the sample database files for this seminar are available on my Web site
(instructions on where to download them are in the course videos). They
are available in Access 2007 and 2000 formats.
This
seminar is available to view online in the Amicron Theater.
You can
click here to watch the first lesson absolutely free.
Of
course, if you have any questions about whether or not this seminar is
for you, please contact me.
Access Security Seminar Outline
00. Introduction (8:00)
01. Database
Setup, Login Form (12:23)
Create a New Database
Trusted
Locations
Set Overlapping Windows
Setup UserT Table
Access User
Level Security
Why ULS is Not Very Secure
"Good Enough" Security
Create Login Form
Popup & Modal Forms
02. Login Form
Code (11:42)
Docmd.Quit
Check for blank username/password
DLOOKUP Username and Password
NZ Function
03. Main
Menu (9:43)
Exit Database Button
UserID, Username Fields
on Main Menu
04. User Groups (14:35)
GroupT
Table
Group to User Cross Reference Table
Junction Table
UserF
Form
GroupF Subform
Adding Users to Groups
05.
Checking Group Permissions (12:35)
Open User Form Button
Code to Check if User in Group
Is the User an Admin
Defining Your
Own Function
Public Function
Return a Boolean
Global Modules
06. Service Process (9:06)
Define Service
Processes
Create CustomerT Table
CustomerF Form
StatusT Track
Work Order Status
ServiceT Detail on Service Call
07.
Service Form (10:40)
Build Service Form
Create Combo
Boxes
08. Service Listbox (11:23)
Listbox
Service for Current Customer
OnCurrent Event
Modify SQL for
Listbox to Format
Format Function in SQL
09. Sales Rep
Security 1 (10:51)
Security: Who can Browse Customers
Assigning Customers to Sales Reps
Query to Show Only Sales Reps
Sales Rep Combo Box for Customers
10. Sales Rep Security
2 (10:43)
AllowAdditions, AllowDeletions, AllowEdits
Setting Properties in VB Code
Change Properties Based on User Group
Change Properties Based on SalesRepID
11. Locking Fields
on Form (8:56)
Locking All Fields on a Form
For Each
Control Loop
RGB Color Function
12. Unlocking Needed
Fields (7:08)
Unlock Fields for Sales Reps
Unlock Fields
for Service Techs
13. Create Service Order 1 (10:08)
Create New Service Order Button
Enable/Disable the Button
Get
Default Value from Another Form
14. Create Service Order
2 (14:01)
Requery Service Listbox OnGotFocus
Open a
Specific Service Order
Set a Default Sales Rep
Security Opening
Service Orders
15. Assign to Service Tech (10:27)
MsgBox vbYesNoCancel
Lock Fields Once Assigned to Tech
16. Unassigned Service List (12:47)
UnassignedServiceQ Query
service List Form
17.
Accepting Service Call (7:33)
Accept Service Call Button
Perform Service
18. Unassigned or My Calls (7:58)
Show Unassigned Service Calls
Show My Service Calls Button
Show
ALL Service Calls
Dynamic SQL in RowSource of ListBox
19. Service Completed (11:32)
Mark Service Completed
Show
Status on Service List
Show New, Open Service Calls
20. Hide Buttons on Main Menu (14:30)
Show/Hide User Form
Button
Show/Hide Browse Customers Button
Open Service List Show
Different Things
Private v. Public Form Functions
21.
Fixing Some Problems (8:01)
Fix Problem with Sales Reps
Fix Missing Button Problem
Force Service Details to be Entered
22. Manager Review (12:29)
Manager Review 1
Button
Show Service Calls to be Reviewed
Assign For Followup
Button
Hiding All Buttons on ServiceF
23. Followup,
Closing Call (11:38)
Sales Rep Followup Completed Button
Manager Closed Button
24. Sending Email Notices (13:00)
Sending Email using Microsoft Outlook
Docmd.SendObject
25. Admin User Combo (11:51)
Make the UserID a Combo
Box
Quick Change to Another User
26. Locking the
Database 1 (9:43)
Split the Database
Front End v. Back
End Databases
Encrypt Database with Password
Open Exclusive
Linked Table Manager
Get External Data
Create Links to Backend
Tables
27. Locking the Database 2 (13:28)
Backup Your Database
Turn off Navigation Pane
Turn off Database
Window
Show Startup Form - LoginF
Application Title
Display
Navigation Pane
Allow Full Menus
Allow Shortcut Menus
Hold
SHIFT Key to Bypass Security
VBA Code to Shut Off Bypass Key
AllowBypassKey
Database CreateProperty
Properties Collection
28. Locking the Database 3 (10:09)
Import Linked
Tables Bypasses Security
Create Admin Menu
29. Locking
the Database 4 (13:55)
Global Const
Dynamically Link to
Tables
Destroy Links to Tables
30. Locking the
Database 5 (14:45)
Create a Recordset
OpenDatabase
OpenRecordset
Read User Info Before Linking Tables
31.
Locking the Database 6 (17:09)
OnClose Event
Terminate
Table Links
The Navigation Pane Shows
The Database Window Shows
Link Tables using TableDefs
32. Creating an ACCDE File
(9:11)
Make ACCDE File
Make MDB File
Source Code
(Design) is Gone
VB Debug Compile to Find Errors
33.
AC2003 User Level Security 1 (10:36)
User Level Security
Wizard Part 1
34. AC2003 User Level Security 2 (12:43)
User Level Security Wizard Part 2
Create a Workgroup File
Securing
Objects
Selecting Security Groups
Creating User Accounts
Creating an Unencrypted Backup
Logging on with User Permissions
User and Group Permissions
Add or Delete Users
Add or Delete User
Permissions
35. Other Database Properties (8:40)
Show All Database Security Properties
VBA Code to Change Properties
StartupShowDBWindow
StartupShowStatusBar
AllowBuiltinToolbars
AllowFullMenus
AllowShortcutMenus
AllowDefaultShortcutMenus
AllowToolBarChanges
AllowBreakIntoCode
AllowSpecialKeys
AllowBypassKey
36. Database Log (17:02)
Track
Employees with a Database Log
Create LogT Table
Create LogIt
Function
Track User Logons
Track Data Changes
OldValue Property
37. Review (2:59)
Review Topics
MAIN CONCEPTS COVERED
Creating your own security inside the database
-
Making a user table
- Logon form
- User groups with permissions
Controlling the "work flow" in your database
-
Sales rep enters a work order
- Service tech performs the work
-
Manager approves it
- Sales rep follows up
- Control who can enter
data into what fields
- Buttons to move to the next step in the
process
- Locking access to data once that step is completed
-
Sending an Email notice with Outlook for the "next step"
- Creating a
system log to watch everything your users do
Determining
which users can access what forms, fields, data
- Sales reps
can't edit each others customers
- Service techs can't see customer
data
- Users can't bypass the "work flow"
- Managers can edit
everything
- Admins can supervise and change access rights
- Who
can browse customers?
- Who can see service reports?
- Showing or
hiding buttons/forms based on access rights
- Preventing record
edits, deletions, or additions
- Forcing users to enter required data
Properly Securing Your Database
- Split the
database into Front-End and Back-End files
- Encrypt Back-End
database with a password
- Turn off Navigation Pane, full menus,
toolbars, etc.
- Disable the Bypass Key for startups
- Dynamically
link to tables using VBA code on database startup
- Destroy those
links when database closes
- Compile your database into an ACCDE
(MDE) file
- Access 2003 User Level Security Wizard
- Working with
Access 2003 users, groups, and permissions
- Advanced database
properties in VBA such as AllowBreakIntoCode
Programming
Topics
- Using DLOOKUP to lookup usernames & passwords
-
Many-to-many relationships with junction tables (users/groups)
-
Locking fields on a form based on user rights
- For Each control
loops
- Creating your own global modules with public functions
-
Dynamic SQL rowsource for listboxes
- AfterUpdate, OnCurrent,
OnGotFocus events
- Use the RGB function to set color in VBA
-
Getting a value from a form using Forms!Formname!Field notation
-
Create and edit databases in VBA
- Define global constants
-
Create and work with RecordSets to read user info
- Use a recordset
to read from an external database with a password
|
