Re: Encrypted global subscripts

[Bill McCormick]

I am with Mark on this one. SQL Queries just won't work correctly 
against an encrypted index. I had an interesting conversation internally 
about this just a few weeks ago. A co-worker pointed out what a mistake 
it was to encrypt data in the database. ANY encryption can be beaten 
given time. If someone can get inside a CACHE.DAT long enough to 
recognize that data in a global is encrypted then it is likely they had 
enough time to make a copy of the CACHE.DAT as well. Given that no 
encryption scheme is going to be sophisticated enough to prevent that 
person over weeks to crack the encryption. This is a case of trying to 
latch a hen house after the fox has already been inside and eaten every 
hen. The effort should be focused on preventing malicious OS/Environment 
level access in the first place and making sure unauthorized users can't 
access the DB. This is where the focus of development on 5.1 is going 
and is also what is recommended in the evolving DBMS security standards.

Mark Sires wrote:
There is no value in included the encrypted field in the index for anything
other than exact matches.  Since it is not the actual value, and the
collation of the encrypted value should not match the collation of the
unencrypted value.  If it did, then the encryption would be predictable, and
therefore of little value.  It is not going to speed up the retrieval, and
would probably slow it down since it would always require a full travel of
that level to evaluate all the encrypted values. The only way this could be
corrected would be to store the value in the subscript level in the clear,
but that would eliminate the value of having it encrypted in the first
place.
Mark
"Nick Hershberger" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
The app is still being developed.  I've been using AESEncode and AESDecode
with no problems, at least none that I've noticed.  The trouble is getting
the SQL compiler to recognize that some of the subscripts of the index
global are actually encrypted data and to execute code accordingly to
decode
those subscript values so that operators other than equality operators can
be used against those values.
Nick Hershberger
"Peter Lowndes" <peter dot lowndes at safewire dot net> wrote in message
news:[EMAIL PROTECTED]
Are you stuck with the data that is already encoded or is the
application
still being developed? I had problems with the encrypted format until I
changed my encryption method to wrap the AES stuff in the
Base64Encode/Decode methods, also part of the $System.Encryption class.
This
gave me much more convenient / readable encrypted versions that were ok
for
data and subscripts.
e.g.
$System.Encryption.Base64Encode($System.Encryption.AESEncode(Text,Key))
and

$p($System.Encryption.AESDecode($System.Encryption.Base64Decode(EncryptedTex
t,Key),$c(0))
The $p(...,$c(0)) was to get rid of trailing ascii zeroes that occurred
when
the original text was less than 16 characters long.
Peter Lowndes
Safewire Ltd.
"Nick Hershberger" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
I've got an encrypted field that is used as one of the subscripts in a
multi-field index, with the structure for the index being
^UICREDIT(field1,
field2, record id) with field 2 being the encrypted field.  The data
has
been encrypted using the AESEncode method from the $system.Encryption
class.
Specifying retrieval code for this field works for getting the data
from
disk when a query is executed, however, I've run into problems with
indexes
that use encrypted fields.  The retrieval code does not work when
doing
traversals on the index.  Intersystems suggested that I write a
user-defined
data type for encrypted fields.  I've tried that, creating a rough
Encrypted
data type class that has a StorageToLogical and LogicalToStorage
method.
The StorageToLogical method gets generated into the compiled SQL if
the
field is mapped as Encrypted.  However, it still isn't being used for
the
encrypted subscript.
Since that didn't work, I modified the SQL Storage map for the index
global
in Studio, specifying the access type for the encrypted subscript as
Sub,
with an expression of:
##class(arch.system.Encrypted).FieldDecryption(Data).
This does get generated into the global when it is referenced in the
compiled SQL:
s

%cur123850d(1)=$o(^UICREDIT(%cur123850d(4),##class(arch.system.Encrypted).Fi
eldDecryption(Data),%cur123850d(1)))
However, if instead of data I pass in {L2} for the subscript level the
class
won't compile.  If I use the field's SqlFieldName, {AUCREDIT} then the
class
method call is not added to the compiled SQL.
Does anyone have any thoughts on this, or any experience with writing
data
type classes, or with using the $system.Encryption class?  Any help
would
be
greatly appreciated.  Thanks.
Nick Hershberger