Custom Project Securing a Microsoft Access Database

You must complete this custom project in order to work with the hands-ons in this chapter.

Part 1: Creating a New Database

1. Create a new folder on your computer and name it BookProject.

2. Start Microsoft Office Access and create a new blank database called SpecialDb.mdb. Save this database in the BookProject folder you created in step 1. Keep this database open and proceed to Part 2 below.

Part 2: Establishing User-Level Security

We will use the built-in User-Level Security Wizard to secure the blank Access database we created in Part 1.

1. Choose Tools | Security | User-Level Security Wizard.

2. Click Yes in response to the message that the database should be opened in the shared mode to run the Security Wizard.

3. Microsoft Access closes the database and reopens it in the shared mode. If the Security Warning message appears, click Open.

4. Microsoft Access automatically activates the Security Wizard (Figure 17-1). Click Next to continue.

Creating and Manipulating Databases with ADO

Creating and Manipulating Databases with ADO

Figure 17-1

5. Another Security Wizard window appears (Figure 17-2). Do not make any changes in this screen. Click Next to continue.

Figure 17-2

6. The Security Wizard window now shows an empty tabbed screen that normally displays database objects (Figure 17-3). Because our database does not contain any tables, queries, reports, etc., there's nothing you can do in this screen. Press Next to continue.

Part II

Figure 17-3

The Security Wizard window now goes on to display a list of optional security accounts that you could include in your new workgroup information file (Figure 17-4). Because we will define our accounts in programming code later in this chapter, do not make any selections in this screen. Press Next to continue.

Figure 17-4

8. Now the Security Wizard asks you whether you want to grant permissions to Users group (Figure 17-5). Do not make any changes in this screen. The Users group will have no permissions. We will work with permissions in our VBA procedures later. Press Next to continue.

Creating and Manipulating Databases with ADO

Creating and Manipulating Databases with ADO

Figure 17-5

Now the Security Wizard shows a screen where we finally can do a little bit of work. We need to define a new user in our database. This user will function as a new Admin. We'll call our new user a Developer and allow him to log into the database using chapter17 as a password. Fill in the User name and the Password boxes as shown in Figure 17-6 and click the Add This User to the List button. The Developer now appears in the users list. Do not leave this screen yet.

Figure 17-6

Figure 17-6

10. Now we will remove the user account with which we have logged into Access to start this custom project. In the list of users, select the user name you logged in with (I selected JKorol) and click the Delete User from the List button. Now the Developer is the only user in our database. Click Next to continue.

Part II

11. The Security Wizard shows the screen where you can assign users to groups in the workgroup information file (Figure 17-7). Notice that the Developer user you created in step 9 above is a member of the Admins group. Press Next to continue.

Figure 17-7

Figure 17-7

12. The Security Wizard has now collected all the required information. As a final step it suggests the name for the backup copy of the unsecured database (Figure 17-8). The unsecured database is, in this case, the blank database we started with. When we are done with this final step, our database will still be blank; however, it will be secured. Do not make any changes in this screen. Click Finish.

Security Wizard

That's al the rf urination the visard needs to create your security-enhanced database.

What name wodd you like for the backup copy of your unsecured database?

Important

After eompfethg its work, the wizard wJI display a report of the settings used to create the users and groups m your workgroup Informaticn file. Keep this Infarrnatlon, because you'i need it If you ever have to re-create your workgroup fie.

[""] Display Hdp on customizing security.

Help ] [ Cancel ] [ <Back | CM > [ Eirêh ]

13. Access performs its final tasks of securing your database and displays the Security Wizard report. If you are connected to the printer, it's a good idea to take a minute now to print this report. You can also magnify the report to

Creating and Manipulating Databases with ADO

read it on screen. When you are done, close the Security Wizard report window. Upon closing the window, the Security Wizard displays a warning message that asks whether you would like to save the report as a Snapshot (.snp) file that you can view later. For this exercise, click No in response to this question. You should see the confirmation message that the Security Wizard has encoded your database and to reopen the database you must use the new workgroup file you have created by closing Access and reopening it. We'll do as suggested in the next step. Click OK to this message.

14. Close the Microsoft Access application window. Part 3: Opening a Secured Database

After completing the previous two parts of this custom project, the BookProject folder on your computer contains three files: ■ A database file named SpecialDb.mdb

A workgroup information file named Security.mdb that stores user and group account information for the SpecialDb database A backup copy of the SpecialDb database named SpecialDb.bak

Also, there is a shortcut on your desktop (created by the Security Wizard) that allows you to quickly start the SpecialDb database using the new workgroup information file (Security.mdb). If you right-click that desktop shortcut and choose Properties, you will see in the Target box the following path:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

"C:\BookProject\SpecialDb.mdb" /WRKGRP "C:\BookProject\Security.mdw"

Because this path is very long, I have split it into two lines. Notice that the first part of this path is the location of the Microsoft Access executable program on your disk surrounded by quotation marks. The path to the Access .exe is then followed by a space and the full path of the database file (also in quotation marks). Because this database file is secured, we must also include a space and a command-line switch, /WKGRP, followed by a space and the name of the accompanying workgroup information file (also in quotation marks).

The /WKGRP command-line switch tells Access that you want to start a database with a specific workgroup. If you know what user account you want to log on with, you can use the /User and /Pwd command-line switches to avoid being prompted by Access for the user name and password:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

"C:\BookProject\SpecialDb.mdb" /WRKGRP "C:\BookProject\Security.mdw" /User "Developer" /Pwd "chapter17"

The information about the user name and password follows the name of the workgroup information file and a single space.

Now that you know how the path to a secured database is built, you can create similar shortcuts to other secured databases if they use different workgroup information files.

1. On your desktop, double-click the shortcut to SpecialDb.mdb to open this database. Because this database is protected, a logon box appears. Enter

Part II

Figure 17-9: In Custom Project 17-1, we removed the default Admin user from the Admins group while running the built-in User-Level Security Wizard.

Developer in the Name box and chapter17 in the Password box and click OK. If the Security Warning message appears, click Open.

Now that your secured database file is open, we will take a look at the changes the Security Wizard has made in the Users and Groups accounts. Choose Tools | Security | User and Group Accounts. Notice that the Admin user is a member of the Users group (Figure 17-9). The Security Wizard removed the Admin account from the Admins group. If you open the drop-down list in the User area of this screen and select Developer, you will see that Developer is a member of two groups: Admins and Users. Click Cancel to exit the User and Group Accounts window.

Having checked the Users and Groups accounts, you can also examine the changes made by the Security Wizard in the group permissions. Choose Tools | Security | User and Group Permissions. The Developer and Admin users don't have permissions on any new objects (Figure 17-10). To view group permissions, click the Groups option button. The Admins group has all the necessary permissions to administer the database while the Users group has no permissions at all. We will learn how to grant and revoke permissions to database objects in the example procedures in this chapter. Now

Figure 17-10: Use the User and Group Permissions window to check current permissions for the Admin and Developer users after running the User-Level Security Wizard in Custom Project 17-1.

click Cancel to exit the User and Group Permissions window.

Now let's import some objects to our still empty database. Later in this chapter when we learn to handle permissions to database objects we will need a couple of objects available in this database for our tests. In the Database window, choose File | Get External Data | Import. In the Import

Creating and Manipulating Databases with ADO

dialog box, switch to the folder that contains the Northwind sample database, highlight Northwind.mdb, and click Import.

5. In the Import Objects window, click Select All to select all the tables. Click the Queries tab, and then Select All to select all the queries. Finally, click OK to begin importing.

6. The objects you selected in step 5 have now been added to your database. Close the SpecialDb database and close the Access application. This step concludes the special project of creating a secured Access database. Now, let's proceed to the programming part.

0 0

Post a comment