Why is Developer Workgroup Security so Important? | Real World Microsoft Access Database Protection and Security

Once again, I will provide alternative overviews about what's important to the developer, the DBA, and the IT manager. In this section, I will also describe why workgroup security set up in this way is in fact secure and why denying your users access to the developer workgroup file is a good form of defense, and then I will provide a complete list of brief instructions for setting up workgroup security.

Overview for the Developer

I can say without any hesitation that the developer workgroup concept that I discuss in this project has been used in a vast majority of the projects that I have been involved in. In most of those projects, I have only ever secured a few key objects in the database. The impact of this approach is that customers generally need to contact me again when they upgrade Access, and I can maintain ownership rights on the objects that I have perfected over many different projects.

If you are an Access developer, you need a secure workgroup file in your collection of database tools. This chapter shows you how to set one up and then how to use it to secure one or more of your databases. Like all the other Access protection measures that I have covered in the book, there are many varied approaches that you can follow to achieve different security and protection outcomes . On initial scanning of the material in this chapter, the process may appear to be a little complex.

Some of the useful outcomes that you will learn by reading this chapter include

The simple developer workgroup file concept.
The VBA code that will rebuild a secure workgroup file from scratch.
The ability to secure the Access startup options properly.
The undocumented Access 2000 permissions that will secure modules from being opened from the Database window.

If you follow the developer workgroup strategy that I have described, most of your users will probably never even guess that they are using security in the first place, and that will always be good for maintaining a good rapport with your users and clients .

Overview for the DBA

An Access DBA must be able to understand the basic concepts behind workgroup ( user -level) security, as it is the key to managing secure Access databases. The developer workgroup strategy detailed in this chapter gains its security because the users do not have access to the workgroup file that is used to secure the database. In addition, it is simple for users to run because they do not have to log on to use the database.

If the strategy is fully implemented with the appropriate startup options, users will not be able to change or avoid the user interface. If the DBA does not want to implement all these steps, the DBA will appreciate that there are significant and simple security outcomes. Unfortunately, these developer workgroup files do not provide specific security for the data and queries, so the DBA should review additional options in the next few chapters to build on the security that this strategy provides.

Overview for IT Manager

When reviewing this chapter, the IT manager should appreciate the simplicity of the developer workgroup strategy because it will help to ensure that your users will not tamper with or spend time fiddling with corporate database applications. Another key benefit is that this system does not rely on passwords and all the maintenance issues that passwords bring. In addition, you should be aware of these issues:

If external developers are using workgroup files to create your databases, you need to ensure that you have copies of the workgroup files or understand what parts of the application you do not have access to.
You should watch for the internal proliferation of workgroup files because they will definitely lead to headaches when they become lost or the users connect to incorrect workgroup files. This strategy assumes that most users will simply use the default workgroup file installed by Access, so you may not run into most of those hassles.

In the next section, I will describe why the material in this chapter can claim to offer security rather than just another protection measure.

Security Rather Than Protection

For the first time in the book since the introduction in Chapter 1, I am going to describe how you can secure parts of your database rather than simply protect them. To secure your database, you need to apply Access workgroup security in a certain way. By doing so, these techniques will make your database so secure that only a very competent computer person who has a lot of time on his or her hands could work out how to break into the database objects or database settings.

The rest of us, including yours truly, will just not be able to change the designs of queries and tables or look at the design of forms, reports , or modules. We should not be able to readily use any software to crack the workgroup security that we set up, and we should be able to stop the greatest security threat of all, the Access Import command (discussed in Chapter 9). To crack the database will involve paying considerable amounts of money to companies that have those skills and making a legal declaration that the person has the rights to the information. Alternatively, our would-be hacker could waste days or weeks picking through the content of the database in a text editor. Because the hacker would either have to announce him or herself as a criminal to the world or become very determined, my assessment is that this style of security is pretty good.

Note

I should be using the term "user-level security" rather than "workgroup security" if I was to follow the conventions used in Access help. My problem with "user-level security" is that it becomes complicated to differentiate between Access users and Windows users. The term "user-level security" also obscures the connection with the all-important workgroup file. So I believe that the term workgroup security is more definitive, even if it is not the term generally used in the help file. That this term is more definitive is especially true in relation to this chapter because all users will actually be the anonymous Admin account, and thus the term becomes a complication in itself.

Getting your workgroup security properly set up is vital if you are looking for really good security for your data. Workgroup security, properly applied to a front-end database, will deny the user access to the database window or the VBA projects, making it very hard for someone to guess the location of the back-end database. When these techniques are combined with good operating system security, ordinary users of your database will not be able to browse the folders to copy your databases or import the objects into another database.

Denial is a Good Form of Defense

Those of you who are familiar with Access workgroup security will probably start throwing your hands in the air and wondering how a system can be safe if it allows anyone to open the database. The answer is that a number of companies have cracked the encrypted Access workgroup file, so you need to build systems in which the administration and the objects in a database are controlled by a workgroup file that is not available to the database users. To do this, you first must build a developer workgroup file that is never distributed to your users. If you set up that workgroup file correctly, you can add a number of very useful security measures to the database without introducing user accounts to the workgroup file. Once you have done this, you can then take a number of approaches, including securing your database files, by using the operating system or producing a second workgroup file that does not include the administrator's information.

In the next section, I will show you the different steps that you can take to secure your database with workgroup security.

The Access Workgroup Security Driving Instructions

Before I launch into the details of developer workgroup security, I am going to outline all the different tasks that you can do to apply workgroup security to your database. Because workgroup security requires a different approach for different tasks such as user interface security or data security, the information has been divided into different chapters. This list shows what you can do rather than offer discussions on the issues surrounding the tasks . While reviewing this list, remember that you may want to skip a number of steps by using the User-Level Security wizard. Even if you use all the steps, you really should work through the chapters to understand the issues so that you implement a secure model.

Chapter 8: Developing Workgroup Security

You really must follow most of the steps in this chapter if you are looking for security.

Create a workgroup file.
Add a password for the Admin account.
Add a new user, which will be your Developer account.
Add your Developer account to the Admins group .
Remove the Admin account from the Admins group.
Secure just a few objects by using the Developer account to mark your territory.
Transfer ownership of the database and all its objects to the Developer account.
For the Admin account and Users group, change permissions as follows :
- Clear the Administer and Exclusive database permissions.
- Clear Read Design, Modify Design, and Administer permissions for forms, reports, and macros.
- Clear Modify Design and Administer permissions for tables and queries.
- For modules in Access 97, clear all permissions. For Access 2000 or later, use the frmProtectModule form that I have provided to remove the permissions for the modules.

Chapter 9: Security Concerns, Encryption, and Database Passwords

Use these protection measures where appropriate, but be aware of their flaws.

Encrypt the database.
Use a database password.

Chapter 10: Securing Data

Securing data requires you to build on the previous workgroup security, as follows:

Remove the Open/Run permission for the database for the Users group and the Admin account.
Create a new group of users and add some users to that group.
Give that group account appropriate permissions to access your data and objects.
Create a shortcut file to open the database with the appropriate workgroup.
In Access 2000 or later, use the User-Level Security wizard to undertake most of steps outlined in the preceding sections ("Chapter 8: Developing Workgroup Security" and "Chapter 9: Security Concerns, Encryption, and Database Passwords").
Use the Access 97 Security wizard to create a secure database by using most of the steps in the section "Chapter 8: Developing Workgroup Security."
Add one of the PID authentication techniques to protect your workgroup file.
Distribute data outside the company network very prudently, because security is not easy to control.
Use Read with Owner Permissions (RWOP) queries for protecting data.

Chapter 11: Object Protection and Security Measures

Here are some other measures that you can undertake to protect the objects in your database.

Create a developer workgroup file with the User-Level Security wizard.
Protect linked tables with remote queries.
Protect queries design by using RWOP queries.
Protect forms and reports by opening them with special VBA code.
In Access 2000 or later, use VBA project passwords.
Use data access pages with workgroup security.
Secure forms, reports and VBA code by using the MDE format.
Hide queries in encrypted MDE-format databases.

Chapter 12: Protecting and Securing Your Database with the Operating System

Although the operating system has nothing to do with workgroup security, many of the permission strategies that workgroup security provides can be provided by the operating system. If you take the time to combine both methods , you will be well on your way to having a very secure Access back-end database and a quite a secure front-end database.

Now I will introduce you to the developer workgroup file strategy and, in the process, explain some of the essentials of Access workgroup security.