Is this a reasonable strategy for protecting the database? 1

[postermmxvicom]

I am developing a database for Access 2003 for a small company. Possibly 5 users for the database itself, more with time. Practically, only 2 users at this time.

I am only physically at the company for a few months out of the year, and hopefully that will change as they have installed a server proper and I will be able to give the more support from a distance.

I have had one or two run-ins, where a user has messed things up. Now that they have a server (with back ups) this is less of a big deal, but I still want to protect the more important files, yet still leave it open enough for those who are daring or skilled enough to expand and use this database to their liking (since it is not "my" database).

I came across this article

http://office.microsoft.com/en-us/access/HP052578501033.aspx

(the second link: "Help protect a database without requiring users to log on") and I was wondering if I could use that approach to protect the tables, and the design of the forms and queries I have made. Then make some queries which mirror the tables, but can only read the data, and let the users who want to, base their personal endeavors off of those.

I just started reading up on this and would like any and all input on this topic.

Aaron

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[Hap007]

Hi Aaron,

Seems like too much to just backup your data.

You may have already completed these steps, but here they are.

1) Split the Application into a FrontEnd (Programs, reports, code and queries) and a Backend (Just your Data Tables
2) Compile the FrontEnd into an Access Mde file
3) Password Protect the BackEnd
4) Place the BackEnd on a shared drive and folder
5) Copy the FrontEnd mde to each user's PC, and Link the tables to the shared BackEnd. When Refreshing the link, make sure you select the option to remember the password.


Make sure to keep a copy of the FrontEnd Source MDB

Note: for remote connect/support, try

http://www.logmein.com

This website offers a free version that will allow you to connect remotely to their system

You could instal LogMeIn on the server and on each PC, all under one account, all free.

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[postermmxvicom]

You are right, I have done some of these things.

1, 2 and 4 are done.

I am curious about 3 and 5. The back end is in the network share folder, so theoretically, anyone could move/copy/delete it. In addition to this, I tried to open it directly and the system denied me this privilege, saying I had to first make a local copy, then open it. Yet access can still access the data and you can move/copy/delete the file...just not open it directly. Would implementing 3 and 5 add anything to this scenario.

Also, either I am confused, or was confusing.

Seems like too much to just backup your data.

I am not worried about backing up the data, now that we have a server proper and regular backups. But, I am worried about a user messing with the data in the tables directly or modifying the guts of a query or writing their own query which makes bad data entries, (either someone who knows nothing about access and shouldn't be touching anything or one who knows enough about access but accidentally messes it up).

However, I don't want totalitarian control. I don't want to take away the ability of an informed user to make their own queries to give them the data the way they want it without having to circumvent any security I put in.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[Hap007]

Hi Aaron.

I do not know why you can not open the backend on the network. That may be a network setting, or
You may be trying to open the Access file (on the network) in exclusive mode and some other user already has the database open in shared mode (Just a guess here)

If you password protect the Access Backend, no one would be able to open the database without the password.

One trick I have done to protect the data from being accessed in the Frontend (by unauthorize use) is to delete all 'Linked' tables in the frontend when the application closes.

Then, in VBA Code, I re-create all the links then the FrontEnd starts back up. Not Perfect but it works.

Another method regarding SQL, put all of your SQL into VBA code and do not use Access Queries.

Remember, MS Access is a friendly database, so no matter what you do, it probably can be broken if the user has enough knowledge and time.

If security really is an issue, you might need to go to MS SQL backend, and possibly a compiled frontend such as VB or C#.

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[postermmxvicom]

Thanks Hap007,

For the record, I misread your #2 on your first post. I had not done that.

But, it looks like everyone around here is leaning towards leaving it wide open. Soooo, unless someone higher up overrules this (which is a real possibility), it'll probably go that way and just be on them to protect it.

Aaron

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[paulfenwick]

Hi, I have a somewhat similar situation, I have done 1,2,4,5 along with stopping people pressing shift at start/up etc.

However just did a bit of playing and realised that I can just open up excel, go to Data, import data, new DB query then point it to either the MDE or backend and edit all of the data!!!

Equally, I could create a blank database and import or link to any of the tables in the proper database and edit the data.

Is there anyway to prevent this?

Thanks

 

[Hap007]

Hi Paul,

Well try option 3, password protect backend.

Then relink all tables and select option remember password.

No, if you do not tell your users the backend password, they should not be able to link with Excel.

Remember, Access is not an industrial strength backend, so passord protection is 'good' at best.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[paulfenwick]

Hi Hap007

Thanks - will give that a go.

 

[jrbarnett]

A slightly more industrial strength approach would be to use MySQL on your database server, then use ODBC to connect through to the front end.
It gives you far more concurrency and data protection than Access, but for 5 users with 2 concurrent it may be too much effort to implement.

John