Bulletproof Databases

[Garridon]

Just thought this might be a good place to post methods of how to bulletproof your database and keep users from doing unintentional (or intentional) things to it.

One is to go into Tools>Startup and turn off "Display Database Window." Users should never see the database window or use it to navigate.

Linda Adams

http://www.hackman-adams.com

http://www.david-hedison.com

 

[jebry]

You would also need to turn off the special keys since hitting F11 automatically displays the database window. And disallow builtin toolbars since they have a button which shows the database window. And change the AllowBypassKey property or the user will be able to hold down the shift key when opening the database and bypass all of the startup rules, macros and code.



Jeff Bridgham
bridgham@purdue.edu

 

[Hap007]

Dear Linda,

Nice try, but that is just the tip of the iceberg.

1) Compile your Access (mdb) to a psuedo (mde) where the users can not modify your code.
2) If you have the runtime environment, then use the runtime engine and place code in your app to check and allow only open and run when started using the runtime version of Access.
3) Use the Access security model with passwords.
4) Select all objects in your database and set the 'Hidden' property to true.

I am sure there are other items, but you certainly need to mention these.

Enjoy,
Hap...


Access Developer

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

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[rdodge]

Here's 2 more thoughts in addition to what has already been said.

To keep from your DB program corrupting, or at least not as much of a chance, you need to have your DB program split into 2 parts, a front end, and a back end. Your back end would only contain the various table structures and the actual data. Your front end will contain everything else (relationships may be another thing, but I haven't been able to get them to work properly with my queries, so I don't really use the relationships so much, though I also understand the Integrety issues that's involved to help keep the data normalized, which gets into the 5 rules of normalization form. The first 3 levels are very much so required and not hard to understand. The last 2 levels requires a little more understanding of which I have already been required in my DB program to use 4NF, but not quite to the point that I been required to use the 5NF just yet. I am however, keeping that rule in mind too cause I will eventually be required to use it.

Don't forget the replication steps you need to do so as only you and others, if there is/are any other(s) that involved in the development has access to the Design Master Replica and no one else can create a replica of the DB files.

The FE replica that users can use should be on a server, which then should be copied from there to the user PC which then the user would open the access program from their own PC (this helps in reducing file corruption chances)

My second thought is dealing with Data Validation and User friendliness. As we all should know, data validation is a must in the DB world, cause as they say, garbage in, garbage out. However, with that said, we also must make the forms as user friendly as possible without sacrificing data validation checks and efficiencies. For this, the common thought has been to use bound controls (efficiency purpose not to mention normally, doesn't create as much work for the developer), but I have ran into one issue with bound objects. Nearly all Access developers knows to use the BeforeUpdate Event for data validation purposes, but if you happen to have users that doesn't understand the concept of pressing F1 for help, pressing Esc or Clicking the X button on the form to close the form out, to where you have to create a command button for either one of these 2 tasks or for resetting the form, then the BeforeUpdate Event fails out in those minor few cases, thus for that reason, I have created my own modulated data validation code to similate VB6's CausesValidation Property and Validate Event.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[CajunCenturion]

Just out of curiosity rdodge, what was the application which required 4NF? They are few and far between.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Hap007]

Hey Ron,

You make some good points, but what does that have to do with 'Bullet prrof Databases'.

Replication is only needed when you are distributing remote applications and then later retrieving data for update to a cental location.

Normalization is also important, but you are now discussing good programming and design methods rather then protecting a database from user modifications, which I beleive was the original topic.

Obviously, I have too much time on my hands to be entering this, but I just wanted to add my 2 cents.

Hap...

Access Developer

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

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[rdodge]

Take Vendors and Items. We can get many items from a single Vendor and we can get a single itemfrom many Vendors. Of course, we will have other fields anyhow along with it such as Pricing and lead times.

Item Table

<Various fields, but not going to get into this>

Vendor Table (actually, this would be in the same table as the list of all other entities, just would be distinguished by Entity Type)

Given we have a many to many relationship, we would create a table that starts out with the following fields:

fldID
fldEntityID
fldItemID

Of course we would have other fields in this table such as pricing, but that in turn could create even more levels such as just for 1 item from a single vendor, there may be 5 different pricing levels determined by quantity. So in this case, we can't even use the EntityID and ItemID fields as the primary key, but rather a unique ID field, and the pricing may be based on at least so much quantity or it may be based on up to so much quantity, of which if you are dealing with continuous numbers (non-discrete), you about have to have 2 different quantity fields. Yes, in practice, we tend to convert continuous numbers into discrete numbers, but with computers advancing more and more, it's allowing the numbers to be more and more precise, so where does the buck stop at in this regards?

So with this aspect, we would then have the following fields at the minimal:

fldID
fldEntityID
fldItemID
fldMinQty
fldMaxQty
fldPrice
fldQtyPer

and to validate it further, we may have to deal with differet types of quantities such as if we are dealing with Ink for printers, the ink may be shown on the BOM in pounds, but the pricing may be showing in per barrel basis.

Anyhow, you get the picture. Han is also right, this is primarily dealing with good design issues. The splitting mainly deals with avoiding data corruption and surely, the db replication would deal with db proofing for when it's needed.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000