Access database backend delete prevention

I have an Access application with the backend database in a folder on a shared drive.  Users need r/w privileges to this folder to update the database as they do via the front end.  The backend database is password protected.  How do I prevent a user from simply deleting the database file itself?
LVL 4
Keyboard CowboyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
If you want security, you will need to move the BE to SQL Server.

There are a number of ways to mitigate this issue, but none are truly secure from a malicious employee or from inadvertent deletion by an over ambitious IT guy.  To improve security of your application:

1.  Put the backend in a location where your users don't know the name of the folder.  I generally ask the IT guys to create a folder with a name like: "ContactMikeJonesBeforeDeletingAnything"
on one of the network file servers.  Put it in the root directory so that it is not buried in nested folders and might get deleted in advertently.

2.  Place your backend files in that folder.  Change the extension of the backend files so that it is not obvious that they are database files.  You obviously don't want to make them .txt or .doc or something like that, but make it something other than accdb, mdb, dbf, ...  They don't have to have that that extension for you to link to them,

3.   Hide the display of the Navigation Pane in your front end

4.  Only deploy the front end as an mde or accde

5.  Disable the "Use Access Special Keys" to prevent malicious users from bypasssing the lockout.  Not fool-proof, but can slow people down.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can prevent the db from being copied or deleted by placing it in an unknown sub folder, then give all users traverse folder priv only on the main folder.

They will be able to use the app,  but if they try to navigate to the folder outside of the app,  they can't because they don't know the subfolder name.

You must make sure however the app does not expose the full path.
Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keyboard CowboyAuthor Commented:
Thanks -  all good ideas - I'm planning on moving to MySQL but can't right now -
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Jim ... have you actually tested that ?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, I do it whenever a client wants the DB secure.

The critical item is using the traverse folder only priv and adding a sub folder.   With that, they can jump over the folder, but only if they know the full path.

If they try to list the folder, they get nothing.  They could do a change directory, but again they would need to know the sub folders name.  As long as the sub folder name is something they would never guess, it's safe.

The app however knows the full path, so does not have an issue and they have full rights on the directory where it resides, so everything works fine.   The only real problem is not exposing the full path in the app.

As long as you do that, it works very well.

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
cool.  Seems the parent folder would need Full Rights also ?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Depends on the structure and what you store where.    I usually do:

\MyApp
   \Data
   \Program
   ...

Which I then change to:
\MyApp
    \Data
       \xyz
    \Program
     ....

   xyz would have full "normal" rights (read, write, and delete files),  Data would have traverse only.    DB's would be in xyz.

  Above that, users only have read, list, and execute.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.