security grouping and passwords

[Nydia]

I want to create a database for a department that has 4-5
different groups (ex. substance abuse department, Drug
Abuse group, alchol abuse group, etc.)

I would like to use 1 database for all groups, but the
alchol group shouldn't be able to see the drug abuse group
information. Is there a way to put a security, so that
when a person enters there password, it will show only
their group information?

Also, we work with several databases, if I put a security
on this database, will it have any affect on the other
databases that are used? I don't want it to prompt of a
password on any other database..

any help is greatly appreciated

 

[Scott McDaniel]

You can do exactly what you want with the builtin security features of
Access. Before doing this, however, do your homework. Access security is a
complex animal, and requires some time and effort to get things right.
Merely running the security wizard isn't enough. You must do certain things
in order to properly secure your database ... and I would encourage you to
read the many, many posts on Google regarding Access security, and to
download and read the MS Access Security FAQ (from the MS website). And
while I am a big believer in Access and the security features of the same,
with the type of data you are dealing with you may wish to consider a more
secure solution like SQL Server.

 

[Jack MacDonald]

I would echo the comments made by Scott -- you can do what you
describe in Access, but you must evaluate the confidentiality
requirements. Access is not as secure an environment as SQL server.

There are two key aspects required to accomplish your task. One is
so-called Read With Owners Permission queries. Using these RWOP
queries, you can deny users the permission to read the tables
directly. Instead, they must use queries for which you can restrict
which records they can see.

The second aspect is the CurrentUser() function. You can use it to
restrict the query to show only the records that (say) were entered by
the same person who is currently running Access. You would probably
want to expand that notion to determine whether the logged-in person
is a member of a specific group, and restrict the records accordingly
in the RWOP query.

Neither of these tasks is trivial, but certainly do-able in Access.

I want to create a database for a department that has 4-5
different groups (ex. substance abuse department, Drug
Abuse group, alchol abuse group, etc.)

I would like to use 1 database for all groups, but the
alchol group shouldn't be able to see the drug abuse group
information. Is there a way to put a security, so that
when a person enters there password, it will show only
their group information?

Also, we work with several databases, if I put a security
on this database, will it have any affect on the other
databases that are used? I don't want it to prompt of a
password on any other database..

any help is greatly appreciated

=======================================================
Jack MacDonald
remove UPPERCASE LETTERS from email address
Vancouver, B.C. Canada
Info about MSAccess user-level security
www.geocities.com/jacksonmacd

 

[Joan Wild]

There are two key aspects required to accomplish your task. One is
so-called Read With Owners Permission queries. Using these RWOP

Why do you always refer to them as 'so-called'?
http://dictionary.reference.com/search?q=so-called

 

[TC]

Why do you always refer to them as 'so-called'?
http://dictionary.reference.com/search?q=so-called

That phrase is useful, IMO, to warn the user: "Hey, I'm going to use a term
that you might not have heard of before".

As opposed to: "Hey, just use Run With Owner Permission queries!", which
tends to imply: " ... you dope!"

TC

 

[Jack MacDonald]

Thanks TC - that's exactly what I meant.
Sorry Joan -- didn't intend to raise a hackle.

That phrase is useful, IMO, to warn the user: "Hey, I'm going to use a term
that you might not have heard of before".

As opposed to: "Hey, just use Run With Owner Permission queries!", which
tends to imply: " ... you dope!"

TC

=======================================================
Jack MacDonald
remove UPPERCASE LETTERS from email address
Vancouver, B.C. Canada
Info about MSAccess user-level security
www.geocities.com/jacksonmacd

 

[Joan Wild]

That phrase is useful, IMO, to warn the user: "Hey, I'm going to use a term
that you might not have heard of before".

I see. I don't think I've heard it used in that context. I always hear it
as meaning "it's of questionable value".

 

[Joan Wild]

Thanks TC - that's exactly what I meant.
Sorry Joan -- didn't intend to raise a hackle.

Huh? Where did you get that idea?