<< . .

. 45
( : 51)

. . >>

You can™t synchronize replicated databases that have database passwords. If you plan to use
Jet™s replication features and you need database security, you must use user-level security.

After you save the database password, any user who attempts to open the database must
enter the password. Although this method controls who can access the database, it doesn™t
control what users are allowed to do with the objects and data after they have opened the
database. To control objects, you need to fully implement Jet™s user-level security, which is
discussed in the following section.

After a database has been protected with a database-level password, you must supply the
password when linking to any of its tables. This password is stored in the definition of the link to
the table.

To remove a database password, follow these steps:
1. In Access, open the secure database exclusively. You must open the database
exclusively to be able to remove the database password.
2. Select Tools_Security_Unset Database Password. This menu option replaced the
option labeled Set Database Password before the database password was set.
3. In the Password field, type the password of the database (see Figure 22-4).
4. Click OK to unset the password.

Figure 22-4: You can remove a database password by entering the password in the
Unset Database Password dialog box.
Chapter 22 ¦ Adding Security to Access Applications 521

If you remove a database password from an Access database, users are no longer required to
enter a password to access the database unless you have enabled user-level security.

Any user who knows the database password has the ability to change or remove the database
password. You can prevent this situation by removing the Administer permissions from the
database for all users except the database administrator. This is discussed in more detail later
in this chapter.

Microsoft Access stores the database password in an unencrypted form. If you have sensitive
Caution data, this can compromise the security of the password-protected database. In situations where
data security is critical, you should consider defining user-level security to control access to
sensitive data. User-level security is covered in depth later in this chapter.

Using Visual Basic to Set A Password
You also can set a database password using Visual Basic code. The following code changes the
database password of the currently opened database:
Public Sub ChangeDatabasePassword()
On Error GoTo ChangeDatabasePasswordErr
Dim szOldPassword As String, szNewPassword As String
Dim db As Database
Set db = CurrentDb
szOldPassword = “”
szNewPassword = “shazam”
db.NewPassword szOldPassword, szNewPassword
Exit Sub
MsgBox Err & “: “ & Err.Description
Exit Sub
End Sub
If no database password is set, you pass a zero-length string (“”) as the old password parameter. If
a database password is assigned and you want to remove the password, pass the database
password as the old password parameter and pass a zero-length string (“”) as the new password.

Using the /runtime Option
If you™re not concerned with protecting your application but simply want to prevent users
from mistakenly breaking your application by modifying or deleting objects, you can force
your application to be run in Access™s runtime mode. When a database is opened in Access™
Part III ¦ Beyond Mastery: Initiative Within Office

runtime mode, all the interface elements that allow changes to objects are hidden from the
user. In fact, while in runtime mode, it is impossible for a user to access the Database
window. When using the runtime option, you must ensure that your application has a startup
form that gives users access to whatever objects that you want them to be able to access.
Normally this is the main menu or main switchboard of your application.

You must purchase and install the Microsoft Visual Studio Tools for the Microsoft Office
Note System to use the /runtime switch. This suite of tools includes a runtime version of Access that
allows you to distribute a royalty-free licensed copy of your Access 2003 applications to users,
whether they have Access on their machine or not.

To assign a form as a startup form, open the database that you want to use, choose Tools_Startup
and select the form that you want to be the startup form from the Display Form/Page drop-down
list. Startup forms are covered more in-depth in the following section.

To create a shortcut to start your application in Access™s runtime mode, follow these steps,
using the Chap34Start.mdb database:
1. Go to the subdirectory that contains Microsoft Access (MSACCESS.exe).

On most computers, the MSACCESS.EXE file is located in the “C:\Program Files\Microsoft
Office\OFFICE11\” folder.

2. Highlight the Microsoft Access program and select File_Create Shortcut, or right-
click on the program file and select Create Shortcut from the menu-on-demand.
Windows creates a shortcut in the same directory, naming it “Shortcut to
3. Right-click the newly created shortcut, select Properties from the menu, and then
click the Shortcut tab when the Properties dialog box opens.
4. In the Target: field, append the following parameters to the path of
MSACCESS.EXE (program): A space, the full path name and filename of the
database to open in runtime mode, another space, and then /runtime.
For example, the following command line starts Access and opens the
Chap34Start.mdb database in runtime mode on our computers:
“C:\Program Files\Microsoft Office\OFFICE11\MSAccess.exe”
“C:\Access 2003 Access Auto Auctions\Chap34Start.mdb” /runtime

The path to MSAcess.exe should have already been in the Target: field. Note that Windows
Note automatically places the path and filename for MSAccess.exe in quotation marks. The /
runtime switch should not be enclosed in quotes. If you enclose the /runtime switch in quotes,
an error occurs when you attempt to execute the shortcut.
Chapter 22 ¦ Adding Security to Access Applications 523

5. After you have specified the path and filename, placing the /runtime switch at the
end of the Target: field, you can optionally remove the path name in the Start in:
Figure 22-5 shows how the Shortcut properties should look at this point.

Figure 22-5: Modifying the Target: and Start in: fields of the shortcut by using the /
runtime switch of Access 2003.

6. After the fields have been updated, click the Apply button to process the changes
and save the shortcut.
7. Finally, you can rename the shortcut icon to any name that you want and move it
from the current directory to another directory, or even to the desktop. After you
have created the shortcut, you can distribute or re-create the same shortcut for each
user installation.

Tip If your database has a password associated with it, the user will still be prompted to enter the
password prior to opening the database.

Using a Database™s Startup Options
A slightly less secure alternative to using the /runtime option is to set a database™s startup
options. This alternative is not a complete solution for situations where tight security is
paramount. Figure 22-6 shows the Startup options dialog box. To access the Startup options
dialog box, select Tools_Startup.
Part III ¦ Beyond Mastery: Initiative Within Office

Figure 22-6: Using the Startup options dialog box provides another option for securing
an application.

By making the appropriate specifications in the Startup options dialog box, you can do the
. Assign a title to the application.
. Assign an Application Icon to the application.
. Assign a form or data access page to immediately run when the database is open.
. Prevent the Database window (container) from being displayed.
. Prevent the status bar from being displayed.
. Designate a menu bar to be used on startup of your application.
. Designate a shortcut menu to be used on startup of your application.
. Prevent Access™s built-in menus (full menus) from being displayed.
. Prevent Access™s built-in shortcut menus from being displayed.
. Prevent Access™s built-in toolbars from being displayed.
. Prevent users from modifying toolbars (toolbar/menu changes).
. Prevent users from using Access™s special keys to display the Database window,
display the immediate window, display the VB window, or pause execution.
To designate the frmSwitchboard form as the default form to open whenever the
Chap34Start.mdb database opens, follow these steps:
1. Open the Chap34Start.mdb database and select Tools_Startup to open the Startup
dialog box.
2. Click in the Display Form/Page: field and select the frmSwitchboard form from the
pull-down list (refer to Figure 22-6).
3. Click OK.
Chapter 22 ¦ Adding Security to Access Applications 525

After you have assigned a form to open automatically, you can also specify that the
Database window or status bar not be displayed to give even greater security to your
application. By selecting these two items, when the user clicks the Close button on the
startup form, the database window (container) will not display. By using a database
password and the Startup options, you can assign minimum security to the database and
your application.

The user can bypass the Startup options by simply holding down the Shift key while opening
the database. However, if you assign a database password, users will still be required to
enter the password in order to use the database.

Using the Jet User-Level Security Model
Most often when security is required, setting a database password and run-time options is
simply not enough.
When you need more security, you can use Access user profiles that are implemented by the
user-level/object permissions security of Jet 4.0. The Jet Database Engine offers additional
levels of customization and security for your application. When using Jet level security, you
need to complete the following series of functions:
1. Select or create a workgroup database.
2. Define the workgroup database™s security groups.
3. Create the users of the workgroup database.
4. Define permissions for each user and security group.
5. Enable security by setting an Admin user password.

What Is Jet and a User Profile?
When you create a Microsoft Access database (.mdb or .mde), Access uses an internal program to
create and work with the database and its objects. Microsoft calls this internal program the Jet
Database Engine. Its purpose is to retrieve and store data in user and system databases. Some
people refer to the Jet engine as a data manager that the database system is built upon. Jet only
works with Access databases ” it doesn™t work with other ODBC databases, such as SQL Server,
Oracle, and others. The current version of Jet is 4.0 (also in Access 2000 and 2002). When you
installed Access, the installation program created several registry settings for the Jet engine. You
can use the Registry Editor to examine and even change these settings for Access. However, we
highly recommend that you do not change the setting in the Microsoft Windows registry.
Using Jet, you can build an Access user profile that is comprised of a special set of Window™s
registry keys, which will override the standard Access and Jet database engine settings.
Part III ¦ Beyond Mastery: Initiative Within Office

Enabling security
Jet database security is always on. Whenever a new workgroup database is created, an
Admin user is automatically created within the workgroup. This Admin user has no
password assigned to it. When the Admin password is blank, Access assumes that any user
attempting to open the database is the Admin user, and that this user is automatically logged
in to the database as the Admin user. To force Access (Jet) to ask for a valid user name and
password to log in to the database (see Figure 22-7), you simply need to create a password
for the Admin user. (Creating passwords is discussed later in this section.) To disable
security, simply clear the Admin user™s password. The security permissions that you have
designed are still in effect, but Access doesn™t ask for a user name and password ” it logs
on all users as the Admin user with whatever permissions were assigned to the Admin user.
Be careful about clearing the Admin user™s password when you have modified the
permissions of your users.

Figure 22-7: When security is enabled, Jet forces all users to enter a valid user name
and password to use the secured database.

Any changes that you make to security won™t take effect until you restart Access. If you have
Tip cleared the Admin password only to find that some or all of the Admin user™s permissions have
been revoked, open the database and create a password for the Admin user. Then exit Access
and restart Access (not the database). When you restart Access, you are prompted to enter a
user name and password.

Working with workgroups
A workgroup is a collection of users, user groups, and object permissions. You can use a
single workgroup file for all of your databases, or you can use different workgroups for
different databases. The method that you use depends on the level of security that you need.
If you give Administrative rights to users of some databases but not to users of other
databases, you need to distribute separate workgroup files with each database. Access
always uses a workgroup file when you open it. By default, this workgroup file is the
SYSTEM.MDW workgroup file. This file comes with Access 2003.
Chapter 22 ¦ Adding Security to Access Applications 527

Creating a new workgroup
You can create new workgroups or join existing workgroups by using the Workgroup
Administrator program that comes with Access 2003 (see Figure 22-8). To begin creating a
new workgroup, select Tools_Security from the Access menu.

Figure 22-8: Using the Workgroup Administrator to create new workgroups and to join
existing workgroups.

You should completely close down Access after creating new workgroups or joining existing
workgroups. When you use the Workgroup Administrator to join a workgroup, that workgroup is
not actually used until the next time you start Access.

To create a new workgroup file, follow these steps:
1. Start Access (with or without a database), select Tools_Security, and then select
Workgroup Administrator.
2. Select the Create button in the Workgroup Administrator dialog box to display the
Workgroup Owner Information dialog box.
The workgroup that you create is identified by three components: Name, Organization, and
Workgroup ID (see Figure 22-9).

In order to re-create the workgroup file in the event that it becomes corrupt or deleted, you
Caution need all three pieces of information. For this reason, to ensure that no other user can
create your workgroup and access your secured database, you should supply a unique,
random string for the Workgroup ID. Someone may possibly guess the name and organi-
zation used in your workgroup file if he or she knows who you are, but to guess all three
items ” especially if you create a random, unique ID ” is almost impossible.
Part III ¦ Beyond Mastery: Initiative Within Office

Figure 22-9: Workgroups are identified by these three key pieces of information. A
workgroup can™t be re-created without all three of these items.

3. When you are satisfied with your entries, select OK to display the Workgroup
Information File dialog box.
4. Enter a name for the new workgroup file, and select OK to save it (see Figure 22-
10). If you enter a filename that already exists, like SYSTEM.MDW, you will
receive a confirmation box requesting that you confirm replacing the existing file.

Figure 22-10: Assigning a filename for the new workgroup.

5. The Workgroup Administrator displays a confirmation dialog box (see Figure 22-11)
containing the information that you entered for the new workgroup and explains the
importance of writing down and storing the information. If you are satisfied with
your entries, select OK to save your workgroup. If you want to change anything,
click the Change button to return to Step 3.
Chapter 22 ¦ Adding Security to Access Applications 529

Figure 22-11: Confirming the information for the new workgroup.

When you select the OK button in the Confirm Workgroup Information dialog box,
a message displays to inform you that you have created the workgroup information
file correctly.

In order to ensure that you can recover from the loss of your workgroup file, you should
Tip immediately make a copy of the workgroup file. In addition, you should write down the three
pieces of information that you used to create the workgroup file, exactly as they were en-
tered, in the event that you have to re-create the workgroup file from scratch. Store both the
backup file copy and the written information in a secure place.

Joining an existing workgroup
When you create a new workgroup, Access automatically joins the new workgroup. If you
don™t want to use the new workgroup right away, or if at any time you need to use a
workgroup other than the current workgroup, you can use the Workgroup Administrator to
join another workgroup.
To join an existing workgroup, follow these steps:
1. Activate the Workgroup Administrator program from the Tools_Security menu.
2. The Workgroup Administrator dialog box displays the current workgroup (refer
back to Figure 22-10). Click the Join button to select a workgroup file. If you aren™t
sure of the filename, click the Browse button to display a File dialog box in which to
locate the workgroup file.
3. A prompt displays so that you can confirm or cancel joining the workgroup. Select
OK and then select Exit to close the Workgroup Administrator.
Part III ¦ Beyond Mastery: Initiative Within Office

Working with users
Every time a user opens an Access (Jet) database, Jet must identify the user opening the
database. In Access, security is always enabled ” regardless of whether or not you have
explicitly created a workgroup for your database. If you have not defined a workgroup, Jet
assumes that any user who opens the database is the Admin user. When a new workgroup is
created, Access automatically creates a default user named Admin. The Admin user
automatically receives full permissions to all objects in the database. Obviously, when you
secure a database, you don™t want everyone to be able to open the database with full
permissions on all objects, so you must create additional users for the workgroup.

Adding and deleting user accounts
To add, delete, and edit user information, you use the User and Group Accounts dialog box
(see Figure 22-12). To open the User and Group Accounts dialog box, select
Tools_Security_User and Group Accounts ¦ from the Access menu. The Users tab of the
User and Group Accounts dialog box consists of two sections: User and Group Membership.
You use the User section to create and maintain user names and passwords. You use the
Group Membership section to assign users to user groups. Assigning users to groups is
discussed in detail later in this chapter.

Figure 22-12: Creating and maintaining users in the User and Group Accounts dialog

To fully secure your database with users and groups, you should generally follow
these steps:
Chapter 22 ¦ Adding Security to Access Applications 531

1. Create a new user.
2. Add the new user to the Admins group.
3. Remove the Admin user from the Admins group.
4. Assign all object ownerships to the new user.
When you create a user, you supply the user name and a personal identifier. Jet then
combines these two items and processes them in a special algorithm, producing a unique
security ID (SID). It is this SID that Jet uses to recognize users. In order to re-create a user
in the workgroup, you need to know the user name and the personal ID (PID) that was used
to create the user. Consequently, you should always write down and store all names and
PIDs of users that you create in a safe place.
To create a new user in a workgroup, follow these steps:
1. Open the database Chap34Start.mdb.
2. Select Tools_Security_User and Group Accounts to display the User and Group
Accounts dialog box.
3. Select the New button in the User section to display the New User/Group dialog box
(see Figure 22-13).

Figure 22-13: Jet combines the User Name and Personal ID to create a unique SID for
the user.

<< . .

. 45
( : 51)

. . >>