Best Strategy for Restricting Certain Fields

[TC]

Hello, I am designing a database that will be used to
generate orders. One of the main tables is specs
(specifications). Management needs full rights to all
fields in the table. Ordering personnel need rights to
update only selected fields and no rights to add or
delete records.

Sounds good so far!

Should I use one table and restrict what
the logged in user can do by code at the form level or
should I split the table in 2 so the ordering people can
edit the info they need to?

Neither. You should use Access "user level security" to impose those
restrictions. It will do everything that you want here. Unfortunately, it is
a considerable learning curve. Start by reading the access security FAQ
about *twent five gazillion times*, & experiment on some test databases.
Expect to take *at least several weeks* learning about it, before you can
even think of trying to do it "for real".

security FAQ:
http://support.microsoft.com/?kbid=207793

security newsgroup:
microsoft.public.access.security

HTH,
TC

 

[Steve Dunn]

Hello, I am designing a database that will be used to
generate orders. One of the main tables is specs
(specifications). Management needs full rights to all
fields in the table. Ordering personnel need rights to
update only selected fields and no rights to add or
delete records. Should I use one table and restrict what
the logged in user can do by code at the form level or
should I split the table in 2 so the ordering people can
edit the info they need to?

Also, I plan to store the database on a network drive. Is
there any way to make it "undeletable" (Win 2000) by
someone snooping around network neighborhood?

thanks

 

[Graham Mandeno]

Hi Steve

First, if you haven't already, you need to read the Access Security FAQ at
http://support.microsoft.com/?id=207793 where you can download it or read it
on line. Then you need to properly secure your database with user-level
security.
Also, as the database is shared, it should be split into a front-end
containing everything except data (one per user) and back-end containing
only data (shared).

You've probably done all this already, so sorry about the unnecessary
lecture

Now, there are two ways to provide different security on different fields.
One is to split the table into two parts, putting the restricted fields in
the second table along with a copy of the primary key, and creating a
one-to-one relationship between the two tables. You can then apply
table-level security to the two tables.

The other method is to remove all permissions from the table, and to create
RWOP (run with owner permissions) queries to access the data. One query
might include all the fields in the table, and another only a few of them.
You then secure the queries so that only the appropriate groups have access
to them.

Incidentally, RWOP queries can also be used to implement row-level security.
For example, if you want users to see only those records that they added,
create a RWOP query with selection criteria:
... where AddedBy=CurrentUser()

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.