Passwords & Forms - Access 2000

[Mark Thiel]

I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?

Thanks in advance for any help you can provide.

mark

[MGFoster]

You are describing user-level security. To find out how to set it up
read the Security FAQ at this URL:

http://support.microsoft.com/default...93&Product=acc

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Mark Thiel wrote:

I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?

[Fletcher Arnold]

"Mark Thiel" <mm*****@optonline.net> wrote in message
news:7c**************************@posting.google.c om...

I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?

Thanks in advance for any help you can provide.

mark

If you are *really* concerned about restricting access to the information,
then Access may not be the right tool. If you provide direct access to the
mdb file then it is possible to crack it.

If you are *moderately* concerned about this, you should use Access's
in-built user-level security. This allows you to specify permissions on a
per user or per group level. You would then remove permissions for the
table as a whole, but grant them access to appropriate queries which are set
to RWOP (run with owner's permissions). If you are new to this, it might be
a steep learning curve, but there is plenty written about it. From the FAQ
for this group, you could try this:

Microsoft Access Security FAQ:
http://support.microsoft.com/support.../Q165/0/09.asp

http://download.microsoft.com/downlo...-US/Secfaq.exe
(This is the Security FAQ for *ALL* version of Access)
http://support.microsoft.com/support...ent/secfaq.asp
(On-Line version of the Security FAQ)

If you need *low-level* security to keep out the casual snooper, then you
could do soemthing like this. Go to Tools>Startup and select a form to be a
startup form - also do not display the database window. This form could
have 2 textboxes with username and password and code checks if this is OK
and fills in a combobox listing the reports they can open.

[Keith Wilby]

mm*****@optonline.net (Mark Thiel) wrote:

s it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed?

In addition to the other replies, you might find the beginner's guide to
security on my website useful.

Keith.
www.keithwilby.org.uk

[Mark Thiel]

Thanks for your help. I'll read through the security FAQs for further
help. I am new to developing a database of this magnitute, and am
feeling like I got myself way in over my head. Anyway, I am not too
concerned about the overall security of the database - the users are
not familiar with Access at all so I doubt they could crack it, and,
it wouldn't be the worst thing in the world if someone somehow saw all
of the data. Anyway, so just to make sure I understand you correctly,
once I establish the password for the form, I will be able to restrict
what information is included in the report?

For example, we divide the country into 6 geographic areas. I would
like each area to have its on password. Will I be able to set up
security on a form such that if someone from the Northeast enters
their password, the data in the report will only be from the
Northeast, vs. someone from the Southeast entering a password and only
obtaining info related to the Southeast?

Also, can anyone recommend a good Access "Bible"? Based on what I've
seen in some books, the stuff I need to do is somewhat advanced.

Thanks again for all of your help!





"Fletcher Arnold" <fl****@home.com> wrote in message news:<c1**********@hercules.btinternet.com>...

"Mark Thiel" <mm*****@optonline.net> wrote in message
news:7c**************************@posting.google.c om...

I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?

Thanks in advance for any help you can provide.

mark

If you are *really* concerned about restricting access to the information,
then Access may not be the right tool. If you provide direct access to the
mdb file then it is possible to crack it.

If you are *moderately* concerned about this, you should use Access's
in-built user-level security. This allows you to specify permissions on a
per user or per group level. You would then remove permissions for the
table as a whole, but grant them access to appropriate queries which are set
to RWOP (run with owner's permissions). If you are new to this, it might be
a steep learning curve, but there is plenty written about it. From the FAQ
for this group, you could try this:

Microsoft Access Security FAQ:
http://support.microsoft.com/support.../Q165/0/09.asp

http://download.microsoft.com/downlo...-US/Secfaq.exe
(This is the Security FAQ for *ALL* version of Access)
http://support.microsoft.com/support...ent/secfaq.asp
(On-Line version of the Security FAQ)

If you need *low-level* security to keep out the casual snooper, then you
could do soemthing like this. Go to Tools>Startup and select a form to be a
startup form - also do not display the database window. This form could
have 2 textboxes with username and password and code checks if this is OK
and fills in a combobox listing the reports they can open.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Good "bible" _Access ## Developer's Handbook_ by Litwin, Getz &
Gilbert. Sybex Publishing. The ## represents the Access version
number. Acc2000 has 2 volumes (desktop & enterprise). Haven't seen
any versions for AccXP nor Acc2003.

To restrict users access to certain areas:

Create a table like this:

CREATE TABLE UserAreas (
UserID INTEGER NOT NULL ,
AreaID INTEGER NOT NULL ,
CONSTRAINT PK_UserAreas PRIMARY KEY (UserID, AreaID) ,
CONSTRAINT FK_AreaID FOREIGN KEY (AreaID)
REFERENCES Areas (AreaID)
)

The Primary Key of UserID, AreaID avoids duplicates. The Foreign Key
ensures there is an AreaID in the table Areas before the AreaID can be
added to UserAreas. Change column names as required.

The query/view that shows only the Area data for the users:

SELECT SD.AreaID, <... other cols ... >
FROM UserAreas AS UA INNER JOIN SalesData SD
ON UA.AreaID = SD.AreaID
WHERE UA.UserID = CurrentUser()

If you set up the application to use user-level security the method
CurrentUser() will return the logon name of the current user.

The query will only show Area info where the user has their UserID
associated w/ AreaID in the table UserAreas.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBQDZdYIechKqOuFEgEQLYvgCgn3zIW80OROgVTjwy3z l5aJ8ta/UAmMu5
OeVzLzYHyrugaaG40CHCgoM=
=BdRf
-----END PGP SIGNATURE-----
Mark Thiel wrote:

< SNIP >

....Anyway, so just to make sure I understand you correctly,
once I establish the password for the form, I will be able to restrict
what information is included in the report?

For example, we divide the country into 6 geographic areas. I would
like each area to have its on password. Will I be able to set up
security on a form such that if someone from the Northeast enters
their password, the data in the report will only be from the
Northeast, vs. someone from the Southeast entering a password and only
obtaining info related to the Southeast?

Also, can anyone recommend a good Access "Bible"? Based on what I've
seen in some books, the stuff I need to do is somewhat advanced.

[Diverdon]

http://www.peterssoftware.com/las.zip

I have used the software above to have light security on a multi user
database, give it a try as it is very inexpensive and simple to implement.
In addition the support from the author is first rate.

Don

[Fletcher Arnold]

"Mark Thiel" <mm*****@optonline.net> wrote in message
news:7c**************************@posting.google.c om...

Thanks for your help. I'll read through the security FAQs for further
help. I am new to developing a database of this magnitute, and am
feeling like I got myself way in over my head. Anyway, I am not too
concerned about the overall security of the database - the users are
not familiar with Access at all so I doubt they could crack it, and,
it wouldn't be the worst thing in the world if someone somehow saw all
of the data. Anyway, so just to make sure I understand you correctly,
once I establish the password for the form, I will be able to restrict
what information is included in the report?

For example, we divide the country into 6 geographic areas. I would
like each area to have its on password. Will I be able to set up
security on a form such that if someone from the Northeast enters
their password, the data in the report will only be from the
Northeast, vs. someone from the Southeast entering a password and only
obtaining info related to the Southeast?

Also, can anyone recommend a good Access "Bible"? Based on what I've
seen in some books, the stuff I need to do is somewhat advanced.

Thanks again for all of your help!

It all really depends on whether you can write code or not or whether you
want to learn. Most of the people who post to this group will tell you that
you can't get very far designing databases before you have to start writing
code.

If you worked out how to use user-level security then you would create users
and groups, eg

NorthEastUsers (Bill, Bob, Ben, Brian)
NorthWestUsers (Dave, Derick, Dawn)
.....
.....
Managers (Martin, Mary)
DatabaseDesigners (Mark)

You then have a number of queries like "qryNorthEast", "qryNorthWest",
"qryAllRegions" and assign permissions so that NorthEastUsers can run
qryNorthEast, but managers can run all of the queries including
qryAllRegions. But you, as a member of the DatabaseDesigners group, will be
able to change the design of the queries, reports and tables.

****
My guess is that it would be more useful to learn a bit of code to implement
the low-security option. Mostly on the basis that it's so useful to be able
to write a few lines of code.
The code you write could be as simple as putting something in the report's
open event, like this:

Private Sub Report_Open(Cancel As Integer)

If InputBox("Enter password") <> "secret" Then
Cancel = True
MsgBox "Wrong Password", vbCritical
End If

End Sub

Which is as simple as it gets, however using the Inputbox function would
need to be replaced with something where the user didn't type in plain text
on the screen. However, if you gave access to the mdb file, then anyone who
knew Access could look at the code.
One final thought - many databases are split front and back end. You could
distribute different versions to different people so although they shared
the same data (back end), the reports held in the front end would be
different. At least it is a solution which requires neither coding nor
user-level security.
Fletcher