Integrating Access security with user options

[Brian]

I have been using simple tables to store user ID's & passwords and want to
move toward inherent Access DB security to prevent some clever user from
reading/manipulating the user/group tables manually. However, I now have the
disadvantage of having developed security-related needs well beyond simple
user security, such as user-based/user-settable options.

I need a jump start. I don't need full code--just a pointer in the right
direction. Pointers on any or all of the following would be most helpful
(since they are related, I put them all in one post instead of 7 separate
posts):

1. How does one lookup/reference the current user ID from within VBA? For
instance, I record the current user as the user who created or edited a
record in some cases.

2. I enable/disable various controls based on group membership. How do I
determine the current user's membership in a particular group at runtime? How
do I refer to the group in VBA? Right now, I have a global Boolean variable
for each group, and when the user logs on, I set each to True or False,
depending on the user's group membership. Then, I have code such as this in
Form)Open: If GroupAdmins Then Button1.Enabled = True Else Button1.Enabled =
False.

3. How might one attach other options not inherent to Access security to the
user ID. For example, I currently use a UserOptions table that has a
relationship to my Users table, and one of the fields is Yes/No ConfirmQuit
so that each user can decide for himself whether he wants to be required to
confirm when closing the program.

4. I disable all standard menus and replace them with VERY rudimentary ones
(just print options) to prevent users from monkeying with my data using
native Access tools and instead provide delete/add/etc. buttons on forms.
Since this makes Access security options unavailable to all users, how might
one go about managing (adding, deleting, resetting passwords) user security
from a form?

5. I need some form of referential integrity so that the user cannot be
deleted if, for instance, his userID is recorded on a particular invoice or
contract. Right now, I can rely on referential integrity without Cascade
Delete in my users/groups tables, and I do not relish the thought of running
through DCounts of all pertinent tables where User = current user before
allowing deletion.

6. In some cases, I simply deactivate the user account so that it still
exists but the user cannot log in nor can his User ID be attached to new
records.

7. I allow the client to place the front end & back end wherever they like,
and then provide a re-linking tool in the front end. In a new distribution to
a client using Access runtime (2003), how do I package the distribution to
ensure that the path to the workgroup file is set correctly, or is this even
necessary?

 

[Paul Overway]

Answers in-line

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

I have been using simple tables to store user ID's & passwords and want to
move toward inherent Access DB security to prevent some clever user from
reading/manipulating the user/group tables manually. However, I now have
the
disadvantage of having developed security-related needs well beyond simple
user security, such as user-based/user-settable options.

I need a jump start. I don't need full code--just a pointer in the right
direction. Pointers on any or all of the following would be most helpful
(since they are related, I put them all in one post instead of 7 separate
posts):

1. How does one lookup/reference the current user ID from within VBA? For
instance, I record the current user as the user who created or edited a
record in some cases.

Use CurrentUser() function

2. I enable/disable various controls based on group membership. How do I
determine the current user's membership in a particular group at runtime?
How
do I refer to the group in VBA? Right now, I have a global Boolean
variable
for each group, and when the user logs on, I set each to True or False,
depending on the user's group membership. Then, I have code such as this
in
Form)Open: If GroupAdmins Then Button1.Enabled = True Else Button1.Enabled
=
False.

The User object has a Groups collection property. You would need to
interogate the collection to determine whether or not the User in question
is a member of the required group.

3. How might one attach other options not inherent to Access security to
the
user ID. For example, I currently use a UserOptions table that has a
relationship to my Users table, and one of the fields is Yes/No
ConfirmQuit
so that each user can decide for himself whether he wants to be required
to
confirm when closing the program.

You would need to build the table records based on actual users within the
User collection. Aside from that, you could pretty much continue what
you're doing....although you should probably create RWO permission queries
for the user options table to ensure that users don't change anyone's
options other than their own.

4. I disable all standard menus and replace them with VERY rudimentary
ones
(just print options) to prevent users from monkeying with my data using
native Access tools and instead provide delete/add/etc. buttons on forms.
Since this makes Access security options unavailable to all users, how
might
one go about managing (adding, deleting, resetting passwords) user
security
from a form?

You would need to implement an appropriate UI for managing security within
your application. This might be as simple as using the built-in workgroup
security features, but it sounds like you may want to implement something
tailored more to your specific application. You should probably assign
appropriate permissions based on group membership beforehand, and then
implement some feature to create users and assign those users to groups.

5. I need some form of referential integrity so that the user cannot be
deleted if, for instance, his userID is recorded on a particular invoice
or
contract. Right now, I can rely on referential integrity without Cascade
Delete in my users/groups tables, and I do not relish the thought of
running
through DCounts of all pertinent tables where User = current user before
allowing deletion.

If this is really required, the simplest solution for this is not to allow
deletes at all....except an administrator type.

6. In some cases, I simply deactivate the user account so that it still
exists but the user cannot log in nor can his User ID be attached to new
records.

Nothing would prevent you from continuing to do this....except the user
account really must be removed from the workgroup. So, you might have a
table of users as they exist in the workgroup, and if the user no longer
exists in the workgroup, the record for that user gets deactivated.

7. I allow the client to place the front end & back end wherever they
like,
and then provide a re-linking tool in the front end. In a new distribution
to
a client using Access runtime (2003), how do I package the distribution to
ensure that the path to the workgroup file is set correctly, or is this
even
necessary?

Assuming that you do in fact implement workgroup security and do so
correctly the shortcut for your application MUST point to the proper
workgroup file or your users will not be able to open the app. The
requirements for your installer will be more complex than what PDW allows
for, i.e., your users would need to install the back end and the workgroup
along with it, and then separately install the front end and point to the
proper location for the MDW. The simplest solution to this would be to
create a single installer that includes a BE and workgroup file template,
and a setup component of your own design that would:

1. Copy the BE and workgroup template files to a user defined location (f
the files don't already exist)
2. Relink the FE to the BE
3. Create shortcut(s)

You really need to read the Security FAQs in detail and ensure that you
understand allow the concepts and requirements before implementing workgroup
security in your application.

 

[Brian]

Thanks, Paul. Good info. So far, I have not had any users sophisticated (or
curious) enough to get anywhere near creating their own db with a link to the
back end so that they can snoop on other users' passwords. I turn off
AllowBypassKey on the back end with a password-controlled administator access
to turn it on to prevent users from directly opening the back end.

If someone was just a little clever, though, I just don't see any way to
truly secure this stuff without native Access security. I originally thought
that a Password input mask on the table would do it...oops.

 

[Keith]

If someone was just a little clever, though, I just don't see any way to
truly secure this stuff without native Access security.

For the record, Access security is not unbreakable. A hundred quid will buy
you an application that will allow a determined hacker to break it.

Regards,
Keith.
www.keithwilby.com

 

[TC]

Brian wrote:

5. I need some form of referential integrity so that the user cannot be
deleted if, for instance, his userID is recorded on a particular invoice or
contract. Right now, I can rely on referential integrity without Cascade
Delete in my users/groups tables, and I do not relish the thought of running
through DCounts of all pertinent tables where User = current user before
allowing deletion.

I don't understand your concern. Why would anything change in that
regard? Referential integrity is referential integrity, regardless of
whether or how the database is or isn't secured.

HTH,
TC

 

[Brian]

Right now, the UserID exists in a table that has a one-to-many relationship
with the UserID field in the Contracts table, with referential integrity
turned on. If the supervisor attempt to delete a user, the delete-denial
message pops up.

Are you saying there is a way to create that relationship between
Contracts.UserID and the user ID within the workgroup file and enforce
referential integrity across it?

 

[Paul Overway]

There would be only if the UserID matched whatever is used within the
workgroup file. If this is important, it makes sense to ensure that they
match. Which goes back to what I was suggesting earlier in interogating the
Users collection and ensuring that you have a record for each User.

 

[TC]

No, you can't establish /declarative/ referential integrity to
usernames in the workgroup file. But you could do that programatically.

I must not have read the thread closely enough. It sounds like Paul is
on to what you need.

Cheers,
TC

 

[katydid]

Dear Paul,

I tracked this thread (re:Brian/7 points) and thought this may intersect
with problems I am having with my PC?? I have a Gateway Windows 2000
professional OS that had been running on administrator for most the time I've
had it. At the end of last year, a Microsoft driven menu pops up on my screen
and I seem to have buried all of my Outlook contents as well I am not
considered as having administrator rights to my own computer. Could I use
Microsoft Exchange (or similar) Server Mailbox Merge Wizard to extract data
from the mailbox from one server running 2000 and then merge the data with
the current server running mailbox with this tool?
Could you help?