Why security from only one computer?

[Guest]

Hi -

I have a multiuser database (Access 2000) with some custom security checks
built into the coding, as the Access permissions were a bit too generalized
for our needs. A single copy of the database exists on a shared drive. We
relied on our Network security to ensure only the appropriate users could get
into the db to begin with.

Now, the client wishes to place the application on a more open shared drive,
and have individual passwords for each user. I'm a bit out of my depth, not
much experience as an Access DB Administrator. My first attempt has been a
failure:

Basically, I went into Tools/Security/User and Group Accounts, and added new
User accounts for each individual, and set up an initial password for them,
as well as changing the password on the Admin account. The logons work as
expected from my workstation.

The problem is that the logons do not come up from any of the other
workstations. Why is this? Is there a best way to get the application to
prompt for passwords for the new users I have created?

Many thanks,

Phil Freihofner

 

[Joan Wild]

Hi Phil,

I have a multiuser database (Access 2000) with some custom security
checks built into the coding, as the Access permissions were a bit
too generalized for our needs.

Not sure what you mean; you can make it as complex as you need.

A single copy of the database exists
on a shared drive. We relied on our Network security to ensure only
the appropriate users could get into the db to begin with.

Sharing a single copy of the database is a recipe for disaster - corruption.
Although you can use windows security to restrict access to the folder where
the mdb is located, the users that do have permission need
read/write/create/delete permissions in order to create/delete the
associated ldb file.

Basically, I went into Tools/Security/User and Group Accounts, and
added new User accounts for each individual, and set up an initial
password for them, as well as changing the password on the Admin
account. The logons work as expected from my workstation.

Ouch. You got off on the wrong foot. Step 1 is to create a new workgroup
file. What you've done is modified the standard system.mdw workgroup file
that ships with Access, and is used in all sessions of Access for unsecured
databases.

Undo what you did. You need to follow a detailed step-by-step to ensure you
secure it properly.

You'll want to create Groups and assign permissions to the Groups. Then all
you need to do is add users to various groups - no need to assign
permissions to individual users.

Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

Although the whitepaper is old, it contains information to help you
understand security.

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm

Note that version 2000 has a flaw in the security wizard. You don't want to
use the wizard.

 

[Guest]

I appreciate your taking the time to reply, but it doesn't seem to address my
question.

Hi Phil,



Not sure what you mean; you can make it as complex as you need.

Wish to control individual fields on forms. Also, built in security allows
the client to make modifications themselves via a custom form without the
need for a DBA.

Sharing a single copy of the database is a recipe for disaster - corruption.

I'm sorry, but this is far from helpful. What is the alternative, barring
moving up to SQLServer with Access front-end? The split of Access forms and
data into two databases is impractical and cumbersome, and I don't see how
that makes the database any less susceptible to corruption. Lastly, the db is
of a nature where a daily backup suffices. It's low-activity and data can be
re-entered if there is a breakdown.

Although you can use windows security to restrict access to the folder where
the mdb is located, the users that do have permission need
read/write/create/delete permissions in order to create/delete the
associated ldb file.

Yes. That is what we have in place.

Ouch. You got off on the wrong foot. Step 1 is to create a new workgroup
file.

How does one create a workgroup file? Can I use my Security Wizard for this
task? You say below the Wizard is faulty, (but don't specify the manner in
which it is faulty). Also, it seems I do not have access to a "Workgroup
Administrator" option on my Security dropdown. This may be due to some aspect
of the network implementation of Access which is outside of my control.

What you've done is modified the standard system.mdw workgroup file
that ships with Access, and is used in all sessions of Access for unsecured
databases.

But apparently, it only affects a single computer, not others logging onto
the system from different computers. Why is that?

Undo what you did. You need to follow a detailed step-by-step to ensure you
secure it properly.

"Undo what you did." Do you mean go in and delete each of the new Users?
Change the Admin password back to ""?

You'll want to create Groups and assign permissions to the Groups.

Can I just use the some basic User group? I already have all the security
(within the database itself) that I wish to have. All I wish to control is
who gets into the database. After that, they can and should have all the
normal privileges.

 

[Joan Wild]

Wish to control individual fields on forms. Also, built in security
allows the client to make modifications themselves via a custom form
without the need for a DBA.

You can control individual controls on forms using built-in security. You
can make them disabled, or invisible depending on the group the current user
is in. This is covered in the security FAQ. You have the flexibility to
allow (or not) the client to make modifications to security.

I'm sorry, but this is far from helpful. What is the alternative,
barring moving up to SQLServer with Access front-end? The split of
Access forms and data into two databases is impractical and
cumbersome, and I don't see how that makes the database any less
susceptible to corruption. Lastly, the db is of a nature where a
daily backup suffices. It's low-activity and data can be re-entered
if there is a breakdown.

You can read more about this at
http://www.granite.ab.ca/access/corruptmdbs.htm
The alternative is to split the database. With each user having their own
copy of the frontend on their workstation, corruption is less likely.

If you are happy with the setup you have, then fine.

How does one create a workgroup file? Can I use my Security Wizard
for this task? You say below the Wizard is faulty, (but don't specify
the manner in which it is faulty).

The issue with the wizard is covered in the FAQ. In Access 2000, the
workgroup administrator is a separate program.
Start, Run, wrkgadm.exe and create a new workgroup file.

But apparently, it only affects a single computer, not others logging
onto the system from different computers. Why is that?

Because the Users Group and the Admin User is common to all mdw files. Also
your database (as is) is owned by the 'Admin' user. Even if you remove
permissoins from that user, other mdw files will still be able to use the
mdb, because Admin user owns everything.

"Undo what you did." Do you mean go in and delete each of the new
Users? Change the Admin password back to ""?

Yes, and also delete any groups you created.

Can I just use the some basic User group? I already have all the
security (within the database itself) that I wish to have. All I wish
to control is who gets into the database. After that, they can and
should have all the normal privileges.

No you can't use the Users Group. That group is common to all workgroup
files. So you need to create a new workgroup file, and then create a Group
that has the permissions for everyone. Just create each user and assign
them to this group. You'll want to remove all permissions from the Users
Group, and ensure the Admin user doesn't own anything.

Follow the detailed steps outlined in the links I gave you.