Lee said:
Hi there,
I use Access 97 and I'm trying to run the User-Level
Security Wizard but as I'm logged in as an Amin it wont
do it. I'm working within my own defined Workgroup and
I've set up an Admin password but for the life of me I
can't see how to log on as a plain old User.
I created a new User called UserGroup which is part of
the User 'group' (confused?....you will be!) but that's
as far as I've got as it's not apparent how you set the
password for this new User......aaarrggghhh.......I know
I'm missing something obvious but I've totally lost the
plot!
Hope you can help.
Regards,
Lee
MS Access Security
Updated December 15, 2003
We have recently been working quite a bit with MS Access, and ran into
a situation where User level security was required. Implementing this
turned out to be quite a strange and difficult process. As a result,
the following documentation has been provided in an attempt to clarify
a few of the issues.
The first, and most important concept to grasp is that user accounts,
including passwords, are managed in a system database. User
privileges for a particular database are stored and managed in the
database itself. Unfortunately, the two processes (managing user
accounts and managing privileges) are done through the same user
interface in Access, and there is no indication that the system
database file is being altered. In fact, you may not even be aware of
the existence of the system database or its location as you set up the
user level security in Access. As a result, moving the database file
to another computer, or asking a user on another computer to access
the file from a shared drive will normally result in that user's
complete inability to access the database using any login account.
By default, every user has a work group file called SYSTEM.MDW stored
in a location such as
C:\Program Files\Common Files\System\SYSTEM.MDW
By default, this file will define a single user named "Admin" and two
groups, named "Admins" and "Users". This file will exist for every
user on every P.C. Therefore, a database that is created when logged
in as Admin (the default login), will be completely accessible to
anyone on any P.C.
There is NO WAY to change the security on a database once it has been
created in the default way. For example, you cannot delete the Admin
user from the Admins group, remove privileges from the Admins Group,
or remove privileges from the Users group. Setting up new groups or
users in an existing database in order to try and implement user level
security WILL NOT WORK.
The ONLY way to create an Access database with user level security is
to create the database as an Admins user other than Admin and to make
sure that the Users group has no privileges. The User-Level Security
Wizard will do this step for you, but you must first change the
information in your system work group file.
There is good documentation on how to create a new work group file and
set it up correctly on the Microsoft site. In brief, you need to do
the following;
Create the work group file – for Access 2000 use the Wrkgadm.exe tool.
Note: The documentation that I found on the net indicates that you can
give your new work group file any name you want to. However, by
default, Access will look for the file SYSTEM.MWD in the same
directory as your database file. There are ways around this (by
creating a shortcut as described later), but it's easiest to just use
the default naming convention.
After the new work group file has been created, open up MS Access
(with or without opening an actual database – I like to NOT open up a
database to reduce confusion).
Note: When you open up an Access database and you are not prompted for
a login user ID and password, you automatically log in as the Admin
user.
Select Tools -> Security -> User and Group Accounts
By default the Admin user is highlighted. Go to the Change Login
Password tab and add a password to the Admin account. This step is
essential in order for Access to prompt you for a User ID and password
the next time you use it.
In the User pane choose new and enter a new user name and ID. The
actual value for the ID doesn't matter*, but is required if you ever
need to recreate the account. I use the 1st three letters of the
users first and last name concatenated together.
Immediately add the new user to the Admins group and then remove the
user Admin from the Admins group. Exit Access and start it up again,
logging in as the new Admins user you just created, not Admin. Note
that the new user account will not have a password yet. Go to the
Change Login Password tab and add a password to this account.
Now open the database that you want to apply user level security to.
Select Tools -> Security -> User-Level Security Wizard
You can accept all the defaults for this wizard except the third
screen where you should check off some user security groups that you
want to create. You will end up with a secured database. You can
then set up new users and groups that have access to particular forms,
etc.
There is one last wrinkle with all of this. Other users attempting to
open your secured database will be greeted by a pop-up that says the
following;
You do not have the necessary permissions to use the
r:\public\mydatabases\mydatabase.mdb object.
This is because they are using their own default work group file, not
the one that you built for your database. Their work group file will
not have the same users set up as the one you created to implement
user level security.
You can either have them join the work group by using the Wrkgadm.exe
and browsing for your secured work group file, or you can set up a
shortcut that explicitly defines the work group file to be used by
Access. If you create a shortcut, the target must include the full
path to the Access executable, followed by the wrkgrp switch, the full
path to the work group file, and the full path to the database file.
An example target is shown below;
J:\Office2000\PFiles\MSOffice\Office\MSACCESS.EXE /wrkgrp
"r:\public\mydatabases\system.mdw"
"r:\public\mydatabases\mydatabase.mdb"
Note: for the same reason you will need to specify the System file for
any JDBC connections you want to make to a database that has
implemented user level security. You can test that the setup is
correct using Excel.
For more information, consult the following documents.
Microsoft Knowledge Base Article - 254372
Microsoft Access 95 Security White Paper - ACC95
The current location for these articles can be found on the Microsoft
site.
*Note: anyone that knows exactly how the users are set up in your new
work group file, including the exact ID, can duplicate any user in
another work group file. Therefore, if you use a standardized
approach for creating the ID you are opening up a security hole. For
example, suppose you set up the user John Smith with ID johsmi. John
Smith then opens up your database and sets a password for his account.
He now has secure access to your database. However, if someone knows
your convention for setting up user accounts, they can create the user
John Smith in another work group file without a password and they will
be able to access your database with the same privileges as the real
John Smith. The way to avoid any issues in this regard is to use an
ID for each user that is impossible to guess.
