May 22, 2006 at 8:47 am
I have a visual basic / sql2k app. I need to encrypt credit card data in several tables. I'm thinking about renaming these tables, creating views and using instead of triggers to encrypt the credit card numbers and store them in the renamed tables. It seems to me that using this strategy, I would not have to touch the vb app.. The only alternative would be to handle the encryption on the client. I'm inclined to use the encryption stuff in the sql2k dba toolkit (Michael Coles 4/13/06). Anybody have any advice/experience here?
.
May 22, 2006 at 9:44 pm
There are several issues with encryption at the database level:
1) If I get access to the views, I can see the unencypted data anyway.
2) if I am a sysadmin and can run a trace (such as through Profiler), I am going to see the unencrypted credit card information come across.
3) If the connection between the VB application and the SQL Server isn't encrypted, I can see the unencrypted credit card information on the network (if I have a sniffer).
With all that said, it is certainly going to be easier to modify SQL Server than the client app, given recompile, redistribution, etc. So this boils down to a risk assessment question, something no one on this site is likely going to be answer for you. It may be worth the effort to rewrite the application. Or it may be considered too costly given the risk to the organization.
But out of curiosity, why are you needing instead of triggers? Does the application currently perform INSERT and UPDATE statements against the base tables? If not, couldn't you code the stored procedures to handle the encryption?
K. Brian Kelley
@kbriankelley
May 23, 2006 at 7:13 am
Thanks for the response.
To answer your question, yes, our app. uses a lot of pass through sql. It also uses a good deal of rdo as well. I'm not sure if my solution will work for the rdo stuff.
Here is my solution:
Lets say I have table cctran with column ccnum. I would rename this table cctranbase, create a view named cctran, create an insteadOf trigger against the cctran view. The instead of trigger would encrypt the ccnum and write it to the the cctranbase table. Now, when the app. "selects" from cctran, it gets back the encrypted card number. The view would also return an encrypted card numbers since it is "selecting" from cctran.
.
May 23, 2006 at 9:08 am
Don't forget that I can see those credit card numbers as they go across the wire too. All I need is a spot between the app and the database that I have access to the network and I can sniff them from the database calls. Definitely encrypt/decrypt at the application level.
May 23, 2006 at 9:21 am
Hmm, so you don't think "protocol encryption" would be sufficient?
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply