Secure *.accde files

[Michael Steiner]

Hi all!

I am currently at the stage of distributing my access db, and after a
few hours of reading i have still not come to a satisfying solution
regarding security.

My db is split into FE and BE, my customer will most likely be running
the Runtime version of Access.
Nevertheless, i dont want anybody with a Retail Version to mess with the
db files.
So following questions arised:

1) In order to keep my vba-code secret and prevent users from changing
forms/reports designs i can convert my accdb to accde. Fine.
But: tables and queries can still be viewed, modified and created.
I dont want anybody to modify data using the linked tables in the FE -
which is possible through an accde using A07 retail.
Ok: I can hide all the tables and queries, which will take the user only
a few seconds to change the access options in order to see hidden
objects again.
So - is there really no way to prevent user-access to
tables/queries/macros in an accde file for users that use A07 retail?

2)If i were able to accomplish 1) there would be only one more point:
Securing the BE.
I dont want users to be able to open the BE directly, so i was thinking
about encrypting it with a db-pwd.
If i were able to pass the passwd within the startup-routines of the FE
i could link the tables of the encrypted database at startup without
suppling the passwd to the customer.
Is there a way to do this? Or on the other hand: Is there anything
concerning db-encryption that i should consider?
What about data integrity of encrypted databases?

Regarding this matter i would greatly appreciate any tips/points in the
right direction of the pros here

cheers,

--Michael

 

['69 Camaro]

Hi, Michael.

I am currently at the stage of distributing my access db, and after a few
hours of reading i have still not come to a satisfying solution regarding
security.

If security is a concern, then it needs to be incorporated into the design
of the application at the very beginning, not at the end. Otherwise, the
cart is before the horse, and you'll have a serious problem that will become
evident to you when you want to "Giddy-up!"

So - is there really no way to prevent user-access to
tables/queries/macros in an accde file for users that use A07 retail?

The only security available for ACCDE files is the database password. Your
users need that to open the database application. Once they're in, you
can't stop them from doing as they please if they have more than just a
rudimentary understanding of Access. However, if it was in MDE format,
User-Leve Security can prevent access to the tables, queries, et cetera.

If i were able to pass the passwd within the startup-routines of the FE i
could link the tables of the encrypted database at startup without
suppling the passwd to the customer.

In previous versions of Access the database password for each linked table
was stored in a system table. I haven't seen how Access 2007 does it, but
my guess is that the password is encrypted in the system table, but it's
there.

Or on the other hand: Is there anything concerning db-encryption that i
should consider?

"Should have" considered. Perhaps you're beginning to see the wisdom of
considering security measures at the beginning of the software application
design cycle, instead of after it's completed.

What about data integrity of encrypted databases?

Data integrity isn't at risk in an encrypted database, but it will probably
run slower than an unencrypted database. But perhaps you mean "data
security"? Anyone reading the data file in a Hex editor won't be able to
read and instantly understand much in an encrypted database file, but there
are sure to be "readers" and "security breakers" on the market, even for
Access 2007.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Arvin Meyer [MVP]]

In addition to Gunny's always correct answers, let me add that converting to
an MDB/MDE shouldn't be too difficult as long as you haven't used any of the
format only features found in the new format. The typical manner of dealing
with table security is to deny everyone permission on the tables located on
the server and give them access to the data through OWNERACCESS OPTION
queries. Then you can make it so they can't even create their own queries to
get at the data. To learn more about User-level security, ready the security
FAQ. Here is a list of good security resources.

Security FAQ
http://support.microsoft.com/download/support/mslfiles/SECFAQ.EXE

Lynn Trapp's summarization:
http://www.ltcomputerdesigns.com/The10Steps.htm

KB articles:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q165009
http://download.microsoft.com/download/access97/faq1/1/win98/en-us/secfaq.exe
http://support.microsoft.com/default.aspx?kbid=325261

Joan Wild's articles:
http://www.jmwild.com/security02.htm
http://www.jmwild.com/security97.htm
http://www.jmwild.com/SecureNoLogin.htm
http://www.jmwild.com/Unsecure.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Michael Steiner]

Hi Gunny and thanks for your reply!

If security is a concern, then it needs to be incorporated into the design
of the application at the very beginning, not at the end. Otherwise, the
cart is before the horse, and you'll have a serious problem that will become
evident to you when you want to "Giddy-up!"

You are right of course on this, and if security would have been a topic
from the beginning i would have been thinking about it before starting
the project
Security was no topic, the application is designed for max. 5 users of
the department who are storing order informations within the database.
All of them will be using the A07 Runtime, and the only reasons for me
thinking about this stuff is:

* i am paranoid
* _if_ a user happens to use the retail version of A07 he/she could
accidentally modify table content, bypassing my validation routines.

Second point is what i fear most - thats why i asked.
So if theres really no way to prevent this in an accde file - i will
have to live with that..

In previous versions of Access the database password for each linked table
was stored in a system table. I haven't seen how Access 2007 does it, but
my guess is that the password is encrypted in the system table, but it's
there.

I was able to pass the pwd in cleartext within the routines that do the
re-linking of the tables.
Just had to supply the parameter "pwd=<passwd>" to the Connect property
of the table definition objects.
Since i will deliver the FE as accde the passwd should be safe..

Data integrity isn't at risk in an encrypted database, but it will probably
run slower than an unencrypted database. But perhaps you mean "data
security"? Anyone reading the data file in a Hex editor won't be able to
read and instantly understand much in an encrypted database file, but there
are sure to be "readers" and "security breakers" on the market, even for
Access 2007.

Well no - i meant indeed integrity.
I dont want to end up with a corrupted Backend, with the customer
blaming me because it was encrypted.
Frequent backups are made by the customer, so, well - this will
hopefully be no issue.

Thanks again for your input!

--Michael

 

['69 Camaro]

Hi, Michael.

* i am paranoid

When it comes to security, this is a very good trait to have!

* _if_ a user happens to use the retail version of A07 he/she could
accidentally modify table content, bypassing my validation routines.

They really don't need the retail version of Access 2007 to make changes.
Other Office applications can connect to the database, such as Excel.
There's also a free Office compatibility pack that allows Office 2003
applications to connect to the ACE file format to read (and, I believe, to
update) data. Perhaps Validation Rules or table or column constraints at
the table level will prevent the users from doing what you don't want them
to do if they find a way to bypass your forms' validation routines.

So if theres really no way to prevent this in an accde file - i will have
to live with that..

Consider Arvin Meyer's advice of converting the ACCDB file to an MDB file
and implementing User-Level Security. He gave you a lot of good links. And
you may want to consider one of the free database engines, such as SQL
Server 2005 Express or Oracle 10g Express to store the data. Those can be
made secure. Both allow storage of up to 4 GB of data, but otherwise
they've got the robustness, security, speed, and reliability of their big
brother enterprise level database engines. (They just don't have as many
convenient features.)

I was able to pass the pwd in cleartext within the routines that do the
re-linking of the tables.
Just had to supply the parameter "pwd=<passwd>" to the Connect property of
the table definition objects.
Since i will deliver the FE as accde the passwd should be safe..

Check the "Connect" column in the MSysObjects table in the front end for
each of the linked tables. Do you see the database password listed?

I dont want to end up with a corrupted Backend, with the customer blaming
me because it was encrypted.

You don't need to worry about that. If the file gets corrupted, it won't be
due to encryption. It's usually due to a flakey network or a user hitting
the power switch on a networked computer when Access was busy writing to
disk.

Frequent backups are made by the customer, so, well - this will hopefully
be no issue.

That's a very good policy to have. Remind them about the second step in the
frequent backups, which is to periodically test the backups to ensure that
they work.

Thanks again for your input!

You're welcome!

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Michael Steiner]

Hi Arvin!

In addition to Gunny's always correct answers, let me add that converting to
an MDB/MDE shouldn't be too difficult as long as you haven't used any of the
format only features found in the new format. The typical manner of dealing
with table security is to deny everyone permission on the tables located on
the server and give them access to the data through OWNERACCESS OPTION
queries. Then you can make it so they can't even create their own queries to
get at the data. To learn more about User-level security, ready the security
FAQ. Here is a list of good security resources.

Exactly for the reason to be backwards compatible i didnt use any of the
new A07 features.
So converting to MDB/MDE shouldnt be that problem. The point is, that
the customer said access control on a per user basis wouldnt be
necessary - therefore not paying for the time it takes to implement it.
So for now i think i wont implement it, maybe the time comes they say
the need it.
(Up to now they have been working with an Access 2.0(!) db, so there
should be another 10 years left for me to maintain the database *g*)

Regarding permission on tables issue: Is this only possible using the
user-level security of A03?

I will go through your links to get a deeper understanding of this issue.
Thank you very much for your time!

cheers,

--Michael

 

[Arvin Meyer [MVP]]

Regarding permission on tables issue: Is this only possible using the
user-level security of A03?

I will go through your links to get a deeper understanding of this issue.
Thank you very much for your time!

User-level security is only possible with an MDB. The formats available
right now as an mdb are:

Access 2000 (the default)
Access 2002/2003 which I believe is the default for Access 2007 (not sure
and my Access 2007 is not availalble to check) although 2000 is still
available.

Using 2003/2007, you cannot make an MDE using the 2000 format.