Security required - replace Access?

[Stav]

Hi there.

I'm working on an application that currently uses DAO to connect to an
Access 97 database. The database is created by and used exclusively by
the product to store search results and statistics during the
product's operation. The number of searches stored in one database
vary a lot (one user might only do 100 whilst another might do 1
million).

Once the search results have been stored, the product provides a
summary of the results using SELECT statements containing SUM
functions.

A new requirement for the product is that these results be encrypted
in such a way as that they cannot be viewed externally of the product
despite any reasonably determined attempt to do so.

I first considered using passwords and worgroup permissions but it
would appear that this protection is insufficient. I then considered
encrypting each of the fields in question, but these fields are also
the ones I want to carry out SUMs of, so I could no longer use them
and must implement my own function to decrypt each field and keep the
runnting total. Initial investigation seems to confirm this to be very
slow - are there any suggestions on how the performance of this could
be improved? The performance of the decryption algorithm itself is
reasonable, so I think my algorithm for performing the sum might be at
fault (simply MoveFirst, GetFieldValue, decrypt, add to total,
MoveNext) - how would one implement SUM normally?

Assuming I cannot get the performance up using encryption, I will have
to look at replacing Access. I want royalty-free distribution, so
initially looked at MSDE - will this inherently offer the security I
require? Are there any major downsides to implementing MSDE? Are there
other RDMBSs that I should also investigate?

Any advice you can offer me welcome. Thanks in advance,
Duncan

[Br@dley]

Re: Security required - replace Access?

Stav wrote:[color=blue]
> Hi there.
>
> I'm working on an application that currently uses DAO to connect to an
> Access 97 database. The database is created by and used exclusively by
> the product to store search results and statistics during the
> product's operation. The number of searches stored in one database
> vary a lot (one user might only do 100 whilst another might do 1
> million).
>
> Once the search results have been stored, the product provides a
> summary of the results using SELECT statements containing SUM
> functions.
>
> A new requirement for the product is that these results be encrypted
> in such a way as that they cannot be viewed externally of the product
> despite any reasonably determined attempt to do so.
>
> I first considered using passwords and worgroup permissions but it
> would appear that this protection is insufficient. I then considered
> encrypting each of the fields in question, but these fields are also
> the ones I want to carry out SUMs of, so I could no longer use them
> and must implement my own function to decrypt each field and keep the
> runnting total. Initial investigation seems to confirm this to be very
> slow - are there any suggestions on how the performance of this could
> be improved? The performance of the decryption algorithm itself is
> reasonable, so I think my algorithm for performing the sum might be at
> fault (simply MoveFirst, GetFieldValue, decrypt, add to total,
> MoveNext) - how would one implement SUM normally?
>
> Assuming I cannot get the performance up using encryption, I will have
> to look at replacing Access. I want royalty-free distribution, so
> initially looked at MSDE - will this inherently offer the security I
> require? Are there any major downsides to implementing MSDE? Are there
> other RDMBSs that I should also investigate?
>
> Any advice you can offer me welcome. Thanks in advance,
> Duncan[/color]

Can't you use a backend database with a database password?

However, it is quite easy to break into any Access database regardless
of any security.

I think SQL Server allows you to create application security but haven't
used it myself.

Br@dley

[duncan.fawkes@gmail.com]

Re: Security required - replace Access?

That's what I think I'll need to do, hence why I'm considering e.g.
MSDE. Encrypting on a field-by-field basis feels like jumping through
hoops to get Access to work for me, when replacing it is the proper
solution. However, replacing the backend DB is going to be quite a big
job so I want to make sure I've considered all options before doing so
- and then I have to think about choosing the right replacement (don't
want something too heavyweight).

Cheers.

Br@dley wrote:[color=blue]
> Stav wrote:[color=green]
> > Hi there.
> >
> > I'm working on an application that currently uses DAO to connect to[/color][/color]
an[color=blue][color=green]
> > Access 97 database. The database is created by and used exclusively[/color][/color]
by[color=blue][color=green]
> > the product to store search results and statistics during the
> > product's operation. The number of searches stored in one database
> > vary a lot (one user might only do 100 whilst another might do 1
> > million).
> >
> > Once the search results have been stored, the product provides a
> > summary of the results using SELECT statements containing SUM
> > functions.
> >
> > A new requirement for the product is that these results be[/color][/color]
encrypted[color=blue][color=green]
> > in such a way as that they cannot be viewed externally of the[/color][/color]
product[color=blue][color=green]
> > despite any reasonably determined attempt to do so.
> >
> > I first considered using passwords and worgroup permissions but it
> > would appear that this protection is insufficient. I then[/color][/color]
considered[color=blue][color=green]
> > encrypting each of the fields in question, but these fields are[/color][/color]
also[color=blue][color=green]
> > the ones I want to carry out SUMs of, so I could no longer use them
> > and must implement my own function to decrypt each field and keep[/color][/color]
the[color=blue][color=green]
> > runnting total. Initial investigation seems to confirm this to be[/color][/color]
very[color=blue][color=green]
> > slow - are there any suggestions on how the performance of this[/color][/color]
could[color=blue][color=green]
> > be improved? The performance of the decryption algorithm itself is
> > reasonable, so I think my algorithm for performing the sum might be[/color][/color]
at[color=blue][color=green]
> > fault (simply MoveFirst, GetFieldValue, decrypt, add to total,
> > MoveNext) - how would one implement SUM normally?
> >
> > Assuming I cannot get the performance up using encryption, I will[/color][/color]
have[color=blue][color=green]
> > to look at replacing Access. I want royalty-free distribution, so
> > initially looked at MSDE - will this inherently offer the security[/color][/color]
I[color=blue][color=green]
> > require? Are there any major downsides to implementing MSDE? Are[/color][/color]
there[color=blue][color=green]
> > other RDMBSs that I should also investigate?
> >
> > Any advice you can offer me welcome. Thanks in advance,
> > Duncan[/color]
>
> Can't you use a backend database with a database password?
>
> However, it is quite easy to break into any Access database[/color]
regardless[color=blue]
> of any security.
>
> I think SQL Server allows you to create application security but[/color]
haven't[color=blue]
> used it myself.
>
> Br@dley[/color]

[Tom van Stiphout]

Re: Security required - replace Access?

On 19 Apr 2005 03:19:46 -0700, duncan.fawkes@g mail.com (Stav) wrote:

I see a couple of options:
* Store the search data in such a way it is not easily humanly
readable but it is the same for same values.
Say a search for "firstname='Joe '"
is recorded as "2='Kpf'"
every time we search for Joe. You can sum over this data first, and
decrypt later:
Msgbox Decrypt(DSum("E ncryptedField", "SomeTable" ))
(Note: I do NOT recommend DSum when speed is required - this is just
for illustration)

* Select sum(Decrypt(Enc ryptedField)) from SomeTable
Here you're having SQL do the heavy lifting of calling your public
Decrypt function in a module repeatedly. Of course this function is
optimized for speed. No error handler.

* Go to battle with the boss about the need for encryption on this
data.

-Tom.

[color=blue]
>Hi there.
>
>I'm working on an application that currently uses DAO to connect to an
>Access 97 database. The database is created by and used exclusively by
>the product to store search results and statistics during the
>product's operation. The number of searches stored in one database
>vary a lot (one user might only do 100 whilst another might do 1
>million).
>
>Once the search results have been stored, the product provides a
>summary of the results using SELECT statements containing SUM
>functions.
>
>A new requirement for the product is that these results be encrypted
>in such a way as that they cannot be viewed externally of the product
>despite any reasonably determined attempt to do so.
>
>I first considered using passwords and worgroup permissions but it
>would appear that this protection is insufficient. I then considered
>encrypting each of the fields in question, but these fields are also
>the ones I want to carry out SUMs of, so I could no longer use them
>and must implement my own function to decrypt each field and keep the
>runnting total. Initial investigation seems to confirm this to be very
>slow - are there any suggestions on how the performance of this could
>be improved? The performance of the decryption algorithm itself is
>reasonable, so I think my algorithm for performing the sum might be at
>fault (simply MoveFirst, GetFieldValue, decrypt, add to total,
>MoveNext) - how would one implement SUM normally?
>
>Assuming I cannot get the performance up using encryption, I will have
>to look at replacing Access. I want royalty-free distribution, so
>initially looked at MSDE - will this inherently offer the security I
>require? Are there any major downsides to implementing MSDE? Are there
>other RDMBSs that I should also investigate?
>
>Any advice you can offer me welcome. Thanks in advance,
>Duncan[/color]

[David Schofield]

Re: Security required - replace Access?

On Tue, 19 Apr 2005 10:55:02 GMT, "Br@dley" <n0mail@4u.co m> wrote:

<big snip>[color=blue]
>
>However, it is quite easy to break into any Access database regardless
>of any security.[/color]


Rubbish

David

[Steve Jorgensen]

Re: Security required - replace Access?

On 19 Apr 2005 10:06:01 -0500, d.schofieldREMO VE@blueyonder.c o.uk (David
Schofield) wrote:
[color=blue]
>On Tue, 19 Apr 2005 10:55:02 GMT, "Br@dley" <n0mail@4u.co m> wrote:
>
><big snip>[color=green]
>>
>>However, it is quite easy to break into any Access database regardless
>>of any security.[/color]
>
>
>Rubbish[/color]

No - it's not rubbish. Access was not designed for high security, and it
doesn't have high security. Fortunately, most of us only need the kind of
security that keeps honest people honest.

[Steve Jorgensen]

Re: Security required - replace Access?

Do you really mean "cannot be viewed externally of the product", or do you
mean "cannot be viewed any better outside the product than inside"? In other
words, is it that you want to limit what kinds of searches can be done by
whom, or is it that you want to prevent automated extract of the same search
results that could be obtained using the application UI.

The solution will be very different depending on the answer to the above.

On 19 Apr 2005 03:19:46 -0700, duncan.fawkes@g mail.com (Stav) wrote:
[color=blue]
>Hi there.
>
>I'm working on an application that currently uses DAO to connect to an
>Access 97 database. The database is created by and used exclusively by
>the product to store search results and statistics during the
>product's operation. The number of searches stored in one database
>vary a lot (one user might only do 100 whilst another might do 1
>million).
>
>Once the search results have been stored, the product provides a
>summary of the results using SELECT statements containing SUM
>functions.
>
>A new requirement for the product is that these results be encrypted
>in such a way as that they cannot be viewed externally of the product
>despite any reasonably determined attempt to do so.
>
>I first considered using passwords and worgroup permissions but it
>would appear that this protection is insufficient. I then considered
>encrypting each of the fields in question, but these fields are also
>the ones I want to carry out SUMs of, so I could no longer use them
>and must implement my own function to decrypt each field and keep the
>runnting total. Initial investigation seems to confirm this to be very
>slow - are there any suggestions on how the performance of this could
>be improved? The performance of the decryption algorithm itself is
>reasonable, so I think my algorithm for performing the sum might be at
>fault (simply MoveFirst, GetFieldValue, decrypt, add to total,
>MoveNext) - how would one implement SUM normally?
>
>Assuming I cannot get the performance up using encryption, I will have
>to look at replacing Access. I want royalty-free distribution, so
>initially looked at MSDE - will this inherently offer the security I
>require? Are there any major downsides to implementing MSDE? Are there
>other RDMBSs that I should also investigate?
>
>Any advice you can offer me welcome. Thanks in advance,
>Duncan[/color]

[Lyle Fairfield]

Re: Security required - replace Access?

David Schofield wrote:[color=blue]
> On Tue, 19 Apr 2005 10:55:02 GMT, "Br@dley" <n0mail@4u.co m> wrote:
>
> <big snip>
>[color=green]
>>However, it is quite easy to break into any Access database regardless
>>of any security.[/color]
>
> Rubbish
>
> David[/color]

Why do you say this, David? I have felt for a very long time that any
application/file, whatever, is secure only in inverse proportion to the
worth of cracking it. Condie's chastity belt may be more secure than Jlo's.

That is, if it's worth my while, I or someone else will crack it.

In a current application I am using the crypt api functions from
advapi32 dll compounded by two false security trails interwoven into my
code which resides in an mde. I do hope that this scheme will defeat
someone who is playing around over morning coffee, but I'm not
confident it will prevent a determined and skillful hacker from breaking in.

[David Schofield]

Re: Security required - replace Access?

On Tue, 19 Apr 2005 08:21:58 -0700, Steve Jorgensen
<nospam@nospam. nospam> wrote:
[color=blue]
>On 19 Apr 2005 10:06:01 -0500, d.schofieldREMO VE@blueyonder.c o.uk (David
>Schofield) wrote:
>[color=green]
>>On Tue, 19 Apr 2005 10:55:02 GMT, "Br@dley" <n0mail@4u.co m> wrote:
>>
>><big snip>[color=darkred]
>>>
>>>However, it is quite easy to break into any Access database regardless
>>>of any security.[/color]
>>
>>
>>Rubbish[/color]
>
>No - it's not rubbish. Access was not designed for high security, and it
>doesn't have high security. Fortunately, most of us only need the kind of
>security that keeps honest people honest.
>[/color]
If done properly, it is rubbish that it is EASY, else PM wouldn't have
made a living at it. You try it.
David

[David Schofield]

Re: Security required - replace Access?

On Tue, 19 Apr 2005 11:48:15 -0400, Lyle Fairfield <lylefair@yahoo .ca>
wrote:
[color=blue]
>David Schofield wrote:[color=green]
>> On Tue, 19 Apr 2005 10:55:02 GMT, "Br@dley" <n0mail@4u.co m> wrote:
>>
>> <big snip>
>>[color=darkred]
>>>However, it is quite easy to break into any Access database regardless
>>>of any security.[/color]
>>
>> Rubbish
>>
>> David[/color]
>
>Why do you say this, David? I have felt for a very long time that any
>application/file, whatever, is secure only in inverse proportion to the
>worth of cracking it. Condie's chastity belt may be more secure than Jlo's.
>
>That is, if it's worth my while, I or someone else will crack it.[/color]
I agree with this![color=blue]
>
>In a current application I am using the crypt api functions from
>advapi32 dll compounded by two false security trails interwoven into my
>code which resides in an mde. I do hope that this scheme will defeat
>someone who is playing around over morning coffee, but I'm not
>confident it will prevent a determined and skillful hacker from breaking in.[/color]
Hi
see my reply to Steve. If an access db is secure properly it is
quite/very hard to bypass it. Only an idiot would say it is EASY.
David

[Larry Linson]

Re: Security required - replace Access?

"David Schofield" wrote
[color=blue]
> If done properly, it is rubbish that it is
> EASY, else PM wouldn't have made
> a living at it. You try it.[/color]

David, Sergei Gavrilov had (and I assume still has) a free package on his
site that would retrieve all the necessary information to get into an Access
97 database, even if you did not have the workgroup file. I think he has it
there just to demonstrate what Steve just said. With that package, it IS
easy.

Access security has not been significantly changed since Access 97.

There are other third-party "password retrieval" packages around for later
versions... the going rate for a very capable one that will break 'user and
group' level security used to be US$140. If you Google, you might find
someone has said, "If your data is worth more to you than $140, use
something other than Access security to protect it."

Encrypting in Access is a separate issue, in any case. You do not specify
the keys, and anyone opening the database in Access can see the unencrypted
data. It is a feature just to keep someone with a disk zapper from going
around reading the information.

Larry Linson
Microsoft Access MVP

[Larry Linson]

Re: Security required - replace Access?

"David Schofield" wrote
[color=blue]
> see my reply to Steve. If an access db
> is secure properly it is quite/very hard
> to bypass it. Only an idiot would say
> it is EASY.[/color]

It doesn't require an idiot to be wrong; only someone who doesn't really
know what they are talking about. Steve Jorgensen is definitely not an
"idiot"; he does know what he is talking about.

I presume you are not, either.

However, a quick visit to Gavrilov's site, and trying his package on a
well-secured Access 97 database may convince you that you are wrong on this
issue.

Larry Linson

[Br@dley]

Re: Security required - replace Access?

Larry Linson wrote:[color=blue]
> "David Schofield" wrote
>[color=green]
>> If done properly, it is rubbish that it is
>> EASY, else PM wouldn't have made
>> a living at it. You try it.[/color][/color]
[color=blue]
> David, Sergei Gavrilov had (and I assume still has) a free package on
> his site that would retrieve all the necessary information to get
> into an Access 97 database, even if you did not have the workgroup
> file. I think he has it there just to demonstrate what Steve just
> said. With that package, it IS easy.[/color]

There are also tools that basically give the default Admin user full
permissions again, hence no workgroup info is needed.
[color=blue]
> Access security has not been significantly changed since Access 97.
>
> There are other third-party "password retrieval" packages around for
> later versions... the going rate for a very capable one that will
> break 'user and group' level security used to be US$140. If you
> Google, you might find someone has said, "If your data is worth more
> to you than $140, use something other than Access security to protect
> it."[/color]

You can get many tools free (or illegally find serial codes for them).
You can buy tools to break any MSOffice product.

<>

Br@dley

[Br@dley]

Re: Security required - replace Access?

David Schofield wrote:[color=blue]
> On Tue, 19 Apr 2005 10:55:02 GMT, "Br@dley" <n0mail@4u.co m> wrote:
>
> <big snip>[color=green]
>>
>> However, it is quite easy to break into any Access database
>> regardless of any security.[/color][/color]
[color=blue]
> Rubbish[/color]

I've got tools to break pretty much any Access database. You can easily
find them on the net.

Database passwords - easy
Workgroup security - easy
Encrypted database - takes a while longer but possible

Br@dley

[Br@dley]

Re: Security required - replace Access?

David Schofield wrote:[color=blue]
> On Tue, 19 Apr 2005 08:21:58 -0700, Steve Jorgensen
> <nospam@nospam. nospam> wrote:
>[color=green]
>> On 19 Apr 2005 10:06:01 -0500, d.schofieldREMO VE@blueyonder.c o.uk
>> (David Schofield) wrote:
>>[color=darkred]
>>> On Tue, 19 Apr 2005 10:55:02 GMT, "Br@dley" <n0mail@4u.co m> wrote:
>>>
>>> <big snip>
>>>>
>>>> However, it is quite easy to break into any Access database
>>>> regardless of any security.
>>>
>>>
>>> Rubbish[/color][/color][/color]
[color=blue][color=green]
>> No - it's not rubbish. Access was not designed for high security,
>> and it doesn't have high security. Fortunately, most of us only
>> need the kind of security that keeps honest people honest.[/color][/color]
[color=blue]
> If done properly, it is rubbish that it is EASY, else PM wouldn't have
> made a living at it. You try it.
> David[/color]

I can easily break an workgroup secured database in a matter of seconds
even without the workgroup file.

I'm afraid it is very easy.

Br@dley