Access 2003: Thinking about going multiuser with user security,...

Any suggestions, knowledge base articles, books? We are not going to
go field level but now we have a couple clients that would like to
limit what employees can see of our application (forms and reports). I
guess we would need to create a database containing user names and
what forms and reports they can access (and then probably going to
have to be responsible for that database too .?

Thanks,

-paulw

Re: Access 2003: Thinking about going multiuser with user security,...

On Thu, 06 Dec 2007 20:39:25 -0700, PW
wrote:

>Any suggestions, knowledge base articles, books? We are not going to
>go field level but now we have a couple clients that would like to
>limit what employees can see of our application (forms and reports). I
>guess we would need to create a database containing user names and
>what forms and reports they can access (and then probably going to
>have to be responsible for that database too .?
>
>Thanks,
>
>-paulw


We most likely will also want to log who made what changes to what
record and when.

Re: Access 2003: Thinking about going multiuser with user security,...

"PW" wrote in message
news:jqfhl3lhr3on45j746q26cl8a1crn18p6c@4ax.com...
> Any suggestions, knowledge base articles, books? We are not going to
> go field level but now we have a couple clients that would like to
> limit what employees can see of our application (forms and reports). I
> guess we would need to create a database containing user names and
> what forms and reports they can access (and then probably going to
> have to be responsible for that database too .?

Any self-implemented security is, at best, "security lite", and more
commonly, "security not at all" because it can be so easily defeated by
someone who knows Access even moderately well. Even though it is "fallible"
(as is, to a great extent, any security system that a penetrator can get
his/her hands on), Access' user/group level security is much less easy to
break. That is still available in Access 2003, and even in Access 2007 for
MDB / MDE databases. It is not available for the Access 2007-specific ACCDB
databases.

Larry Linson
Microsoft Access MVP

Re: Access 2003: Thinking about going multiuser with user security,...

"PW" wrote in message
news:9eghl3po8gkk6o391jed5voinlib1duc67@4ax.com...
> On Thu, 06 Dec 2007 20:39:25 -0700, PW
> wrote:
>
>>Any suggestions, knowledge base articles, books? We are not going to
>>go field level but now we have a couple clients that would like to
>>limit what employees can see of our application (forms and reports). I
>>guess we would need to create a database containing user names and
>>what forms and reports they can access (and then probably going to
>>have to be responsible for that database too .?
>>

There are many issues here and you have a major task on your hands if you
are not familiar with the concept of user level security (ULS). These are
the issues you need to consider:

1. Hide the database window.
2. Disable all startup options.
3. Disable the bypass (shift) key. If you don't then any user can bypass
item 2.
4. Provide custom menus. You will have disabled built-in menus in item 2.
5. Split the database.
6. Produce an mde front end file and give each user their own copy.
7. Apply ULS. This is a complex topic and reading the FAQ (link on my web
site) is a must. You should follow all steps in the order stated and omit
nothing, however trivial it might seem to be. Practice on dummy files in
case you lock yourself out.
8. Apply appropriate permissions at file and folder level.

>
> We most likely will also want to log who made what changes to what
> record and when.

This is a completely different issue to ULS. You will need to create a
table to store your log information and you will need to write code in a
form's After Update event to loop through the controls to determine what has
been changed. Again, this is not a trivial task but I do have some code
that will do the job if you're comfortable with using VBA.

I'll leave you to digest all of this but please do post back with any
specific questions.

Keith.
www.keithwilby.com

Re: Access 2003: Thinking about going multiuser with user security,...

Keith Wilby wrote:
> There are many issues here and you have a major task on your hands if
> you are not familiar with the concept of user level security (ULS).
> These are the issues you need to consider:

All excellent suggestions below. Just a few additional comments based on
the way I've set up my multi-user apps. (One caveat: my comments pertain
to Access 2003 and below. I haven't started working with Access 2007...I
prefer to let others spend about the next year or so finding all the
bugs and rabbit holes and posting them to this board.)

1. Personally, I prefer to do #6 first: split the file into front end
(app only) and back end (data only).

2. Next, you're going to want some modules in the app that run on
startup (via an AutoExec macro). These modules verify that the path from
the front end to the back end is valid. If the path is not valid for
some reason (i.e., the back end was moved, or the directory structure on
the server was modified, happens all the time), it raises a File Open
dialog that allows the user to point the app to the correct path.

Modules to do this were available in the Access 97 and Access 2000
Developer's Handbooks, were used extensively, are still running reliably
in several apps I've build, and are probably available in many other
sources. For another possible example, see Chapter 31 of this book:

http://www.microsoft.com/mspress/books/toc/5600a.aspx

3. After these two steps, you'll want to immediately create a backup
copy of the front end (and probably back end as well), but mainly the
front end. Then you can turn off most or all of the startup options.

This is especially important with regard to disabling the bypass key.
Once you've disabled this option, it's no longer possible to get back
into the design environment by holding down the Shift key and then
pressing Enter or double clicking the icon (well, that's not entirely
true, but re-enabling the bypass key takes some doing and you'll really
want to practice that a lot before deploying an app with that feature
turned off.)

4. Definitely disable all the common menus. If you use any menus at all,
just use ones you've created yourself that only allow the user to do
certain specific things.

5. Read through some tutorials about User Level Security and walk
through the ULS Wizard a bunch of times. Then do some more reading and
research. Creating a workgroup security file that's specific to one
particular application, and does not affect all Access sessions on a
machine, is a little tricky.

6. For deployment, create an mde file of the application (be sure to
read all the help file documentation on this and practice a
lot...another good reason to create a backup copy of the app file after
splitting). Also consider putting the mdw (workgroup security file) on a
protected shared directory so you don't have multiple copies of that
security file floating around all over the place.

Just my 8 bits worth.

Logging transactions is a big task that suggests that Access might not
be your best solution. "Before/After" transaction logging is something
that's handled much better in SQL Server. In fact, for enterprise-level
solutions, you might want to consider going with a VB/SQL Server
solution anyway.

> 1. Hide the database window.
> 2. Disable all startup options.
> 3. Disable the bypass (shift) key. If you don't then any user can
> bypass item 2.
> 4. Provide custom menus. You will have disabled built-in menus in item 2.
> 5. Split the database.
> 6. Produce an mde front end file and give each user their own copy.
> 7. Apply ULS. This is a complex topic and reading the FAQ (link on my
> web site) is a must. You should follow all steps in the order stated
> and omit nothing, however trivial it might seem to be. Practice on
> dummy files in case you lock yourself out.
> 8. Apply appropriate permissions at file and folder level.
>
>>
>> We most likely will also want to log who made what changes to what
>> record and when.
>
> This is a completely different issue to ULS. You will need to create a
> table to store your log information and you will need to write code in a
> form's After Update event to loop through the controls to determine what
> has been changed. Again, this is not a trivial task but I do have some
> code that will do the job if you're comfortable with using VBA.
>
> I'll leave you to digest all of this but please do post back with any
> specific questions.
>
> Keith.
> www.keithwilby.com

Re: Access 2003: Thinking about going multiuser with user security,...

Fester Bestertester previously wrote:
> 5. Read through some tutorials about User Level Security and walk
> through the ULS Wizard a bunch of times. Then do some more reading and
> research. Creating a workgroup security file that's specific to one
> particular application, and does not affect all Access sessions on a
> machine, is a little tricky.

Here's a good MS FAQ page on creating a security file that is specific
to a particular database or application (I believe this also applies in
Access 2003):

http://support.microsoft.com/default.aspx?scid=%2fsupport%2f access%2fcontent%2fsecfaq.asp#_Toc493299688

Re: Access 2003: Thinking about going multiuser with user security,...

On Fri, 7 Dec 2007 09:29:30 -0000, "Keith Wilby"
wrote:

>"PW" wrote in message
>news:9eghl3po8gkk6o391jed5voinlib1duc67@4ax.com...
>> On Thu, 06 Dec 2007 20:39:25 -0700, PW
>> wrote:
>>
>>>Any suggestions, knowledge base articles, books? We are not going to
>>>go field level but now we have a couple clients that would like to
>>>limit what employees can see of our application (forms and reports). I
>>>guess we would need to create a database containing user names and
>>>what forms and reports they can access (and then probably going to
>>>have to be responsible for that database too .?
>>>
>
>There are many issues here and you have a major task on your hands if you
>are not familiar with the concept of user level security (ULS). These are
>the issues you need to consider:
>
>1. Hide the database window.
>2. Disable all startup options.
>3. Disable the bypass (shift) key. If you don't then any user can bypass
>item 2.
>4. Provide custom menus. You will have disabled built-in menus in item 2.
>5. Split the database.
>6. Produce an mde front end file and give each user their own copy.
>7. Apply ULS. This is a complex topic and reading the FAQ (link on my web
>site) is a must. You should follow all steps in the order stated and omit
>nothing, however trivial it might seem to be. Practice on dummy files in
>case you lock yourself out.
>8. Apply appropriate permissions at file and folder level.
>
>>
>> We most likely will also want to log who made what changes to what
>> record and when.
>
>This is a completely different issue to ULS. You will need to create a
>table to store your log information and you will need to write code in a
>form's After Update event to loop through the controls to determine what has
>been changed. Again, this is not a trivial task but I do have some code
>that will do the job if you're comfortable with using VBA.
>
>I'll leave you to digest all of this but please do post back with any
>specific questions.
>
>Keith.
>www.keithwilby.com

Thanks guys. Oh boy. Seems like quite the project. We've only had
two clients out of a hundred plus ask for this so I don't know if it
will be worth the effort. Thanks again for all the information! I
bookmarked Keith's website for sure!

-paulw

Re: Access 2003: Thinking about going multiuser with user security,...

Fester Bestertester wrote in
news:fjc221$np0$1@gondor.sdsu.edu:

> All excellent suggestions below. Just a few additional comments based
> on the way I've set up my multi-user apps. (One caveat: my comments
> pertain to Access 2003 and below. I haven't started working with
> Access 2007...I prefer to let others spend about the next year or so
> finding all the bugs and rabbit holes and posting them to this board.)
>
> 1. Personally, I prefer to do #6 first: split the file into front end
> (app only) and back end (data only).
>
> 2. Next, you're going to want some modules in the app that run on
> startup (via an AutoExec macro). These modules verify that the path
> from the front end to the back end is valid. If the path is not valid
> for some reason (i.e., the back end was moved, or the directory
> structure on the server was modified, happens all the time), it raises
> a File Open dialog that allows the user to point the app to the
> correct path.
>
> Modules to do this were available in the Access 97 and Access 2000
> Developer's Handbooks, were used extensively, are still running
> reliably in several apps I've build, and are probably available in
> many other sources. For another possible example, see Chapter 31 of
> this book:
>
> http://www.microsoft.com/mspress/books/toc/5600a.aspx
>
> 3. After these two steps, you'll want to immediately create a backup
> copy of the front end (and probably back end as well), but mainly the
> front end. Then you can turn off most or all of the startup options.
>
> This is especially important with regard to disabling the bypass key.
> Once you've disabled this option, it's no longer possible to get back
> into the design environment by holding down the Shift key and then
> pressing Enter or double clicking the icon (well, that's not entirely
> true, but re-enabling the bypass key takes some doing and you'll
> really want to practice that a lot before deploying an app with that
> feature turned off.)
>
> 4. Definitely disable all the common menus. If you use any menus at
> all, just use ones you've created yourself that only allow the user to
> do certain specific things.
>
> 5. Read through some tutorials about User Level Security and walk
> through the ULS Wizard a bunch of times. Then do some more reading and
> research. Creating a workgroup security file that's specific to one
> particular application, and does not affect all Access sessions on a
> machine, is a little tricky.
>
> 6. For deployment, create an mde file of the application (be sure to
> read all the help file documentation on this and practice a
> lot...another good reason to create a backup copy of the app file
> after splitting). Also consider putting the mdw (workgroup security
> file) on a protected shared directory so you don't have multiple
> copies of that security file floating around all over the place.

Many developers do almost none of the above, with the exception of
splitting their applications.
I am one of them.

--
lyle fairfield

Re: Access 2003: Thinking about going multiuser with user security,...

lyle fairfield wrote:

> Many developers do almost none of the above, with the exception of
> splitting their applications.
> I am one of them.

Hmmm...raises an interesting point for discussion. Seems that at the
very least I'd want to deploy the application with some kind of failsafe
mechanism, so that if the back end data file got moved, the front end
had some reasonably elegant way of restoring the links without requiring
developer intervention. At the most, it would require 1 minute of time
from a system administrator who knew the correct path for the backend
and could point the app there. That's probably why the Verify Links
modules have been adopted so widely. Why re-invent the wheel when it's
already out there?

As far as the other stuff goes, setting up a specific security file for
that app might make for an inconvenience for the users, since they have
to remember a separate username and password for the application. But I
can't imagine allowing users to go in and monkey around with the design
of the app. So it seems that, again at the very least, you'd want to
turn off all or most of the startup options.

Also, one other caveat. I recall seeing a lot of issues a few years back
when developers tried to deploy a split front/back database, but where
there were multiple versions of Access installed on the various
workstations, especially 97 vs. 2000. What I learned from that was that,
as a general rule, it's a good idea to migrate everybody altogether from
one version of Office to the next before deploying an application. Just
to be on the safe side.

Have you really deployed split apps with no security and no front
end/back end verification and no startup options turned off? No troubles
with that?