Secure DB - Poison pill

[JohnPapa]

Assume you create a DB with

Set db = DBEngine.CreateDatabase(DBFullPath, dbLangGeneral)
db.NewPassword "", pstrPassword

Unfortunately, there are tools that can retrieve the DB password, in this case pstrPassword.

This DB is accessed programmatically through another db. I would like to be able to not allow the user to directly open the DB using the password, or if the user opens the DB directly using the password to delete all tables from the DB.

Any code that would achieve the above would need to be automatically inserted during DB creation.

Any ideas?

 

[JohnPapa]

Maybe a short description would help,
1) Database A creates empty database B.
2) Database A creates/copies tables into database B.
3) The user shoud be able to use A to access B, BUT if the user tries to open B directly that is when the poison pill should keep in.

 

[DJkarl]

You could accomplish something like that through VBA code and an AutoExec macro...however those can be pretty easily circumvented. There is no guaranteed method to do what you are wanting to do in Access.

 

[JohnPapa]

DJKarl,

I guess there is no way to disable the prompt to enter the DB password!!

 

[DJkarl]

DJKarl,

I guess there is no way to disable the prompt to enter the DB password!!

None that I am aware of. In terms of security Access is pretty light, you would need to invest in SQLserver, Oracle, MySql or the like to better ensure data security.

 

[bparkinson]

None that I am aware of. In terms of security Access is pretty light, you would need to invest in SQLserver, Oracle, MySql or the like to better ensure data security.

I agree. If you need real security, move to one of these databases.

 

[JohnPapa]

bparkinson & DJKarl thanks for your input. Modifying my Access/Jet applications (www.VisualDentist.com and www.TelTraq.com) to SQL Server or MySql is an option, but is not a trivial one. It would take a lot of db redesign especially for the dental applicaton which has been around since 1997.

I was looking for perhaps some simple method that would make the application safer. I do not mind if all data is deleted if the user does not co-operate and tries to get into the database structure etc.

 

[bparkinson]

bparkinson & DJKarl thanks for your input. Modifying my Access/Jet applications (www.VisualDentist.com and www.TelTraq.com) to SQL Server or MySql is an option, but is not a trivial one. It would take a lot of db redesign especially for the dental applicaton which has been around since 1997.

I was looking for perhaps some simple method that would make the application safer. I do not mind if all data is deleted if the user does not co-operate and tries to get into the database structure etc.

In the USA you have HIPAA, so storing any medical information in an unsecured Access DB is not a good idea.

 

[JohnPapa]

bparkinson many thanks for your input,

I am aware of HIPAA. Are you aware whether a "secure" (as much as possible) Access db would qualify under HIPAA?

 

[gemma-the-husky]

what are you concerned about - the data, or the applcation design?

if the latter, then issue an mde/accde. You also need some protection against copying, to prevent users getting hold of your stuff without payment

if the former (data) the what exactly is the issue/problem? why are you so concerned about users seeing their own data?

 

[JohnPapa]

gemma-the-husky,

I am concerned about the data (backend). I issue an mde for the application (front-end).

I am not concerned about the users seeing their own data. I am concerned about unauthorized users seeing data that they should not have access to, or making this as difficult as possible.

If an unauthorized user steals the backend .mdb file, I would like to prevent this unauthorized user from viewing the data, much less programmatically copying the data.

It seems a bit dissappointing to accept that Access/Jet cannot be secure enough. Any ideas?

John

 

[DJkarl]

gemma-the-husky,

I am concerned about the data (backend). I issue an mde for the application (front-end).

I am not concerned about the users seeing their own data. I am concerned about unauthorized users seeing data that they should not have access to, or making this as difficult as possible.

If an unauthorized user steals the backend .mdb file, I would like to prevent this unauthorized user from viewing the data, much less programmatically copying the data.

It seems a bit dissappointing to accept that Access/Jet cannot be secure enough. Any ideas?

John

If someone steals the back-end, it would be easy to break into the database and get the data, especially programmatically. The problem you have is any code you could potentially put in place to protect your database would need to be run by Access itself, if someone were to write something in VB.NET or C# that opened a connection to your DB, it would bypass any code/macro or security features you had in place. Access doesn't really have table level security so once they established a connection, it would be easy to read any/all data available in the database.

Maybe you could store encrypted data, and have your front-end decrypt the data as needed, of course that would be a change to the front end and would impact performance, possibly severely impact it.

 

[gemma-the-husky]

i see what you are saying, but personally, i am never too bothered about data.

many programs provide ODBC links to their data, so that they can be used by external programs.

It is one thing seeing the data structure, or even copying the data, and quite another being able to reproduce the functionality of the data base.

Data theft has probably been an issue ever since USB Memory Sticks.

If you are storing sensitive data, maybe an access backend is not the best solution.

 

[JohnPapa]

My software also runs over the Web using Citrix (in addition to a ocal installation). All files including the front end and back end are situated on the server of the data center. In this case, is the data more secure? Do you think that someone could copy the data file from a secure data center site?
BTW, in most cases Web based access is quicker than local access!!!

 

[bparkinson]

bparkinson many thanks for your input,

I am aware of HIPAA. Are you aware whether a "secure" (as much as possible) Access db would qualify under HIPAA?

It isn't so much qualifying - the federal government doesn't approve or disapprove technologies or strategies for safeguarding unclassified information, they just impose penalties if you fail to protect the information under HIPAA.

When I was working with a US health insurance company, we used Access, but we never stored any patient information in an Access DB. It was always in SQL Server. Since an Access DB cannot be secured, if I were selling an app that stored patient information into the US market, I'd break it into Access front end, SQL Server back end (or any other securable back end database).

In this case, dental practices, since they are usually small, and have no IT department unlike hospitals which do, they have no way of knowing that using Access to store their patient info puts them at risk of HUGE HIPAA violations (like losing ALL their patient data).

 

[JohnPapa]

bparkinson many thanks for your useful comments,

My application has about 50 tables in the backend (Jet).

Would SQL Server Express be an option to store the backend data? I believe it is free. Is there another db that you can suggest?

Assuming that I will look into SQL Server Express can you suggest some reading that I can do to see how I can do the migration?

If you have experience in the matter, how difficult is it to migrate from Jet to SQL Server Express?

Thanks in advance,
John

 

[bparkinson]

bparkinson many thanks for your useful comments,

My application has about 50 tables in the backend (Jet).

Would SQL Server Express be an option to store the backend data? I believe it is free. Is there another db that you can suggest?

Assuming that I will look into SQL Server Express can you suggest some reading that I can do to see how I can do the migration?

If you have experience in the matter, how difficult is it to migrate from Jet to SQL Server Express?

Thanks in advance,
John

SQL Server doesn't have a limit on tables or other objects. SQL Express is free, but lacks some things Standard and Enterprise have, such as scheduled jobs. Since Access has none of the things that Express is missing, it should not cause a problem.

I've used the upsizing wizard in Access to migrate to SQL Server. It works well.

Books by Roger Jennings are usually great. The SQL Server Bible or any other complete reference will suffice as well.

As to difficulty in migrating to SQL Server, I find that if all your tables have a non-composite primary key, hopefully an autonumber field called "ID", and no spaces or other bad characters in other column names it goes well. After you migrate your data, straighten up your indexes, make views for any SELECT queries you left behind, and Stored Procedures and Triggers for any INSERT/UPDATE queries. Link tables back to your Access front end. Test.

Thare are lots of companies out there that will host your SQL Server for less than US$10/month. I use one and it's a good solution for me, since my developers and users are scattered all over the earth. As you needs grow, you can upgrade from Express to Standard easily.

Oh yeah, also take a look at MSDN.