Garry Robinson    Read The Same Article At Microsoft

Summary

The purpose of this article is so that you can improve your database security for a situation such as this. Your company has ten people who all share the same network file server or in a bigger organization, share the same Windows Server domain. Within that group of people 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 will probably not want any of the others to have access to the database. If you are an Access database specialist, and there is every chance that you are because you are reading this article, you will probably turn to workgroup file security and you will be well advised to do so. To apply additional level of security, you should also embrace the security found within the operating system on your server. This will go a long to ensure that only the authorized people will be able to open the folder where the database files exist and thus open the database file(s) themselves.  Only allowing certain Windows users access to the database is a fundamental technique used by SQL server and other enterprise level databases and now you are going to be shown how you can embrace Windows security so that you can really start to trust that old workhorse (Access) again.

A Summary of Protected Folder Security

We are going to establish a Windows user group to which we will add the Windows user accounts that will be allowed to open the folder where you store the database. We will call this group the Access Editors group. Then we are going to use Windows operating system security to ensure that anyone who is not a member of either the Administrators group or the Access Editors group will be denied access to the database folder.

In technical terms, what we are going to achieve with this protection is that all the Windows users who we do not grant specific permission to use the database folder or its sub-folders will encounter the 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.

Figure 1- Warning Any Unauthorized User Receives When Trying to Open a Protected Folder 

In summary, no matter what internal protection and security measures you add to your Access database, you can improve your protection substantially by using a modern operating system on a file server or peer-to-peer server. If you were reading a enterprise level security book, they would explain that using a protected folder with other Access security and protection as creating layers of defense for your database.

What You Need To Work Through The Demonstration Material Yourself

To work through the examples you will need to have a copy of Windows XP Professional. If you are running Windows 2000 Professional, the example will provide you with enough information to undertake the research yourself or you can read my book where I wrote the related chapter primarily using Windows 2000 Pro. If you are using Windows XP Home, you can use this operating system security as a client pc to access a protected folder. Unfortunately you cannot setup folder security as discussed in this article using XP Home. 

Proof-of-Concept Operating System Security

The main purpose of these examples is to help you become familiar enough with the underlying concepts of folder permissions. Once you have grasped those concepts, you will be able to demonstrate the viability of this technique to a system (Windows server) administrator because they will probably need to set it up for you in the Windows domain. You will also have the knowledge to test anything that they set up for you. For some of you who have small networks that do not use a Windows server computer—such as small teams of developers—you may well be able to use the concepts straight away.

For these illustrations, I will use Windows XP Professional. On this computer, which I will call the peer-to-peer server, you will need to have an NTFS-formatted drive volume (not a FAT or FAT32 volume) for this demonstration to work. See the further reading section at the end of this article for links to good information on NTFS volumes.

Preparing Databases in a Protected Folder

The first stage of the exercise is preparing a folder that will hold the database files that we want to protect. For this exercise, I will use a folder called \data\ as the basis for our protected folder as follows:

1.        Log on in 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.

Setting Up a Network Share

The next part of the process, essential if you are to allow windows user accounts from other computers to use your folders is to set up a network share. The steps to complete this are :

1.        Right-click the \data\ folder and choose Sharing and Security.

2.        Enter the details for the network share (which I have called Databases in this demonstration) and Click OK.

3.        You now have set up a network share that other people connected to your windows workgroup can use. You can see the share called Databases that I have created (for the Cow-FX computer) in Figure 2.

   View this picture and all others in this article by clicking on the picture

Figure 2. The network share, now set up and available to be referenced in the Address bar.

Setting Up a User Account - Method 1

First of all we need to setup Windows accounts that can use our database but who do not have permissions to alter folder permission on our peer-to-peer server. For this we need a Limited account or a Restricted account as it was called in Windows 2000. In Windows XP there are two ways to setup a user account. The first method is:

1.        Open the Windows Control Panel by clicking the Start button and choosing Settings.

2.        Double-click User Accounts.

3.        In the Users and Passwords dialog, click Create a New Account.

4.        This starts a Wizard. Enter a name for the new account. Throughout this article, I will use Editor2000 as the account that is allowed to edit the database. Click Next.

5.        Adding the user as a Limited User will stop the user from installing most software, changing user account and changing important folder permission; this is what we want at this stage.

6.        Click Create Account. The Users Accounts dialog now reappears, and you will find that the account that you just set up (Editor2000) is listed as a limited account on your computer with no password.

7.        Click the Editor2000 account and choose Create a password.

8.        Enter and confirm the password and enter a useful but not too exact password hint.

Setting Up A User Account - Method 2

If you are using Windows XP, you may find a more integrated approach is to head for the Computer Management console straight away to create the Account.  To do this, follow these steps:

1.        Choose Start > Settings > Administrative Tools > Computer Management.

2.        Select Local Users and Groups under System Tools 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 will create a limited account and you will have more control over passwords.

Figure 3. The computer management console allows you to add an account.

Setting Up a New Access Editors Group

Now we need to add our new account Editor2000 to a special group of Windows users who will be allowed to open the database, create and delete the LDB file and generally use the folder just like any other. Because this group probably doesn’t exist yet, we first need to create the Windows user group that will hold a list of our database users’ accounts as follows:

1.        Choose Start > Programs > Administrative Tools > Computer Management.

2.        Select Local Users and Groups under System Tools.

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 will use the group name Access Editors throughout the article.

5.        Click Create to add the group and click Close to return to the Local Users and Groups dialog (shown in Figure 4).

Sidebar  As you are reading through these instructions, those of you who have worked with User-Level security in Access will notice the similarities between users and groups in Windows and those in user-level security. The same principle that says it is better to allocate permissions to groups rather than users applies to Windows user groups as well.

Adding the Users to the Group

At this stage, the Local Users and Groups dialog in the management console should now be visible. In the next stage, we need to 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.

Figure 4. Select Access Editors from permission groups on your computer.

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 into 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.

Figure 5. Adding a user to a permissions group.

4.        Click OK when you have completed adding all the users.

You will 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 and the Access Editors group. If you like you can add the user to the groups in this interface.

Figure 6. The new User account is now a member of two groups.

Setting Permissions on the Folder

Now we are finally at the stage where we are ready to establish the permissions for the database folder so that only our Access Editors group can use the folder. To undertake this process, follow these steps:

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.        On some computers, the Security tab may not appear in Windows XP Pro. To ensure that it does appear, choose Tools > Folder Options in Windows Explorer, 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).

Figure 7. Clear this option to make the security tab appear on a folder.

4.        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 will also find that you cannot clear this box.

Figure 8. Viewing the Users group permissions for this folder.

5.        At this stage we do not want anyone to have permission to use this folder. To fix this, click Advanced. Clear the Inherit from Parent... check box on the Permissions tab, then click Remove in the Security dialog that follows. Finally, click OK to close the Advanced Security Settings dialog. You will be shown a warning that says that no one will now be able to access the folder as shown in Figure 9. We’re going to rectify this straightaway, so accept the changes.

   IMPORTANT STEP

Figure 9. Removing inherited permissions from the Users group.

6.        Now we need to grant two groups permissions to use this folder: the Administrators group for this computer and the Access Editors group that we established earlier. In Figure 10, I illustrate 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.

Figure 10. Adding the groups manually using the select Users and Groups dialog.

7.        After you have added the second group, click OK to return to Folder Properties dialog. At this stage you should see the two groups and their permissions.

8.        Now we 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 Properties dialog, which you can open by right-clicking the folder, select all the permissions except Full.

Figure 11. The correct permissions for this folder for the Access Editors group.

9.        For the administrators group, select the Full Control check box. This will select all the permissions for you.

10.     You must log off the administrators account for the folder permissions to take effect.

Testing the Permissions

Let’s 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 peer-to-peer computer should be able to undertake all tasks such as deleting files and creating sub-folders in the folder as normal.

·         Try out a member of the Access Editors group (Editor2000). This account should be able to use the front-end database (Northwind.mdb) as normal.

·         If your peer-to-peer server is part of a local area network, try the \\ComputerName\Databases\Protect\ folder from another computer, and you should encounter the error shown in Figure 1 of this article.

·         If you only have one computer, log on as the administrator of the peer-to-peer computer and create a new restricted/limited Windows account. Do not add this account to any groups. Now test whether that new account can open the Protect folder. It should also encounter the same error as shown in Figure 1.

Sharing Your Folder On A Peer-To-Peer Network

Once you have your folder setup correctly, you can allow other Windows users on your network to have access to your protected folder. To do that, you need to setup Windows account names and passwords on the client PC’s that have EXACTLY the same account name and password as the Windows accounts that you need to setup on the peer-to-peer server (your PC). In essence this means that you duplicate the accounts that appear on two PC’s. Once you have done that, you can add that duplicate account that is now on your peer-to-peer server to the Access Editors group. By this stage though, you should be ready to have a chat with the Windows system administrator if you happen to work in a systems environment where you are just the smart access guy or gal who wants to put files on the server again.

 

Useful Further Reading and Resources

 

If you intend using the management console, it would be a good idea to read the Local Users and Groups section of the  Management Console help guide.

 

Conclusion

Until recently, Microsoft would always ship software with the security turned off which naturally made the software easier to work with. As we can see with internet viruses and macro viruses, maybe this wasn’t such a good idea. If you wish to start being proactive with your Access database security, there is no better place to start than with the Operating system because Access is after all just a collection of files. So if you setup a protected folder or your system administrator sets one up for you on a file server, you will then be in a situation where the administrator of the server will need to add the account to a windows user group for a person to gain permission to use a database. This obviously is 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.

 

I hope this will give you some idea why it is important for Access developers to understand and embrace operating system security as well as internal Access database security. In my book, I expand on this folder protection by showing you how you can further increase your operating system security so that database users cannot 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.

 

To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/ or use a search engine to look for the ISBN 1590591267.

Other Pages On This Site You Might Like To Read

Personalised Email from Access and Word
Consolidation Queries
Backing Up Your Access Data With XML

Autoexec macro in Access

Downloads

  There are no downloads for this article but the material is covered much more fully in chapter 12 of Garry's book on Access protection and security.   

Click on the following button to jump to the next page in the document loop.