none
Encrypt Existing Colum

    General discussion

  • i have table : tab1 (name,address) with existing data.

    i want to encrypt address coulm with out creating any new colum . and user (who having read access) run Select * from tab1; he should display address colum with encrypt way like ***.

     

    pls advice how i will procced .


    SQl Server 2008 Version:
    Thursday, March 31, 2011 11:15 AM

All replies

  • Discussion of encryption, contrasting Transparent Data Encryption with column (or cell) encryption. http://msdn.microsoft.com/en-us/library/cc278098(SQL.100).aspx

    Column-level encryption: http://www.databasejournal.com/features/mssql/article.php/3922881/Column-Level-Encryption-in-SQL-Server.htm

    If you read these over, you will see that neither is exactly what you want.  TDE does not control what you show to a select statement.  Column encryption will require a varbinary column to contain the encryption.  You could, of course, do some obfuscation of the data which would not be up to cryptographic standards, but would make the data less obvious. (E.g. a substitution cipher.)  This would not be security, just a limited about of obscurity.

    You can also do encryption on the application level of your code.  In that case you would send already encrypted data to the SQL Server for that column and would select the encrypted data back to the user.  That way you could keep the single column in your database containing the encrypted data. 

    None of this will provide a *** abstraction of the data to a select.

    You could create a view that would hide the results for you. E.g.

    CREATE VIEW dbo.vtbl1
    AS
    SELECT id, name,
        CASE
           WHEN is_member('role/group/etc') THEN address
            ELSE '***;
        END AS address
    FROM youraddresstable

    In that case, you would not normally allow people to select from tbl1, but from the view instead.  Those who pass the membership test would get to see the data, everybody else would not.

    RLF

     

    Thursday, March 31, 2011 3:51 PM
  • thanks
    Thursday, March 31, 2011 6:21 PM