Steps to secured database

[reidhaus]

I'm using Access 2003. I want the users to open the database and only be able to use the data entry forms and view/print reports.

The main problem I have is that I don't know what order to do things and Access help isn't very helpful. I know I have to split the database, run the security wizard and assign permissions but in what order? For some reason when I try to create the MDE I'm told I don't have permission. Well, that bites since I'm the person who created the database AND I am listed under 'admins'.

I also don't understand this '.bak' thing. When I click one icon I only see the tables - no other objects. When I click another icon I see the tables with little arrows next to them. Where is my front end and how do I get that shortcut onto the other computers?

Another problem is that I created a few backups so when I inevitably screw up I can start again. However, some of the databases are in version 2000 - not sure how that happened when I'm using 2003. So it won't create an MDE until I convert (back?) to the 2003-2003 version.

Believe me when I say I've read countless threads on several forums over the past few weeks but the sheer number of conflicting information is exhausting. I printed out one set of instructions and followed it step-by-step only to lock myself completely out of all access databases and I had to delete all the workgroups and 'reinstall' the default Access workgroup just to get back in (which sort of defeats the purpose of security if an idiot like myself figured that out....but I digress).

I don't know anything about VB or code and 'computer slang' so if anyone out there has constructive advice I'd love to hear it! Much thanks in advance!

 

[dportas]

If security matters to you then you should not be using a Jet/ACE database because that architecture is inherently insecure. There once used to be some token user/role-based "security" features but they are now deprecated and removed - they were essentially worthless from a security point of view anyway.

You should upgrade to a client-server DBMS like SQL Server, MySQL or Oracle and use your Access application with that instead.

 

[HiTechCoach]

I know I have to split the database, run the security wizard and assign permissions but in what order?

The Built in User Level Security (security wizard) is not longer support in the new Access datsbe format (.accdb). This feature had many issues and really should not be used unless you have a lot of experience with it.

See: How I use Microsoft Access User-Level Security by Jack MacDonald

You should upgrade to a client-server DBMS like SQL Server, MySQL or Oracle and use your Access application with that instead.

How does that secure the Access front end so that you can't access the tables/data in the SQL Server?

At least with a client/server back end makes it a lot more difficult to copy the back end database.

 

[reidhaus]

The link to Jack MacDonald's page is fantastic, thank you!
Right now the one thing I'm still fighting with is the 'target properties' for the shortcut. I don't get how if the workgroup is on the shared drive how the target should start on the c: drive. Also, I tried the one he lists as what should be my first shortcut and I cannot get it to work. Any ideas other than to give up and find a new job?

 

[HiTechCoach]

With every multiple user database the application must be split. The app or front end in an the local hard drive. The data or back end is on the "server4. If you are using a custom work group file then it is best to place the work group file in the same location as the data (back end).

See: Splitting your Access database into application and data


This is true even if you have applied a custom work group file. It is best to apply the custom work group to an unsplit database. So you will combine your data. apply the custom work group settings. Then re-split the database. The

**NOTE: If you are going to use any type of security system it should always be applied in the initial design. Before any development actually starts. Security is an integral part of the design. Not something that can be slapped on at the end and really be secure.


Since you did not start with your database using the built-in User Level security and a custom work group file then it really is to late to try to add it just before deployment. That is not how it is properly used.

FWIW:
Personally I would NOT use the built in User Level Security and a custom work group. I stopped using it over 10 years ago. To much extra work and extra issues with support than any benefits I could gain.