Hi Chad,
I'm not an Access expert, but let me provide a *few*
thoughts about Access Security.
Security in an Access database can probably be broken down
into two big categories: home-grown security or Access
User Level Security. A "home-grown" system is something a
developer creates themseleves to control access to various
aspects of a database. User Level Security is using
the "built-in" security features available in Access. They
each have their advantages and disadvantages. User Level
Security, incidentially, is always being used in EVERY
database, it is just well *hidden* until activated to its
full potential.
Now some may diasagree with me, but I believe there is a
time and place for using either security system. A BIG but
coming here....as long as you FULLY understand the
limitations of both models. Yes, even User Level Security
has some limitations, but FAR less than anything made up.
Here are some advantages of using a home-grown system:
1. Can be VERY quick and easy to set up.
2. Can be just about as complex as you want it to be.
3. Can be very easy to maintain.
4. Can sometimes be all that is really needed.
5. You can pat yourself on the back for making it. <g>
Please note that each sentence starts with the word "can."
This is not always the case however! Some may argue the
points about being easier than User Level Security, but I
can guarantee that every one of those people has been
using that model for a LONG time and know it inside and
out! For a beginner, it can be a daunting task.
Here are some advantages of using User Level Security:
1. A developer can have complete control over EVERYTHING
in the database.
2. The developer can *properly* secure all objects in the
database.
3. Permissions can be assigned object by object.
Now let's get to the disadvantages! The achilles heel of
an Access database when you get right down to it is
securing DATA. Code can easily be secured by converting to
an MDE file. Forms and reports can easily be duplicated by
seasoned developers just by looking at someone else's
work. But, DATA is what it all comes down to. If someone
wants to try and "crack" into your database, why would
they? Well, about 99% of the time it's because they want
to look at your DATA. Period. And this is where home-grown
systems will fail virtually every time. No matter how
sophisticated a home-grown system is, any really good
Access developer could probably get to the data faster
than you can say, "Would you like fries with that data?"
With User Level Security you can control access to the
data tables themselves. When you get right down to it,
this is what it's all about and that's why home grown
systems will always be inferior. Now if you really want to
get technical, even properly secured Access databases can
be cracked into by true hackers because they have access
to the file. For the best database security you need to
look at SQL or Oracle which is server-based. For the sake
of argument let's forget about the full time hackers.
Yes, there are programs out there that can easily read
user information stored in MDW files. Which begs the
question, why take all the time to use User Level Security
if someone can get log in information so easily?! Well, if
you REALLY follow all the steps in the Security FAQ
(discussed in the next chapter further down) and create a
distribution MDW then that information can be pretty
useless for getting at everything.
OK, so why would you want to use home grown systems in
various situations? Well, the best answer I can give is to
ask yourself two questions:
What type of data are you trying to protect?
How savvy are your users?
With non critical and/or non personal data, full blown
User Level Security may not really be needed. Also, if you
have real low-tech users it may not be warranted to take
the more secure route. So I believe each situation needs
to be evaluated independently. As long as you fully
understand the pros and cons of home grown systems than
those may suit you just fine.
I work mostly with VERY low tech users. I'm talking about
people with trouble telling the difference between left
click and right click! For these people, making my own
security is WAY more than I probably need. I could
probably just hide the desktop icon and they would have no
clue how to open it!! So I do have experience with setting
up some home brewed security as well as User Level
Security.
Now that we've got that covered let me offer just a few
examples of ways to secure things.
1. Probably the simplest way to protect a form would be to
ask for a password in the click event of a command button
using the InputBox function. Something like this:
' Code Start
Private Sub cmdOpenForm_Click()
On Error GoTo ErrorHandler
If InputBox("Please enter the Administrative " _
& "password to gain access to this form.", _
"Enter Password") <> "password" Then
' The entered password was incorrect
MsgBox "Sorry, the password you have " _
& "entered is incorrect." & vbNewLine _
& "Please contact a Database Administrator.", _
vbExclamation, "Access Denied"
Else
' The entered password was correct
' Open the protected form
' Then close this form
DoCmd.OpenForm "frmProtectedForm"
DoCmd.Close acForm, "frmSampleForm"
End If
ExitPoint:
Exit Sub
ErrorHandler:
MsgBox err.Description, vbCritical, "Error #" &
err.Number
Resume ExitPoint
End Sub
' Code End
This is quick and easy. Making an MDE file will protect
the password, but anytime you want to change it, you have
to make another MDE. Also, anyone that needs this form
will have to know the password. You can't get more
detailed than that. In addition, by using the InputBox,
you can't hide the password as you type it.
I have a sample database showing this option in 97 or 2000.
2. A slightly more advanced approach would be to make an
unbound form that has a text box with an Input Mask set
to "Password" to hide the password as it is typed. This
way someone looking over a shoulder may not see exactly
what the password is. A command button on a form would
open this special password form first. If the correct
password is entered, then the "protected" form would open.
Otherwise, they can't open it. You can also add code to
help stop someone from opening the protected form directly
from the Database Window.
I have a sample database showing this option in 97 or 2000.
3. There is a Microsoft KB article demonstrating a method
to password protect a form or report using a neat KeyCode
function. You can find that here:
http://support.microsoft.com/?id=209871
http://support.microsoft.com/?id=179371
This still requires that everyone who needs to open this
form have access to that password. I took this a step
further and designed custom forms that you can use to
easily add/edit/delete these KeyCodes in the password
table. You can modify all the entries quite easily without
having to open the table directly at all.
I have a sample database showing this option in 97 or 2000.
4. To get even more advanced you could create a table of
users and user levels. Each user would need to log into
the database and you could code each form's Open event to
check their user level. Using this technique you can make
different forms/reports available to different groups.
More coding and administrative work, but a little more
control over individual users. You can also save records
with the name of the person currently logged in.
I have a sample database showing this technique using 97
or 2000. You can create as many security levels as you
wish and it's real easy to maintain in my opinion. I also
incorporated the KeyCode technique from the above
mentioned KB articles in this sample to further deter
snoopers looking at passwords. This type of system is what
I use on my low tech users. It's MORE than adequate for
their experience level!
There are countless other possibilities for creating
something of your own. You're only limited by your
imagination.
Let me know if you would like any of these samples.
Unfortunately I do not have a web site where I can just
place these for download some place. :-((
5. Your absolute BEST protection is to implement full
blown User Level Security with all the bells and whistles.
To study up on this I suggest the following materials:
-Download the Security FAQ here (the Security Bible):
http://support.microsoft.com/?kbid=207793
-Download Jack Macdonald's Security Document:
http://www.geocities.com/jacksonmacd/AccessSecurity.html
Read Lynn Trapp's Ten Security Steps:
http://www.ltcomputerdesigns.com/Security.htm
I also found the security chapter in the Access
Developer's Handbook very useful:
http://www.developershandbook.com
Read ALL this material. Sleep on it. Read it again. Then
practice on some trial databases until you're really
comfortable with it. Always keep lots of backups. The
Security Wizard in all versions seems to always miss an
important step or two so that's why it may be better to do
this on your own. It is a challenge and you will most
likely make mistakes along the way, but keep at it! If
you're running into problems there is a great newsgroup
devoted to this subject here:
microsoft.public.access.security
Spend time there! You will get to know Joan Wild, Mr. Lynn
Trapp, and many other helpful MVPs and non-MVPs
personally! They will help you avoid the many pitfalls
that people find out later seeing that their database
actually isn't really secure! An unasked question is an
unanswered one.
I would also recommend doing the following steps to
further secure your database:
1. Disable the Shift key bypass option. Details here:
http://www.mvps.org/access/general/gen0040.htm
2. Create custom menu and toolbars for your forms and
reports so the users only have access to things they
really need. A good read on this subject can be found here:
http://www.microsoft.com/Accessdev/articles/bapp97/chapters
/ba01_6.htm
(Watch out for line wrapping on that link)
It is written for Access 97, but the same basic concepts
apply to later versions as well.
3. Hide the Database Window.
4. Set limited Startup options.
5. Distribute only MDE files so no design changes can be
made and no one messes up your code.
Well I hope that helps a little with your security issues.
Good luck,
Jeff Conrad
Bend, Oregon
