Microsoft Access 2007 Application Question

[mrs_schmitz]

I have a database that I am developing for a client, and I have a few
requirements that I have never really dealt with before. The first
requirement is to have some sort of security in the database which
will allow the client to control what a user can and cannot view
without having to return to the developer. I believe I have come up
with a simple solution for this requirement, which involves tables to
keep track of the security information. Where I am having my issue is
with the second requirement. Basically the client wants to be able to
archive a years worth of data, have a current year worth of data, and
be able to switch between the two. For example, my client would like
to create a back-end file for 2007, and work with only 2008 data. If
required, however, the client wants to be able to access the 2007 data
from one front end without having to exit. The back end structure for
the databases would be the same, so I believe this can be handled by a
code routine to re-link the front end to a different back end of data.

My issue with both scenarios is how do I develop a security system for
the database that won't be determined or housed by the back end of the
data. My idea was to come up with another database that would house
the security tables and the location of the archived back-end
databases. When the user is prompted to sign in to the database, the
system would check the security database for the permissions and load
the current back-end database.

Is this a feasible approach to my issues with the requirements, or is
there another solution that I am not considering?

Thanks for any help or comments.

 

[Jeff Boyce]

That sounds like a "lot of moving parts", succeptible to breaking...

Archiving doesn't always require separate databases. In many cases, the
real "requirement" is that the "archived" information not be visible. You
can accomplish this by adding a Date/Time field ([DateArchived]) and
changing your queries feeding your forms (and reports) to filter out any
records with [DateArchived] values before today's date (i.e., using the
Date() function).

Security is a relative term. Are you likely to be faced with inadvertant
misadventures? That is, folks ending up somewhere they don't know how to
handle and messing something up they shouldn't have touched? If so, there
are user interface approaches that can accomplish this without resorting to
imposed security.

Are you likely to be facing malicious hackers intent on destroying (or
subtly modifying) your data? If so, it's a matter of degree ... no matter
how seriously you try to shut folks out, there's someone with too much time
on his/her hands who'll figure out a way around your security. So, how
serious do you want to get?

On the other hand, a simpler way to keep the bad folks out is to limit to
whom you give the front-end application. If everyone is located in-house,
and you know where they work, do you have as much of a security concern?

Just a few thoughts on the questions you raise. I hope you get the answers
you need on this (I expect other folks to chime in on this topic).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

Where I am having my issue is
with the second requirement. Basically the client wants to be able to
archive a years worth of data, have a current year worth of data, and
be able to switch between the two. For example, my client would like
to create a back-end file for 2007, and work with only 2008 data. If
required, however, the client wants to be able to access the 2007 data
from one front end without having to exit. The back end structure for
the databases would be the same, so I believe this can be handled by a
code routine to re-link the front end to a different back end of data.

I'm with Jeff on this one. Just "hide" the data. It's too much of a
PITA having table and queries specific by years.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[mrs_schmitz]

That sounds like a "lot of moving parts", succeptible to breaking...

Archiving doesn't always require separate databases.  In many cases, the
real "requirement" is that the "archived" information not be visible.  You
can accomplish this by adding a Date/Time field ([DateArchived]) and
changing your queries feeding your forms (and reports) to filter out any
records with [DateArchived] values before today's date (i.e., using the
Date() function).

The database is being developed for a private school, and they want to
be able to delete students who have graduated or no longer attend the
school for the current school year. This also applies to any faculty
members that might have left the school, but the school wants to be
able to access the records for previous years in case they need the
information for a particular student or faculty member. The only
solution I could come up with was to create archived back-end
databases with the previous school year information. I think, though,
that your solution is a better route to go but would need a few
modifications for this databases particular needs.

Security is a relative term.  Are you likely to be faced with inadvertant
misadventures?  That is, folks ending up somewhere they don't know how to
handle and messing something up they shouldn't have touched?  If so, there
are user interface approaches that can accomplish this without resorting to
imposed security.

Are you likely to be facing malicious hackers intent on destroying (or
subtly modifying) your data?  If so, it's a matter of degree ... no matter
how seriously you try to shut folks out, there's someone with too much time
on his/her hands who'll figure out a way around your security.  So, how
serious do you want to get?

On the other hand, a simpler way to keep the bad folks out is to limit to
whom you give the front-end application.  If everyone is located in-house,
and you know where they work, do you have as much of a security concern?

The security issue is to prevent certain users from having access to
particular data in the system. Most of it can be controlled by
limiting the access of the user to view particular forms. Where it
gets complicated is when the user has access to a form that has
multiple information, but the user is only allowed to see portions of
the information on the form. Luckily this information is contained on
tabs on the form, so I believe it is a simple approach to stating
whether or not the user has view priveleges to that tab. Another
requirement is that the client wants a simple way to modify the
security of the system (i.e. roles for the user, and the privileges)
without having to come back to me each time. I think I have an idea
as to what needs to be done, but I am always open to suggestions from
other developers.

Just a few thoughts on the questions you raise.  I hope you get the answers
you need on this (I expect other folks to chime in on this topic).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you again for the information you provided about the archiving
of the database back-end. It has made me think of a few other issues
that I don't believe that the client or myself has thought of. It is
just amazing how a simple database has blown up into a full fledged
application that has really expanded my knowledge of Access.