Upgrading your Access Application for a Multi-user Environment - Security
(Page 4 of 5 )
Moving from a single-user to a multi-user environment makes plain the need for security. The single most important thing that you can do to secure your database is to use the security features of Access and create users and groups with various rights and permissions.
This is done in Access by creating a new workgroup, a database sometimes referred to as a system database, to describe the rights and permissions you wish to ascribe to various users and groups. Workgroup files are identified by the extension ”mdw”. The default workgroup file is usually located in the “Program Files\Microsoft Office\Office” directory. In most cases it is not desirable to change this file but rather to associate a specific database with a newly created workgroup. The simplest way to do this is to create your workgroup using the “User Level Security Wizard” under the “Tools” and “Security” menu options. Before proceeding I would strongly recommend that you first create a backup copy of your database.
Workgroup files are handled differently, depending on whether you are using a VB front-end to your database or a front-end created in Access. If you are using VB, then you will need to set the System database property. Let’s repeat the code shown earlier:
Dim strpassword as String
Dim struser as String
Dim strConnect as String
Dim cnDemo as ADODB.Connection
‘assume code to retrieve username and password
‘from a dialog box
Set cnDemo = New ADODB.Connection
strConnect = “Data Source=” & App.Path & “\dbname.mdb;User ID=” & _
struser & “;Password=” & strpassword & “;Provider=MSDASQL.1”
With cnDemo
.Mode = adModeShareDenyNone
.ConnectionString = strConnect
.Properties("JetOLEDB:SystemDatabase")=App.Path & "\Secured.mdw"
.CursorLocation = adUseServer
.Open
End With
As you can see the system database property is set to a workgroup database called “Secured.mdw” located in the same directory as the VB application. In order to open the database, this workgroup file must be identified and the user and password in the connection string must be found in this workgroup file.
If you have created your application using Access only, then you will need to take a different approach to using a workgroup file. To open a database that uses a workgroup file other than the default, requires setting up a short-cut to your database application using the command line switch “/workgrp” and specifying the “.mdw” file you wish to use with this application. You cannot simply double-click the database and open it. When setting up a workgroup using the wizard, this shortcut will be created for you, but if you wish to do it manually, make sure your shortcut follows the instructions above and specifies the path to “Access” with the full path to the database as a parameter and not the path to the database itself.
While there is not space here, nor perhaps the necessity, for an in-depth discussion of how user and group rights are assigned, some further comments are required. Using the wizard will force creation of a database owner with the name of the current Windows user. This owner will have administrative rights over the database. The wizard will also remove “Admin” from the “Admins” group and suggest that the “Users” group have no permissions. These are all very necessary steps in securing your database and it’s certainly worthwhile researching them further if you do not fully understand the implications.
Next: Performance Improvements >>
More Microsoft Access Articles
More By Peter Lavin
/ 33 
