Hi Karen,
Here's a past post of mine on this subject which should give you some ideas.
Let me provide my *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 themselves 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, incidentally, is always being
used in EVERY
database, it is just well *hidden* until activated to its full potential.
Now some may disagree 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 to post these on.
5. Here is another place with a sample file:
http://www.angelfire.com/sports/f1pictures/AccQuestion6.htm
6. 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
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.
