user level security wizard

[Debbie S.]

I just tried to use the user level security wizard.
It created the .bak copy, and I could see the path it was in, but now that
file is nowhere to be found; in addition, when I try to open the database, I
get the message "you do not have the necessary permissions to use the '(file
path and name of database here)' object. Have your system administrator or
the
person who created this object establish the appropriate permissions for
you." The person who created the object is me. I was already assigned as the
admin automatically. I must have missed something. Luckily I tried this in a
copy so I can throw this out and try again on another copy, which is what I
will have to do. Not sure if this is related, but what is the difference
between creating user level access that requires a log on, and creating user
level access that does not require a log on? What did I miss? Any
suggestions? Thank you.
Debbie

 

[Joan Wild]

Debbie,

The wizard created a desktop shortcut for your secure mdb. Use that to start it. You are getting the message because you are attempting to open your secure mdb while joined to the standard system.mdw that ships with Access. If your mdb is secured properly, you shouldn't be able to open it using system.mdw. The desktop shortcut uses the /wrkgrp switch, which tells Access to use the secure mdw and not your system.mdw.

The bak copy is a backup of your mdb before security is applied; it doesn't point to any path. If you want to start over, you'd delete the secure mdw, and your secure mdb and then rename the bak file to have a mdb extension, and you'd be back to square one.

 

[Debbie S.]

I found the shortcut. I clicked on it to open it and got the message:
Microsoft office Access couldn't find the file (file path here), except that
after the name of the database there was an extra slash followed by "se." THe
message continued with, "This file is required for startup." I have not
renamed or changed anything anywhere, so I don't understand what this message
means. The folder with all the files related to this database contains the
following files (I'll call the original database "database X):

Database X.mdb
Database X_be.mdb (this was created when I split the original database)
Database X_2007-11-01.mdb (this was created when I made a backup of the
database using the access utility)
Copy of database X_2007-11-01.mdb (I made this copy of the backup myself)
Copy of database X_2007-11-01.bak (this was created when I tried to use the
security wizard)
Copy of database X_2007-11-01.snp (I believe this is the report with all the
workgroup information)
Security.mdw (this was created with the security wizard)

I was testing this all out so I employed the wizard on the copy of the
backup.

What is a workgroup file?
What does "system.mdw" mean?
What is the difference between front end database and back end? Someone
tried to explain this to me once but I still don't understand it.
When I split a database, do I employ the user level access and permissions
on the front end or the back end? Does it matter? When you are trying to
allow other users to use the database on a shared network drive, which
database goes in the shared drive folder--the front end copy or the back end
copy? If it is the front end copy, then is it correct that that is the copy
in which you would want to employ the security wizard and create the user
level access and permissions? I have read your step by step instructions on
your web site and part of the PDF explaining access security on
www.geocities.com/jacksonmacd. I am thoroughly confused. Am I correct in my
understanding that windows automatically assigns the user name on the pc's
system as the "admin?" Should I have assigned "admin" to a group that allows
the most permission possible? I did not quite understand what Jackson was
talking about, as far as the difference between admin and super user. He
seems to be opposed to the admin as a user. I don't understand why.

I'm sorry to throw all of these questions at you but this is unbelievably
confounding. I would be grateful for any explanations you could provide.

Thank you,
Debbie

 

[Joan Wild]

Access security is hard to master (as you are finding out). More in line...

--
Joan Wild
Microsoft Access MVP

I found the shortcut. I clicked on it to open it and got the message:
Microsoft office Access couldn't find the file (file path here), except that
after the name of the database there was an extra slash followed by "se." THe
message continued with, "This file is required for startup." I have not
renamed or changed anything anywhere, so I don't understand what this message
means. The folder with all the files related to this database contains the
following files (I'll call the original database "database X):

The shortcut target would have the following in it:
"path to msaccess.exe" "path to secure mdb" /wrkgrp "path to secure mdw"

all on one line, and each path would be in double quotes as shown above.
path to msaccess.exe - it depends on the version of Access you are using, but this would be the path to the msaccess.exe file on your computer
path to secure mdb - this would be the path to Copy of database X_2007-11-01.mdb file according to your list below
path to secure mdw - this would be the path to the mdw file that the wizard created for you; from your list it would be the path to security.mdw

What is in the target of your shortcut?

Database X.mdb
Database X_be.mdb (this was created when I split the original database)
Database X_2007-11-01.mdb (this was created when I made a backup of the
database using the access utility)
Copy of database X_2007-11-01.mdb (I made this copy of the backup myself)
Copy of database X_2007-11-01.bak (this was created when I tried to use the
security wizard)
Copy of database X_2007-11-01.snp (I believe this is the report with all the
workgroup information)
Security.mdw (this was created with the security wizard)

I was testing this all out so I employed the wizard on the copy of the
backup.

What is a workgroup file?

This is the mdw file that holds usernames, groups, group membership, passwords.

What does "system.mdw" mean?

Every session of Access uses a mdw file (even for unsecured databases). It ships with a mdw file named system.mdw and it uses this for all sessions. System.mdw contains a user called 'Admin' and two groups - Admins and Users. Every mdw file has the Admin user and the Users Group in common (that is why you want to remove permissions from these two entities in order to secure a mdb).

What is the difference between front end database and back end? Someone
tried to explain this to me once but I still don't understand it.

The backend database contains just the tables/relationships. The frontend contains all the other objects; in addition it would contain 'linked tables' - meaning it contains links to the tables in the backend. This allows you to put the backend on a file server that all the users have access to. They each would have a copy of the frontend on their PC, and so any changes/additions they make to the data would be stored in the backend database.

When I split a database, do I employ the user level access and permissions
on the front end or the back end?

Usually on both

Does it matter?

Depends on your needs.

When you are trying to
allow other users to use the database on a shared network drive, which
database goes in the shared drive folder--the front end copy or the back end
copy?

You'd put the backend on the server, giving everyone a copy of the frontend on their PC, as explained above. You'd likely keep a copy of the frontend on the server, if only for backup purposes (and also for ease of distribution to users). No one would actually use the frontend on the server though.

If it is the front end copy, then is it correct that that is the copy
in which you would want to employ the security wizard and create the user
level access and permissions?

You generally want to secure both the frontend and the backend.

I have read your step by step instructions on
your web site and part of the PDF explaining access security on
www.geocities.com/jacksonmacd. I am thoroughly confused. Am I correct in my
understanding that windows automatically assigns the user name on the pc's
system as the "admin?"

No, 'Admin' is the default user that comes with Access (it isn't a Windows username) in the system.mdw. This is a user that you can't delete, however when you secure the mdb, you want to ensure this user doesn't own any objects, and ensure that it doesn't have any permissions to anything. The same is true for the Users Group. This is because these two are common to all mdw files.

I'm sorry to throw all of these questions at you but this is unbelievably
confounding. I would be grateful for any explanations you could provide.

No problem; security is confounding (at first). Post back with what is in the target of your shortcut, so we can figure out why it isn't working.

 

[Debbie S.]

Joan,
Thank you for your time and attention to my onslaught of questions.

In answer to your question:

My shortcut file path does not look anything like the one you outlined
(“path to msaccess.exe†“path to secure mdbâ€/wrkgrp “path to secure mdwâ€).
When I click on the shortcut on the desktop, the error message box shows the
following path:

C:\DocumentsandSettings\system-username-here\MyDocuments\nameof-folder-containing-the-database-here\name-of-database-here\Se

Note: the name of the database in this shortcut was not the copy, it was the
original, which is even more confusing, because I did not do anything with
the original, all of this was done on the copy, which has the word “copy†in
the name of the database.

Then it says: This file is required for startup.

A few clarifying questions:

1) You said that every mdw file has two groups, Admins and Users. In
addition, you said that every mdw file has the admin user and the Users Group
in common. Do I understand correctly from this that you are making a
distinction between the Admin group and the Admin user. Furthermore, you said
that it is necessary to remove all permissions from the admin user and the
Users Group. Is this correct? Lastly, is the admin user stored in the Admin
Group or the Users Group?

2) If it is necessary to remove all permissions from the Users Group, then
where in the wizard do you tell it who the users are, if not in the “users
group.â€

3) If I understood you correctly regarding front end/back end, then you are
saying that the back end needs to be on the server or in the shared drive
folder, so that changes made in the front end copies that are on individual
PC’s will automatically be saved in the back end. Is that correct? (Is there
a difference between putting a back end database file on a server versus a
shared drive folder on a network—if in a shared drive folder, would the
individual front end copies have to be in the same folder, or would you have
to link each front end copy on each pc to the file path of the back end in
the shared folder? What I don’t understand is, if I put a back end copy in
the shared folder, how will I distribute the front end copies to each user,
and how will the front and back ends be linked if the front ends are on
individual pc’s and the back end is in a shared folder on the network?)

4) Regarding where to employ the user level access and permissions—do I
understand correctly that you would use the wizard on both the front end and
the back end, individually (making sure to put the same users and levels of
access as on both) ? Or if you employ the wizard just on the back end should
it automatically be saved on the front end? Or vice versa?

5) I had asked if the windows user name becomes the “admin†because when I
was working through the wizard, I noticed that my user name (for my pc) has
been automatically included in the user group. (I think it was the user
group). I got the two confused; I understand now that there is a user called
admin. Is it typical for access to take your system user name and put it in
the users group automatically? If so, do I want to put that user in a
different group (since I would be removing permissions from the User Group)
or do I just want to create a different user name for myself for this
database? If you work with several different databases, is it advisable to
use the same user name for your self in each one, or use different ones, or
does it not matter?

I really appreciate the time you took to answer my questions. Thank you for
your help, I look forward to hearing from you.

Debbie

 

[Joan Wild]

Joan,
Thank you for your time and attention to my onslaught of questions.

In answer to your question:

My shortcut file path does not look anything like the one you outlined
(“path to msaccess.exe†“path to secure mdbâ€/wrkgrp “path to secure mdwâ€).

So what does it look like? That is the source of your problem.

When I click on the shortcut on the desktop, the error message box shows the
following path:

C:\DocumentsandSettings\system-username-here\MyDocuments\nameof-folder-containing-the-database-here\name-of-database-here\Se

Note: the name of the database in this shortcut was not the copy, it was the
original, which is even more confusing, because I did not do anything with
the original, all of this was done on the copy, which has the word “copy†in
the name of the database.

But the error message you got I thought was in reference to the mdw file, not the mdb. Therefore 'copy of' doesn't enter into it. Is the message really cut off like that? That too could be part of the problem. The target is just too long.

Then it says: This file is required for startup.

A few clarifying questions:

1) You said that every mdw file has two groups, Admins and Users. In
addition, you said that every mdw file has the admin user and the Users Group
in common. Do I understand correctly from this that you are making a
distinction between the Admin group and the Admin user.

Note that it's the Admins group not the Admin group (there's a 's').

Every mdw has two groups
Admins
Users
and one user
Admin
The Admin user and the Users Group is the same in every mdw file. The Admins Group however is different in different mdw files.

Furthermore, you said
that it is necessary to remove all permissions from the admin user and the
Users Group. Is this correct?

Yes, and the security wizard does this for you.

Lastly, is the admin user stored in the Admin
Group or the Users Group?

Every user (Admin included) is a member of the Users Group - you can't remove a user from the Users Group.

This is why when you secure a mdb, you want to remove permissions from the Users Group and the Admin user.

2) If it is necessary to remove all permissions from the Users Group, then
where in the wizard do you tell it who the users are, if not in the “users
group.â€

During the wizard, you are given an opportunity to choose the groups you want. Then you create your users, and put them in the groups that you want.

This is the disadvantage of using a wizard, when you don't understand what it is doing. All the wizard does is a series of steps for you. Everything it does, you could do yourself manually (and perhaps in doing so, you'd understand better).

3) If I understood you correctly regarding front end/back end, then you are
saying that the back end needs to be on the server or in the shared drive
folder, so that changes made in the front end copies that are on individual
PC’s will automatically be saved in the back end. Is that correct?
Yes

(Is there
a difference between putting a back end database file on a server versus a
shared drive folder on a network

same thing

—if in a shared drive folder, would the
individual front end copies have to be in the same folder, or would you have
to link each front end copy on each pc to the file path of the back end in
the shared folder? What I don’t understand is, if I put a back end copy in
the shared folder, how will I distribute the front end copies to each user,
and how will the front and back ends be linked if the front ends are on
individual pc’s and the back end is in a shared folder on the network?)

The best thing to do is:
1. put the backend in the shared folder on the network
2. open your copy of the frontend
3. Tools, Database Utilities, Linked table manager
4. Put a check in the box at the bottom, Select All, click OK
5. In the next dialog locate the backend by using My Network Places (don't find it via the mapped drive letter).

This will ensure that the table links use what's called the UNC path to the backend file.
Next copy your frontend mdb to the shared drive. Then copy from the shared drive to each person's computer. You won't have to relink, as the links in the frontend will still point to the correct location of the backend.

4) Regarding where to employ the user level access and permissions—do I
understand correctly that you would use the wizard on both the front end and
the back end, individually (making sure to put the same users and levels of
access as on both) ? Or if you employ the wizard just on the back end should
it automatically be saved on the front end? Or vice versa?

The users/groups would be the same for both. You'd just need to be sure that you use the same mdw file when you secure the backend. You'd open the frontend via the shortcut (once you get it working properly). Then hit Ctrl-O and open the backend. Then you can run the wizard, paying carefull attention to the information at each step - choose to modify the current workgroup file (don't create a new one).

5) I had asked if the windows user name becomes the “admin†because when I
was working through the wizard, I noticed that my user name (for my pc) has
been automatically included in the user group. (I think it was the user
group). I got the two confused; I understand now that there is a user called
admin. Is it typical for access to take your system user name and put it in
the users group automatically?
Yes
If so, do I want to put that user in a
different group (since I would be removing permissions from the User Group)
or do I just want to create a different user name for myself for this
database? If you work with several different databases, is it advisable to
use the same user name for your self in each one, or use different ones, or
does it not matter?

You don't need to create a different username. Yes the wizard picks up your Windows username as a username to put in the mdw. You'd want this user to be a member of the Admins Group, plus any other groups you choose.

 

[Debbie S.]

Joan,
Thank you again. I think I will build a dummy database and try to figure
this all out.

Thanks,
Debbie

 

[Olduke]

Debbie:
Do not enter the dark and uncharted waters of Access security without a guide.
I would recommend:
http://www.geocities.com/jacksonmacd
download the first file – Security Paper by Jack Macdonald. It’s a little
long but it tells you everything you need to know.