More security questions

[BruceM]

I have studied a number of articles and documents about user-level security
(Access 2003). I finally decided to use Joan Wild's step-by-step
instructions to implement security, then I read Jack MacDonald's paper to
learn more about what I had just done. I may be ready to look through the
MS FAQ again.

It seems to have worked in my limited test, but I have a few questions.

1) From what I can tell, when I go to User and Group permissions and view
group permissions, one of the items that can be checked is "Administer". I
expect this is to administer user accounts, assign ownership if needed,
clear passwords, and all that sort of thing. Is that correct?

2) I have created a number of test users, all of whom have the same password
and the default PID (that 20-character string of letters and numbers). The
idea is that I will create shortcuts for individual users so that clicking
on the shortcut fills in that person's user ID. To do that I am using this
as the target in the shortcut:
"path to msaccess.exe" "path to the mdb file" /user UserName /wrkgrp "path
to secure mdw file"
I realize that a separate shortcut for each user is an administrative hassle
on the one hand, but there are some advantages such as the username always
being correct on that user's computer. The number of users is small enough
that it should work OK. I can change the strategy if need be. Anyhow, my
question is about a strategy for deploying the shortcuts, and specifically
about forcing the users to choose a password.

3) In a related question, I know that I can clear a user password if it is
forgotten, but then the shortcut above opens the database without any
security at all. Again, how can I force the user to select a password?

4) In a split database, I'm still trying to sort out when I assign
permissions. Do I assign permissions to objects, then split the database,
or do I assign permissions for tables in the back end and other objects in
the front end, or what? I'm still having trouble getting a conceptual
handle on this aspect of security.

5) I would like to have the username appear in records at times. Are spaces
in the user name OK, or is it like spaces in field names, which can create
extra work down the road?

6) If I assign permissions to groups, but there is one user with a unique
set of permissions, can I assign permissions to that user independent of the
group? Even if it is possible, is it advisable, or should I create a custom
group for that one user?

7) Why would I as the developer want to change ownership of database
objects? It is possible, I know, but I can't quite imagine why unless maybe
on a large project with several developers.

 

[Joan Wild]

1) From what I can tell, when I go to User and Group permissions and
view group permissions, one of the items that can be checked is
"Administer". I expect this is to administer user accounts, assign
ownership if needed, clear passwords, and all that sort of thing. Is
that correct?

Modify permissions on that object. A person needs to be a member of the
Admins Gruop in order to administer user accounts, clear passwords. Also
it's the owner who can change ownership (as well as the owner of the
database object)

2) I have created a number of test users, all of whom have the same
password and the default PID (that 20-character string of letters and
numbers).

There is no 'default' PID; also I don't understand why you'd give everyone
the same password.

The idea is that I will create shortcuts for individual
users so that clicking on the shortcut fills in that person's user
ID. To do that I am using this as the target in the shortcut:
"path to msaccess.exe" "path to the mdb file" /user UserName /wrkgrp
"path to secure mdw file"

OK, you can do that. However without the /user switch, Access will
automatically fill in the last Access username that opened a secure mdb on
that computer. It's only the first time that it'll use the user's Windows
login name. So I'm not sure there is any advantage to doing this.

Anyhow, my question is about a strategy for
deploying the shortcuts, and specifically about forcing the users to
choose a password.

I would deploy without a password set. You can check that the password
isn't blank; if it is, then give them a form to set their password. Also
you can then send everyone the same shortcut (assuming they have installed
Access to the same folder, and installed the frontend to the same location).

4) In a split database, I'm still trying to sort out when I assign
permissions. Do I assign permissions to objects, then split the
database, or do I assign permissions for tables in the back end and
other objects in the front end, or what? I'm still having trouble
getting a conceptual handle on this aspect of security.

One can do it either way.
1. - split first - just be sure you use the same secure mdw to secure both
the FE and BE
2. - secure first - be sure you don't use the splitter wizard as that will
result in an unsecure BE. There's a page on my site explaining how to split
manually (which is very easy to do).

I generally give only Open Permission on the BE database object. All data
interaction is done via RWOP queries; users then don't need any permission
on the backend tables.

5) I would like to have the username appear in records at times. Are
spaces in the user name OK, or is it like spaces in field names,
which can create extra work down the road?

In this case you can use CurrentUser() function to retrieve the Access
username. Since this is data (as opposed to a field name), spaces are just
fine

6) If I assign permissions to groups, but there is one user with a
unique set of permissions, can I assign permissions to that user
independent of the group? Even if it is possible, is it advisable,
or should I create a custom group for that one user?

You can. I always use groups; as soon as you think there is only one user
in a group, you'll find another user that needs to be a member.

7) Why would I as the developer want to change ownership of database
objects? It is possible, I know, but I can't quite imagine why
unless maybe on a large project with several developers.

The main thing is that neither the Users Group, nor the Admin User own
anything. These two entities are common to every mdw. So if either owns
something, then anyone with Access (i.e. they'd be using system.mdw) could,
as owner, do anything with the object.

 

[BruceM]

Modify permissions on that object. A person needs to be a member of the
Admins Gruop in order to administer user accounts, clear passwords. Also
it's the owner who can change ownership (as well as the owner of the
database object)


There is no 'default' PID; also I don't understand why you'd give everyone
the same password.

When I create a user the PID is filled in with a 20-character string. I
called it the default PID, but "automatically generated" would probably have
been more accurate. Regarding the password, I was testing security by
"pretending" to be different users with different permissions. To do that I
used the same password just so it would be easy to remember for the testing.
I'm ot sure why I bothered to add the detail about the password, as it could
only have muddied the waters.
Is there any value to using something other than the automatically generated
PID?

OK, you can do that. However without the /user switch, Access will
automatically fill in the last Access username that opened a secure mdb on
that computer. It's only the first time that it'll use the user's Windows
login name. So I'm not sure there is any advantage to doing this.

I once attempted to deploy a front end by e-mail, but could neither send the
front end or link to it because of security settings in Microsoft Exchange.
I renamed it as a text file, and send it in an e-mail that started with (in
all caps) "Do not try to open the file from the e-mail", followed by
instructions for saving and renaming the file. Sure enough, I got calls
saying "When I tried to open the file I just got a lot of gibberish." There
are a number of people here who are very good at their jobs, but struggle
with things like this.
The trouble is that a number of computers are shared. On those computers
there may need to be a user-specific shortcut. Without it people would
double click the shortcut, add their passwords (regardless of the user
name), and then complain that the database doesn't work. It will not occur
to them to check the user name.

I would deploy without a password set. You can check that the password
isn't blank; if it is, then give them a form to set their password. Also
you can then send everyone the same shortcut (assuming they have installed
Access to the same folder, and installed the frontend to the same
location).

Perhaps I can start with the same shortcut for everybody, then modify it as
the circumstances demand. For a single-user computer there shouldn't be a
problem.

My main question regarding user passwords is how to get a user to enter a
password. The only way I can find is to log on as the user, then navigate
to User and Group Accounts by way of the menu (or I suppose I could have an
icon). Also, I would need either to rely on the user to navigate to the
appropriate dialog box, or I would need to go to each workstation, navigate
to the password dialog, and leave the user to enter a password.
You say that I can check that the password isn't blank, but the only way I
can figure out how to do that is to log on as that user. Is there another
way of doing that? Regarding giving the user a form, I'm not sure what you
mean by that. Are you talking about having them tell me the password so
that I can enter it for them, or are you talking about an Access form, or
something else? I'm sorry if I'm being dense about this, but I just don't
get how to do this.
A person's signature on a document is unique unless somebody is skilled at
forgery. I am looking for a way to simulate that assurance of uniqueness in
Access. As the Administrator I can clear a user's password and log in as
that user. Since I will also be using the database, it may be necessary to
set up an administrator who can only administer accounts, but not work
directly with data.
When my e-mail account and network logon were implemented there was a dialog
box asking for the password. Without completing the information I could
never have logged in. I get a similar sort of thing from time to time when
I need to change the password. Is there a way of producing the "You can't
go any further without a password" dialog in Access, or must it be a manual,
station-by-station process?

One can do it either way.
1. - split first - just be sure you use the same secure mdw to secure both
the FE and BE
2. - secure first - be sure you don't use the splitter wizard as that will
result in an unsecure BE. There's a page on my site explaining how to
split manually (which is very easy to do).

I generally give only Open Permission on the BE database object. All data
interaction is done via RWOP queries; users then don't need any permission
on the backend tables.

I will try again to comprehend the MS FAQ. I must say it is really too bad
that such important information has not been updated in over six years. I'm
not sure how much to believe (for instance, it is among the very few
documents I have read that mention without any caveats using a database
password), but in any case there is a discussion of RWOP queries which
should be of value now that I know a little more about how the security
works.

In this case you can use CurrentUser() function to retrieve the Access
username. Since this is data (as opposed to a field name), spaces are
just fine


You can. I always use groups; as soon as you think there is only one user
in a group, you'll find another user that needs to be a member.


The main thing is that neither the Users Group, nor the Admin User own
anything. These two entities are common to every mdw. So if either owns
something, then anyone with Access (i.e. they'd be using system.mdw)
could, as owner, do anything with the object.

I have already banished the Admin user to the Users group, and removed all
permissions from the Users group, thanks in large part to your instructions
and reinforced by other things I have read (Jack MacDonald's paper in
particular). I really appreciate all of your help with this. I could not
have done it using only Microsoft's documentation.

 

[Joan Wild]

Is there any value to using something other than the automatically
generated PID?

No that's fine. I didn't realize you were using the wizard. If you want to
add users later, you'd do so via Tools, security, accounts, and there you
have to come up with your own PID.

The trouble is that a number of computers are shared. On those
computers there may need to be a user-specific shortcut. Without it
people would double click the shortcut, add their passwords
(regardless of the user name), and then complain that the database
doesn't work. It will not occur to them to check the user name.

I would use /user "" in the shortcut instead - that'll pop up the login with
no username filled in and force them to enter one - then you only need a
single shortcut for all.

You say that I can check that the password isn't blank, but the only
way I can figure out how to do that is to log on as that user. Is
there another way of doing that? Regarding giving the user a form,
I'm not sure what you mean by that. Are you talking about having
them tell me the password so that I can enter it for them, or are you
talking about an Access form, or something else?

I'm talking about using an Access form to change passwords, and using code
to determine if their password is blank.

You'd create an unbound form with three textboxes and a command button. The
three textboxes are for the user to enter their username (or have it default
to CurrentUser()), their old password, and their new password (maybe even
another textbox to confirm the new password). There is sample code in the
security FAQ for how to change a password.

As for checking if it's blank, you can do this in on startup (in your main
form, or have a hidden form that opens at startup and checks this. You
could attempt to open a DAO workspace in code using the CurrentUser() for
the username, and a blank password.

If the password is wrong (i.e. they have a password set), you can trap the
error.

If you get no error then you'll know it is blank, and can throw up a nice
message, and then open your change password form.

Dim ws as Workspace
Set ws = DBEngine.CreateWorkspace("tempws", CurrentUser(),"")
If Err=0 then
'they have a blank password
'open a change password form
DoCmd.OpenForm "frmChangePassword"
Else
'they don't have a blank password
'do nothing
End If

Since I will also be
using the database, it may be necessary to set up an administrator
who can only administer accounts, but not work directly with data.

That is also covered in the security FAQ. You'd need to create a second mdw
for production, while keeping your first secure mdw for development. Look
in the FAQ.

I will try again to comprehend the MS FAQ. I must say it is really
too bad that such important information has not been updated in over
six years. I'm not sure how much to believe (for instance, it is
among the very few documents I have read that mention without any
caveats using a database password), but in any case there is a
discussion of RWOP queries which should be of value now that I know a
little more about how the security works.

I've also got some info at www.jmwild.com/RWOP.htm

I have already banished the Admin user to the Users group, and
removed all permissions from the Users group, thanks in large part to
your instructions and reinforced by other things I have read (Jack
MacDonald's paper in particular).

That may not be sufficient. If you used the wizard in 2002 or 2003, then
you're likely OK. However just double-check that Admin is not the owner of
the Database Object (or any other object either). Even without any
permissions, the 'owner' trumps all. The owner can always take over.

 

[BruceM]

No that's fine. I didn't realize you were using the wizard. If you want
to add users later, you'd do so via Tools, security, accounts, and there
you have to come up with your own PID.


I would use /user "" in the shortcut instead - that'll pop up the login
with no username filled in and force them to enter one - then you only
need a single shortcut for all.
Cool!


I'm talking about using an Access form to change passwords, and using code
to determine if their password is blank.

You'd create an unbound form with three textboxes and a command button.
The three textboxes are for the user to enter their username (or have it
default to CurrentUser()), their old password, and their new password
(maybe even another textbox to confirm the new password). There is sample
code in the security FAQ for how to change a password.

Thanks for pointing that out. I think I found it in the part about clearing
the password.

As for checking if it's blank, you can do this in on startup (in your main
form, or have a hidden form that opens at startup and checks this. You
could attempt to open a DAO workspace in code using the CurrentUser() for
the username, and a blank password.

If the password is wrong (i.e. they have a password set), you can trap the
error.

If you get no error then you'll know it is blank, and can throw up a nice
message, and then open your change password form.

Dim ws as Workspace
Set ws = DBEngine.CreateWorkspace("tempws", CurrentUser(),"")
If Err=0 then
'they have a blank password
'open a change password form
DoCmd.OpenForm "frmChangePassword"
Else
'they don't have a blank password
'do nothing
End If

Thanks for providing the code example and the sketch of the form I would
use. Again, more study is needed, but I think I can figure it out. A lot
of new information has been added to my brain in the past few days, and it's
taking a while to incorporate it all.

That is also covered in the security FAQ. You'd need to create a second
mdw for production, while keeping your first secure mdw for development.
Look in the FAQ.

I saw something about that in Jack MacDonald's article as well. I'll need
to study that some more. I think I understand that only members of the
Admins group may manage user accounts. Permissions are stored in the
database (mdb) file, so when Workgroup File 1 has the same user and group
information as Workgroup File 2, the database will "think" that it is the
same group of users, and all permissions will continue to apply. The
difference is that Workgroup File 2 does not have I still need to mull this
over for a while, but as I will be administering security at first (for
databases in which security is not that big an issue) I don't need to worry
about it right away.
By the way, creating duplicate users and groups seems like a good reason not
to use the PIDs generated by the wizard.

I've also got some info at www.jmwild.com/RWOP.htm

Thanks for that link, and for the referring me back to the FAQ. One of the
problems with the FAQ is that there are parts I clearly can skip (about
Access 2.0, for instance), but sometimes I skip too far. This is another
concept I expect I will understand in time. My early grasp of it is that
for somebody to create a query they need permission to the underlying
tables. A RWOP query can let users sort of borrow those table permissions
from the query owner without actually needing permission to the tables.

One thing I'm still trying to sort out has to do with permissions. As I
understand it, a user who is a member of two groups will have the least
restrictive permissions. If one group can add data only, and another can
add or edit but not delete data, the user will be able to add and edit data.
What I can't quite sort out is whether this "least restrictive" rule applies
to users as well. In the example just given, if the user is given
permission to delete data, but that permission is not allowed any group of
with that user is a member, will the user be able to delete data anyhow? I
think so, because the least restrictive permission, whether it is a user
permission or a group permission, "wins". Users don't need any permissions
at all as long as they belong to groups that have permissions. Do I
understand correctly?

That may not be sufficient. If you used the wizard in 2002 or 2003, then
you're likely OK. However just double-check that Admin is not the owner
of the Database Object (or any other object either). Even without any
permissions, the 'owner' trumps all. The owner can always take over.

Admin owns nothing.

Joan, thanks again for all of your help. I don't think I could have done
gotten this far, at least not anywhere near this quickly, without it. If
you have a chance to answer the few lingering questions, that would be
great, but I think that I have enough to keep me busy for a while. I have
undertaken several times to learn user-level security, but this is the first
time I feel like I might actually be getting it.

 

[Joan Wild]

I saw something about that in Jack MacDonald's article as well. I'll
need to study that some more. I think I understand that only members
of the Admins group may manage user accounts. Permissions are stored
in the database (mdb) file, so when Workgroup File 1 has the same
user and group information as Workgroup File 2, the database will
"think" that it is the same group of users, and all permissions will
continue to apply.

Exactly. But since mdw2 has a different Admins Group than mdw1, they won't
have the same permissions. So mdw2 Admins Group will be able to manage
users that is: create/delete users and add/delete them from groups, but
won't have any other permissions.

One thing I'm still trying to sort out has to do with permissions. As I
understand it, a user who is a member of two groups will have
the least restrictive permissions. If one group can add data only,
and another can add or edit but not delete data, the user will be
able to add and edit data.
Right.

What I can't quite sort out is whether
this "least restrictive" rule applies to users as well. In the
example just given, if the user is given permission to delete data,
but that permission is not allowed any group of with that user is a
member, will the user be able to delete data anyhow?

Yes they will.

I think so,

because the least restrictive permission, whether it is a user
permission or a group permission, "wins". Users don't need any
permissions at all as long as they belong to groups that have
permissions. Do I understand correctly?
Yes.

Joan, thanks again for all of your help. I don't think I could have
done gotten this far, at least not anywhere near this quickly,
without it. If you have a chance to answer the few lingering
questions, that would be great, but I think that I have enough to
keep me busy for a while. I have undertaken several times to learn
user-level security, but this is the first time I feel like I might
actually be getting it.

You're welcome; it sounds to me like you are "getting it". It takes time,
but when it clicks it makes sense.

 

[BruceM]

Joan,

This response showed up in a different thread than the one in which you
intended to post it.

 

[Joan Wild]

?? Not in my newsreader.

 

[Lynn Trapp]

Bruce,
Actually, Joe G interjected his question intoyour original thread and all
the posts between he and Joan were not showing up yesterday. They are all
appearing today. Some problem with the synchronization on the servers.

 

[Joan Wild]

Are you able to log in as *any* user at all?

 

[Joan Wild]

After all that, couldn't you tell us how you resolved it?

 

[Joan Wild]

The Admin user should not have any permissions on the database at all. The
Admin user is common to all mdw files, including system.mdw that ships with
Access. If that user had permission, then anyone with Access using the
standard system.mdw would have permissions.

The Users Group and the Admin user are common to all mdw files. The Admins
Group is not.

Hope that helps (and don't be embarrassed)

 

[BruceM]

There's nothing wrong with joining an existing thread, especially if you
have questions or comments about a specific point in the thread, but your
question is more likely to be seen by many eyes if it is its own thread. In
this thread your question was seen for sure by Joan Wild and Lynn Trapp.
When either or both of them see your question there is an excellent chance
that you will receive sound and insightful advice, but unless your topic is
very closely related to the topic in the existing thread you increase your
chances by starting a new thread.