I maintain an ms-access application splitted to frontend and backend files. The frontend file is in the users conputers. The backend file is in a shared folder in the server.
What is the lowest permissions required? can I give some of the users only read-only permissions in that folder? (or hide it from them in some other way) but still enable them to view the data?
How should I give the best security to the data file and to the folder containing it?
Yes - it resolves down to file access permissions as well as read/write. You can't execute any type of data update stuff (you'll get "operation requires an updateable query") unless the user supplies credentials that allow them to write, or you allow write on the file.
Running a query requires only read access.
Unfortunately, the lock file (ldb) must be created, updated and deleted. If a user with insufficient permissions opens the database, it will be locked for all other users, therefore all your users need Read/Write/Delete permissions on the back-end.
EDIT #1 The lock file must be created every time the database is opened, this includes via linked tables, and deleted when the database is closed. If a lock file exits and the database is closed, it indicates a problem has occurred. You will also run into problems with compact and repair if it is run with insufficient permissions.
Edit #2 Security for Access is quite a large subject and depends to a great extent on your environment and requirements, for the back-end, it ranges from a database password, which is tissue thin, but quite suitable for most offices, to Access security, which can be complicated and has been dropped in 2007. Here is a link http://support.microsoft.com/kb/207793 for a download for the Microsoft Access Security FAQ for versions < 2007. Information on security for 2007 can be found here http://www.microsoft.com/technet/security/guidance/clientsecurity/2007office/default.mspx.
Well I first thaought that a "read-only" permission on the back-end folder will not allow the "read-only" users to open the MDB file, as Access needs to create a LDB file in the folder when a MDB is open. But what happens if this back end database is opened "remotely" as a data source (and not a full "access file")? Access provides us with many methods to do so, such a linked tables (native access, ODBC connections, etc) & ADO connections.
Objectives:
The idea would be to check if there is a way to connect to the tables in the MDB file without "opening" it as an access file. One solution could be to create a link to a table located on the back-end database. Such a link can have different types, Access, ODBC, etc. I first thought it had to be ODBC, but native Access link seems to be ok here.
My steps:
Public Sub connectToData()
Dim rs As DAO.Recordset
''link Tbl_Object to current Access file
DoCmd.TransferDatabase acLink, "Microsoft Access", _
CurrentProject.Path & "\..\data\data.mdb", _
acTable, "Tbl_Object", "MyTable"
''open a recordset and browse through the records
Set rs = CurrentDb.OpenRecordset("MyTable")
rs.MoveFirst
Do While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
''display some important data in the immediate window
Debug.Print "Connection", CurrentDb.TableDefs("myTable").Connect
Debug.Print "Table", CurrentDb.TableDefs("myTable").SourceTableName
Debug.Print "Updatable", CurrentDb.TableDefs("myTable").Updatable
''drop the link
DoCmd.DeleteObject acTable, "myTable"
End Sub
Execute the code (run sub or call the sub in the immediate window)
Conclusion:
Check the results of the debugs. table can be viewed and browsed even if mdb file/folder are in read-only mode. No ldb lock file is created.
Read-only permissions set at the folder level will allow users to see the data without being able to update it, as long as a proper remote connection to the back-end file is created.
Place the back end file in a folder on a hidden network share. Remove permissions for "Everyone." Give only network admins and a group for these Access db users access to the folder. They all require read/write/create on that folder, but either some of those users or all of them need delete permissions on the folder to delete the ldb file occasionally.
I'd like to add some clarification to Remou's mistaken statement about Access 2007 having dropped user level security, because it hasn't been dropped. User level security still works on secured dbs.
OS and network permissions are usually sufficient to keep Access dbs secure, but if you need Access user level security to prevent opening the database and to assign permissions to access individual objects, it's available for all versions of Access, including Access 2007. In Access 2007 it requires the database file to be in mdb format. The new accdb format doesn't accommodate user level security. Access 2007 has replaced the weak database password with an encrypted password that is much stronger and harder to break than in former versions.
Your best bet is to move the data side over from access to MS-SQL Server (maybe even the free version). Since the app and db sides are now separate, that shouldn't be to hard (I've done it on my own apps). Then you can use MS-SQL's permissions to give fine grain access permissions to the data.
You could do the same thing using MS-Access's accounts and security (mdw) files, but it's a pita as you now have to setup special shortcuts, and a knowledgeable user can create their own mdw file and override their access rights to the data.
a knowledgeable user can create their own mdw file and override their access rights to the data.
@CodeSlave, if the db is secured correctly according to the steps in the Access Security FAQ, users can't use any other workgroup file to open the db. If you can open the db with a different workgroup file, it wasn't secured in the first place.
Many have suggested that you must give FULL permissions to users, but this is not true. You need only give them MODIFY permissions -- you can deny them DELETE permission, which is a good idea, as it prohibits the users from "accidentally" deleting your data file.
It is true that for a user with DELETE permissions, the LDB file will be deleted on exit when that user is the last user exiting the database. But it is not required that the LDB file be deleted -- indeed, in Access 2 and before, the LDB files were not deleted on exit, but just left there hanging around. This generally has no downside, but occasionally the LDB file gets corrupted and causes problems and really does need to be deleted and recreated afresh.
What I do is have two classes of database users (as defined in custom NT security groups specific to my Access application(s)) -- DBAdmins and everyone else. The DBAdmins have FULL permissions, everybody else only CHANGE. The result is that any time a DBAdmin exits as the final user, the LDB is deleted. This setup works really well, and I've been using it for well over a decade.
--
David W. Fenton
David Fenton Associates
Using a hidden share for your back end is really only "security by obscurity," and not really worth the effort. Sophisticated users can figure it out through any number of methods (depending on how you've locked down your front end):
Now, if you've properly secured your app using Jet user-level security (and it's very easy to think you've secured your database and find out that there are holes, simply because it's really easy to forget some of the crucial steps in the process), they won't be able to do this, but even if you have, Jet ULS security is crackable (it's pretty easy to Google it and find cracking software), so is not really something you should depend on 100%.
--
David W. Fenton
David Fenton Associates
Re: Access 2007 and Jet user-level security:
The fact is that ULS in A2K7 has been eliminated from the new database format, ACCDB, but if you continue to use the MDB format, you can still use ULS just like you always have. ACCDB offers only a database password -- it's a nice strong one (in comparison to the old db password), but it's still absolutely no real security solution (since all users have to know it, you might as well not have one!).
If you need to secure your application, distribute an MDE or an ACE, which strips the canonical code and leaves only the compiled p-code.
If you truly need to secure your data, you really need to use a data store other than Jet, and just about any server database engine can provide you the security you need.
--
David W. Fenton
David Fenton Associates
Philippe Grondier recommends using an ODBC connection to overcome problems with the LDB file. This is a nonsensical solution, as the LDB file is an aspect of the Jet database engine, and is created when you have a shared back end opened. You can't use ODBC because Access will not allow connections to Jet data via ODBC connections, because it's Access's native file format.
Perhaps Philippe was suggesting upsizing the back end to something other than Jet, and, indeed, that is a useful suggestion if you really need to secure your data.
But ODBC is simply not relevant when your back end is a Jet database.
--
David W. Fenton
David Fenton Associates