Back/Front End Location (2003)

Topic

New Reply

Hi,

just a quickie. if i have a front end database in an area with access for all users, but the back end in a folder with much more secure access, and have the tables linked, it seems users with access to the front end can still view the database, althoguh they cant get into the folder where the tables are stored.

is there anywhere i can get a run down of how NTFS permissions etc affect the working of such a database, as unusual things seem to be happening.

If 1 user is in the database now, it opens as read only, or says that the database is locked by another user. It didn’t use to do this when the backend was in a folder with the same NTFS access permissions, but I have moved it to keep it secure.

I am thinking of instead of this method I should create a workgroup file and use this to secure the database. Again, any pointers on doing this to a database which is already in place and working?

Cheers

Reply | Quote

Replies

Access requires that users have modify permissions (read/write/create/delete) in the folder in which a database resides, whether it be a frontend or a backend database.
When someone opens a database (directly as a frontend, or indirectly as a backend), Access checks if there is already an .ldb file with the same name as the database in the same folder. If there is a .ldb file, Access tries to add the name of the computer to the .ldb file; if the user doesn’t have sufficient permissions to do so, the user is denied access. If there is no .ldb file, Access lets the user open the database read-only and will deny all others access to the database.
So folder-level permissions are NOT suitable to control access to a database, except for keeping users out entirely.
You need user-level security with a workgroup file to give different users different permissions in a database. See The Secrets of Security on WendellB’s website. It contains an introduction and useful links; I particularly recommend the Security FAQ from Microsoft.

Reply | Quote

Hans, thank you – thats very clear and exactly what i needed to know, and makes sense of whats happened here today! I am another step wiser

You are a star

Reply | Quote

To add to what Hans said, when you apply Access User Security to a database, you want to do it for the back-end to establish permissions for the tables. All other object permissions should be set in the front-end. There is also a little trick with queries that you might want to know about – it’s a property that lets user’s run queries with permissions the same as the owners, but you can prevent them from making design changes to queries.

Reply | Quote

Thanks guys,

I have now got the new version of the database ( I gave it a colour overhaul too !) working really well with a workgroup and correct permissions. Good to finally understand all this stuff!

Reply | Quote

Hi again.

I’m sorry if I’m being dumb but…

I have got the access working from the mdw no problem, but i find that I still need to give all users WRITE ACCESS to the folder where the back end database is for them to use it.

Thing is; doens’t this mean they can simply delete the db ? How can I get around this?

Reply | Quote

I’m afraid that’s the way Access works: all users must have full permissions on the folder containing the database, and that means they can delete the database file.

Make frequent backups – we make a snapshot every few hours where I work.

Reply | Quote

Thanks Hans.

Reply | Quote

If you are really concerned about that possibility, you might want to look at the MSDE or even at SQL Server. They have a true database engine that doesn’t require that the user have rights to the folder and all that entails. It also has builtin backup capabilities, and other management goodies, though SQL Server back-ends do require a bit more care and feeding.

Reply | Quote

We back ours up daily, just for historical reasons….at the going rate, with my 160gb hard drive, I can store daily info for up to 40 years!

Reply | Quote