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.
-Robby
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.
-Robby
No comments:
Post a Comment