June 30, 2011 at 12:23 am
Dear SQL-Friends
1.I just need to know is there any ways for Database Encryption on Table level, I know there is two ways in sql server that is Cell-Level Encryption and Database-Level Encryption, Do any one know about how to make encryption on table itself
2.And how to do encrypiton on Numerical Values such as int,decimal,float
Thanks in Advance
Syed Sami Ur Rehman
SQL-DBA
Creative Technosoft System | www.cts-in.com
June 30, 2011 at 1:16 am
There is no table level encryption, but you can use column level encryption on more then one column in a table. If you want to encrypt the whole table, you can encrypt every column. I have to admit that this sounds a bit strange. Most times you want to encrypt part of the information (credit card number, password, etc’) and leave the rest of the information clear. Are you sure that you need to encrypt the whole table?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 30, 2011 at 1:39 am
Dear Adi
Yes i need to encrypt whole as a table bec i am maintaning all secure data into one table and i want to encrypt whole as a table,and this tables containe some finacial data which comes in decimal calcualtion which also need to be encrypted but sqlserver does not support numaric encryption is there any alternate for this as well.:(
Syed Sami Ur Rehman
SQL-DBA
Creative Technosoft System | www.cts-in.com
June 30, 2011 at 2:13 am
You can cast the numbers as string before encrypting them. Here is an example:
create table EncryptingDemo (EncryptedNumericNum varbinary(200))
go
declare @NumericNum numeric(10,5)
set @NumericNum = 12345.6789
insert into EncryptingDemo (EncryptedNumericNum)
values (EncryptByPassPhrase('This is just a demo', cast(@NumericNum as varchar(15))))
go
--see the encrypted data
select * from EncryptingDemo
go
--see the decrypted data
select cast(cast(DecryptByPassPhrase('This is just a demo', EncryptedNumericNum) as varchar(15)) as numeric(10,5))
from EncryptingDemo
go
drop table EncryptingDemo
go
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 30, 2011 at 2:19 am
Dear Adi
Thanks very much for the script and for idea about the numaric values...
But here the problem is table is already created and is in Live so cant really change the schema at this stage.. So was looking for table encryption so that datatype issue can be solved of.
Thanks again for your kind help Adi. 🙂
Syed Sami Ur Rehman
SQL-DBA
Creative Technosoft System | www.cts-in.com
June 30, 2011 at 4:23 am
If I understand correctly, you are looking for a solution that will encrypt and decrypt the data, but won’t modify the data type and the table. If I’m correct, then I don’t think that you’ll find a solution for that. When you need to encrypt the data just like implementing any other feature – you need to design it, create the needed modifications in the schema and write the code for that. You can’t work with encryption and leave the table untouched.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 30, 2011 at 1:55 pm
Let's take a step back...
You are worried about protecting financial data. You go on to say that you cannot change the database schema. For future consideration: database tables contain columns, spreadsheets contain cells.
Transparent Database Encryption (TDE) only protects data "at rest". So, it would prevent prying eyes from seeing your data if someone stole a copy of a data, log or backup file. However, if someone had direct access into your database TDE would not help to protect your data.
Forget that SQL Server does not have Table-level encryption, whether there is a viable solution for you depends on:
> What are you trying to protect against by encrypting your data?
> Who exactly do you need to prevent from seeing this data?
> What part of the data life cycle is important for you to protect?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 11:20 pm
dear opc.three
> What are you trying to protect against by encrypting your data?
I am trying to protect this data from the developers as well as from the end users
> Who exactly do you need to prevent from seeing this data?
Developers and End Users
> What part of the data life cycle is important for you to protect?
During the viewing of information and during the storing the data
Syed Sami Ur Rehman
SQL-DBA
Creative Technosoft System | www.cts-in.com
July 1, 2011 at 8:11 am
opc.three (6/30/2011)
Let's take a step back...You are worried about protecting financial data. You go on to say that you cannot change the database schema.
...
> What are you trying to protect against by encrypting your data?
> Who exactly do you need to prevent from seeing this data?
> What part of the data life cycle is important for you to protect?
Well, if you can't change the schema, you'll need to change the app or the SQL to do the encryption prior to it being put in the table. Note that rolling your own encryption is very failure prone; relying on internet examples is also very failure prone (9 out of 10 .NET examples I saw used a static IV; this is a serious flaw in the implementation).
Let me expand on opc.three's excellent questions:
What _threats_ are you protecting against? A single unskilled (script kiddie) remote outsider selecting random targets ? A single skilled remote outsider without special knowledge [Let's see what vulnerabilities are in X company]? A single skilled remote outsider with special knowledge [Let's see if they've patched the problem Bob heard about from an ex-employee who didn't know it was a problem]? A single skilled local outsider without special knowledge [Let's see if I can put on a suit, walk through the front door, and find an unlocked computer or unguarded trash can with papers in it]? Multiple skilled local insiders with special knowledge [Office Space; your own lead developers maliciously/criminally attacking you from inside the company]?
Who includes in what circumstances; end users from seeing anything except their own group? Developers from seeing production DB's? Developers from ever seeing any production financial data at all, except/even when debugging production problems?
Data life cycle is a little fuzzier, and I'll let opc.three clarify, but I'd suspect he means during investigation/requirements gathering, specification, development, developer testing, end user testing, production, maintenance, and project wind-down; backups and production problem resolution are common cases that aren't considered well enough most of the time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply