locking one field

  • Thread starter Thread starter jamccarley
  • Start date Start date
J

jamccarley

I have a quality database with a field for a managment approval. Is it
possible to lock a field so that only certain people can access it? Will I
have to set up users?
 
Assuming you are using an MDB (not an ACCDB in A2007), you can set up users
and specify permissions on a per-table basis.

JET cannot handle column-level permissions, so the simplest way to do it is
to create another table (related one-to-one to your existing one), and put
the sensitive field(s) in there.

If you want to do it through the Access interface without setting up
permissions, this might help:
http://allenbrowne.com/ser-55.html
 
It's certainly possible to lock a field (or the control to which it is bound
on a form, which is how you should be providing user access to the fields in
a table) for certain users. To do so, you will certainly have to set up
some sort of user identification within the database.

There are several ways to do this, and which one you choose will depend upon
several factors - the "Access savviness" of your users; external control on
access to the database itself (ie. network/server folder privileges); and
the requirements for enforcing this level of security.

The easiest way would be to set up a table of users in the database, and
check the user's log-in name (using something like fOSUserName from the
Access Web site http://www.mvps.org/access/api/api0008.htm) to determine
whether the control should be enabled or locked. Easy, but readily bypassed
by anyone with even a small amount of knowledge of Access. You can make it
harder by start-up options, and using a custom property to bypass shift-key
access (see http://www.mvps.org/access/general/gen0040.htm), but that's
still pretty low-level security.

The next level would involve implementing Access's own User Level Security
model (and if you're using Access 2007, that will mean saving your database
in an earlier format, since 2007 has ditched this capability). That's a
rather steep learning curve - start with the FAQ and White Paper from the
Microsoft site. Here's a link to the White Paper for an early version:
http://support.microsoft.com/kb/148555; there are more recent ones but I
don't have any links to hand. Implementing ULS should be sufficient against
any but the most determined crackers - particularly if combined with
external security measures to limit access to the network where the database
resides.

Finally, there are those who claim the Microsoft's ULS is never secure
enough, and you would need to set up your back-end data file on a more
secure system sush as SQL Server. I'm not able to comment on this, as I've
found that ULS (with other external security) is sufficient for a couple of
quality databases that I've set up.

In both the "kindergarten security" and ULS models, you'll need to write
custom functions to determine what the current user's approval level is, and
use them to enable appropriate controls on your forms.

HTH,

Rob
 
Back
Top