Password Protecting Forms

[ChuckW]

Hi,

I am trying to password protect a form. I read an
article from the Knowlegebase called How to Password
Protect a Form or Report but don't understand it.

I have a main menu with six command buttons. These are
for five of our sales rep with the sixth being the sales
manager. When you click on a button (cmdAnna) on the
Main_Menu screeen it takes you to a form called
SalesInfoAnna that allows Anna to run reports on her
specific accounts. I want to have a password protection
screen come up for Anna (and all other reps) that asks a
password. The password needs to be different for each
rep.

The knowledgebase article had me create a table with two
fields: ObjectName and KeyCode. I didn't understand
what they were getting at and could not get it to work
with my application.

Can anyone help explain how I can password protect each
form?

Thanks,

Chuck

 

[Jeff Conrad]

Hi,

I'm not an Access expert, but I play one on TV.
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.
Now if someone can just teach me how to post these sample
databases to our company's web site that would be great!


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.
Now if you'll excuse me, I have to go buy another keyboard
since I just wore this one out!!

Good luck,
Jeff Conrad
Bend, Oregon

 

[ChuckW]

Jeff,

Thank you for your very thorough response. I am working
with some very unsophisticated users so I probably don't
need to create a very secure site. I am mainly trying to
prevent the different sales reps from running each others
sales reports. They probably wouldn't even know how to
open a table and view it. I have a main menu with
buttons for each rep. When a person clicks on a button
it takes them to a screen that run their sales reports.
So I need a different password for each rep.

Looking at your note, it seem like the best approach is
to create an intermediate password form for each rep.
When they click on their button on the main menu, they
are directed to their password form which contains a text
box and I guess a command button. I created a form for
one of our reps and then added a text box and set the
input mask to "Password".

Do I set the password by typing it in to the Default
Value under properties? Lets say I type in Red for a
password in this default value line. When The user types
in Red at this text box and hits the command button I
want them to be sent to their individual reports form.
If they don't type Red, I can give them a message asking
them to retype their password.

I tried your suggestion for Number 1 - ask for a password
in the click event. I am not very familiar with VBA code
though. I went into the On Click Event procedure for one
of the command buttons on the main menu and wiped out the
existing VBA code and pasted in yours. It was
highlighted in red the line that states:

MstBox err.Description, vbCritical, "Error #" &

The code didn't run. I am not sure if I was supposed to
wipe out the existing VBA code or insert it in
somewhere. Also I couldn't figure out how to actually
assign a password in the code.

I appreciate your help.

Thanks,

Chuck W

 

[Jeff Conrad]

Hi Chuck,

Comments in-line....

Thank you for your very thorough response.

You don't think I was too brief do ya?
;-)

I am working with some very unsophisticated users so I
probably don't need to create a very secure site.

Fair enough. Sounds like you have given this some thought
which is very wise. As long as you understand the
limitations, a "low-tech" solution should be fine in this
case.

I am mainly trying to prevent the different sales reps
from running each others sales reports.

Easily accomplished.

They probably wouldn't even know how to open a table and
view it.

Sounds like my users!!
(Just had a call this weekend: "I can't enter ANY numbers!"
Ok, is your Number Lock key on? "Oh, does it need to be?"
That would be a Yes.)

I have a main menu with buttons for each rep. When a
person clicks on a button it takes them to a screen that
run their sales reports.
So I need a different password for each rep.

Gotcha.
The KeyCode technique in the KB article can work in this
situation, but it may be overkill here.

Looking at your note, it seem like the best approach is
to create an intermediate password form for each rep.
When they click on their button on the main menu, they
are directed to their password form which contains a text
box and I guess a command button. I created a form for
one of our reps and then added a text box and set the
input mask to "Password".

This sounds like an easy option to set up, but it may
require creating a separate password "form" for each rep.
It can be done many, many other ways (including one form),
but it does require more coding.

Do I set the password by typing it in to the Default
Value under properties?

No, I wouldn't.

Lets say I type in Red for a password in this default
value line. When The user types in Red at this text box
and hits the command button I want them to be sent to
their individual reports form. If they don't type Red, I
can give them a message asking them to retype their
password.

Ok your plan is sound, we'll just fill in the details
below.

I tried your suggestion for Number 1 - ask for a password
in the click event. I am not very familiar with VBA code
though. I went into the On Click Event procedure for one
of the command buttons on the main menu and wiped out the
existing VBA code and pasted in yours. It was
highlighted in red the line that states:

MstBox err.Description, vbCritical, "Error #" &

The code didn't run. I am not sure if I was supposed to
wipe out the existing VBA code or insert it in
somewhere.

Ahhhh the dreaded line wrapping!
Two things, there is a typo in the code you typed. The
first part should be MsgBox not MstBox.

Second, this line:

MsgBox err.Description, vbCritical, "Error #" &
err.Number

....should all be on ONE line in the code window. Just
adjust it in the code window and all should be fine.

Also I couldn't figure out how to actually assign a
password in the code.

All right, let's do this:

1. Create a new unbound form and name it frmPassword

2. Add a text box to the form and call it txtPassword. Go
to the Properties area for this text box and
enter "Password" (with no quotes) on the line that says
Input Mask. This will hide the test as it is typed.

3. Add a label above the text box and call it lblPassword.
Set the caption of this label to "Please enter password
for access to this form." (Put whatever you want, but make
sure no quotes)

4. Add a command button called cmdCloseForm. Have the
caption be "Close Form." In the Click event of this button
copy/paste this code:
(Shouldn't have any line wrapping problems here)

Private Sub cmdCloseForm_Click()
On Error GoTo ErrorHandler

' Close the form
DoCmd.Close acForm, "frmPassword"

ExitPoint:
Exit Sub

ErrorHandler:
MsgBox err.Description, vbCritical, _
"Error #" & err.Number
Resume ExitPoint

End Sub

5. Add another command button to the form called cmdOK.
Set the caption to be "OK". In the click event of this
button copy/paste this code:
(I chose colors as the passwords to follow your example.
Replace with whatever passwords you need)

Private Sub cmdOK_Click()
On Error GoTo ErrorHandler

If IsNull(Me.txtPassword) Then
MsgBox "Please enter a password " _
& "before continuing.", _
vbExclamation, "Missing Password"
GoTo ExitPoint
End If

Dim EnteredPassword As String

EnteredPassword = Me.txtPassword

Select Case EnteredPassword
Case "Red"
' Code here to open that
' person's report and/or form
DoCmd.OpenForm "frmRed"
DoCmd.Close acForm, "frmPassword"
Case "Orange"
' Code here to open that
' person's report and/or form
Case "Yellow"
' Code here to open that
' person's report and/or form
Case "Green"
' Code here to open that
' person's report and/or form
Case "Blue"
' Code here to open that
' person's report and/or form
Case "That funky bright yellow " _
& "on the new Oregon Duck's uniform"
' Code here to open that
' person's report and/or form
Case Else
MsgBox "The password you have " _
& "entered is invalid. Please " _
& "call Chuck.", vbExclamation, _
"Invalid password"
End Select

ExitPoint:
Exit Sub

ErrorHandler:
MsgBox err.Description, vbCritical, _
"Error #" & err.Number
Resume ExitPoint

End Sub

Now you will have to "fill in some of the blanks." I'm not
sure what exactly you wanted to do. The first example just
lists the possibility of opening a form called frmRed and
then closing the password form. You could easily have it
open a report as well. Let me know if you need further
help on that.

6. Now on the Main Menu form, just code each of the
various command buttons to open this Password form. Like
so:

DoCmd.OpenForm "frmPassword"

This way each time any of the buttons are pressed they
will be presented with this password form on top of the
main menu. If they do not enter a correct password, their
particular form/report will not open.

7. I would also "dress up" the password form by removing
any navigation buttons, record selectors, Min/Max buttons,
etc.

I hope this is enough to get you started.

I appreciate your help.
Thanks,

No problem, glad to help.
Jeff Conrad
Bend, Oregon