I have a VB6 multiuser application that uses DAO to read from and write to a MS Access XP Database file on a central server. The Database File is password protected but there is nothing to stop 'General Users' using 'Explorer' or 'My Computer' to open the network directory with the Database File and copy, delete or try to open it etc! How can the Network Permissions be set up so that 'Administrators' can login and Maintain the Database File but 'General Users' can not copy/delete/open the file? (I have tried to set the Directory Security Permissions to read only for 'Users' but this sometimes causes a 'Database Locked conflict' and the VB6 application shuts down. Also, the VB6 app won't work by simply setting Hidden Attribute on the Database File to true!)
In order to open/use the database file your users must have Modify permissions (which includes the permission to delete this file). Since Access/Jet is a file-based database, you cannot deny users the ability to delete while still allowing them full access (assuming you want them to read/write to the db, that is). Unfortunately, the answer is to move to a server-type database, which allows you much more latitude in this area.
Thanks for this response, and the future plan is to definitely move to a server-type database, however this is a big move and as such is not a short term solution.
Thus I was hoping there may be a way to 'hide' the Database File on the Network from the 'General User' but in effect always 'show' it to the VB6 app?
Also, I think my DAO connection string is very basic, ";PWD=" & dbPassword (see attached code), so is there a way to assign better permissions, and/or better database sharing rights, with the connection string?
1:
2:
3:
4:
5:
Dim WS as WorkSpace
Dim DB as Database
Set WS = CreateWorkspace("", "admin", "", dbUseJet)
Set DB = WS.OpenDatabase(dbName, False, False, ";PWD=" & dbPassword)
Have you tried removing "List Folder Contents" from the user/group permissions? Then you could rename the database into something that wasn't intuitive and redistribute the (updated) VB application.
Thanks for your response aikimark, I did try removing the 'List Folder Contents' permission but it acted similar to the 'Hidden' attribute where the VB app does not run because it is like it can not 'see' the file(s)
But what do you mean by "rename to something that wasn't intuitive", does this get around the 'hidden' issue?
If the users click on the file, it will not automatically open Access because the .DAT extension is not associated with the Access program. That doesn't prevent a clever user from copying the file to their hard drive and renaming it with a .MDB extension.
* set the priviledges to prevent deletion. * keep backups * have another program running on the server that keeps the database open during the day. (maintains an active connection)
=================== I guess this issue comes down to personnel and policies. Release a statement that says that the users are no longer permitted to have a copy of the database on their PC, not allowed to open the database using any program other than the application, not allowed to perform any file activities on the folder's contents. State that full auditing is enabled for the server and that all employees outside of the admin/support staff will only get one warning for violating these rules. Second violation results in an immediate termination of employment.
All current and new employees need to sign an aknowledgement document of the rules and agree to abide by them.
Survey all users' hard drives, looking for a copy of the database. Remove them and send notice that it has been removed in compliance with the rules. Such first instance removals do not constitute a violation of the new rules.
If an employee refuses to sign the document, prevent them from gaining access to the network directory.
Thanks aikimark, your points 1 and 3 above look like they may resolve the deletion issue and I will invoke them and carry out some testing over the next few days. The Policies setup is a good idea and I'll look into this also.
One Question with point 1 though: Can the priveleges/permissions for a file or directory be specifically set to 'Prevent Deletion', and if so how, or are you simply talking about setting the priveleges to 'Read Only'?
Also, the Database File is protected and lives on the network and is backed up nightly. The chances of one of the employees trying to open it and having it on their PC is very remote, but still a possibility. The main worry is someone deleting the file or maybe hacking into the Network and Stealing the file.
Cheers and watch this space because you may get the points!!
file/folder privildeges include Delete. If left unchecked, deletion is not allowed.
Note: It doesn't prevent someone from corrupting the file.
One thing you might want to do is to change from .MDB to .MDE. You can automatically invoke some code when the database is opened within Access that will effectively close the database. You can keep the original .MDB file safely under lock and key.
