I second Bill on being against full DB encryption. All this does is make develoment a (bigger) nightmare. Yes, you could encrypt after the system is ready for deployment building generic indexes to point at the encryped records, but what is the point? In the case of HIPAA, human readable indexes would probably be in technical violation even though they point at enrcypted records. We have seen this problem in the financial world and it has been exacerbated by HIPAA. You simply have to trust that anyone with programmer access to the system is worthy of that trust. The place to build the wall is at data base access for ISC and network security for the user. ISC can keep unworthies out of its product. It is up to the system administrator to limit access to the drive/s that hold the Cache.dat file.
I have never seen a system that was sucessfully built without at least some debugging with live data. No matter how carefully test data is prepared, real world data will always find some goofy way of screwing things up. Your progammers and systems people will have to have access to the live data, at least during final testing and deployment. All encryption does is hammer system performance and provide a false sense of security. -- John Bertoglio Senior Consultant co-laboratory office: 503-538-8691 mobile: 503.330.6713 fax: 503.538.8691 www.co-laboratory.com "Bill McCormick" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 > >>>> > >>>> > >>> > >>> > >> > > > >
