Microsoft Access Authentication and Authorization | Security for Microsoft Visual Basic .NET

Whereas SQL Server supports two authentication systems, Microsoft Access supports three. Unfortunately, three is not necessarily better than two, and the Access security system is not suited for large enterprise usage. Here are the three types of authentication available to Microsoft Access:

None This is the default option. Everyone can create, read, update, and delete any object in the database. This type of security should not be used for enterprise applications because it leaves the database open to modification by anyone.
Database password A database password is simply a password that Access prompts you to type in when opening the database. A database can have only one password. You cannot assign a different password to different users. This makes it all-or-nothing authentication, which is a major drawback for enterprise applications. There are two additional problems: if a user knows the database password, she can also change it as long as she can open the database exclusively. When programmatically opening a database, you can specify the database password by using the Password parameter in a DAO connection string or the Password parameter in an ADO Connection.Open method. Because the security provided by a database password is authentication without authorization, it’s not recommended for enterprise applications.
User-level security This is the only Microsoft Access authentication type that can be used for enterprise applications. It is similar to SQL Server authentication, requiring a username/password to log on. The list of users is maintained through the database and is stored in a separate workgroup information file. Different databases can be associated with different workgroup information files. Each workgroup information file, and each user within the file, is associated with a unique identifier. This means that to be authenticated, the user must use the right username and password for the workgroup information file associated with a particular database.

Microsoft Access User-Level Security Models

There are two conceptual models for setting up user-level security: Full Rights and Owner-Admin. These models define how both authentication and authorization work.

In the Full Rights model, you set up a logon account within Microsoft Access for each user of the application. So, to use the system, a user first logs on to Windows, and then logs on to the database using a second set of credentials. Each Microsoft Access logon account can be assigned to one or more groups. You then assign database permissions for groups or users. For example, you can assign read-only access to the Employee table and read/write access to the Role table. Conceptually, this is the same model as SQL Server authentication. It also has the same drawbacks: you have to administer the database users separately from the domain users, and people have to log on twice—once on to Windows and a second time on to the database. In addition, this mode has one more drawback—user administration. Adding users, removing users, and changing passwords can be done only from within the Microsoft Access environment itself. It cannot be programmatically controlled from Microsoft Visual Basic .NET. This means to add a user or change permissions, you have to start Microsoft Access, open the database, and use the security tools from the Tools|Security menu.

In the Owner-Admin model, you set up two users. The Owner user is password protected, is the owner of the database, and can change any object in the database. The second user is the Admin account, which is the default account Microsoft Access uses for opening databases. In this model, Admin cannot do any destructive actions such as opening the database exclusively, setting a database password, or deleting a table. However, the Admin account can change information within any table.

Which model should you use? The Full Rights model offers the most control over security; however, it’s awkward—requiring users to log on a second time after logging on to Windows. Full Rights is also impractical—requiring Microsoft Access itself for account administration. For these reasons, the Owner-Admin model is typically the most acceptable option. This option sacrifices control of access in exchange for easier administration. In practice, this trade-off is usually acceptable, and you can control access to the database itself through Windows NT file permissions. For the rest of this chapter, we assume you’ll be using the Owner-Admin model.

Setting up a database for user-level security

In this exercise, you’ll create a new workgroup information file and associate the EmployeeDatabase.mdb database with the workgroup information file. Finally, you’ll set up the database for Owner-Admin security.

Start Microsoft Access XP, and choose the menu item File|Open.
The Open dialog box will be displayed. Navigate to EmployeeDatabase.mdb, and open it.
Start the User-Level Security wizard by choosing the menu item Tools|Security|User-Level Security Wizard.
On the first page of the wizard, ensure the Create A New Workgroup Information File option is selected as shown in the following illustration. You will create a new workgroup information file just for EmployeeDatabase.mdb. Click Next to move to page two.
On page two of the wizard, enter a WID (Workgroup IDentifier) for the workgroup. If you ever lose or accidentally delete the workgroup information file, you can re-create it by entering the same information in this page. You don’t need to write this information down because at the end of the wizard, Microsoft Access will create a report with this information in it. On the bottom of the page, ensure the I Want To Create A Shortcut To Open My Secured Database option is selected as shown in the following illustration. The wizard will create a shortcut on the desktop to the database, and it will pass the location of the workgroup file to Access as a parameter. Click Next to move to page three.
On page three, you can choose what database object the security permissions will be applied to. The default is all objects. Click Next to move to page four.
On page four, you can configure Authorization groups to include in the workgroup information file. Because you’ll be using the Owner- Admin model, you will not be setting up groups in the database. Click Next to move to page five.

On page five, you choose what permissions the default user will have. The default user option is what any user will use to access the system. Ensure the Yes, I Would Like To Grant Some Permissions To The Users Group option is selected, and in the objects tabbed subsection, make the following changes, as shown in the figure:

Database	Open/Run
Tables	Read Design, Update Data, Insert Data, Delete Data
Queries	Read Design, Update Data, Insert Data, Delete Data
Forms	Open/Run
Reports	Open/Run
Macros	Open/Run

click to expand

Page six is used for setting up users. Because this database is being configured for the Owner-Admin model, you should create a new user named Owner and delete any other users that are listed. Make sure Owner has a strong password, and click Next to move to page seven.
Page seven is used for assigning users to groups. By default, there are two users: Admin and Owner. Only Owner will be shown on the page. Ensure Owner is part of the Admins group, and click Next to move to the final page, page eight.
On page eight, you must specify a location for a backup of the original database. After finishing the wizard, you should remove this backup database because it will allow an intruder to access a snapshot of the database. Click Finish to close the wizard, and apply the changes you just made.
After closing the wizard, Access will show you a report of the changes you just made, including the workgroup setup information. Print this report, and then close the report without saving it. Store the printout in a safe place because this is the information you’ll need to use if you need to create the workgroup information file again.

After the wizard has finished, anyone can open the database and modify data. However, to change the design or add and remove database objects, you’ll need to log on as Owner using the shortcut on the desktop.