Problems setting up access security

[Michael D.]

I have an Access 2003 textbook, and 3 web tutorials, on how to set up
user-level security in Access. Although I feel like I'm doing it correctly,
when I put the database on the LAN, it only applies to me. Everyone else
just zips right into the database.

I followed the steps in the Access Security FAQ:

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

The 10 steps here:
http://www.ltcomputerdesigns.com/The10Steps.htm

This article on about.com:
http://databases.about.com/od/tutorials/ss/usersecurity.htm

And the textbook Access 2003 Comprehensive Concepts and Techniques (Shelly
Cashman Pratt)

I have the .mdw file on the network drive along with the database. Do I
have to go to every PC that will access this thing and merge the .mdw into
their own local one? How does that prevent people from getting into it, as
anyone who I don't do this step for will still have access? I set a password
for Admin, moved Admin to the Users group, etc. But when someone else logs
in, they don't get a password dialog for Admin and they are in the Admins
group still...

Confused! Frusterated! haha.. thanks for any guidance! - Mike

 

[Joan Wild]

Setting up security is tricky, and if you miss even one step, it can result in an unsecure mdb.

I have the .mdw file on the network drive along with the database. Do I
have to go to every PC that will access this thing and merge the .mdw into
their own local one?

No, you don't.

How does that prevent people from getting into it, as
anyone who I don't do this step for will still have access?

Exactly. If you set it up correctly, they should only be able to open the mdb while using the correct mdw.

I set a password
for Admin, moved Admin to the Users group, etc. But when someone else logs
in, they don't get a password dialog for Admin and they are in the Admins
group still...

Well, did you start by creating a new mdw?

Here's another step-by-step you could try. Don't skip anything!
www.jmwild.com/AccessSecurity.htm

 

[Michael D.]

Joan,
Thanks for the reply! I was just reading some stuff you'd written in
another post that seemed helpful as well.

I'm going to try your steps. I promise I'll go slow, despite feeling like
somewhat of an expert having gone through the steps so many times! haha..

One quick question: when securing a database, is it better to have the DB
on your hard drive and then move it and the workgroup file to the LAN, or
have the DB on the LAN and secure it from there? Or shouldn't it matter?

Thanks again! - Mike

 

[Joan Wild]

The location shouldn't matter. If you move it after the fact, you'll need to go into the properties of your shortcut and adjust the paths in the target to reflect the new location.

By the way, don't give your secure mdw the same name as your mdb. They each to create their own ldb file, and won't be able to if they have the same name.

 

[Michael D.]

Thanks, it looks like everythings working. Just need to test with some of
the users in Pittsburgh when they get in.

Obviously, something about your tutorial is either different from, or better
than, the 3 others that I worked through. I'd like to go back and do some
analysis/comparison but I just don't have the time..

Thanks again! - Mike

 

[Michael D.]

Joan,

I was able to successfully set up security on a couple of databases in my
area, and test them with multiple users on our LAN, including people from
remote geographic sites and the like, no problem.
But the person that I was trying to help (from another part of the
corporation) couldn't get your steps to work. So I went to her building and
went through the steps on a couple of different access databases, including a
little dummy one that I created on the spot. Everything seemed to work, but
for whatever reason, when ever someone other than her tries to open a secured
..mdb (ie, by going directly to the database itself), it lets them zip right
into the database. But when SHE tries to directly access the database, it
tells her she doesn't have permission (like it should for everyone).

The shortcut works fine, everyone can use it and log in using the
username/password combinations we set up in the workgroup file.

Any suggestions on the most likely thing I missed while at her desk that
would allow other users to access the .mdb file directly?

Thanks again! - Mike

 

[Joan Wild]

I'd only be guessing, but some possibilities:

The Admin user still owns the database object.
You secured it using the system.mdw, thinking that you were using your secure mdw.

 

[Michael D.]

Here is something that makes it even more confusing to me: I had her email
me the database, the workgroup file, and the shortcut, and everything worked
exactly as you would expect. I had to modify the shortcut to point to
different drive letter/path, but didn't change anything else. I can't access
the .mdb directly, others in my dept can't access the .mdb directly, and by
using the shortcut, we have to log in using the username/password that is in
the workgroup file. Basically, it works!

I'm confused. I'll look at the 2 things you suggested.

Thanks again! - Mike

 

[Michael D.]

Joan (and anyone else that is interested),

It turns out that the security was set up correctly. However, 2 of the PCs
that we tested on had Access installed directly on their hard drive, rather
than the normal "network install" that our corporation has configured on all
PCs. There was some sort of problem launching Office applications on these 2
machines so they were allowed to have Office installed directly as a
"temporary" solution, until their workstations come up for replacement under
the normal rotation.

So, for some reason, it seems that Access security does not work when Access
is installed locally? Has anyone ever heard of this? I have now tested on
at least 20 other PCs in our corporation and the security works each time
just as expected, except on these 2 with Access installed locally. All have
Access 2003. For these 2 PCs, they can just go directly into the secured
database. Everyone else gets the "you are not authorized.." message if they
try to go directly into the database, they have to use the shortcut and log
in to use the .mdb...

Very strage, and makes me wonder how "secure" an Access database really is???

Thanks! - Mike

 

[Rick Brandt]

Joan (and anyone else that is interested),

It turns out that the security was set up correctly. However, 2 of
the PCs that we tested on had Access installed directly on their hard
drive, rather than the normal "network install" that our corporation
has configured on all PCs. There was some sort of problem launching
Office applications on these 2 machines so they were allowed to have
Office installed directly as a "temporary" solution, until their
workstations come up for replacement under the normal rotation.

So, for some reason, it seems that Access security does not work when
Access is installed locally? Has anyone ever heard of this? I have
now tested on at least 20 other PCs in our corporation and the
security works each time just as expected, except on these 2 with
Access installed locally. All have Access 2003. For these 2 PCs,
they can just go directly into the secured database. Everyone else
gets the "you are not authorized.." message if they try to go
directly into the database, they have to use the shortcut and log in
to use the .mdb...

Very strage, and makes me wonder how "secure" an Access database
really is???

Any time the file can be opened like that you did not secure it properly. There
is NO other explanation. Most common is to leave Admin as owner of the
database.

 

[New_Access]

I'm sorry Mr.Michael,
would you like to write me how you did it?
Thank's before

Joan,

I was able to successfully set up security on a couple of databases in my
area, and test them with multiple users on our LAN, including people from
remote geographic sites and the like, no problem.
But the person that I was trying to help (from another part of the
corporation) couldn't get your steps to work. So I went to her building and
went through the steps on a couple of different access databases, including a
little dummy one that I created on the spot. Everything seemed to work, but
for whatever reason, when ever someone other than her tries to open a secured
.mdb (ie, by going directly to the database itself), it lets them zip right
into the database. But when SHE tries to directly access the database, it
tells her she doesn't have permission (like it should for everyone).

The shortcut works fine, everyone can use it and log in using the
username/password combinations we set up in the workgroup file.

Any suggestions on the most likely thing I missed while at her desk that
would allow other users to access the .mdb file directly?

Thanks again! - Mike

need to go into the properties of your shortcut and adjust the paths in the
target to reflect the new location.each to create their own ldb file, and won't be able to if they have the
same name.

 

[New_Access]

I'm sorry Mr.Michael,
would you like to write me how you did it?
Thank's before

Joan,

I was able to successfully set up security on a couple of databases in my
area, and test them with multiple users on our LAN, including people from
remote geographic sites and the like, no problem.
But the person that I was trying to help (from another part of the
corporation) couldn't get your steps to work. So I went to her building and
went through the steps on a couple of different access databases, including a
little dummy one that I created on the spot. Everything seemed to work, but
for whatever reason, when ever someone other than her tries to open a secured
.mdb (ie, by going directly to the database itself), it lets them zip right
into the database. But when SHE tries to directly access the database, it
tells her she doesn't have permission (like it should for everyone).

The shortcut works fine, everyone can use it and log in using the
username/password combinations we set up in the workgroup file.

Any suggestions on the most likely thing I missed while at her desk that
would allow other users to access the .mdb file directly?

Thanks again! - Mike

need to go into the properties of your shortcut and adjust the paths in the
target to reflect the new location.each to create their own ldb file, and won't be able to if they have the
same name.

 

[New_Access]

I'm sorry Mr.Michael,
would you like to write me how you did it?
Thank's before

Joan,

I was able to successfully set up security on a couple of databases in my
area, and test them with multiple users on our LAN, including people from
remote geographic sites and the like, no problem.
But the person that I was trying to help (from another part of the
corporation) couldn't get your steps to work. So I went to her building and
went through the steps on a couple of different access databases, including a
little dummy one that I created on the spot. Everything seemed to work, but
for whatever reason, when ever someone other than her tries to open a secured
.mdb (ie, by going directly to the database itself), it lets them zip right
into the database. But when SHE tries to directly access the database, it
tells her she doesn't have permission (like it should for everyone).

The shortcut works fine, everyone can use it and log in using the
username/password combinations we set up in the workgroup file.

Any suggestions on the most likely thing I missed while at her desk that
would allow other users to access the .mdb file directly?

Thanks again! - Mike

need to go into the properties of your shortcut and adjust the paths in the
target to reflect the new location.each to create their own ldb file, and won't be able to if they have the
same name.