SQL Server Performance Forum – Threads Archive

Encrypted column

Is there some ways we can use to encrypt a whole table column or parts of the columns based on the WHERE clause? OR encrypt part of the table rows based on the WHERE any insight? ——————
Bug explorer/finder/seeker/locator
——————

---

I assume you want to scramble contents based on criteria, the same way you would filter rows in a WHERE clause, only not filtering the rows – just scrambling the contents? Assuming you have a UDF that scrambles the contents called MyUDF, this is the syntax: SELECT col1, col2, CASE WHEN <criteria> THEN dbo.MyUDF(col3) ELSE col3 END
FROM MyTable However, I would avoid the UDF and just use: SELECT col1, col2, CASE WHEN <criteria> THEN ‘– contents hidden –‘ ELSE col3 END
FROM MyTable Note that depending on the data type of col3, you would need to cast col3 to VARCHAR.

---

Adriaan, thanks for showing me such a good idea. I am wondering, for SPs, there is an option ‘with encryption’, is there a similar switch I can use in case I want to hide the content of a specific column ——————
Bug explorer/finder/seeker/locator
——————

---

Nothing like that available for column but you can encrypt your data…
Read the following articles… http://blogs.msdn.com/lcris/default.aspx http://www.codeproject.com/database/sqlserver_secure.asp
http://www.databasejournal.com/features/mssql/article.php/3483931
Mohammed U.

---

One thing about encryption, if you are looking for performance then forget about encryption in data. Otherwise tighten the security to return the data on role basis. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>