September 27, 2007 at 1:35 pm
Hi All,
I am looking for methods by which to pin a table into memory/RAM...we have sensitive data i.e. SSN's, Account Nos., that are encrypted when at rest. The problem lies in searching the data. We have 16 million records, and to search an encrypted value, the entire table must be unencrypted, then searched. The account number search is a "like" as opposed to an equal, so partitioning may be out of the question. SSN could be partitioned but we are seeking alternate methods as well.
Has anyone, or does any one know how to place a table in memory, and leave it there, to be searched? our client will approve having data unencrypted while not at rest.
Any ideas would be appreciated...thanks!!
September 27, 2007 at 3:20 pm
If they will accept it "in memory", then try to get them to go one step further and see if they will accept it in a temp table. You could then use a global temporary table (##) with your data unencrypted. When the connection that created the global temp table disconnected, the table would be dropped.
Now back up a bit to your searching. Are you searching for parts of SSN's, or the entire number? If it is, you should be able to encrypt the search criteria and keep an index on the encrypted SSN's giving you a fast search. Decrypting the entire table for long periods of time into any kind of temporary storage (memory of not) would, to me, defeat the purpose of encrypting it in the first place.
Another option would be to keep some searchable information unencrypted while the entire SSN is encrypted. If you find that you typically search using the first character or two characters, see if your auditors would accept the entire SSN being encrypted, but the first two characters stored in a fields for searching. This would drastically reduce the number of records you have to decrypt to complete your search.
September 27, 2007 at 3:45 pm
The client is willing to accept unencrypted data only in a "data in use" or "data in transit" state. I am being told that t here is a way to physically pin a table into RAM, which would be considered as "in use". the problem with creating the global table is that it at least partially has data on the drive. The other problem is maintaining that table - we couldn't write the table over and over as there are too many record to do this with, so we need a table that resides solely in RAM, that we can update, delete, insert, and select.
Regarding the SSN:
That was an idea...decrypting a portion of the SSN...we are still mulling that one around and examining the effects on our processes, but that one can be easily done, and I would expect that the client would accept the last 4 unencrypted in a DB.
September 27, 2007 at 7:51 pm
Don't try to decrypt the whole table... encrypt the data you're searching for instead. Don't forget to add an index.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2007 at 9:40 am
Will that work? I suggested encrypting the search variable, then searching for it, but was told by our DBA that the encryption is time based, and I would never match the value that way. Also, one of our searches is a "like" as opposed to an "equals" so the entire column has to be unencrypted to search properly, right?
September 28, 2007 at 10:41 am
I don't believe encryption is time based. I could be missing something, but that's not how I understand the SQL Server 2005 encryption.
How is encryption set up? Is it symmetric, asymmetric, which algorithm, etc.
FYI, SQL 2008 is supposed to correct some of this.
September 28, 2007 at 11:07 am
Encryption/Decryption By Certificate...
September 28, 2007 at 11:25 am
Certificates expire, but over days or weeks. Plus if they expire, then you need to change the encryption on your data.
You should use symmetric encryption on the data and then use a certificate to encrypt the symmetric key.
September 28, 2007 at 11:28 am
let me double check on exactly how we are encrypting...but do you know if there is a way to pin a table into RAM? I have researched for days on end, and not found anything substantial, other than a PINTABLE command which was removed from SQL 2005
September 29, 2007 at 4:01 pm
It was removed because it was a really bad idea...;)
Like I said, stop trying to decrypt the whole table... encrypt the lookup criteria and do a normal join or WHERE 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2007 at 9:07 pm
Previous versions allowed pinning, but current versions do not.
I think Jeff has the best idea.
September 30, 2007 at 10:22 am
Be careful with the "compare encrypted variables". That will work only for exact matches. OP mentioned wanting to use a LIKE syntax, which if run against encrypted data won't return the same results as the like against unencrypted data.
The encryption algorithms (unless home-cooked) don't encrypt one character at a time. Those are way too easy to break. If you have to do a LIKE statement, then your only two options are to decrypt the column on the fly, or to keep a partial column always decrypted (like your "last 4" idea). I've evn used simple obfuscation methods on those unencrypted fields (e.g. call them "roger" or something entirely different, and stored the data as Hex for example), so that it's not quite so easy to read.
Now - you could also store a "computed column" which had only the pattern you were "exact-matching" in your LIK statement. So - if your WHERE clause included:
.... and [ssn] like '123%'....
you could create a computed column for the first 3 of the SSN, index it, encrypt it, and then use the exact match of encrypted value as Jeff mentioned.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2007 at 10:50 am
Matt, Great catch. I missed that in the thread.
There's no good way to do this for a like in 2005. 2008 is supposed to include encrypted indexes, but I'm not sure that will help either.
What are you searching with a "like" that needs to be encrypted?
The "in use" thing sounds like someone's idea that has very little idea of how encryption works in a database environment. Much different than something like encrypted email.
September 30, 2007 at 12:57 pm
Jeff Moden (9/29/2007)
Like I said, stop trying to decrypt the whole table... encrypt the lookup criteria and do a normal join or WHERE 😉
Won't work with 2005's encryption routines. They're non-deterministic. Encrypt and compare will get you no matches.
Lara Rubbelke did a lovely session at PASS on searching encrypted data. The way her technique works is that, for columns that are encrypted and need to be searched on, you store both the encrypted value and a salted hash (keep the salt encrypted in a separate table. Encryption is non -deterministic and reversable, but hashes are deterministic, and irreversable.
Instead of searching on the encrypted value, you can hash (with the salt) the value you're searching for and search on the hash column, then once you have the records, decrypt the encrypted value.
Since the hash is deterministic, you can also index the column usefully.
She had a version for partial matches as well, provided you can guarantee a certain number of characters will be passed for the search, and the minimum number of characters are selective enough for an index seek. Basically hash the minimum number of characters and compare to the partial hash you have stored in the table, then decrypt and do the like match on the limited result set.
I can hack up some code, if you need, but not right now. Is late here.
Regarding memory, there's no way to guarantee that something in memory only remains in memory. Between windows possibly paging memory out to the swap file, and the lazy writer and checkpoint process writing dirty pages to disk there's no way to be sure that something in memory doesn't go to disk sometime or other. This includes temp tables and table variables
Also, even tempdb has a transaction log, so anything inserted into a temp table or table variable will be written to the tempdb transaction log.
From the sound of things, you may be better off going back to the basic requirements and designing something workable based on db architecture, rather than, like Steve said, someone's idea of how dbs work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2007 at 10:20 pm
I'd have to agree with Jeff here, all you need to do is encrypt/hash the search criteria beforehand.
Take a look at EncryptByCert in the BOL and you should be able to encrypt the search criteria... the real beauty is that you don't necessarily need to return the SSN(s) back to the client at that point, all you would need to return is a list of rows that meet the criteria, SSN(s) could still be masked/encrypted.
Joe
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply