SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date column Encryption
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/11/2013 :  09:29:34  Show Profile  Reply with Quote
Hi,

I have one doubt regarding data type for encrypted date column...

I have date-of-birth column with DATE type... We want to encrypt this field through application and then store encrypted value into database... For that purpose what should be the data type for date-of-birth ?


--
Chandu

Edited by - bandi on 06/11/2013 10:03:33

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/11/2013 :  10:17:06  Show Profile  Reply with Quote
I believe that depends on the applciation (encryption tool's) ouput for a date value. The same approach has been used in one of my company, where encryption was performed via some encryption tool in the application and then storing it as nVarchar(255) in the database.

Cheers
MIK
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 06/11/2013 :  12:18:48  Show Profile  Reply with Quote
The other option would be varbinary of an appropriate length.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/12/2013 :  00:28:39  Show Profile  Reply with Quote
If we used VARCHAR(255)/varbinary types, there are some problems like
1) we have to search specific records based on DOB
2) Sometimes we have to calculate AGE from DOB

those operations might take few code changes... right?

--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/12/2013 :  06:28:36  Show Profile  Reply with Quote

1) You may search specific record(s) for a DOB, where you'll need to pass/compare the encrypted version of date with stored information. e.g. when you select for records where DOB=1984-1-1, pass on encrypted version of information to the stored procedure (WHERE DOB = 'encryptedDOB'). However this might not work in the case when you'll be searching for records having DOB > 1984-1-1 - not sure but a CLR might help you here.

2) you may acheive it using the CLR function, which will decrypt the encrypted version and then calculate the age ..

May be someone can come up with a better solution if you provide a narrative of the business requirements for encrypting the DOB information. If this is just for sake to restrict normal users' access , you may want to set column level permission on this particular field so that only allowed person(s) have access to it.

Cheers
MIK
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/12/2013 :  06:31:52  Show Profile  Reply with Quote
For security purpose we want to encrypt DOB column... Already developed 95% of application... We won't allow huge code changes as of now....
That's why I would like to know the other feasible solutions

--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/12/2013 :  06:59:05  Show Profile  Reply with Quote
I believe there would be lesser/no changes in the application code and can be handled all alone via tSQL.

Another approch could be keep saving the DOB as date format but set column level permission over it. And if the "Security" factor is in context of "inside SQL", then I believe column level permision would be enough .. but if this is for outer mediums (data flow between DB and application) then implement security on both layers.
Layer1 - Inside DB
Layer2 - ouside the DB

Layer1: would be to set colum level permissions to a user/account other than the application one, and to perform any DML/Select operation on the particular table/column.
Layer2: would be to send the data in encrypted form from application to the DB, and vice versa.

Cheers
MIK
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/12/2013 :  07:06:21  Show Profile  Reply with Quote
We just have database... no more DB activities...
They designed to do all through front-end only by using LINQ...
I think ours is Layer2 ( My concern is to store encrypted DOB to database)
Is there any possibility to hack person information by using DOB?

--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/12/2013 :  07:37:02  Show Profile  Reply with Quote
quote:
Originally posted by bandi


Is there any possibility to hack person information by using DOB?
--
Chandu



Well Chandu, far as I know, its the matter of access either to the information repository or to the individual account or to the communication medium (network/domain - a rare one). In all three it's the credentials would be required.

I don't think one can hack personal information just by having DOB in hand. Such information is encrypted mostly due to an agreement between clients and company so that it cannot be disclosed to any one including companys' employees.

Cheers
MIK
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/12/2013 :  07:45:08  Show Profile  Reply with Quote
yes I know DOB encryption is least priority....
Thank you so much for valuable information....
I will get back if needed

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000