Protection

  • Thread starter Thread starter tendoy5
  • Start date Start date
Using Access 2003 or earlier, or an MDB format in Access 2007, you can use
User-Level Security. A sophisticated user can get around the security, but
for the most part it protects forms. You can develop your own security using
the Windows login:

http://www.mvps.org/access/api/api0008.htm

but that still has limitations in that if the user can get directly to the
tables, he can enter/edit/delete data. To cope with that you can use a
SQL-Server integrated security solution on the back-end (the data tables).
 
Most of my users are not very sophisticated and are quite professional, so I
don't have to worry too much about them doing things that they aren't
supposed to. But I recently had to implement a more sophisticated scheme to
prevent them (and others) from doing things they should not be allowed to
do. I would have liked to use SQL Server as my backend, but that is not
currently an option. So, I did this by:

1. Splitting the database (I always do this) and hiding the backend in a
folder location that they would have had to look hard to find.

2. Delete all of the linked tables from the application when I fielded it.
I created a database property that indicates the network path to the backend
database, so they would not know where to look for the backend. Then I had
a table containing the names of all the tables to be linked from the
backend. The same backend is used for several applications, so I only link
those tables I need to, and I do it each time the user opens the
application. When they close the application, the tables are all dropped
from the FE (this can be circumvented by aborting out of the application,
but if you disable all of the special keys it makes it more difficult to
bypass the startup form). I also hide the Navigation Pane that they cannot
see any of the tables.

3. Create a UserRoles table, and form, so that you can assign individuals
specific roles. These might include HR, Finance, Operations, Training...,
but each of these roles will have access to specific forms and controls on a
form. In some cases, you might just lock or disable a control for one role.
In other cases (Salary for example), you might actually hide the control all
together. I generally put the code that enables, locks or hides controls in
the Current event of most forms, but I put it in the Load event of my
navigation forms. For code that Locks or Unlocks a control, refer to Allen
Browne's site: http://www.allenbrowne.com/ser-56.html.

I've modified his code a bit so that I can pass it the name of a single
control, and indicate whether to lock or unlock the control. I then went
one step further to check and see if the control contains conditional
formatting for when it has the focus (I generally set conditional formatting
for all of my text and combo boxes so that they display in a pale blue if
the have the focus). If it does have conditional formatting for Has Focus,
then I change the background to a pale red to indicate that the field is
locked.

With this functionality, I have been able to meet my clients needs for
security and privacy. I know that someone that is extremely proficient and
really wanted to hack this application could find the backend (you can
change the file extension to make this even more difficult) but I've
disabled special keys on that as well, and have a startup form which checks
to see if the users Windows ID is in my Users table, and if not shuts down
the application, so even that is somewhat secure.

HTH
Dale
 
Back
Top