Database Security

[ShanVel]

Thanks to all the folks in this forum who helped me to develop my fist access database application. Access is a great product and I really enjoy working.

I need to put this db for full scale production use and I would like to brainstorm some of your experiences on the security issues I need to address. I have like 12-15 users and everyone has a standalone version of MS Access 2003 full edition installed on their local hard drive. We do have a shared network drive (s :\). I have few questions as follows:

1. Is it a good idea to split the database into front end and back end and install only the FE on individual user machines or just run the .mdb file from shared ‘s’ drive? I don’t have any experience with how the BE and FE works.

2. Currently I don’t have any user workgroup and permissions set to run on the database. Anyone can just open the .mdb file like any MS office suite applications(.xls, .doc) and start using (navigate, input data into the table through forms, search database, print reports, access to DB window, etc). Can some one advise what kind of security level should I incorporate as a minimum?

3. Under Tools-Option-Advanced TAB, I have selected No Locks under Default Record Locking and also checked “Open databases using record level locking” option box. Is it safe to keep it this way? Is it possible to lock only few records on a table? I have a situation that on a particular project (this is a decision supporting db application) after the project (project here refers to a record) is done, I want to lock these data so that no one can modify the existing data. At the same time, if they are working on a current project (record) then the user should have add/edit/delete access to that particular record. I am not sure if this (record level locking on a table) can be done.

4.In future I know that I need to modify the table design, forms, reports, modules. Is it good idea still to consider creating MDE file?


Your input is greatly appreciated and thanks a lot for your support.

Shan.

 

[pbaldy]

1) I would call the split setup with the FE on each person's PC mandatory.

2) It all depends on your needs. You don't need security at all, unless the data requires it.

3) I think you're misinterpreting the meaning of record locking here. There is no built in way to do what you're describing. I can think of 2 ways offhand; first would be 2 tables, one for new, editable records, which are moved to the non-editable table when appropriate. The other would be a "editable" field in the table, which you would set to No or whatever as appropriate. Then your code would examine that field to determine whether the user could edit the record or not. Either way, you'd have to code the solution.

4) I would still create and distribute an MDE. It's easy enough to make your changes to the original MDB and create a new MDE for distribution. I typically distribute MDE's, plus I force the users into Runtime mode. That keeps them from screwing around with anything, but it's not the kind of security you'd want for payroll data, etc.

 

[ShanVel]

Paul,

Thanks for a detailed response. By the way, can you explain to me what is RUNTIME MODE?

For question 1, you suggest to split the database with the FE on each person's PC mandatory. But on Q4, you are talking about creating MDE. Does it mean MDE for FE only?

I have one another question (sorry the terminologies here are new to me), after splitting database (say db1.mdb) the BE is saved as db1_be.mdb. Is the FE is still db1.mdb and this is what I would convert into MDE file and install on every PC and keep the BE (db1_be) at shared network drive? What does the "Linked Table Manger” do on the database utility menu?

I would stick with your suggestion for Q2 on second option with "editable" field with some coding.

Thanks a lot.

Shan.

 

[pbaldy]

/runtime is a command line option that causes the db to be run in runtime mode. That is, even on PC's with a full version of Access, the db will run as if it was on a PC with only the runtime version of Access. That means no access to the database window. Since my users don't open the db itself, they run a version control utility I wrote, I control how they open it.

Typically the BE is left as an MDB. Since it has no forms/reports/code there's really no point in making it an MDE anyway.

Yes, you would create an MDE from db1.mdb and put it on user's PC's. The BE would remain on a shared network drive. The Linked Table Manager is, as its name suggests, used to manage linked tables. If you need to refresh an existing link (structure changed in the BE) or change the location of the BE, you'd do it from there.

By the way, since you mentioned an "S:\" drive earlier, I'll mention that it's smarter to link with a UNC path (\\ServerName\ShareName\db1.mdb) instead of a mapped drive (S:\db1.mdb). That way, you don't need to worry whether the user has the drive mapped to the correct letter, or even whether they have it mapped at all.

 

[ShanVel]

Paul,

Thank you very much for the speedy and a detailed response.

runtime - can you show me/direct me to a link in the forum on how to do for my application? Sounds like this is what I need to do in order to have a complete control for my application.

UNC path (\\ServerName\ShareName\db1.mdb): Also, I would appreciate if you could provide me some more details on how this can be done. I would like to run my application from 'S:\ShareName\db1.db.

Regards,

Shan.