Linking tables

[Guest]

I'm working on a database that mutiple groups will use. The data includes
some sensitive salary information that I don't want everyone to see.
Therefore I'm building a table that will be used by HR (which includes salary
level), and a table that includes the fields that the other groups will use.

The other groups will need to see most of the information on the HR tables,
and will also need to add some other fields that HR does not need. Therefore
my thought is that I build two tables, one for HR with all data, and one for
the other groups with their specific fields only. I thought that if I linked
the two using the autonumber key as a primary key, that my reports could read
the unsecure data from the HR table. Do I include duplicate field names in
the non-HR table, or somehow link the tables so that I can build reports
using the non-sensitive fields before locking down the HR table?

I'm afraid that if I try to combine the information all in one table that I
won't be able to secure those fields that are sensitive.

My experience with Access is all of about 1 month, but I've spent the better
part of the last couple of days trying to figure out the best way to
structure this and trying different things. So far it hasn't worked...

Any thoughts or advise would be appreciated. Thanks!

 

[tina]

you don't want to duplicate data between tables (except for a
primary/foreign key field). identify the sensitive data that must be
restricted to one group only. put those fields in a separate table that has
a one-to-one relationship with the "main" table. to show data to HR, link
the two tables in a query, and choose the fields you want to show. to show
data to other groups, do *not* include the "sensitive data" table in the
query; choose the fields you want to show from the main table only.

you need to consider two points:
1) your data is not secure from anyone who has any Access and/or programming
experience, unless you use the build-in Access security. this is no trivial
undertaking. see the link below for more info
http://www.ltcomputerdesigns.com/JCReferences.html#Security
2) there is no way to completely secure an Access database, even using the
built-in security. you need to carefully consider the ramifications to your
company if sensitive data is accessed by unauthorized persons.

hth

 

[Rick Brandt]

I'm working on a database that mutiple groups will use. The data
includes some sensitive salary information that I don't want everyone
to see. Therefore I'm building a table that will be used by HR (which
includes salary level), and a table that includes the fields that the
other groups will use.

The other groups will need to see most of the information on the HR
tables, and will also need to add some other fields that HR does not
need. Therefore my thought is that I build two tables, one for HR
with all data, and one for the other groups with their specific
fields only. I thought that if I linked the two using the autonumber
key as a primary key, that my reports could read the unsecure data
from the HR table. Do I include duplicate field names in the non-HR
table, or somehow link the tables so that I can build reports using
the non-sensitive fields before locking down the HR table?

I'm afraid that if I try to combine the information all in one table
that I won't be able to secure those fields that are sensitive.

My experience with Access is all of about 1 month, but I've spent the
better part of the last couple of days trying to figure out the best
way to structure this and trying different things. So far it hasn't
worked...

Any thoughts or advise would be appreciated. Thanks!

You secure the ONE table and build two RWOP queries. One that includes the
salary data and one that doesn't. Then you just control who has permissions
to which query.

 

[John Vinson]

The other groups will need to see most of the information on the HR tables,
and will also need to add some other fields that HR does not need. Therefore
my thought is that I build two tables, one for HR with all data, and one for
the other groups with their specific fields only. I thought that if I linked
the two using the autonumber key as a primary key, that my reports could read
the unsecure data from the HR table. Do I include duplicate field names in
the non-HR table, or somehow link the tables so that I can build reports
using the non-sensitive fields before locking down the HR table?

The two tables must NOT be linked autonumber to autonumber: you cannot
edit or control autonumbers, so the "dependent" table (the HR private
table) would not link up right.

Instead, if you use an Autonumber field in the main HR table (ok if
you will not be using that number in any human-readable manner) you
can use a Long Integer primary *and* foreign key in the private table.
If you have a unique manually- or automatically-assigned employee
number, use that field for the Primary Key of both tables. When you
join these fields in the Relationships window Access will
automatically deduce that you want a one-to-one relationship. This
security issue is one of the two usual (rare!) reasons to want a one
to one relationship.

I'm afraid that if I try to combine the information all in one table that I
won't be able to secure those fields that are sensitive.

By odd coincidence, Allen Browne posted another solution to this very
problem just today. I'll quote his post in its entirity:

============= Begin Quote from Allen Browne==========================
Access does not have true field-level permissions, but I was recently
asked to set up a database where some fields would be locked and
others not. The idea was to allow the user who created the record to
make changes, or permit changes only while the record was new (say for
one hour), on a field-by-field basis, and let the manager change the
permissions at runtime.

A custom property on the field, made this simple to implement and
maintance-free. Details and downloadable example in:
Field-level Permissions in Microsoft Access
at:
http://allenbrowne.com/ser-55.html

While it is not true security, it is an adequate padlock for many
applications, and will certainly help avoid unintentional alterations.

This question is not asked often, but hopefully the sample will help
some poster searching for a way to do this.
============== End Quote ===========================================


John W. Vinson[MVP]