Tuesday, March 15, 2011

How secure is Microsoft Access? Part 2

In our last post, we started to discuss the how secure Microsoft Access is. I provided some quick and easy ways to keep out preying eyes and that can be implemented right away. Please look back at "How secure is Microsoft Access? Part 1" to see these.

In this post, I'm going to go into a few more complex methods for securing your Access database.

I. Splitting your tables from the client database. A common practice when devleoping an Access database is to create a "front-end" and a "back-end". The front-end is the "program". It is the database that will store all your forms, reports, queries, etc. It will NOT contain the tables. The tables will be stored in a second database or "back-end". The front-end will access these tables as linked tables. This setup is used for a number of beneficial reasons, but you can utilize this to really secure your data.

1. After you create your back-end database and the tables, set a database password on the file. Then when you go into the front-end and link to these tables, you will be prompted for a password. The password will be stored and now users who don't know the password will only be able to access the tables by going into the front-end.

2. With a little VBA code, you can set up your database to link to the tables every time you open the database. If the user authenticates correctly, then the tables will be linked. If the user does not, the tables will not be relinked. Thus they can not be accessed at all.

3. You don't have to have just one set of tables. You can have mutliple Access files each storing certain related tables. Then, the authentication procedure that you built, can determine which tables need to be linked and which do not. Thus each user will ONLY have access to those tables that they have been granted access to.

II. Turn your front-end into an MDE file - Once you have finished developing your database and before you distribute it users, it is good practice to turn the front-end file into an MDE file (ACCDE file in Access 2007 and later). By creating an MDE file, you restricted the user from going into design view of Forms and Reports, and from viewing VBA code. This not only will protect your code, but it will hide sensitive information like the path to the data files and passwords needed to access them.

On a side note, an MDE file will also perform better than an MDB file.

III. Piggy back on existing security - If your database is going to reside on a network file server, you probably already have network security in place to access those shared drives. You can set Access up to piggy back on top of this existing security. In order for me to gain access to the shared drives at InfoCore, I have to sign into our network with a user login and password (lets say user name rbuonocore). This user name and password is stored in the network management system and not in Access. When I open up Access, you can write code to "pull" the network user name of the person signed in. In this case, when I open up my database, it will determine that the person signed into the network is rbuonocore. I can then build my security in Access around permissions granted to rbuonocore. I don't have to worry about authenticating or storing passwords because my network management system has already done that for me. This same concept can be used with normal Windows logins as well.

So...How secure is Microsoft Access? Although not as secure as a full blown database management system, a very secure system can be created by using all these listed methods in combination; one that protects access to the system, access to the data, and access to functional areas inside the database.


Tuesday, March 1, 2011

How secure is Microsoft Access? Part 1

One complaint I hear from many IT professionals who don't like Microsoft Access is that it really isn't very secure. Although Access is not as secure as some larger Database Management Systems, there are a number of things you can do, that when used in combination, can really help to secure your database. In this post, I'll present a few simple tasks that you can do that will keep out preying eyes. These methods are quick and easy and can be implemented right away.

I. Encrypting your database. Encrypting a database compacts the database file and helps protect it from being read by a word processor. Encrypting is particularly useful when you transmit a database electronically (FTP or Email), or when your store it on a flash drive or compact disc. Remember though that most databases will grow very fast and thus transporting them becomes very difficult anyway. Also, many email programs (Outlook included) don't like to receive emails with Access files attached. They basically view them as threats and it's difficult to receive. One quick way around this is to zip the file first and then send it.

WARNING: Encrypting will slow down the performance of your database.

II. Hide your objects and database window. This is a very simple method of securing objects (tables, forms, reports) from the casual user. Through the Start up properties, you can set your database to not show the database window at startup. You can also select settings that will stop the user from un-hiding the database window. Then if a user gets into the database window they won't see the tables, etc. that would get them into the information.

WARNING: This is not a very secure method. It only stops the casual person from accidently opening these objects. Anyone who know a little about Access will know how to unhide the objects, but it is still good practice to do.

III. Create a database password. As with most Office applications, Access provides the ability to set a database password in order to open it. This again is a simple means of protecting your data from others and limits access to the system to a select few that know the password. In this case, there is only one password. All authorized users must know the password in order to access the system. Although this is a good means of keep unauthorized users out of the system, it doesn't help much when certain authorized users only need some of the data.

WARNING: There are programs out on the market that can break Access passwords very easily. I have a number of them that I use to help people when they forget their own passwords that they set.

IV. Create user sign-on. Access has its own built in user level security. You can create users, provide individual passwords, have the user authenticate when they open the database, and restrict certain users to certain objects. I have always found that this is easy to get started with but not robust enough and have always opted for developing my own security for my databases. In doing so, you can not only restrict certain users to certain objects, but you can also limit the data they see. If Joe is the salesmen for the NY area, then I can limit Joe's view of the customer table to only NY customers.

These are some very simple methods that can help to secure your database. In our next blog post, I'll discuss a few more technical methods that can really lock things down.