Copy secured database to include security

  • Thread starter Thread starter OssieMac
  • Start date Start date
O

OssieMac

What do I have to do to get a secured database to work on another computer? I
understand that some parameters that include the mdw file are needed when
opening the database so I guess that I need to know how to set up these
parameters in a desktop icon. I'll provide the following info in case it is
relevant.

I have a split database and managed to set up the security with a lot of
trial and error and frustration plus help from this forum. I found that I
needed to set the security on both Front End and Backend. It is a stand alone
system and therefore the FE and BE are in the same folder together with the
mdw file (Don't know if this is a good idea or not but wanted to keep files
together and named similarly as a suite). The security works fine on my
computer.

However, I tried to copy it to another computer. I thought that I only had
to copy the mdw file with it and place it all in the same folder; but never
so wrong. The application opens without login prompt. I then have an Admin
user that only has User rights because I removed the Admin user from the
Admins group as per instructions. I cannot do anything with it because the
Database Owner and User that I set up do not exist and of course I can't set
any using Admin user because Admin has no rights.

All help will be greatly appreciated.
 
OssieMac said:
What do I have to do to get a secured database to work on another
computer? I
understand that some parameters that include the mdw file are needed when
opening the database so I guess that I need to know how to set up these
parameters in a desktop icon. I'll provide the following info in case it
is
relevant.

I have a split database and managed to set up the security with a lot of
trial and error and frustration plus help from this forum. I found that I
needed to set the security on both Front End and Backend. It is a stand
alone
system and therefore the FE and BE are in the same folder together with
the
mdw file (Don't know if this is a good idea or not but wanted to keep
files
together and named similarly as a suite). The security works fine on my
computer.

However, I tried to copy it to another computer. I thought that I only had
to copy the mdw file with it and place it all in the same folder; but
never
so wrong. The application opens without login prompt. I then have an Admin
user that only has User rights because I removed the Admin user from the
Admins group as per instructions. I cannot do anything with it because the
Database Owner and User that I set up do not exist and of course I can't
set
any using Admin user because Admin has no rights.

If you can open it on another PC without the custom workgroup file then it's
not properly secured, likely you missed one or more steps in securing it.

That said, once you have a secured database then all you need is the custom
workgroup file to open it. Typically you'd use a destop shortcut to open
your db with a target in the format

"Full path to MSACCESS.EXE" "Full path to your FE file.mdb" /wrkgroup "Full
path to your workgroup.mdw"

including the quotation marks.

HTH - Keith.
www.keithwilby.com
 
Thanks Keith. I have now accessed your publication HOW TO SECURE AN MS ACCESS
2003 DATABASE. I have been confused (and nervous) from the start. Got over
the nervous part when I established that I can reinstate from my unsecured
backup when things went wrong.

Some things that I still don't understand.
On my computer where I set up the security, I am able to simply double click
my database FE file in explorer and I get the login prompt but I need all the
detail in a desktop icon to achieve the same thing on another computer even
though the paths are set up identical and the folder where the database
resides is the default Open/Save path for MSAccess on both computers.

In your publication it says "Access defaults to allowing full admin access
to the ‘Users’ group, of which account ‘Admin’ is a member. You must deny
access to the ‘Users’ group otherwise security is useless." I cannot remove
both Users and Admins groups from the Admin. I can remove one or the other
but not both. I have created a name for myself called AdminUser and attached
myself to Admins group with full permissions and I thought that I should be
able to do that.

Thanks again for your help.
 
OssieMac said:
In your publication it says "Access defaults to allowing full admin access
to the ‘Users’ group, of which account ‘Admin’ is a member. You must deny
access to the ‘Users’ group otherwise security is useless." I cannot remove
both Users and Admins groups from the Admin. I can remove one or the other
but not both. I have created a name for myself called AdminUser and attached
myself to Admins group with full permissions and I thought that I should be
able to do that.

You don't want to remove Admin from the Users group. You want to remove
Admin from the Admins group. If you leave Admin as a member of Admins then
there's no security. As well, the Users group should have only permissions
that you want anybody with a copy of MS Access to have. In my case, I
removed *all* permissions from the Admins and Users groups so that anybody
trying to get into the database with Access and a generic workgroup file will
be totally out of luck. Your needs may vary, but that's the safest thing to
do.

Basically, you *must* assign anyone you want to have access to the database
to the Users group (or they won't be able to open the database) *and* to
another user group you have created. The other user group you have created
will have the permissions you want that user to have.

I suggest that you go back to Keith's web site and follow the link to the MS
Access Security FAQ. Download the FAQ, print it off, and read it from end to
end before doing anything else. Then, starting from a copy of your backup,
I'd start from the beginning again, following the FAQ and Keith's article to
the letter.

One last tip... Make sure you create a new database while you're logged on
as AdminUser and then import all of the objects from your database into it.
You want to do that so that AdminUser is owner of all the objects instead of
Admin. If Admin owns the objects, security is compromised. The FAQ (IMHO)
doesn't explain this sufficiently, but Keith's article does.

So, in short, here's what you want:

1. You, as AdminUser, own the database and all objects in it
2. Admin user has a password and is only a member of the Users group
3. The Admins and Users groups have little (if any) permissions
4. All people who have access to the database are members of the Users
group (with little or no permissions) *and* another group you have created
that gives them the permissions you want them to have.
5. You, as AdminUser, are a member of the Users group with little (if any)
permissions *and* another group you have created that has full permissions.

Hope that's been helpful...

Regards, Chris
(Who went through similar agony until he read the FAQ)

P.S. Do NOT assign permissions to users... it makes things too
complicated. Rather, assign permissions to groups and then assign users to
groups. MUCH easier!
 
Chris O''Neill said:
P.S. Do NOT assign permissions to users... it makes things too
complicated. Rather, assign permissions to groups and then assign users
to
groups. MUCH easier!

Seconded!
 
Thanks to both Chris and Keith. I'll try again.

I had done this "You want to remove Admin from the Admins group." and the
following I also understood:
"P.S. Do NOT assign permissions to users... it makes things too
complicated. Rather, assign permissions to groups and then assign users to
groups. MUCH easier!"

What I didn't properly understand was about creating a another user group
and removing the permissions from the default Users group. I did create the
new user group but did not remove the permissions from the default Users
group. I can see how this should work now and should overcome my problem of
not being able to remove the Admin user from the users group.

Will see how I go with the next attempt and pleased to know that I am not
the only one "Who went through similar agony ."

Anyway thanks again for the help. Much appreciated.
 
OssieMac said:
Thanks to both Chris and Keith. I'll try again.

You're welcome! Always glad to be of help, especially since I've received
so much help here!
I had done this "You want to remove Admin from the Admins group." and the
following I also understood:
"P.S. Do NOT assign permissions to users... it makes things too
complicated. Rather, assign permissions to groups and then assign users to
groups. MUCH easier!"

Good! That's a start.
What I didn't properly understand was about creating a another user group
and removing the permissions from the default Users group. I did create the
new user group but did not remove the permissions from the default Users
group.

The problem with leaving permissions assigned to the default Users group is
that anyone with Access can then use a generic workgroup file (i.e. the one
that comes with Access) to gain entry to your database. By remmoving the
permissions from the Users group and assigning them instead to a custom group
you have created, you thwart that entry point.
I can see how this should work now and should overcome my problem of
not being able to remove the Admin user from the users group.

You can't delete the Admin user, nor can you remove Admin from the Users
group... Access insists on having things this way. As well, you cannot
delete the "Admins" and "Users" groups... they're required, too. So, what
you want to do is make sure that the "Admin" user doesn't belong to the
"Admins" group and the "Users" group doesn't have any permissions. Having
either (or both) of those leaves your database wide open to intruders.
Will see how I go with the next attempt and pleased to know that I am not
the only one "Who went through similar agony ."

LOL! I think we *all* have gone through that agony at one time or another.
Frankly, user level secruity is *not* a trivial subject and one wrong move
can negate everything you're trying to do. IMHO, Microsoft didn't make it
any easier by having a default "Admin" user plus an "Admins" group.... it
makes things quite confusing if you're new to all this. Thankfully, this
forum (and others) are available to help us figure it all out.

Btw, I'm learning that getting user level security right is NOT a one-shot
deal. I find that I'll set things up, do some testing or development, and
quickly find that things aren't quite how I want them so I'll make changes.
In the application I'm currently working on, I think I've gone over the
security setup three times now. It's getting closer to what I want, but I'm
sure there'll still be changes as I continue working on it.

So, don't dispair! Read the FAQ, ask questions here, and (eventually)
you'll get it where you want it.
Anyway thanks again for the help. Much appreciated.

Again, you're welcome! Btw, one last comment... As in real life, where if
someone is determined to break into your house they'll probably succeed, user
level security in Access may prevent casual attempts at compromising your
data but someone who's determined to get in probably will get in. So, if
you're data is *really* sensitive, you're better of going to SQL or something
else that's more secure than Access.

Good luck with your quest....

Regards, Chris
 
OssieMac said:
Some things that I still don't understand.
On my computer where I set up the security, I am able to simply double
click
my database FE file in explorer and I get the login prompt but I need all
the
detail in a desktop icon to achieve the same thing on another computer
even
though the paths are set up identical and the folder where the database
resides is the default Open/Save path for MSAccess on both computers.

I don't think anyone answered this part. You are getting a login prompt,
because your computer is joined to the secure mdw as the default one to use
for all sessions. Other computers are still joined to the standard
system.mdw that ships with Access. You can change your default by using the
Workgroup Administrator back to system.mdw, then you too will require a
desktop shortcut to launch the secure mdb.
 
Thanks for your reply Joan. All replies are much appreciated because I am
gathering a little more info all the time.

I get the gist of what you are referring to and it makes sense but how do I
do this "You can change your default by using the Workgroup Administrator
back to system.mdw"
 
OssieMac said:
Thanks for your reply Joan. All replies are much appreciated because I am
gathering a little more info all the time.

I get the gist of what you are referring to and it makes sense but how do I
do this "You can change your default by using the Workgroup Administrator
back to system.mdw"

From the main Access Window, click on Tools --> Security --> Workgroup
Administrator --> Join. Then navigagte to the location of the standard
workgroup file (system.mdw) and select it.

Hope that helps...

Regards, Chris
 
Keith Wilby said:
Oh yeh, better than I did evidently since that DDL argument is news to me
;-)

LOL! Now I'm *really* proud of myself! ;)

Seriously, though, I wasn't aware of the Ddl argument, either, until I
stumbled upon it (I think it was mvpaccess.org) while researching something
else.

Regards, Chris
 
Back
Top