It is strongly recommended that
Make sure you remember the password of at least one Admin user! Security settings can be one of the most difficult and confusing aspects of databases. Clear notes and documentation are essential; write down user names and password and keep them in a safe place until you know them by heart.
There are two default or built-in groups in Access, Admins and Users. By default, members of the Users group are granted full permissions to databases created in Access. These groups are identical on all default installations of Access so any database created in your copy of Access can be run on almost any other installation.
The default user for Access is 'Admin', which is a member of the default Admins group and has full control over all resources in every database created on a particular computer. Also, all users in the Users group have full permissions to all components of a database. When you run Access without any security settings you are automatically logged on as Admin. If you set a password for Admin you should remember it, otherwise you will be locked out of Access. Until this is pointed out you are not really aware that you are logging on as the Admin user at all, Access seems like any other application such as Excel or Word.
An Access database is protected from illicit use by adding users to a workgroup with specific security settings and deleting the default user. The Admin user cannot be removed from the Admins group until a new Admin user has been defined. Only members of the Admins group may define or modify user and group profiles and reset passwords.
These settings and operations are stored in the default workgroup file, which is called system.mdw, and may be found in a subfolder of the Documents and Settings folder. Security settings for individual databases are stored inside the .mdb database file. The location of the current workgroup file is stored in the Windows registry. As you will see, Access includes a Workgroup Administrator program that can be used to create new workgroups, groups and users.
When a database is created it is owned by the currently active workgroup and the current user. Unless the workgroup has been changed this will be the default workgroup that includes the default Admins and Users groups and the default Admin user. Thus any databases you create without explicit security settings will be owned by Admin and the default workgroup. Applying security settings is a matter of creating new workgroups and restricting access to groups and users who exist only within them.
The sequence of events for securing an Access database and setting security levels for different users is as follows:
To create a new workgroup choose Tools/Security/Workgroup Administrator. Click Create and complete the three sections of the dialogue box. These will be combined to create a unique 64 bit identifier for the workgroup that can only be replicated by typing in exactly the same text as before (it is case sensitive).
Choose a location and a name for the system.mdw file where the group information will be stored. For a networked system this would probably be on a network drive, alongside the database.
When you have finished a confirmation box is displayed:
This file is called 'system1.mdw' but it might equally have been called 'schooldata.mdw' or any other suitable name. The Workgroup Administrator informs you that you have created the new workgroup:
The Workgroup Administrator changes your registry settings to add you to the new workgroup.
Choose Tools/Security/User and Group Accounts, which opens the following dialogue box:
So far there is only one user defined in the Name drop-down list, Admin. The Group Membership section shows that Admin is a member of both the Admins and Users groups. To create a new user click the New button and add the details of the user you want to create:
Exit Access and open the database as normal, at which point you will be prompted for a login ID and password:
There is currently no password for the new admin user so just click OK.
Now you are logged on as the new admin user you can change the password. Choose Tools/Security/User and Group Accounts. Select the user and click on the Change Logon Password tab. Enter a new password, which you will have to use in future. Both admin users, Admin and the new one, require a password to access the database.
You should now set up a new database by importing it from the old one - use File/Get External Data/Import and choose the existing database.
When a new workgroup and admin user have been set up permissions for other users can be set up with the User Level Security Wizard.
As the first window explains, the wizard makes a backup copy of the database and helps to secure the current copy. The first option creates a new workgroup and user but you should have done this manually already. Try to avoid using this option at all by setting up workgroups and users manually. If you do choose Option 1 the wizard will create a new workgroup as follows:
The wizard will create a new user named after the Windows login ID and will make this the owner of the database.
The second option allows you to modify the current workgroup and this is the option that you should choose if you are now logged on as the new owner of the database. The next part of the wizard presents the components of the database and
By default the wizard selects all objects in the database to which security settings will be applied. Security settings do not apply to Data Access Pages as these are run as html pages but the browser will prompt for a logon to such pages if the connection properties specify a workgroup. Here is a data access page set up after completing the application of security:
As you can see this requires you to login as one of the recognised users of this database.
The next stage lets you set up some pre-defined groups with their permissions already set, fro example Read-Only Users.
You probably will have little use for this at present.
The next stage asks if you want to assign any permissions to the Users group. It is best not to assign any permissions to this group because all installations of Access have the same default group so this would be a security weakness.
The next stage lets you define some users, which may be useful as it saves doing it later, but you don't have any groups other than Admin as yet to assign them to.
The next stage lets you assign any user you may have created to groups, though you may not have any other than Admins.
The final stage lets you choose a location for the backup copy of the database and also produces a report on the details of your users and groups.
To recap, you have removed the default user from the Admins group and changed its password to a real value and you have set up an alternative administrator with a password. This sets up Access on your computer as a more secure environment than it was previously. You will now find that you have to complete the login box for any database that you open because a password is required for access by Admin. To remove this feature login as Admin or the new administrator and change the Admin password back to nothing. This removes your security settings from the database as you no longer need to login. To restore Admin to the Admin group you will need to login as another administrator.
You can use Tools/Security/User and Group Accounts to create new groups and users and then use Tools/Security/User and Group Permissions to set up groups and users and their permissions to various objects in a database.
New users are created by clicking on the New button in the User and Group Accounts box. Enter the user name and ID and click OK. You will not be able to set a password for the new user, the user has to login themselves and set their own password.
Once a new user exists they can be assigned to groups and given permissions to the various objects in the database.
