encryption database in access 2007

#1

Hi,

We are looking for install access 2007. For that I would like to know :
- Can we encrypt only some of tables in a database?
- Can we give our own key?
- How use encryption function via API (C# in visual studio) ? What is the
complexity?
Thanks in advance

Marlène

#2

"Marlène Gagnaire" <marlene.gagnaire (AT) atosorigin (DOT) com> wrote

Quote:

Hi,

We are looking for install access 2007. For that I would like to know :
- Can we encrypt only some of tables in a database?
- Can we give our own key?
- How use encryption function via API (C# in visual studio) ? What is the
complexity?

This is not strong encryption, but it will keep out idle prying eyes. If all
you are trying to do is prevent the average user from browsing the
information in the tables, then a simple operation with a master string will
work to both encrypt and decrypt a string:

Public Function Encrypt(ByVal strIn As String, _
ByVal strCryptKey As String) As String

Dim i As Integer
Dim bytDat As Byte
Dim bytKey As Byte
Dim strEncrypted As String

On Error GoTo Err_Handler

Encrypt = vbNullString
For i = 1 To Len(strIn)
bytDat = Asc(Mid(strIn, i, 1))
bytKey = Asc(Mid(strCryptKey, (i Mod Len(strCryptKey)) + 1))
strEncrypted = strEncrypted & Chr(bytDat Xor bytKey)
Next i
If strEncrypted <> vbNullString Then Encrypt = strEncrypted
Exit Function

Exit_Here:
Exit Function

Err_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

Run it in an update query to encrypt the data in an entire field.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access Solutions", published by Wiley

#3

Marlène Gagnaire wrote:

Quote:

Hi,

We are looking for install access 2007. For that I would like to know :
- Can we encrypt only some of tables in a database?

From what I see, it's all or none.

Quote:

- Can we give our own key?

Sure.

#4

Marlène Gagnaire <marlene.gagnaire (AT) atosorigin (DOT) com> wrote in
news:iabhrs$7f3$1 (AT) localhost (DOT) localdomain:

Quote:

We are looking for install access 2007. For that I would like to
know : - Can we encrypt only some of tables in a database?
- Can we give our own key?
- How use encryption function via API (C# in visual studio) ? What
is the complexity?

Are you using Access 2007 or are you using just the database engine,
ACE/Jet?

Your C# question suggests you're not using Access at all, just the
database engine, and so I think you'd need to ask questions about
how to encrypt data in a forum devoted to C#.

For what it's worth, I've implemented credit card number encryption
with a user-supplied key in an Access app using the MS Crypto API.
The code provided by MS is pretty easy to use (though there are few
gotchas with it that I've posted about before).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#5

That crypto really provides a false sense of security. You might as
well use ROT13 as the basis. I am sure you can achieve that with a
formula in SQL directly and be done with the function altogether. All
you have here is obfuscation, not crypto.

The Frog.

#6

The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:26b4f10b-5640-42b1-aaf4-7486bddf1d79 (AT) p1g2000yqm (DOT) googlegroups.com
:

Quote:

That crypto really provides a false sense of security. You might
as well use ROT13 as the basis. I am sure you can achieve that
with a formula in SQL directly and be done with the function
altogether. All you have here is obfuscation, not crypto.

Are you talking to me? I said to use the Windows Crypto API, which
is real AES encryption. You may be referring to Arvin's post, not
mine.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#7

Indeed I am referencing Arvin's post and not yours. The windows crypto
API, although not 'perfect' is a damn sight better than what is
offered above. Given the OP's original question I think you are right
on the money. The only other method I can think of is a roll-your-own,
however I am not confident that the OP has the background knowledge to
achieve this.

Still, I wonder if there is a ROT13 for SQL so it can be run inline???
I must have a google around and see what I can find.

The Frog

#8

The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:6470e8ee-b940-428e-8558-8c042141b629 (AT) l8g2000yql (DOT) googlegroups.com
:

Quote:

Indeed I am referencing Arvin's post and not yours. The windows
crypto API, although not 'perfect' is a damn sight better than
what is offered above. Given the OP's original question I think
you are right on the money. The only other method I can think of
is a roll-your-own, however I am not confident that the OP has the
background knowledge to achieve this.

I can't understand how using the Windows Crypto API would not be the
only possible solution. What's not "perfect" about it? I'm using an
encryption key supplied by the users to encrypt/decrypt and not
stored in the code I'm using, so as far as I can tell, it's as
unbreakable as AES encryption of any variety (which is unbreakable
for all practical purposes).

Quote:

Still, I wonder if there is a ROT13 for SQL so it can be run
inline??? I must have a google around and see what I can find.

Er, what? That's even less effective obfuscation that Arvin's.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#9

The Windows crypto API is not without its detractors. There are
concerns about the implementation of the algorithms, in particular
about in-built exploits known to the manufacturer but not to the
public. There are also issues surrounding the origin of some of the
base elements used in the construction number handling aspects - in
particular that somone knowing the 'master' (although claimed by MS
that this is unknown) can actually subvert the entire crypto system
provided. From a security point of view, in particular with regards to
risk assessment, the lack of peer review and clarity of the
implementation leads to doubt about the quality of the API and its
security. Depending on needs this can be a serious concern.

A roll your own can be a superior way of dealing with risk, but only
if you know what you are actually doing with crypto. If you muck it up
then you can end up with a false sense of security, and even if the
algorithms are correct the implementation you choose to follow can
render them useless if not handled properly. Havign said all this, I
am not aware of a single dedicated security company that would rely
solely on the windows crypto API. The majority of 'serious' players in
that field design their own implementations, and some use modified
implementations of open source methods and code. A lot of them will
actually run their crypto on a separate hardware chip, sometimes
embedded into a token.

In short the windows crypto API is not the be-all and end-all of
security, but for many it is a good start. The usability depends on
teh risk assessment, not on anything else. As for the ROT13 - since it
is just obfuscation the simpler and faster it is, the less complex to
deploy, the more reliable and flexible it will be. If the OP simply
wants to 'hide' data from a casual read then ROT13 might well suit the
task. Again it comes down to the risk assessment. FWIW I have never
seen a risk assessment that has recommended the implementation of the
windows crypto API, and that is after many years working in the
security industry. Risk assessment is the key here, and I suppose only
OP can really make that decision.

Cheers

The Frog

#10

The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:9d5c3edc-afd1-4d76-819c-f7b5bf3cbf8c (AT) s4g2000yql (DOT) googlegroups.com
:

Quote:

The Windows crypto API is not without its detractors.

For use in Access with a Jet/ACE back end, what better alternative
is there? I don't think there is one. I certainly prefer this kind
of functionality to be provided by the OS instead of having to rely
on somebody's hand-coded implementation of the main encryption
standards.

Quote:

There are
concerns about the implementation of the algorithms, in particular
about in-built exploits known to the manufacturer but not to the
public. There are also issues surrounding the origin of some of
the base elements used in the construction number handling aspects
- in particular that somone knowing the 'master' (although claimed
by MS that this is unknown) can actually subvert the entire crypto
system provided. From a security point of view, in particular with
regards to risk assessment, the lack of peer review and clarity of
the implementation leads to doubt about the quality of the API and
its security. Depending on needs this can be a serious concern.

I can't see a reasonable alternative. And, frankly, none of those
concerns is really relevant to my clients' applications. It's
perfectly adequate for encrypting their customers' credit card
numbers.

Quote:

A roll your own can be a superior way of dealing with risk, but
only if you know what you are actually doing with crypto.

I really don't think betting on some buy in his garage is a superior
strategy compared to using MS's implementation.

Quote:

If you muck it up
then you can end up with a false sense of security, and even if
the algorithms are correct the implementation you choose to follow
can render them useless if not handled properly. Havign said all
this, I am not aware of a single dedicated security company that
would rely solely on the windows crypto API. The majority of
'serious' players in that field design their own implementations,
and some use modified implementations of open source methods and
code. A lot of them will actually run their crypto on a separate
hardware chip, sometimes embedded into a token.

But at that level, we're way outside the realm of Access
applications, so I just don't see how this is relevant to the
present discussion.

Quote:

In short the windows crypto API is not the be-all and end-all of
security, but for many it is a good start. The usability depends
on teh risk assessment, not on anything else. As for the ROT13 -
since it is just obfuscation the simpler and faster it is, the
less complex to deploy, the more reliable and flexible it will be.
If the OP simply wants to 'hide' data from a casual read then
ROT13 might well suit the task. Again it comes down to the risk
assessment. FWIW I have never seen a risk assessment that has
recommended the implementation of the windows crypto API, and that
is after many years working in the security industry. Risk
assessment is the key here, and I suppose only OP can really make
that decision.

I think your position here is completely insane. You say ROT13 is OK
for obfuscation, but the Windows Crypto API is so inadequate that
nobody should use it.

There is a certain amount of cognitive dissonance there that I think
is exceedingly problematic.

But to make this a profitable discussion, what would you use to
encrypt sensitive data in an Access application, assuming you're not
using a back end that has its own encryption functions.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/