Protecting an Access Database

[JonathanLewin]

I have built an Access database a while ago and I have gained a lot of
interest in the database. I now need to protect it so no one can change any
of the data or applications within it i.e. make it equivalent to a read only
file or database some how. I have hidden the database window so only the
display screen an menu options can be accessed but this does not stop people
from un hiding the database window and changing the data, tables, queries,
forms, reports etc.

How do I make it so people can open the database and use it but can not
actually go in to it and change ANYTHING!?

Any suggestions would be appreciated.

Thank you
Jonathan

 

[Scott McDaniel]

I have built an Access database a while ago and I have gained a lot of
interest in the database. I now need to protect it so no one can change any
of the data or applications within it i.e. make it equivalent to a read only
file or database some how. I have hidden the database window so only the
display screen an menu options can be accessed but this does not stop people
from un hiding the database window and changing the data, tables, queries,
forms, reports etc.

How do I make it so people can open the database and use it but can not
actually go in to it and change ANYTHING!?

Deploying your database in the .mde format will stop users from being able to view or change Forms, Reports, or Modules.

To protect Tables and Queries, you'll need to implement User Level SEcurity. See Joan Wild's site for detailed
step-by-step instructions for doing this;

www.jmwild.com

You will also want to move all data access to RWOP (Run With Owner Permission) queries so that you can remove
permissions directly from the table for users. Again, see Joan's site for info on this.

Also be aware that ULS is easily breakable for those who wish to purhcase a password unlocker.

Any suggestions would be appreciated.

Thank you
Jonathan

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Nando]

Deploying your database in the .mde format will stop users from being able
to view or change Forms, Reports, or Modules.

To protect Tables and Queries, you'll need to implement User Level
SEcurity. See Joan Wild's site for detailed
step-by-step instructions for doing this;

www.jmwild.com

You will also want to move all data access to RWOP (Run With Owner
Permission) queries so that you can remove
permissions directly from the table for users. Again, see Joan's site for
info on this.

Also be aware that ULS is easily breakable for those who wish to purhcase
a password unlocker.

Any ideas on what to do to better protect the database after using ULS? I
don't know what those unlockers do. Do those programs crack the Admin
password? Curious, because I want to make my database more secure. Sometimes
db users (employees) have too much time on their hands.

 

[Scott McDaniel]

Any ideas on what to do to better protect the database after using ULS? I
don't know what those unlockers do. Do those programs crack the Admin
password? Curious, because I want to make my database more secure. Sometimes
db users (employees) have too much time on their hands.

The password crackers give you a list of all users in the database, along with their passwords. Once you have this
information, it's easy to log in as an Admin user and do what you want.

ULS is about as secure as you can get with Access/Jet. If you need better data security, you'll need to move to a
different database engine like SQL Server, MySQL, etc. Note that SQL Server Express is free and is generally more secure
than Jet - although that's not always the case.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Nando]

The password crackers give you a list of all users in the database, along
with their passwords. Once you have this
information, it's easy to log in as an Admin user and do what you want.

ULS is about as secure as you can get with Access/Jet. If you need better
data security, you'll need to move to a
different database engine like SQL Server, MySQL, etc. Note that SQL
Server Express is free and is generally more secure
than Jet - although that's not always the case.

Thanks Scott! I see...So I guess that not even encryption (which I have not
used yet) would have made the MDBs more secure.

 

[Scott McDaniel]

Thanks Scott! I see...So I guess that not even encryption (which I have not
used yet) would have made the MDBs more secure.

If you mean the builtin Access encryption, then no, it won't make much of a difference. With the builtin encryption,
Access will simply decrypt the data as needed when the database is open, so if a user is actually able to open the
database, they'll see the unecrypted data. The only time buitin encryption would be effective would be if someone
attempted to link to the database from another application (like via ADO) ... in this case, they'd see the encrypted
data.

You can build your own encryption routines and encrypt all data going into the database, then decrypt as it comes out.
If you do this, then your data would be as secure as your encryption routines. This would, of course, come with a price
re: performance ... encrypting and decrypting take time. This would also mean moving to a fully unbound app, which can
take some time to create.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Nando]

If you mean the builtin Access encryption, then no, it won't make much of
a difference. With the builtin encryption,
Access will simply decrypt the data as needed when the database is open,
so if a user is actually able to open the
database, they'll see the unecrypted data. The only time buitin encryption
would be effective would be if someone
attempted to link to the database from another application (like via ADO)
... in this case, they'd see the encrypted
data.

You can build your own encryption routines and encrypt all data going into
the database, then decrypt as it comes out.
If you do this, then your data would be as secure as your encryption
routines. This would, of course, come with a price
re: performance ... encrypting and decrypting take time. This would also
mean moving to a fully unbound app, which can
take some time to create.

Hmm...that's sounds very good to have the encryption at the field-content
level. I like that! But using it will not only affect performance, but the
ability to use bound data-controls on forms. That would make application
development quite painful.

I think Microsoft could have created a built-in encryption function within
the VBA library to intercept the I/O, writing and reading of data in tables.
The function could have been initialized with a decryption key once a Access
session is started. This key (or password) could have been supplied through
the a user's VBA module and called at start up.

Either Microsoft don't care, "don't know," or seems to be just favoring
"marketing" rather than the application principle they once preached.

I'm just analyzing here, I like to thing about this kind of architectural
issues, not that this is a problem for me right now (but I know it will be
soon). I once wrote a client/server application using VB and SQL Server. But
sometimes (depending on the scenario and users) to have a server to host a
database is just extreme and non-possible. If the Access object model is as
flexible as thought, then perhaps there is a way one can intercept the I/O
of the data controls and implement an encryption function of my own. What
are your thoughts on all this?

 

[Scott McDaniel]

Hmm...that's sounds very good to have the encryption at the field-content
level. I like that! But using it will not only affect performance, but the
ability to use bound data-controls on forms. That would make application
development quite painful.

Yes, which is exactly what I said in my last line re: Unbound app. Many professional Access developers choose unbound
mode, simply because it provides so much more flexibility over bound mode (same with VB apps).

I think Microsoft could have created a built-in encryption function within
the VBA library to intercept the I/O, writing and reading of data in tables.
The function could have been initialized with a decryption key once a Access
session is started. This key (or password) could have been supplied through
the a user's VBA module and called at start up.

They could have, but what's the point? Jet was never really intended to be a highly secure platform. It was, instead,
intended for small workgroups and we - that is, the development community - have stretched those limits.

Besides, I think it's pretty much impossible for MS - or any company trying to accomodate millions of users, for that
matter - to consider every scenario and to accomodate the needs of every developer. This was the reason they included
VBA with Access, so that developers could extend and augment beyond the base functionality. It's also why ActiveX
control vendors can make a comfortable living!

Either Microsoft don't care, "don't know," or seems to be just favoring
"marketing" rather than the application principle they once preached.

I think MS is a company and like any other company they make decisions based on what they believe will bring the most
profits! You can bet that if MS thought they could sell more copies of Access by including better encryption routines,
those routines would have been developed and provided ... assuming they could justify the man-hours spent developing and
testing them, that is.

I'm just analyzing here, I like to thing about this kind of architectural
issues, not that this is a problem for me right now (but I know it will be
soon). I once wrote a client/server application using VB and SQL Server. But
sometimes (depending on the scenario and users) to have a server to host a
database is just extreme and non-possible. If the Access object model is as
flexible as thought, then perhaps there is a way one can intercept the I/O
of the data controls and implement an encryption function of my own. What
are your thoughts on all this?

I suppose you could do something in the BeforeUpdate event of a form and encrypt from there ... but doing so may trigger
an endless loop, and if you allow direct access to the tables, or allow updates through Queries, then you can't do it
from there.



Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com