Gauging your security needs; alternatives to Access/JET security

Access/JET security is a built-in component that allows for a database designer to protect code, form and report design, and restrict access to data stored on Access/JET tables. Access/JET security is also painful to use and carries a steep learning curve, and is not fully customizable. As a result, many developers choose to enforce their own authentication schemes to suit their needs. This FAQ has been written to provide facts to help you decide whether Access/JET security is for you.


Contents:
[ol]
[li]Definition of security[/li]
[ol][li]Levels of security[/li]
[ul][li]Uncrackable security [/li]
[li]Pretty good security [/li]
[li]Casual security[/li]
[li]The honor system[/li]
[/ul][li]What needs to be protected in an Access database?[/li]
[ul][li]Your database objects, from total deletion[/li]
[li]Your data, from total deletion[/li]
[li]Your data, from unsanctioned modification[/li]
[li]Your sensitive data, from unwanted/unapproved access[/li]
[/ul][/ol][li]Your security needs[/li]
[ul][li]How sensitive is my data? [/li]
[li]How valuable is my data?[/li]
[li]How much do I trust my users? How much should I trust my users, and all future users of this database?[/li]
[li]What is the cost of implementing security?[/li]
[/ul][li]Other security methods besides Access/JET security[/li]
[ol][li]Database passwords[/li]
[li]Using compiled MDE files[/li]
[li]Relying on your corporate network folder security[/li]
[li]Programmatical security features[/li]
[ol][li]Using the current Windows user to authenticate/determine data access[/li]
[li]Using a table-based permissions system with my own login form[/li]
[li]Programmatically checking database location and current user to ensure the user can open the database or specific features[/li]
[/ol][/ol][li]What Access/JET security can provide you[/li]
[li]Attempting to bypass security; ways to bypass BAD security[/li]
[/ol]


1. Security

There are several levels of security:
[ul][li]Uncrackable security
[li]Pretty good security
[li]Casual security
[li]The honor system
[/ul]

Uncrackable security

Uncrackable security is security that is absolutely safe. Uncrackable security cannot be broken given any amount of resources, time, or other means. It is an ideal goal of security architectures, though it is not attainable.

Uncrackable security is not possible using Access. Access databases are stored on files, and files can be copied, and even the best encryption schemes can be cracked. Access' encryption schemes are *not* the best, by any stretch of the imagination, and its encryption schemes (both Access/JET security restrictions and password-protected databases) are crackable. Cracks and de-securing programs exist for every version of Access. You cannot secure your data in a publicly-accessible database file.

Using a home analogy, uncrackable security would be a hidden underground bunker--unattainable by any means (okay, perhaps there is no uncrackable home security, but this is the nearest analogy).


Pretty good security

Pretty good security is that which requires more resources to crack so as to outweigh the benefit of cracking your system. Simply put, the costs outweigh the benefits. Pretty good security implies that given incentive, someone could break into your data.

It is a known fact that Access databases can be cracked programmatically; even the best security implementation will not protect your data if a malicious hacker/employee wants it. Access files can be copied. However, presuming the data you are storing on your database is not particularly sensitive, this sort of security system encourages rules adherence. In other words, if you set up good security on your database, your users will be less inclined to tinker with the tables or forms directly.

Using the home analogy, pretty good security would be a safe in your house. Given time, any malicious entity can get inside your safe, but it won't be easy for them to do so.


Casual security

Casual security is security built to prevent users from doing things they should not. There are levels of casual security, but the general definition is this: you have done many things to prevent the user from accessing parts of the database that they should not, but it is still easily possible for them to do so, should they wish.

Access provides many methods of casual security. You can hide objects from the user (right-click on any object and select the Hide option), you can hide the database window altogether, hide the Access window altogether, log system usage, and many other things I haven't listed here. But in all cases, your users can bypass these security methods.

In a home, casual security could be a fence surrounding the house with 'No Tresspassing' signs placed on the perimeter, or closed doors, or shut windows. These things will not prevent a determined invader, but they will help steer the honest passerbys away.


What needs to be protected in an Access database?
-Your database objects, from total deletion
-Your data, from total deletion
-Your data, from unsanctioned modification
-Your sensitive data, from unwanted/unapproved access
-Your code, including form design, report design, code modules


2. Your security needs

You must consider several things to gauge your needs.
-How sensitive is my data?
-How valuable is my data?
-How much do I trust my users? How much should I trust my users, and all future users of this database?
-What is the cost of implementing security?

Sensitive data
If your data is very sensitive, do not use Access as your means of security. Storing company-wide payroll information on a shared network folder is not wise, to say the least. If your information is truly sensitive, your IT/IS department will accommodate your needs, either by setting you up with strong network folder security, or storing the information on a server-based DBMS (such as SQL Server or Oracle), or by some other means. Do not rely on Access security features to hide sensitive data! Even with the strongest security, with full logging implemented, your users can still copy the database file itself and manipulate the copy. Again, do not store sensitive data in Access! If the cost of losing this information (i.e. financial ruin/legal issues) is high, then by all means invest in truly secure systems.


Valuable data
Consider the cost in time and effort to rebuild data placed in your database. This is the value of your data. Again, if the cost of losing this data is high, consider investing in a system that guarantees data integrity. Access is a good solution for many situations, but you should not fully trust any system that places all its proverbial eggs in one proverbial basket. A perfect example of this sort of misuse (in my opinion): Diebold voting machines. They store voting information in an Access MDB on a server. The value of this data is almost incalculable--why trust something so important (millions of voter records) to something that has a measured probability of failure (however small)?

In any event, the value of your data comes into play when your data is stored in Access database files. These files may be deleted by any user of the database, and so you must consider this fact. The cost of repairing your data is the cost of recovering from the last backup, plus manually re-entering everything lost since the last backup.

User Trust
Professionally designed databases should allow no room for the honor system of database usage. Users are at times nosy and potentially harmful. With this said, there are cases where you may forgo security altogether--specifically, in situations where there are few users. The fewer the users, the greater the accountability and thus the more responsibly the users behave. By this, I mean that a two-user database can possibly forgo security measures, whereas a 50-user database would and should require the most possibly restrictive security.

The average life of a computer program is twenty years. I do not believe that Access databases on average last so long, but it is something to consider. The average employee stays in one position for far less time. If you can trust your current users, can you trust the users that will replace them? Again, this is a decision you make--I am simply presenting the issue.


Cost of security
Generally, this can be referred to as 'developer hassle/user hassle'. It takes time to set up most security schemes, and at least as is the case with Access/JET security, takes some learning curve to use it properly. For users, the database password and/or their logon password is just one more password to remember. Or, as is often the case, one more password to forget, or one more password to paste on their monitor.

Take into account the fact that security takes time to set up, and adds overhead to anything related to the database.

Maybe this should be entitled 'The KISS Factor' instead - keep it simple if security isn't necessary.


3. Other security methods
-Database passwords
-Using compiled MDE files
-Relying on your corporate network folder security
-Programmatical security features
--Using the current Windows user to authenticate/determine data access
--Using a table-based permissions system with my own login form
--Programmatically checking database location and current user to ensure the user can open the database or specific features


-Database passwords

You may add a password to any database by clicking Tools->Security->Set Database Password. I personally do not use database passwords as they add no additional protection onto any existing Access/JET security. I am also not a fan of placing a single password for the entire sphere of data which you must give out to all users of the database and (often) place in password fields of linked tables, documentation, etc. I consider database passwords as casual security, because once the user is in your database, they may do anything they wish. Database passwords protect well against *casual* external intruders, but against your users provides no protection.

Database passwords may be cracked. I believe the method for cracking database passwords is easier than Access/JET security; an example is provided here ( http://www.trigeminal.com/lang/1033/codes.asp?ItemID=5#5 ) as for how to crack Access 97 database passwords. In any event, both security systems are poor, so it is of little recourse.


-Using compiled MDE files

MDE files are compiled MDB files. This means that the code and design of your forms, reports, and code modules are completely compiled and (thus) uncrackable. I understand that any executable program may be "decompiled" to assembly code, but assembly itself is an "uncrackable code" that I never want to untangle. I have great respect for anyone who programs in assembly code.

Compiling a database to an MDE does *not* in any way protect your data.


-Relying on your corporate network folder security

Access databases are stored as files. As such, if no one can access your file, no one can access your data. If you are the only user of the database, or there is some other simple setup such that all data is available to all users of the database, you may setup folder security. Get with your IT/IS department to set up a special folder so that you can control who has access to the database.

This method of security can be the *most* secure option available with the least allocated resources, and is simple (for you) to set up. However, this still requires a trust of your users and the honor system.

From external/unathorized users, this provides full uncrackable security, so long as the file stays on the server/specific folder. Once the file is copied, however, it has absolutely no security.


-Programmatical security features


--Using the current Windows user to authenticate/determine data access

This is relatively simpler to program than a table-based system, and in addition it is easier to grasp because you can see all of the parts of your system. This is one common positive trait of all custom-built systems; if you build it yourself, you can understand it much easier.

Your data and forms are protected from the casual user, but anyone with knowledge of SHIFT key/F11 key bypasses can break this security system. I consider this casual security, as users somewhat easier access to the table data, so long as they don't use your system.

--Using a table-based permissions system with my own login form

Of all systems, this I dislike the most. Users may go into the "user permissions" table, change their permissions, and use the system you built with full permissions. So not only will they be able to grab the raw data, but with a little tweaking of their user profile, they are able to set themselves with full access using your forms and reports. Sensitive data is especially unsuited to these environments.

--Programmatically checking database location and current user to ensure the user can open the database or specific features

I consider this type of security as random checking. Somewhat like completely barring one window of the house while leaving the front door open. Any checks you perform may be bypassed at database startup, or may be commented out as the user wishes (though the situation is unlikely).


4. What Access/JET security can provide you

-Built-in login system

No need to design your own login form with accompanying code; Microsoft has already done it for you.


-Tested administration system

Adding and deleting users builds on a system that has been firmly tested, something not always the case with custom-built security. Attempt to add two users, "billg" and "Billg"? Delete a user while they're in the database? What happens if the user fails to "log off" your system properly?


-Strong permissions security and customizability (may I say "Robust?")

This system is more secure than any custom-built security because it is built into the system itself. Access, before retrieving data, will check with the security permissions of the current user and will throw an error if the user is not allowed to access the data.

The same goes for form design, report design, code modules (newer versions require a different method to secure your code modules). Access will not read, export, modify, or delete a database object unless you the logged in user has permission to do so.

With this, you may prevent users from opening the "frmAdmin" form unless they are a member of the Admins group. You may prevent users from opening "tblPayroll" unless they are a member of the "Payroll" user group. You may alternately, disallow *anyone* direct access to tables and instead enable users to run RWOP queries which perform very specific tasks, i.e. open "qryListAllMyInvoices" which shows only the current user's entries in the Payroll table.

You may prevent design changes to *any* database object, so long as the current user is not a member of the Admins group.


-The strongest security available for Access itself

It is the only system you can use to protect the data. All other methods may be circumvented, so long as your data resides in Access/JET tables.


-Use of the exact same system that is in use by thousands of other Access users

Don't like something? Maybe someone has written a handy utility to fix your problem or designed a form to suit your needs (Change password form: http://www14.brinkster.com/phappyman/ ).



5. Bypassing bad security


All Access security is crackable, with the possible limited exception of compiled MDE files (the data is still accessible, but the code/design is not). So this provides you several methods of bypassing the easier security systems.


-Hold down the SHIFT key

Hold down the SHIFT key when entering the database. So hold SHIFT while double-clicking on the MDB file. Or, if using Access/JET security, enter your username and password, then hold SHIFT before clicking on OK to open the database. If opening the database from the Recent Documents list, hold SHIFT before clicking on the menu item.

This bypasses any startup form or procedure set up in the database, either in the Startup options or via the "Autoexec" macro. You may now edit the database objects at will.


-The F11 Key

Press F11. If you can see the database window, and can edit forms/tables/queries, you have bypassed the security on the database.


-Edit your permissions in a custom "User Permissions" table

Use the F11 key or the SHIFT key trick to bypass security. Find the user permissions table. Open it. Find the administrator of the database, and set your permissions to that of the administrator. If in doubt, give yourself access to everything in the database.


-Import the database objects into a new database

Open a new (blank) database file. Import all objects from the secured database. If you are able to do so, your database is not well-secured.


-The ability to delete files related to the database

For this problem there is no solution. Any user with read/write permissions on the folder the database is stored in has the ability to delete the database file itself. The only pragmatic solution to this is frequent backups and employee training. The only bulletproof solution is to use a server-based DBMS for the back-end, and keep backups of all your necessary files elsewhere.