Restricting Who Can Use Your Database

  • Article

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Restricting Who Can Use Your Database

Garry Robinson

Garry Robinson is back after a long silence. He explains why he's been away and gives you a preview of his upcoming book on Access security. In this article, you'll see how to add another level of security to your database by leveraging the Windows security system.

If you've been reading Smart Access for a while, you'll have noticed that my personal contributions in 2003 have been few and far between. I've been busy—I decided that it was time to step up to the plate and write the book that Gary Cornell and Karen Watterson from Apress had been nagging me about for years. After reviewing the Microsoft newsgroups, I realized that security was one area where Access developers were having trouble—and where the consequences were dramatic: People were losing applications or, even worse, data. After many months of hard slog with a team of five editors, I've finished a comprehensive book on Access security and protection. Smart Access readers will be getting some previews of that book's content (see the sidebar "A Rant" for a description of what it's like to write a computer book).

In this article I'm going to discuss a common scenario: Suppose your company has 10 people who all share the same network file server or, in a larger organization, share the same Windows Server domain. Within that group of users are two managers and an assistant who you would like to use a human resources database that maintains confidential information about the other people in the group. Naturally, you'll want to prevent any of the other users from having access to the database. If you're an Access database specialist, you'll probably turn to workgroup file security to handle this problem. That's a good choice, but to apply an additional level of security, you should also embrace the security found within the operating system on your server, a topic many Access developers aren't familiar with.

This is my message: No matter what internal Access protection and security measures you add to your database, you can improve your protection substantially by using an operating system's built-in security. In the terms used by enterprise-level security experts, using a protected folder in conjunction with other Access security and protection creates "layers of defense" for your database. This extra layer will allow you to ensure that only authorized users will even be able to open the folder where the database files exist and thus open the database file(s) themselves. Limiting access to specific Windows users is a fundamental technique used by SQL Server and other enterprise-level databases. Most Access books don't discuss operating system security—it's not, after all, a "purely Access" topic. This article will plug that hole for you.

Protected folder security

You begin by establishing a Windows user group to which you'll add the Windows user accounts that will be allowed to open the folder where you store the database. I'll call this group the "Access Editors" group. Then you use the Windows operating system security to ensure that anyone who's not a member of either the Administrators group or the Access Editors group is denied access to the database folder.

In technical terms, what you're adding with this protection is this: All the Windows users to whom you don't grant the specific permission to use the database folder (or its subfolders) will encounter an "Access is denied" warning (shown in Figure 1) when they try to open the database—or even try to list the files in the database folder.

To work through the examples, you'll need to have a copy of Windows XP Professional using the NTFS file system. (I'll refer to the computer that I'm implementing security on as the server. If you're running Windows 2000 Professional, the examples in this article will provide you with enough information to get started; some dialogs are changed and/or relocated.) My book includes a chapter that applies these techniques using Windows 2000 Professional. If you're using Windows XP Home, you can use operating system security as a client PC in order to access a protected folder. Unfortunately, you can't set up folder security as discussed in this article using XP Home. For links to more information on NTFS, see the "Further Reading" sidebar at the end of this article.

The main purpose of these examples is to help you become familiar enough with the underlying concepts of folder permissions. Once you've grasped those concepts, you'll be able to demonstrate the viability of this technique to a system (Windows server) administrator who will normally be the person to set up the security for you in the Windows domain. You'll also have the knowledge to test anything that's set up for you by a network administrator. For those of you who have small networks that don't use a Windows server computer (for instance, a small team of developers), you may well be able to use the concepts straight away.

Preparing databases in a protected folder

The first stage is to prepare a folder that will hold the database files that you want to protect. For this example, I'll use a folder called \data\ as the basis for our protected folder:

  1. Log on as Administrator on your Windows XP computer.
  2. On an NTFS-formatted drive, create a folder called \data\.
  3. Create a subfolder called \data\Protect\.
  4. Add a copy of the Northwind database (or a copy of your own database) to that subfolder.

The next part of the process is essential if you're going to allow Windows user accounts from other computers to use your folders. Set up a network share:

  1. Right-click the \data\ folder and choose Sharing and Security.
  2. Enter the details for the network share (which I've called Databases in this demonstration) and click OK.
  3. You've now set up a network share that other people connected to your Windows workgroup can use. You can see the share called Databases that I've created (for the Cow-FX computer) in Figure 2.

Setting up a user account

Next, you need to set up Windows user accounts for users that will be allowed to use your database but won't have the ability to alter folder permissions on your server. For this, you need a Limited account (or a Restricted account, as it's called in Windows 2000). Adding a user as a Limited user will stop that user from installing most software, changing user accounts, and changing important folder permission; this is what you want at this stage.

In Windows XP, there are two ways to set up a user account. The first method is:

  1. Open the Windows Control Panel by clicking the Start button and choosing Settings | Control Panel.
  2. Double-click User Accounts.
  3. In the Users and Passwords dialog, click Create a New Account to start the New Account wizard.
  4. Enter a name for the new account (throughout this article, I'll use Editor2000 as the account that's allowed to edit the database). Click Next.
  5. Click Create Account. The User Accounts dialog now reappears, and you'll find that the account that you just set up (Editor2000) is listed as a Limited account on your computer with no password.
  6. Click the Editor2000 account and choose Create a Password.
  7. Enter and confirm the password. Also enter a useful but not too exact password hint.

If you're using Windows XP, you may find that a more accessible approach is to use the Computer Management console to create the account. For that method, follow these steps:

  1. Choose Start | Settings | Administrative Tools | Computer Management.
  2. Select Local Users and Groups under System Tools in order to see the Users and Groups dialog.
  3. Select Users, and a list of the users on your computer will appear in the right pane.
  4. As shown in Figure 3, you can right-click on Users under Local Users and Groups and create an account from there. This interface doesn't allow password hints, though you can revert to the User and Passwords dialog if you consider this important. If you choose this method, you'll create a Limited account and will have more control over passwords.

Setting up a new Access Editors group

Now you need to add your new Editor2000 account to a special group of Windows users who will be allowed to open the database, and create and delete the LDB file (that is, use the folder just like any other folder). Because this group probably doesn't exist yet, you first need to create the Windows user group that will hold a list of your database users' accounts:

  1. Choose Start | Programs | Administrative Tools | Computer Management.
  2. Under System Tools, select Local Users and Groups.
  3. To set up a new group, right-click Groups and choose New Group.
  4. Enter the details for the new group in the New Group dialog (I'll use the group name Access Editors throughout this article).
  5. Click Create to add the group, and click Close to return to the Local Users and Groups dialog (shown in Figure 4).

Adding the users to the group

At this stage, the Local Users and Groups dialog in the Management console is visible. In the next stage, you add one or more users to the Access Editors group, as follows:

  1. Select Groups (as shown in Figure 4), and then select Access Editors in the list of groups.
  2. Choose the menu Action | Properties. You can also open the Properties dialog by right-clicking Access Editors and choosing Properties.
  3. Add all the users that are going to belong to the group by clicking Add on the Access Editors Properties dialog. Enter the name of the user in the Select Users dialog, as shown with the Editor2000 account in Figure 5. Now click the Check Names button to ensure that you've typed the name of a valid user. You can also use the Advanced button to retrieve a list of accounts.
  4. Click OK when you've completed adding all of the users.

You'll now return to the Local Users and Groups dialog, where you can explore the properties of the Editor2000 account, as shown in Figure 6. As you can see, this new account is now a member of both the Users group and the Access Editors group. If you like, you can add the user to the groups in this interface.

Setting permissions on the folder

You're finally at the stage where you're ready to establish the permissions for the database folder so that only the Access Editors group can use the folder. However, on some computers, the Security tab that you need to use may not appear. To ensure that the dialog does appear, choose Tools | Folder Options in Windows Explorer and then select the View tab. Ensure that the Use Simple File Sharing (Recommended) check box in the Advanced Settings list is cleared (shown in Figure 7).

  1. Open Windows Explorer and find the Protect subfolder within the new Database network share by using the path \data\protect\. Right-click the Protect subfolder and choose Properties.
  2. Select the Security tab.
  3. Select the Users group as shown in Figure 8. As you can see, the Read & Execute, List Folder Contents, and Read permissions for this folder are selected in a grayed-out box. This means that folder permissions from higher up the directory tree have been inherited by this group. You'll also find that you cannot clear this box.
  4. At this stage you don't want anyone to have permission to use this folder. To set this, click the Advanced button. In this dialog, clear the Inherit from Parent... check box on the Permissions tab, and then click Remove in the Security dialog that follows. Finally, click OK to close the Advanced Security Settings dialog. You'll be shown a warning that says that no one will now be able to access the folder (as shown in Figure 9). Don't panic—you're going to fix that in the next step, so accept the changes.
  5. Now you need to grant two groups permissions to use this folder: the Administrators group for this computer and the Access Editors group that you established earlier. In Figure 10, I show where to add these two groups to the Permissions by typing the names of the groups (Administrators and Access Editors) into the object name field, separated by a semicolon. You can then click Check Names to ensure that you entered valid user or group names.
  6. After you've added the second group, click OK to return to the Folder Properties dialog. At this stage you should see the two groups and their permissions.
  7. Now you need to establish the correct permissions for the Access Editors group (as shown in Figure 11) so that members of that group can read, edit, and delete any data or file in the Protect subfolder. On the Security tab on the Protect folder's Properties dialog (which you open by right-clicking the folder), select all the permissions except Full.
  8. For the Administrators group, select the Full Control check box. This will select all the permissions for you.
  9. In order for the folder permissions to take effect, you must exit out of all the dialogs and log off as Administrator.

Testing the permissions

Before going any further, you should test that all the permissions for the \\ComputerName\Databases\Protect\ folder have been set up correctly. To do this, you need to try out the permissions for user accounts that belong to different groups.

  • The administrator of the server should be able to perform all tasks (for example, deleting files and creating subfolders in the folder as normal).
  • A member of the Access Editors group (Editor2000) should be able to use the front-end database (Northwind.mdb) as normal.
  • If your server is part of a local area network, try accessing the \\ComputerName\Databases\Protect\ folder from another computer. You should encounter the error shown in Figure 1 of this article.
  • If you only have one computer, log on as the Administrator and create a new restricted/limited Windows account. Don't add this account to any groups. Now test whether a user logged on as this new account can open the Protect folder. You should encounter the same error as shown in Figure 1.

Sharing your folder

Once you have your folder set up correctly, you can allow other Windows users on your network to have access to your protected folder. To do that, you need to set up Windows account names and passwords on the client PCs with exactly the same account name and password as the Windows accounts that you set up on the server. Once you've done that, you can add the duplicate account that's now on your server to the Access Editors group. By this stage, though, you'll probably have to chat with the Windows system administrator to implement these changes.

Until recently, Microsoft would always ship software with the security turned off, which, naturally, made the software easier to work with. As the proliferation of viruses, worms, and Trojans has shown, this may not have been a good idea. You're going to have to be proactive with your Access database security, and there's no better place to start than with the operating system. Access, after all, is just a collection of files.

This change isn't free. By setting up a protected folder (or having your system administrator set one up for you) on a file server, the administrator of the server will need to add an account to the Access Editor user group for every person who needs to gain permission to the database. But, of course, this is the point: You're now controlling access to your database. This is obviously something that will improve your database security and give some peace of mind to the IT manager—both useful goals in these days of Windows security mayhem.

This has been a long article, but I've given you some idea why it's important for Access developers to understand and embrace operating system security, as well as the internal Access database security that most Access books focus on. I use the extra space in my book to expand on folder protection by showing you how you can further increase your operating system security so that database users can't copy the database file or export the database objects to another database. This assists in closing some of the bigger security holes that the very smart user can take advantage of.

Writing a computer book isn't for the faint-hearted. First of all, you need a robust bank balance because you'll have to give up a substantial amount of work to finish your book. And then you need a well-researched plan for the book, based on a fundamental need shared by a sizable audience, if you have any hope of recouping some of that lost income. Of course, you also need a publisher. A lot of writing experience won't do any harm, but that's probably not as important as the will power to write for a long time.

Once you have those things in place, you'll need more than 200 very long days to grind out the content. While this is happening, you'll need a lot of support from your partners, family, and friends. If some of those folks tell you that they never thought you could write a book, that will help give you that will power I mentioned before, just so that you can prove them wrong. When you get to the end of the book, the biggest beneficiary of the book will probably be you: You'll know what to do when your clients ask for your expertise.

Finally, if your audience can also use your book to achieve the same results that you have with their company or clients... well, then you'll find that the pain was all worthwhile (and, hopefully, your revenue stream will start to swell again).

If you intend to use the Management console, it would be a good idea to read the Local Users and Groups section of the Management console Help guide. I've collected a lot of operating system resources at www.vb123.com/map/opr.htm, including converting from FAT to NTFS volumes, Windows peer-to-peer networks, and network file sharing.

To find out more about Smart Access and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the January 2004 issue of Smart Access. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.

© Microsoft Corporation. All rights reserved.