Encrypting fields and email

Wondering if anyone has done any work encrypting fields using pgp. We would
like to ensure that certain data stays strictly confidential, not even dba
should be able to see the data in the database. Only way I can see that is
to ensure encrypting and decrypting data real time using keys.

How about plugging in encryption to the email system as well. Some of the
emails would contain confidential data that should not be readable by just
anyone.

Anyone done anything like this? Any plans for RT to go in this direction?

Thanks
Dave

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier �lectronique est confidentiel et prot�g�. L’exp�diteur ne renonce pas aux droits et obligations qui s’y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu’il contient par une personne autre que le (les) destinataire(s) d�sign�(s) est interdite. Si vous recevez ce courrier �lectronique par erreur, veuillez m’en aviser imm�diatement, par retour de courrier �lectronique ou par un autre moyen.

Wondering if anyone has done any work encrypting fields using pgp. We
would
like to ensure that certain data stays strictly confidential, not even
dba
should be able to see the data in the database. Only way I can see
that is
to ensure encrypting and decrypting data real time using keys.

And how are you going to keep the DBA from seeing the key in plaintext
right there in your program that needs to do the decryption?

Vivek Khera, Ph.D.
+1-301-869-4449 x806

smime.p7s (2.42 KB)

david.falkenburger@rbc.com wrote:

How about plugging in encryption to the email system as well. Some of
the emails would contain confidential data that should not be readable
by just anyone.
Drop all emails in MTA then all who has access to mails only after MTA
will never read this for sure, but this technique don’t save from outsiders.

//joke :slight_smile:

			Best regards. Ruslan.

I was thinking more along the lines of public and private key technology. If
I was to use the private key to encrypt all the data in one or more fields,
a key that I as developer generate, then having access to the database or
the server will not do you any good unless you have the corresponding
private key to decrypt. I could distribute the private key to authorized
individuals only. So now you would not only have to be authorized to access
RT, and the appropriate queue, but would also need the public key available
to your browser for decryption.

Something along the lines of what Lotus Notes is able to do. Not sure if
their Domino technology can do it as well.

Dave-----Original Message-----
From: Vivek Khera [mailto:vivek@khera.org]
Sent: Thursday, September 30, 2004 10:37 AM
To: RT Mailing List
Subject: Re: [rt-users] encrypting fields and email

http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT wiki at http://wiki.bestpractical.com

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier �lectronique est confidentiel et prot�g�. L’exp�diteur ne renonce pas aux droits et obligations qui s’y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu’il contient par une personne autre que le (les) destinataire(s) d�sign�(s) est interdite. Si vous recevez ce courrier �lectronique par erreur, veuillez m’en aviser imm�diatement, par retour de courrier �lectronique ou par un autre moyen.

Maybe you could integrate this with RT:

http://www.fourmilab.ch/javascrypt/

-ToddOn Thu, Sep 30, 2004 at 10:27:30AM -0400, david.falkenburger@rbc.com wrote:

Wondering if anyone has done any work encrypting fields using pgp. We would
like to ensure that certain data stays strictly confidential, not even dba
should be able to see the data in the database. Only way I can see that is
to ensure encrypting and decrypting data real time using keys.

How about plugging in encryption to the email system as well. Some of the
emails would contain confidential data that should not be readable by just
anyone.

Anyone done anything like this? Any plans for RT to go in this direction?

Thanks
Dave

I was thinking more along the lines of public and private key
technology. If
I was to use the private key to encrypt all the data in one or more
fields,
a key that I as developer generate, then having access to the database
or

So, the reader would be required to type the password to the private
key to view every message? What a pain. You can’t store it in the
session or the DBA could get it from there. This is the dilemma of a
stateless protocol called HTTP :slight_smile:

Vivek Khera, Ph.D.
+1-301-869-4449 x806

smime.p7s (2.42 KB)

I was thinking more along the lines of public and private key
technology. If I was to use the private key to encrypt all the data in
one or more fields, a key that I as developer generate, then having
access to the database or the server will not do you any good unless
you have the corresponding private key to decrypt. I could distribute
the private key to authorized individuals only. So now you would not
only have to be authorized to access RT, and the appropriate queue,
but would also need the public key available to your browser for
decryption.

So would all searching be done on the client side? If so, does that
mean that I’d have to shovel several gigs of ticket content to the end
user for searching?

No, that would of course not be a good design. I don’t think all fields need
to be encrypted either. Transporting the public key for every http request
would be a bit of overhead but probably not that bad. Searching would of
course be quite a nightmare, may just have to make encrypted fields non
searchable otherwise like you said, would have to decrypt everything just to
search it, time consuming, would have to be done server side.

Not sure what a good solution for an incident database would be that would
store sensitive confidential data that other employees should not see. What
if we are investigating the dba or someone in the support team that supports
the machine, or someone very high up like a VP or CEO for that matter. We
also have to ensure that emails are encrypted for any communications
regarding the investigation.

I am drawing on my past experience with Lotus Notes where this type of
encryption was possible. Fields could be protected by public/private pair
keys and the public keys could be shipped to authorized users. When looking
at the database the key would automatically be used to decrypt the contents.
Even the developers would not be able to decrypt the data yet they could
continue development support. I don’t know if this functionality made it
into Domino or not. Any RT developer or support person would pretty well
have the keys to all the data in the database.

The http://www.fourmilab.ch/javascrypt/ site has some interesting ideas, not
sure how this could be implemented. I would prefer to use certificates,
maybe imported into the browser.

If RT had protection down to the field level similar to RTFM then maybe that
would be sufficient. Will that be available anytime soon?

Thanks
Dave-----Original Message-----
From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Thursday, September 30, 2004 12:41 PM
To: david.falkenburger@rbc.com
Cc: rt-users@lists.bestpractical.com; vivek@khera.org
Subject: Re: [rt-users] encrypting fields and email

On Sep 30, 2004, at 11:02 AM, david.falkenburger@rbc.com wrote:

I was thinking more along the lines of public and private key
technology. If I was to use the private key to encrypt all the data in
one or more fields, a key that I as developer generate, then having
access to the database or the server will not do you any good unless
you have the corresponding private key to decrypt. I could distribute
the private key to authorized individuals only. So now you would not
only have to be authorized to access RT, and the appropriate queue,
but would also need the public key available to your browser for
decryption.

So would all searching be done on the client side? If so, does that
mean that I’d have to shovel several gigs of ticket content to the end
user for searching?

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier �lectronique est confidentiel et prot�g�. L’exp�diteur ne renonce pas aux droits et obligations qui s’y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu’il contient par une personne autre que le (les) destinataire(s) d�sign�(s) est interdite. Si vous recevez ce courrier �lectronique par erreur, veuillez m’en aviser imm�diatement, par retour de courrier �lectronique ou par un autre moyen.

FWIW, I’ve been thinking this one through as well. What I would like
is to store customer run-books in RT ticket notes, including passwords.
I would like the passwords to be encrypted so that even if you have
access to RT’s database tables, you can’t do anything without some
private key (which lives on an RT user’s computer, not on the server).
I don’t need to be able to search the encrypted notes.

The best idea I have come up with so far is some kind of java or
javascript public key crypto, but I haven’t thought through the whole
set of problems that may encounter, or really looked into the
alternatives much.

  • Jason

If RT had protection down to the field level similar to RTFM then maybe that
would be sufficient. Will that be available anytime soon?

RT 3.4, which is currently in testing, has that level of granularity for
access control.

Jesse

Excellent. One question though, will the new RTIR 1.1.2 be compatible or are
there enough changes in RT that would require updates to RTIR?

Thanks
DaveFrom: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Thursday, September 30, 2004 1:42 PM
To: Falkenburger, David
Cc: rt-users@lists.bestpractical.com; vivek@khera.org
Subject: Re: [rt-users] encrypting fields and email

If RT had protection down to the field level similar to RTFM then maybe
that
would be sufficient. Will that be available anytime soon?

RT 3.4, which is currently in testing, has that level of granularity for
access control.

Jesse

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier �lectronique est confidentiel et prot�g�. L’exp�diteur ne renonce pas aux droits et obligations qui s’y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu’il contient par une personne autre que le (les) destinataire(s) d�sign�(s) est interdite. Si vous recevez ce courrier �lectronique par erreur, veuillez m’en aviser imm�diatement, par retour de courrier �lectronique ou par un autre moyen.

Excellent. One question though, will the new RTIR 1.1.2 be compatible or are
there enough changes in RT that would require updates to RTIR?

RT 3.3/4 should be fully backwards compatible with RT 3.2’s API (Which
means RTIR 1.2 should work out of the box.) As of RT 3.3.7, this isn’t 100%
true yet.

I’m sorry, y’all seem somewhat confused about how cryptography works
and it’s making this discussion nonsensical. Backing up several steps…

There are 2 basic forms of encryption. Both start knowing the
plaintext.

Symmetric encryption uses the same key for encryption and
decryption. This means that they key is secret, and only people
knowing the secret can encrypt or decrypt.

Public/Private encryption uses separate keys for the encryption and
decryption. The public key is known to all, and is only used to
encrypt things, the private key is secret and only used to decrypt
things. The public key is never needed for decryption, the private
key is never needed for encryption.

If you were only concerned about protecting the ticket content in
transport, and didn’t mind having the server able to read it, you
would use public/private. The server would use the public keys for
each recipient and encrypt the outgoing data. The clients would then
use their private keys to decrypt.

Unfortunately, that would mean storing the data in plaintext on the
server. The simple approach to solving that problem is to have the
client encrypt the data before it reaches the server. However, that
has several downsides. The client has to know who it’s encrypting the
data for, and the server won’t be able to index or search data.

One possible approach would be to only encrypt fields that didn’t need
to be indexed, credit card data, for example. The client would handle
all the encryption and decryption. Has the aforementioned downside that
the client would have to know who to encrypt it to. You couldn’t just
add a watcher, without also giving them a copy of the key.

So now that we have a basic over view of the possibilities, what
problem are you trying to solve? My experience in industry is that the
most common sort of encryption is to have the data encrypted in the
database, to a symmetric key in the application layer. Hardly perfect,
but when the app layer needs to work with the data, there’s not much
else to do.

seph

Yes, aware of symmetric versus asymmetric encryption.

Just protecting the data during transmission can be achieved with SSL, which
I do now.

I was looking for a way so that if someone compromises the box, had access
to the code, all files, there would be no way for them to decrypt the data.
So no storing secret encryption keys in the code, in any config file for
symmetric encryption.

I was thinking along the lines of asymmetric encryption, and yes you are
correct that any user that needed to read the data would need a copy of the
public key. It could work something like this:

  • any data that is filled out in a form would be encrypted using a private
    key that is stored on the server. Having this key would not be of use to
    anyone since it could not be used to decrypt any of the data
  • when a record is displayed by the user and the field has encrypted data,
    JavaScript could perhaps be used along with a copy of the public key to
    decrypt the data on the client side.

Yes this makes things more complicated but it would guarantee that no one
could read the data without the public key. Using a secret key stored on the
server and using DES or AES encryption would be better than storing data in
the clear, and perhaps with field level security and tight physical access
restrictions would be enough to satisfy security requirements for sensitive
data that may be stored in the database.

Perhaps something as simple as a passphrase typed in a form that would allow
the application to decrypt a symmetric key that was stored on the server
could then be used by the application to further decrypt or encrypt data
server side would be sufficient. Actually a very simple solution. The
symmetric key could be reencrypted with a new passphrase whenever required,
say someone should no longer have access because they left the department or
the company. Hmmmm, almost doable with some glue code. Probably still some
little gotchas to work out I am sure.

Dave-----Original Message-----
From: seph [mailto:seph@directionless.org]
Sent: Friday, October 01, 2004 12:38 PM
To: david.falkenburger@rbc.com
Cc: vivek@khera.org; rt-users@lists.bestpractical.com
Subject: Re: encrypting fields and email

I’m sorry, y’all seem somewhat confused about how cryptography works
and it’s making this discussion nonsensical. Backing up several steps…

There are 2 basic forms of encryption. Both start knowing the
plaintext.

Symmetric encryption uses the same key for encryption and
decryption. This means that they key is secret, and only people
knowing the secret can encrypt or decrypt.

Public/Private encryption uses separate keys for the encryption and
decryption. The public key is known to all, and is only used to
encrypt things, the private key is secret and only used to decrypt
things. The public key is never needed for decryption, the private
key is never needed for encryption.

If you were only concerned about protecting the ticket content in
transport, and didn’t mind having the server able to read it, you
would use public/private. The server would use the public keys for
each recipient and encrypt the outgoing data. The clients would then
use their private keys to decrypt.

Unfortunately, that would mean storing the data in plaintext on the
server. The simple approach to solving that problem is to have the
client encrypt the data before it reaches the server. However, that
has several downsides. The client has to know who it’s encrypting the
data for, and the server won’t be able to index or search data.

One possible approach would be to only encrypt fields that didn’t need
to be indexed, credit card data, for example. The client would handle
all the encryption and decryption. Has the aforementioned downside that
the client would have to know who to encrypt it to. You couldn’t just
add a watcher, without also giving them a copy of the key.

So now that we have a basic over view of the possibilities, what
problem are you trying to solve? My experience in industry is that the
most common sort of encryption is to have the data encrypted in the
database, to a symmetric key in the application layer. Hardly perfect,
but when the app layer needs to work with the data, there’s not much
else to do.

seph

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier �lectronique est confidentiel et prot�g�. L’exp�diteur ne renonce pas aux droits et obligations qui s’y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu’il contient par une personne autre que le (les) destinataire(s) d�sign�(s) est interdite. Si vous recevez ce courrier �lectronique par erreur, veuillez m’en aviser imm�diatement, par retour de courrier �lectronique ou par un autre moyen.