Is it possible to make the MDB non-readable after a certain period?

[AA Arens]

I do have a database with customer info in it. To avoid it will be
taken out of our office, is it possible to make it not-readable after
a certain period?
then every let say seven days, I needs to "extend the license", so it
will last another week.

It consists of queries, forms, and tables, format Access 2003.

Bart

[salad]

AA Arens wrote:

I do have a database with customer info in it. To avoid it will be
taken out of our office, is it possible to make it not-readable after
a certain period?
then every let say seven days, I needs to "extend the license", so it
will last another week.

It consists of queries, forms, and tables, format Access 2003.

Bart

Make what unreadable? The data tables?

If someone has the database opened, they could export your table(s) to
Excel or a text file or whatever.

An alternative is that at the close of business, zip it up and encrypt
the mdb. That way the entire database; tables and all, are encryped and
password protected. The first person in decrypts the database. But the
problem is someone taking the database while working with it during
regular business hours.

Summary, I don't think so.

[ManningFan]

You can put a license on it to allow it to open for a limited amount
of time, but if the database isn't encrypted then someone can just go
through the back door to your data and export it to a fresh database
before that time expires.

On Feb 27, 5:01 am, "AA Arens" <[e-mail]:

I do have a database with customer info in it. To avoid it will be
taken out of our office, is it possible to make it not-readable after
a certain period?
then every let say seven days, I needs to "extend the license", so it
will last another week.

It consists of queries, forms, and tables, format Access 2003.

Bart

[The Frog]

Hi Bart,

When dealing with this type of problem you may find that the level of
complexity required in the coding is not worth the effort. There are
ways of encryting your data, with code, so that only authorised people
can see it / work with it. The warning that goes with this is that it
can be really painful to implement, and will require much trialling so
as to avoid "mishaps".

The model that might work for you is to use a two layered approach to
the encryption. As an example, when a user logs in to the application,
they can be assigned a user number. Using DAO it is possible to add
extra properties to the tables, columns, or the entire database if
necessary. For each user, at the level of granularity that is
appropriate for your purposes, you would add a property for the users
ID. The value that you then assign this property would work as
follows:

1/ the user has a password (for example), and this password is either
hashed or encrypted and stored as a variable in your code.
2/ the encrypted or hashed password is then used as a password for
encrypting via symmetric encryption (eg/ AES) the actual encryption
key for the table / column / database.
3/ this encrypted database / table / column key is then stored in the
userID property for that user.

In this way each user can be given access to the data they need. If
they try and read the data in the table without the correct key, or no
key at all, then all they will see is gibberish.

This method also needs a "recovery" key, or master key, that is stored
simply as another userID property against each Database / table /
column for the "administrator". The way that users are given access to
a particular component in the database is via the administrator
decrypting the Database / table / column key (the one encrypted with
eg/ AES and stored in the userID property), and then re-encrypting the
key with the users hashed password and storing it in the users userID
property. This gets to be rather tricky due to the nature of the
method. Effectively the user would have to supply you a password
(hashed or not) that they work with (or you assign them one).

The database can then be "re-saftey'd" weekly if you wish by changing
the Database / Table / column key (the AES encrypted one). This in
turn requires that the entire database is re-encrypted for the
process, and could take an awful long time, especially if you have a
lot of data to work with.

Make no mistake about it, this is getting to be a pretty complex
design and administrative problem.

I would suggest that a better way to solve the issue would be to
either migrate the application to a more sophisticated database
platform, or in turn to separate the application from the data and
store the data mdb in a secure location, if you like "hidden" from the
users and only accessable through the application.

In order to understand what is possible in your environment we would
need to know a little more about it, and the application. The other
thing to ask of course is: What is the level of threat and possible
damage that may occur if the database is copied. Is it actually worth
the effort of encrypting it severely? Would a simple separation of the
application from the data be sufficient? Are there other options
available for hosting the data that are already more secure (eg / SQL
Server)?

I suppose you have to ask yourself if the risk justifies the effort.
This is also one of the times when perhaps "abstracting" the whole
problem may be a good solution. You may use a Terminal Services type
approach here so that users have actually go no direct access to the
database at all, only a "window" to the application side of what you
have. That can prove expensive though, and it depends if your up for
the costs and the maintenance of the components needed for it. Is it
even worth going that far for this application? If it is, and you dont
have access already to a Terminal services server you could work with,
then I would suggest that maybe moving the app to SQL server is a
better option. Again though, you may require people to support that.

In the end, you have to weigh up the risk vs cost and determine what
is right for you. If this is a potentially dangerous / costly thing to
your company, then perhaps it is also a good idea to locate a
professional developer to shift the application to a more secure
environment.

Good luck, and please keep us posted with what you decide.

Cheers

The Frog

[AA Arens]

On Feb 28, 6:33 pm, "The Frog" <[e-mail]:

Hi Bart,

When dealing with this type of problem you may find that the level of
complexity required in the coding is not worth the effort. There are
ways of encryting your data, with code, so that only authorised people
can see it / work with it. The warning that goes with this is that it
can be really painful to implement, and will require much trialling so
as to avoid "mishaps".

The model that might work for you is to use a two layered approach to
the encryption. As an example, when a user logs in to the application,
they can be assigned a user number. Using DAO it is possible to add
extra properties to the tables, columns, or the entire database if
necessary. For each user, at the level of granularity that is
appropriate for your purposes, you would add a property for the users
ID. The value that you then assign this property would work as
follows:

1/ the user has a password (for example), and this password is either
hashed or encrypted and stored as a variable in your code.
2/ the encrypted or hashed password is then used as a password for
encrypting via symmetric encryption (eg/ AES) the actual encryption
key for the table / column / database.
3/ this encrypted database / table / column key is then stored in the
userID property for that user.

In this way each user can be given access to the data they need. If
they try and read the data in the table without the correct key, or no
key at all, then all they will see is gibberish.

This method also needs a "recovery" key, or master key, that is stored
simply as another userID property against each Database / table /
column for the "administrator". The way that users are given access to
a particular component in the database is via the administrator
decrypting the Database / table / column key (the one encrypted with
eg/ AES and stored in the userID property), and then re-encrypting the
key with the users hashed password and storing it in the users userID
property. This gets to be rather tricky due to the nature of the
method. Effectively the user would have to supply you a password
(hashed or not) that they work with (or you assign them one).

The database can then be "re-saftey'd" weekly if you wish by changing
the Database / Table / column key (the AES encrypted one). This in
turn requires that the entire database is re-encrypted for the
process, and could take an awful long time, especially if you have a
lot of data to work with.

Make no mistake about it, this is getting to be a pretty complex
design and administrative problem.

I would suggest that a better way to solve the issue would be to
either migrate the application to a more sophisticated database
platform, or in turn to separate the application from the data and
store the data mdb in a secure location, if you like "hidden" from the
users and only accessable through the application.

In order to understand what is possible in your environment we would
need to know a little more about it, and the application. The other
thing to ask of course is: What is the level of threat and possible
damage that may occur if the database is copied. Is it actually worth
the effort of encrypting it severely? Would a simple separation of the
application from the data be sufficient? Are there other options
available for hosting the data that are already more secure (eg / SQL
Server)?

I suppose you have to ask yourself if the risk justifies the effort.
This is also one of the times when perhaps "abstracting" the whole
problem may be a good solution. You may use a Terminal Services type
approach here so that users have actually go no direct access to the
database at all, only a "window" to the application side of what you
have. That can prove expensive though, and it depends if your up for
the costs and the maintenance of the components needed for it. Is it
even worth going that far for this application? If it is, and you dont
have access already to a Terminal services server you could work with,

Thanks the Frog for your explenation.

To make things just easy for now, how about the idea that you need to
login to access the files (like under Security Set password. This
login will be activated after let say a week. Can this be programmed
in VB code?

I understand this all is not waterproof.

Bart

then I would suggest that maybe moving the app to SQL server is a
better option. Again though, you may require people to support that.

In the end, you have to weigh up the risk vs cost and determine what
is right for you. If this is a potentially dangerous / costly thing to
your company, then perhaps it is also a good idea to locate a
professional developer to shift the application to a more secure
environment.

Good luck, and please keep us posted with what you decide.

Cheers

The Frog

[John]

I had a similar problem and fixed it this way
I created a table that had the date and time of the update.
When the database opened it checked this time was betweem date() and
date()+10 if it was over this date It would not proceed. and also it deleted
tables that contained the sensitive data.
Of course that data could be updated from the main source if they were still
allowed access to the main data.
May suit you
John

[storrboy]

On Mar 4, 6:14 pm, "John" <[e-mail]:

I had a similar problem and fixed it this way
I created a table that had the date and time of the update.
When the database opened it checked this time was betweem date() and
date()+10 if it was over this date It would not proceed. and also it deleted
tables that contained the sensitive data.
Of course that data could be updated from the main source if they were still
allowed access to the main data.
May suit you
John

And what happened if someone changed the date on their computer? Even
if by accident? Sounds kind of dangerous if the info really is
sensitive.

[John]

The info is only sensitive when the road warrior quits and leaves town with
all your sensitive data. At least when tables are deleted he/she is
restricted to what is retained in there heads. and they can always replace
the data from the main database if they can get back on the premises.
Life is just one big compromise.
John.

[storrboy]

On Mar 4, 7:09 pm, "John" <[e-mail]:

The info is only sensitive when the road warrior quits and leaves town with
all your sensitive data. At least when tables are deleted he/she is
restricted to what is retained in there heads. and they can always replace
the data from the main database if they can get back on the premises.
Life is just one big compromise.
John.

True.
It's just a scary thought considering some of the people I have to
work with.
Short of the headaches ULS brings, I'm not sure there is any 'solid'
way to prevent access to the tables. Lots of things to protect forms,
reports and code, but not much for tables. Most other methods rely on
the database being opened normaly.

[The Frog]

Hi again AA,

This wont really work, as the issue then becomes what do you do with
the passowrd? How do you get the person to be able to log back in the
following week when they are actually still allowed to.

The way I see it you have two problems you have to deal with.
1/ How to secure the actual data itself
2/ How to stop someone from accessing the data when their time is
expired

The first problem can be solved with varying levels of encryption, and
for most intents and purposes here I think we can find a suitable
ebcryption to work with - so let us consider this for the moment
relatively solved.

The second problem is the more difficult one. One of the ways I can
think of to do this is to have a type of "rolling" key generator. For
each user in the database you could make a simple login type
arrangement with a username and password, as well as a date field for
when they were last made active (or do it the other way, when they are
going to be active until). The password is actually generated by the
program, and on a pre-determined time / date the login code is
"rolled" by a known (only by you) algorithm. It doesnt really matter
what this algorithm is as long as the passwords can consist of only
symbols that are present on a normal keyboard, and are long enough to
not be easily guessed.

To initiate a user to a database you would need an "administrative"
user and password that does not change. You would login to the
database, set the username and password for the user. You would then
enter these exact same details as well as any required date
information for the "rolling" into a separate application only you
posess. The date will arrive, the password for the user will be
"rolled", and they will have to contact you to get the new password.
Maybe you can email it if this does not present a security threat, but
probably a phone call is better.

The encryption on the data stored in the tables has a key that is
known / embedded in the application. If the users password is out of
date the application doesnt proceed. All the data in all the tables
can be secured this way, especially the login table. Compile the
module that does the encrypting / decrypting so that the key doesnt
become public knowledge. It can be reverse engineered but it takes a
good deal of effort to do this and is probably beyond most sales
peoples ability do so so (no offence, I was one once and love the
work).

This is by no means perfect as a security model, but perhaps it can
suffice for your needs in this situation. The trick here is to roll
the keys at the same time / date both in your application and the
users copy of the db. You might want to do a "roll" say every Monday,
so that when the application starts up it can see when the last monday
was that the key was "rolled", and roll the key the appropriate number
of times to produce the correct key in sequence to match what you
would have in your username / password key generator application. This
issue is as old a cryptography itself, and is known as the key
distribution problem. PKI systems are designed to try and overcome
this, but they take a fair bit of work to create and can be an
expensive option. This simplified model here might do the trick for
you.

Do you have any idea of how seriously you may want to encrypt the
data. Would a simple XOR shift do the trick or do we need to go for
something really solid like AES or 3DES? It really depends on the
value of the data and the risk it poses if it is in the wild. I can
provide you some VBA routines for most symmetric encryption types. The
more advanced the encryption the slower the run, but the more secure
they are. There are ways to make this appear faster to the user too,
such as loading all the tables into memory as ADO recordsets,
disconnect the recordsets from the datasource, then parse the data
through the encryption / decryption routine. Or perhaps use some
temporary tables (make sure to delete them), or maybe just use some
user defined variables with data types that match the tables. Users
seem more willing to wait at the startup of a program than during its
operation, so if all the heavy lifting is done at the start (in a way
that doesnt compromise the security of the data) then maybe the user
wont even really notice the difference at all.

I dont have code for a "rolling" key system at hand. Perhaps another
user already has something we could adapt? Perhaps you are able to
write one? It may be something that you want to keep exclusively to
yourself anyway, so that it makes the job much harder to reverse
engineer. I would suggest that this is the best approach, but I am
certainly happy to help if you need it.

Let us know how you wish to proceed

Cheers

The Frog :-)

[The Frog]

Oops, I almost forgot to answer your questions about the Tools-

>Security->Set Database Password. Dont bother using this, there are

free utilities that can return the password instantly. It gives you a
false sense of security when you use this, but it is most definitely
not secure at all. The standard "security measures" built into access
are for the large part worthless. You can get some security by using
workgroups and so on, but it is still not ideal and they can be a pain
the administer.

The trick here for you it seems is to keep the day-to-day operation of
the system / application as simple and reliable as possible while
still keeping the data relatively safe. My previous post (just a few
minutes ago) I think could be on the right track for you. Let me know
what you think.

Cheers

The Frog