Home > Encryption > How we can use Varchar in encryption column

How we can use Varchar in encryption column


This method is little different here we have to work with View and will send the one more method like encryption by stored procedure technique. Below I have mentioned the total demonstration.

 

 

The goal of the encryption process is to abstract the tables with cell level encryption by over-laying them with views.  This allows the application (with the proper permissions) to continue to work with the encrypted data seamlessly via the normal development methodologies without having to consistently write the code to decrypt / encrypt the columns (cells).  This process is the generally the same for new applications and existing applications that need to be modified to encrypt data with the key difference being the requirement to open the keys for new applications to provide additional security.

 

The SQL Server encryption functions return varbinary data, so all columns to be encrypted need to be changed to varbinary.  The use of Views to over-lay the tables provides an abstraction layer so the data types can be properly viewed for data modelers, developers, administrators, analysts and other end users.  To encrypt a SSN, varchar(9) field requires the column in the base table to be implemented as varbinary(200). 

 

Users that access the views, who do not have permissions to open the proper key(s) will not receive an error, but the column will not be decrypted and will be populated with nulls.

 

Database developers will need to work closely with the data modeling team and the administrators to ensure the data types, views, encryption keys, backup and restore of keys are implemented properly.  Data is not recoverable if the backup of keys and databases are not implemented properly.

 

Guidelines for implementing cell level encryption.

 

All tables that include an encrypted cell (column) follow the normal standard naming conventions with the addition of an underscore and the word base to the table name {table name}_base.  All columns follow the standard implementation with the exception of those to be encrypted, which must use a varbinary data type.  The length of the varbinary column is determined by the max length returned from the encryption function. 

 

A view is created that follows the normal standards, except the name is implemented off the base table by dropping the underscore and the word base that was used to name the table (this allows legacy code to continue to work).  The view will make use of the decryption function and the proper Cast and Convert statements to manipulate the varbinary column to the correct un-encrypted data type.

 

Inserts, updates and deletes are handled in the normal manner, with the tsql statements being executed against the view, NOT the underlying tables.  “Instead of Triggers” are created on each view to handle the Inserts, updates and deletes.  This provides an additional layer of abstraction so that consistency can be maintained with the code.

 

A short tsql script example:

 

–Database Creation

 

Create database encrypt_test;

 

 

go

 

use encrypt_test;

 

go

 

  –Created masterkey and Certificate

 

Create master key encryption by password = ‘knights12$gzmlauncher#1@%dmissionisclear*()’

 

go

 

Create certificate cert_sk with subject = ‘Certificate for accessing symmteric keys – for use by App’

 

–SQL SERVER can use the following algorithms in encryption:DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 AND AES_256.

 

go

 

  CREATE SYMMETRIC KEY sk_encrypt_test

    WITH ALGORITHM = RC4_128

    ENCRYPTION BY CERTIFICATE cert_sk ;

GO

 

 

  —Table Creation

 

create table dbo.Client_Base

 

      ( client_Id int Identity(1,1) primary key,

 

            ssn varbinary(200),

 

            Amount_due varbinary(200),

 

            Comments varchar(1000)

 

      );

 

go

 

  –Created View with a convert clause so we can use

 

create view dbo.Client

 

as

 

      Select Client_ID,

 

            convert(varchar(9),decryptbykeyautocert(cert_id(‘cert_sk’),

 

                  Null,SSN,1,convert(varchar(10),client_Id))) AS SSN,

 

            convert(money,convert(varchar(10),decryptbykeyautocert(cert_id(‘cert_sk’),

 

                  Null,Amount_Due,1,convert(varchar(10),client_Id)))) AS Amount_Due,

 

            Comments

 

      From dbo.Client_Base;

 

go

 

 

 

select * from dbo.client;

 

go

 

—Created Insert Trigger

 

create trigger dbo.trg_client_insert

      on dbo.Client

 

INSTEAD OF INSERT

 

AS

 

      BEGIN

 

 

 

            Declare @Client_ID int, @SSN varchar(9), @Amount_Due money,

 

                  @Comments varchar(1000);

 

            open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk;

 

            DECLARE cur_Client CURSOR FOR

                  SELECT      SSN,Amount_Due,Comments FROM INSERTED;

 

            OPEN cur_Client;

 

            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments;

 

            WHILE @@FETCH_STATUS = 0

 

            BEGIN

 

                  Insert into dbo.Client_Base (Comments) values (@Comments)

 

                  set @Client_Id = scope_identity()

 

                  Update dbo.Client_base

 

                        set SSN = encryptbykey(key_guid(‘sk_encrypt_test’),@SSN,1,Convert(varchar(10),@Client_ID))

 

                              ,Amount_Due = encryptbykey(key_guid(‘sk_encrypt_test’),convert(varchar(10),@Amount_Due),1,Convert(varchar(10),Client_ID))

 

                        where Client_ID = @Client_ID

 

 

 

            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments

 

            END

 

            CLOSE cur_Client

 

            DEALLOCATE cur_Client        

 

            CLOSE SYMMETRIC key sk_encrypt_test

 

      END

 

go

 

  –Created Update Trigger

 

create TRIGGER trg_Client_Update ON dbo.Client

 

INSTEAD OF UPDATE

 

AS

 

BEGIN

 

 

 

      open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk

 

      Update Client_base

 

            Set SSN = encryptbykey(key_guid(‘sk_encrypt_test’),i.SSN,1,Convert(varchar(10),i.Client_ID))

 

                  ,Amount_Due = encryptbykey(key_guid(‘sk_encrypt_test’),convert(varchar(10),i.Amount_Due),1,Convert(varchar(10),i.Client_ID))

 

            FROM Client_Base

 

            inner join inserted i on Client_Base.Client_ID = i.Client_Id

 

 

 

      CLOSE SYMMETRIC key sk_encrypt_test

 

END

 

go

 

  –Created Delete Trigger

 

create trigger dbo.trg_Client_Delete

      on dbo.Client

 

INSTEAD OF DELETE

 

AS

 

BEGIN

 

      Delete Client_Base

 

      from Client_Base db

 

      inner join deleted d on db.Client_ID = d.Client_ID

 

END

 

go

 

———————————————————-

 

— MUST OPEN KEY or all insert statements will FAIL

 

OPEN MASTER KEY

 

DECRYPTION BY PASSWORD = N’knights12$gzmlauncher#1@%dmissionisclear*()’;

 

GO

 

–Insert Value

 

Insert into dbo.client (ssn,amount_due,comments) values

 

      (‘123456789′,256.01,’This is a test of encryption’)

 

go

 

select * from dbo.client;

 

select * from dbo.client_base;

 

go

 

  –Update the value

 

Update Client

 

      set ssn = 987654321,

 

            Amount_Due = 100

 

      where ssn = 123456789;

 

go

 

select * from Client;

 

go

 

Close Master Key;

Advertisements
Categories: Encryption
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: