Workgroup Security; Log in as "jsmith" and CurrentUser() returns "Admin"???

[David Altemir]

I just built a database using Access 2003 that I am trying to secure.
I think I've done everything I need to do: I created an account for
myself under the "Admins" group. Two users, "Admin" and I, now enjoy
full administrative privileges for all datatabse objects. I also
entered a password for user "Admin" so as to secure the database.

I also added a third user, "jsmith", under the "Users" group with
restricted access.

Privileges seem to work as expected on my local Windows XP Pro
machine. However, upon moving the .mdb database file and its
"Security" Workgroup Information File to a shared network drive
(running on Windows Server 2003), privileges no longer seem to work
correctly: I log in as "jsmith" and I get full administrative access
even though I set him up as a common user!! CurrentUser() also
returns the current user as "Admin", not "jsmith"!!

Is there something special I need to do when moving my datatasbe to a
network or is the problem internal to the database's security
settings?

-- David

 

[Michael]

Try the following steps (always make a backup of both the .mdb and .mdw
files in advance):
- Move the Admin account to the Users group
- Create a new group (e.g., DBusers) and give it the privileges you would
want the regular users to have
- Move the "jsmith" account to the group DBusers (or whatever name you gave
it); ensure any new user accounts are in that DBusers group, as opposed to
just the Users group
- Remove all privileges from the Users group (this ensures that Admin is
totally incapacitated)

Let us know if this fixed it.

Michael

 

[david epsom dot com dot au]

returns the current user as "Admin", not "jsmith"!!

take a good night's sleep, and have another look at it tomorrow.

If current user shows 'admin', you are NOT logged in as jsmith.

Note that to properly secure a database, you have to CREATE (ie
import) a database using a new, different ADMINS group: otherwise
the database will always be unsecured to /anybody/ who uses the
default admin user in the default admins group in the default
system database.


(david)

 

[David Altemir]

Thanks for your clear instructions ... still isn't working though. I
also told you wrong: I did not even get a prompt to login when
launching the database from a remote machine.

After following your instructions, the current situation is as
follows:

1) I don't get a login prompt when launching the database (which is
located on a network drive) from a remote machine. But I do get the
prompt for the same database when I launch it from my machine!?!

Doesn't that say that this problem has to do with either network
related issues or the way I copied the .mdb and .mwi to the network
drive? Do more files need to be loaded onto the network file server
in order for Access security to take effect?

 

[Rick Brandt]

Thanks for your clear instructions ... still isn't working though. I
also told you wrong: I did not even get a prompt to login when
launching the database from a remote machine.

After following your instructions, the current situation is as
follows:

1) I don't get a login prompt when launching the database (which is
located on a network drive) from a remote machine. But I do get the
prompt for the same database when I launch it from my machine!?!

Doesn't that say that this problem has to do with either network
related issues or the way I copied the .mdb and .mwi to the network
drive? Do more files need to be loaded onto the network file server
in order for Access security to take effect?

Access user-level security is dictated by the workgroup (mdw) used. Which
mdw file is used is NOT controlled by the mdb file you are opening. In
other words, even though you secured the mdb file "A" with mdw file"B",
this does not mean that workgroup "B" is automatically used when you
attempt to open mdb "A". You have to specify mdw "B" prior to opening mdb
file "A".

You do this either by making "B" your default workgroup file using the
workgroup administrator utility (not recommended as it affects all mdbs
opened afterwards), or by specifying the mdw file as a command line
argument when opening the mdb. The syntax is roughly...

"Full path to Access executable" /wrkgrp "full path to mdw" "full path to
mdb"

IMPORTANT NOTE!!!
If you can open the mdb using any mdw other than the one used to secure it
then it IS NOT secured properly. You should have been denied access
entirely instead of having the file open without a login prompt.

You cannot use "Admin" as a user at all. This account must have zero
permissions, be removed from the Admins group, and have zero ownership.
Otherwise any user with any mdw file will be able to access your file since
any non-secure mdw logs the user in silently as the user "Admin".

 

[DUNDAPANJ]

I don't think anyone has an answer for you. I've posed
this question several times and worded it differently
every time thinking that i wasn't clear enough, but i
haven't gotten an answer that works.

 

[david epsom dot com dot au]

also told you wrong: I did not even get a prompt to login when

launching the database from a remote machine.

You need to carefully read the security faq:
http://support.microsoft.com/support/access/content/secfaq.asp

and follow the instructions mechanically.

There is nothing wrong with what you have done: you just haven't
done it right :~)

The instructions are complex, but it is all fairly simple when
you have done it once.

(david)

 

[Michael]

For details, see the steps listed in MS KB Article:
http://support.microsoft.com/default.aspx?scid=kb;[LN];289885

I remember having a similar problem in the past, and I was able to fix it
using these steps, so it may work for you as well..

Michael