Return to IFS
110
Notes!
Tutorial 9
Working with Object Models and Securing the
Database
Introduction to Object Models
n
Access Object Model diagram (next slide)
q
object model located in the Help files.
n
discuss (objects, classes, properties, methods,
collections, etc.).
two different object models
1.
Data Access Objects (DAO)
2.
ActiveX Data Objects (ADO)
DAO vs. ADO
n
DAO
(Data Access Object) - The
interface has been used to programmatically manipulate data, create
tables and queries, and manage security in Access databases
since Access 2.0 was released.
q
DAO
is a reliable interface for working with native Access data
n
DAO is on its way out,
there will be no further development of DAO.
q
using DAO in this tutorial
because bound forms use DAO recordsets
q
DBEngine object
- Represents the DAO interface
into the JET engine and ODBC Direct technology
q
Many company databases created in
programs other than Access, Access can be used as the front-end or
client, the data in databases is attached as back-end tables
n
Attached files accessed
programmatically using DAO appear just like Access tables
q
Problem using DAO
to manipulate data is it is not very efficient.
§
DAO communicates with tables using
Open Database Connectivity (ODBC)
DAO vs. ADO
n
Open Database Connectivity
(ODBC) – a standard method
of sharing data between databases and programs
q
ODBC drivers
– use the standard SQL to gain access to external data
q
ODBC
can be used to connect to relational databases, it cannot be used to connect
to data that is not relational (Microsoft Exchange mail system)
n
To address this issue, and ODBC
efficiency problems, Microsoft developed the OLE DB technology
that provides access to both relational and nonrelational data.
q
OLE DB interface
provides object linking and embedding technology to a database
source.
n
This technology has a single
programmatic interface called ADO (Active Data Objects) used by client
applications such as VBA, regardless of the type of data provided
n
ADO
(ActiveX Data Objects) - A
single programmatic interface that client applications use,
regardless of the type of data provider.
q
ADO is much more versatile
and allows the programmer to connect with any type of data provider
even a non-relational database.
q
while some of these terms appear
new, actually been working with recordsets all along.
n
result of a query
is a datasheet that displays a recordset, in particular a DAO recordset.
n
DAO - top of the hierarchy is the
DBEngine, the DBEngine is what drives the database.
OLE DB Technology
n
OLE DB technology
provides a means to interface to data source, presenting this to ADO
programmatic interface in a uniform manner
q
Can manipulate underlying
data without understanding how the data accessed, updated,
or stored
n
Separate OLE DB interfaces
for SQL Server, Oracle, Access, and other data sources
because objects, syntax, and rules are different for different
data sources.
n
OLE DB interface
contains all correct rules for working with given data source
q
Invoke appropriate OLE DB
provider, allows ADO to
directly call into the data source
§
Once connection established
to data source with OLE DB, can use same set of ADO properties and
methods to work with any underlying data source exposed by OLE DB
The Workspace Object
n
Workspaces collection
– contains all active Workspace
objects of the database engine.
n
Workspace object
- Represents a single session
or instance of a user interacting with the database engine.
q
analogy
to associate with the workspace object.
n
Consider that someone has made
copies of a file folder and its contents. These folders have been delivered to
several people in the company. As each recipient opens his/her copy of the
folder an instance of the folder is viewed.
q
This can be compared to a Workspace
Object; the particular user instance of the folder.
The Database Object
n
Database object
- Contains five collections:
Containers, QueryDefs, Recordsets, Relations, and
TableDefs
q
The database object is
simply the database that is currently open
q
Containers
(container object for each collection of documents)
n
(documents contain information
about objects in database (owners and permissions))
q
QueryDefs
(definitions of all queries),
q
Recordsets
(sets of records)
q
Relations
(definition of all established relations)
q
TablesDefs
(definitions of all tables in the database)
n
Includes system tables,
excludes linked tables
n
Note: there are more tables and
queries then are displayed in the database window.
The Recordsets Collection
n
Recordsets collection
- Contains one Recordset object
for every recordset that is currently open in the database.
q
The Recordset object
represents the records in an underlying table or query,
and can be considered simply a set of records
q
Exercise:
enter the statements shown below in the immediate window using
Movie9.mdb
to list the number of records
?currentdb.OpenRecordset
("tblTimeCards").RecordCount
n
opens the recordset, tblTimeCards,
and counts the records in the recordset.
?currentdb.OpenRecordset
("qryEmployeesList").RecordCount
n
This statement should produce 15
as the record count.
Types of Recordset objects
n
Table:
- (default) can be local or attached
q
Can be used to add,
change, or delete records
n
Dynaset:
- result of query
q
Can be used to add,
change, or delete records from underlying table or tables.
q
Can contain fields from one or more
tables that are either local or attached
q
Reflects changes
other people making to underlying tables
n
Snapshot:
- cannot be updated, does not reflect changes other users make to
underlying tables
q
Static
representation of data from one or more local or attached table
q
Advantage:
– faster to create than dynasets
n
Forward-only:
- identical to snapshot, except can only scroll forward through records
q
Read-only
does not reflect user changes
q
Improve performance
when only single pass through a recordset needed
n
Dynamic:
- used for accessing data in remote OBDC databases
q
Query result
from one or more tables
q
Can add, change, or
delete records
q
Reflects changes
other people making to underlying tables
Changing the Object Library Reference
n
Object Library
- A collection of pre-built
objects and functions that can be used in VBA code.
q
By default, the ADO
library is selected in Access 2002.
n
If students in a lab, the DAO
object library may already be selected.
q
However, make sure students
understand that this needs to be done on their own computers as well if they
want to repeat the steps in this tutorial on their own computers.
q
By default, the ADO library is
selected. If want to use the DAO object, however, will have to change to the
DAO library.
The RecordsetClone Property
n
RecordsetClone
property -
A property can be used to get a copy of
the form’s underlying recordset.
q
By creating a copy of table or
query on which form is based, can navigate or operate on form’s records
independently of the form.
n
After record in copy has been
navigated or modified, can set actual recordset of form equal to the copy
q
Advantage: can use DAO methods
on the clone that cannot be used with forms to navigate through records
n
Requires a pair of statements.
q
The Dim sets up a
variable as a DAO recordset object.
q
Example: (Dim rst As
DAO.Recordset)
§
this is a data type and that the
variable, rst, can hold only DAO recordset objects.
q
The Set statement
then establishes variable to contain the clone of form recordset.
q
Example: (Set rst =
Forms!frmTimeCards.RecordsetClone)
q
From this point on, the programmer
can use the object variable, rst.
n
Any program statement that
references this variable will represent the recordset object clone.
FindFirst, FindLst, FindNext, and
FindPrevious methods
n
The FindFirst, FindLst,
FindNext, and FindPrevious methods are used to locate particular
records in a dynaset or snapshot recordset object.
q
Methods only available in DAO
n
Syntax:
recordset.{FindFirst | FindLast
| FindNext | FindPrevious} criteria
n
Recordset
– object variable representing existing dynaset or snapshot
n
Criteria
– string used to locate record
q
Like WHERE clause in SQL
statement without the word WHERE
q
NoMatch property
set to True when no matching record found
q
If recordset contains more than
one record satisfying criteria,
FindFirst method locates 1st record, FindNext method locates next
record etc.
q
FindFirst
– begins searching at beginning of recordset searches to end
q
FindLast
– begins searching at end of recordset searches to beginning
FindFirst, FindLst, FindNext, and
FindPrevious methods
n
Example:
rst.FindFirst "TimeCardID = " &
"'" & lstFind & "'"
n
rst
points to the recordset clone and the FindFirst method is a method
that will be the first occurrence of a record according to some criteria.
q
user
will have already selected an item in the list box, lstFind.
n
The list box is set up to
allow the user to select a name but, in the background, the
list box is returning the ID that matches with that name. The TimeCardID
of the person selected, then, will be the value of lstFind. This statement
then will find the first record in the recordset that contains this TimeCardID.
Once the record is found, the next statement establishes a bookmark (a
placeholder) for that record.
q
When new Recordset object
opened, its first record is current record,
when one of the Find methods used to make another record current, must
synchronize current record in Recordset object with form’s current record.
n
Accomplished by assigning value of
recordset’s bookmark property to form’s bookmark property
Bookmark Property
n
When bound form opened in
Form view, each record assigned unique bookmark.
n
Bookmark property
– contains string expression created by Access
q
Can get or set form’s
Bookmark property separately from DAO Bookmark property of underlying
table or query
q
Bookmarks not saved with
the records they represent, only valid when file is open
n
To set Bookmark property of
form to Bookmark property of Recordset object
q
Forms!frmTimeCards.Bookmark =
rst.Bookmark
Security Overview
n
User-level Security
- User-level security prevents
users from changing tables, queries, forms,
reports, and macros, and also protects sensitive data in the
database.
q
User-level security,
allows for the most flexible method of securing a database.
q
With this type of security, the
administrator can set up users to access portions of the database but
disallow access to other portions of the database.
n
Most commonly, the administrator
establishes groups of users, sets permissions for the groups, and then
chooses who can and cannot be in the group.
q
users can be in more than one
group.
n
This method of assigning
permissions makes it quite simple for the administrator to establish
permissions.
n
With this method, the administrator
does not have to set permission for each individual user.
Workgroups Information File
n
Access workgroup
- A group of users in a multi-user
environment who share data. If user-level security is defined, the
members of a workgroup are recorded in an Access workgroup information
file that is read at startup.
q
Workgroup Information File
- This file contains the users’
names, passwords, and the group to which they belong.
n
When user-level security is
established for groups of people, a workgroup information file is
established.
q
When the database is
opened, this file is read and is in effect.
q
A workgroup information file
can be used for many databases, but each database can only have one workgroup
information file
§
Before using a fully secured
database, a user must join the appropriate workgroup information
file.
Workgroups Information File (Steps)
q
It is extremely important to
follow EVERY SINGLE step in this section. Failure to do all the steps, and in
the right order, can lock out the database.
n
Make a backup prior to
setting up workgroups.
q
Steps to create new workgroup
information file
1.
Create
the administrative user (usually the developer)
2.
Log
into the database as the administrator
3.
Run
the User-level Security Wizard
n
If the administrator finds that
irreparable-errors have been made, the backup can be used to
recover.
Access Database Security
n
Security is established
by default for each Access database created.
q
Only user in database is Admin
n
Admin
by default has full rights to all objects in database and has no
password
n
Admin is the owner of each database
q
Owners
and administrators have full rights to database objects
2 reasons to create new workgroup information
file
1.
Since Admin default owner of all
databases, users will need to know administrator password to use any
database
2.
Since default workgroup
information file contains the name and organization provided when
software installed, user could use this information to identify themselves as an
administrator account.
n
To guard against this, create new
file protected by a workgroup ID
Workgroup ID
n
Workgroup ID
– case-sensitive string that contains letters and/or numbers and is 4 to
20 characters long.
q
Think of workgroup ID as a
password for the workgroup information file
n
Only someone knowing the workgroup
ID can re-create the workgroup information file
n
Can create workgroup information
file by running the Workgroup Administrator program installed with
Access.
n
User-Level Security Wizard
– contains dialog box asking whether to use existing workgroup
information file or create a new one.
n
After creating or joining another
workgroup file, the default and other files still remain on system.
q
If problems arise with a
workgroup file, can always rejoin the default file
§
Should never delete workgroup
file installed with Access (System.mdw)
§
Never edit System.mdw file,
always create a new workgroup file
Grading the Workgroup Information File (MDW)
n
When doing an assignment that
requires setting up a MDW, need to submit the database as well as the MDW
file.
q
When ready to grade work, will
need to join the student’s version of the MDW in order to determine if the work
was completed correctly.
n
If do not join the student’s MDW,
will be viewing whatever MDW to which you are currently joined.
n
When students turn in their work,
give them instructions as to how you want the files turned in.
q
A good approach is to have them zip
the files into a single zipped file and submit the zipped file to you.
Then you can unzip the file to a folder of your choosing to grade their work.
Security Accounts
n
three predefined
user and group accounts.
n
Usually the administrator
of the database is set up in the Admins account group and has full
rights to the entire database.
q
no other users should be assigned
to this group unless the developer really wants that person(s) to have full
rights to the database.
n
Example: an administrative staff
member.
q
usually better to give the
administrative staff a Users group of their own.
§
This way the administrator can give
this group ample rights and yet have some control over parts of the database to
which only the administrator should have.
Creating a New User
n
make a backup of the
database before making changes to the user groups.
n
create a new user, MovieAdmin.
This user will actually become the new administrator of the database.
q
The default administrator is Admin
but rarely is this user left as the administrator of the database.
n
A workgroup information file
can be used for many databases, but each database can only have
one workgroup information file.
q
If a corporation has more than one
database, might want to use the same workgroup information file for some,
perhaps not all, of those databases.
n
This makes it unnecessary to
recreate the file for each database in the organization.
n
importance of setting up a PID
for the administrator that is not something anyone would be able to guess.
q
The PID adds additional security
for the administrative activities.
Setting Passwords
n
Each user can have a password. In
this case, if they log on as Admin or MovieAdmin, they will use the moviecam
password (after they have set the passwords for both these users).
q
importance
of the order of these operations.
1.
should always set up the
administrator first
2.
then login as the
administrator
3.
then run the User-level
Security Wizard.
User-Level Security Wizard (tasks)
n
Option to create new workgroup
information file
n
Secures objects selected
in database open when wizard run
n
Adds new groups
with predefined level of security
n
Adds new users and passwords
n
Assigns
ownership of database and objects to current user
n
Prints User-Level Security
Wizard report containing passwords
and user names created
n
Makes back-up
copy of database with same name using extension .bak
Testing User Level Security
n
When going through this testing process, imagine different
scenarios where want certain users to have permissions that
don’t want other users to have.
q
Example: payroll file only want members of the
Accounting department to have access.
n
Could set up the Accounting
department as the only group that has access to this table and then could deny
any access to this table to users who are not members of this group.
Joining the Default Workgroup
n
Rejoining the default workgroup
has the effect of removing all permissions for users other than the
administrator.
q
To change permissions
n
need to establish a
workgroup file and set up the permissions
n
can rejoin a workgroup file
that has already been created.
Assigning Permissions
2 Types of permissions
in user-level security
1.
Explicit
permissions -
granted directly to a user account (no
other account is affected)
q
Explicit
permissions are assigned to individual users
2.
Implicit
permissions -
granted to a group account
q
indirect
permissions are assigned to groups.
q
The permissions of group apply to
the members of that group.
q
You can increase but not
decrease security for individual members of a group.
n
User’s security level
based on both implicit and explicit permissions of that
user, level is less restrictive of the two
n
Note: Permissions can only
be changed by administrator(s) of the database.
Owners and Permissions
n
Owner
- The user who creates
a table, query, form, report, or macro object
q
The same group previously
identified as having ability to change permissions can also change ownership
of database or its objects
n
Simplest way to change
ownership of all objects in database
is to create new database and import all of the objects or run
User-Level Security Wizard
q
User logged on
will then have ownership
q
Group accounts cannot own databases, but
can own database objects
n
All members of group own the
object
n
Example: Read-Only Users group
has read-only rights to all data tables in database except tblEmployees
(salaries)
Setting Passwords
n
Often the practice for new
users is to leave the password off and let the user set the password.
q
problem
with this approach is that the user might forget to set the password.
n
leaves access to this user’s
permissions available to anyone who knows that users user name.
n
It is best to set up a
password in the beginning and then let the user change the
password after the first login.
q
Often the person’s SS# is
used as the beginning password.
q
If security is a major
issue, force the user to change their password.
n
This can be done in several ways.
One way is to provide an input box that requests a new password
from the user and disallows any other activity until the password is changed.
n
Sometimes users will forget
their password.
q
Only the administrator can
reset the password.
n
password
can be cleared or reset to its original value.
Removing User-Level Security
(2 Step Process)
1.
Log on
to database as a workgroup administrator, and give Users group
permissions on all tables, queries, forms, reports,
and macros
2.
Return ownership of database
and objects to the default Admin user
q
To do this, exit, log on as
Admin, create blank database, import all objects from
original database into blank database
q
Should clear any password
set for Admin user if users will not be using the default workgroup
information file
n
Recall that in default workgroup
information file, Admin has no password
n
Note: Rejoin the default
workgroup if do not intend to do any of the exercises at the end of this
tutorial.
q
Until rejoin the default
workgroup, the permissions in the MDW still apply even if another
database opened.
Return
to IFS 110 Notes!