Transcript exp07_a09_ppt

Exploring Microsoft
Office Access 2007
Chapter 9
Bulletproofing the Database
Robert Grauer, Keith Mulbery, Maurie Wigman Lockley
Committed to Shaping the Next Generation of IT Experts.1
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
Objectives





Encrypt and password protect a database
Digitally sign and publish a database
Save the database as an ACCDE file
Analyze database documentation
Analyze database performance
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
2
Objectives




Analyze database table structure and
relationships
Move data to a new database file
Analyze/optimize object relationships in a
database
Create usable switchboards
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
3
Data Protection

Countermeasures


Measures taken to protect your assets
Access provides three
countermeasure strategies



Password Protection
Ability to digitally sign database
Creation of ACCDE file
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
4
Password Protection


Prevents unauthorized
use of a file
Need to follow certain
guidelines



Last name and age
Bad
Smith18
Include combination of
Good
letters and numbers
criD8st!
Six or eight characters long
Combine uppercase,
lowercase and symbols Combination of uppercase, lowercase,
numbers, letters and symbols
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
5
Assigning a Password in Access
Prompt to Open database in Exclusive mode

Open database for Exclusive Use




Click to add password
Access open dialog box
Click arrow next to Open button
Click Open Exclusive
Use the Encrypt with Password command

Database tools tab, database tools group
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
6
Assigning a Password in Access
Enter password
Verify password


Enter password in Password text box
Reenter password in Verify text box

If no match, you must re-verify
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
7
Removing a Password
Decrypt Password


Open in Exclusive mode
Choose Decrypt database


Enter
Password
Database Tools group, Database Tools tab
Enter the Password, click OK
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
8
Digitally Sign and Publish a
Database - Terminology

Digital Signature


Certification authority


Electronic Authentication
Commercial company that validates
signatures
Timestamp

Encrypts date/time as part of the signature
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
9
Creating a Digital Signature
Package and Sign



Click the Office button
Click Publish
Select Package and Sign
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
10
Creating a Digital Signature


Select a certificate
Click OK


Can also view existing certificate
Database is packaged as ACCDE file
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
11
ACCDE Files
Previous Versions = MDE files
Office 2007 = ACCDE files

When saved as an ACCDE file




Visual Basic code is removed
Certain permissions are denied
Use of database objects is prohibited
Code and objects can be used

They can not be modified
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
12
Creating an ACCDE File
Make ACCDE file
Save As
dialog box
ACCDE extension



Package and Sign a file OR
Click Make ACCDE file
 Database Tools tab, database tools group
Click Save
 Uses the same filename unless specified
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
13
Caution: ACCDE Files


Before converting to ACCDE format
Make a back-up copy


Conversion cannot be undone!
Changes may need to be made later



ACCDE file can’t be modified
Modify the backup and re-convert
You must open in exclusive mode
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
14
Database Analysis Tools

Access provides three tools



Database Documenter
Performance Analyzer
Table Analyzer
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
15
The Database Documenter
Database
Documenter

Creates documentation on all objects

Create a data dictionary


Itemizes and lists object attributes
Click Database Documenter

Database Tools tab, Analyze Group
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
16
Using the Database Documenter
Database
Documenter
dialog box


Documenter Report
Select objects to include in report
Generate the report by clicking OK
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
17
The Performance Analyzer
Performance Analyzer




Identifies design flaws
Analyzes one or more objects
Recommends optimizations
Launched by clicking Analyze
Performance
 Database Tools tab, Analyze
Group
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
18
Using the Performance Analyzer
Select objects to
analyze


Results
Select the objects to analyze
View results in the Results Analysis pane

Given three potential solution for problems



Recommendations – click icon to make changes
Suggestion – click the icon to make changes
Idea – You must implement manually
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
19
Table Analyzer


Examines how tables are structured
Aids in normalization—eliminating
redundant data

Three different levels of normalization



1NF (First Normal Form) – Repeating
groups eliminated
2NF – Removes partial dependences
3NF – Removes transitive dependencies
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
20
Understand Normalization


Progression from one normal form to the next
1NF Promotes atomic fields


2NF removes partial dependencies


Fields that contain only one value
Depends on part of a composite key
3NF removes transitive dependencies

Relationship problems between tables
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
21
Using the Table Analyzer
Analyze Table
Identify table
to analyze





Click Analyze Table
 Database Tools tab, Analyze Group
Analyzer Wizard launches
Identify table for analysis
Make desired choices in Wizard
Click Finish
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
22
Splitting a Database
One database
Back-end
Copy of original database
only tables
Split
Front-end
Copy of original database
excluding tables

Back-end


Contains only tables
Front-end

Contains forms, queries, report – no tables
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
23
Database Splitter
Database
Splitter



Creates front-end and back-end simultaneously
Prompts to back up the database
Prompts you to name both ends
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
24
Switchboards
Switchboard



Allow non-Access users to use Database
Provide an attractive graphical interface
Provides an intuitive environment
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
25
Create a Switchboard
Click Edit

Use the Switchboard Manager



Database Tools tab, Database Tools group
Click Yes to create new switchboard
Click Edit in Switchboard Manager
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
26
Completing the Switchboard
Manager



Type text to appear in menu
Choose how to how to open object
Click Form arrow


Select object to run
Repeat process for additional items
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
27
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
28