szyfrowanie

Szyfrowanie w SQL Server to temat rzeka, standardowo administrator tworzy klucze i certyfikaty dane są  szyfrowane i sprawa załatwiona. Co możemy zrobić jeżeli mamy tabelę w której dane są zaszyfrowane kilkoma kluczami, jak sprawdzić którymi i czy któryś z kluczy nie został usunięty.

Tworzymy bazę do zabawy

CREATE DATABASE test;

USE test;

Tabelę przechowującą zaszyfrowane dane

CREATE TABLE test (

            id INT IDENTITY(1,1) PRIMARY KEY,

            konto VARBINARY(100)

            );

Tworzymy hierarchię szyfrowania, główny klucz bazy

CREATE MASTER KEY

ENCRYPTION BY PASSWORD ='#####....bardzo długie I skomplikowane hasło @@@@@...'

Przy jego pomocy szyfrujemy certyfikat

CREATE CERTIFICATE certyfikat

WITH SUBJECT ='certyfikat do testów';

Tworzymy klucze symetryczne do szyfrowania danych, które są zaszyfrowane certyfikatem

CREATE SYMMETRIC KEY key1

WITH ALGORITHM =TRIPLE_DES

ENCRYPTION BY CERTIFICATE certyfikat;

CREATE SYMMETRIC KEY key2

WITH ALGORITHM =AES_128

ENCRYPTION BY CERTIFICATE certyfikat;

CREATE SYMMETRIC KEY key3

WITH ALGORITHM =AES_192

ENCRYPTION BY CERTIFICATE certyfikat;

CREATE SYMMETRIC KEY key4

WITH ALGORITHM =TRIPLE_DES_3KEY

ENCRYPTION BY CERTIFICATE certyfikat;

Otwieramy klucze

OPEN SYMMETRIC KEY key1

DECRYPTION BY CERTIFICATE certyfikat;

OPEN SYMMETRIC KEY key2

DECRYPTION BY CERTIFICATE certyfikat;

OPEN SYMMETRIC KEY key3

DECRYPTION BY CERTIFICATE certyfikat;

OPEN SYMMETRIC KEY key4

DECRYPTION BY CERTIFICATE certyfikat;

Dodajemy dane do tabeli szyfrując je wcześniej otwartymi kluczami

INSERT INTO dbo.test

VALUES      (ENCRYPTBYKEY(KEY_GUID('key1'),'111-222-333-444-555')),

            (ENCRYPTBYKEY(KEY_GUID('key2'),'222-333-444-555-666')),

            (ENCRYPTBYKEY(KEY_GUID('key3'),'333-444-555-666-777')),

            (ENCRYPTBYKEY(KEY_GUID('key4'),'444-555-666-777-888'));

W wyniku dostajemy

SELECT * FROM dbo.test;

Po odszyfrowaniu, możemy zobaczyć dane

SELECT id,

CONVERT (VARCHAR,DECRYPTBYKEY(konto)) AS konto

FROM dbo.test;

id          konto

----------- ------------------------------

1           111-222-333-444-555

2           222-333-444-555-666

3           333-444-555-666-777

4           444-555-666-777-888

(4 row(s) affected)

Żeby zobaczyć jakiego klucza użyliśmy i jaki jest jego algorytm możemy użyć zapytania

SELECT k.name AS klucz ,k.algorithm_desc,t.* FROM dbo.test t

LEFT JOIN sys.symmetric_keys k

ON convert(UNIQUEIDENTIFIER,t.konto)=k.key_guid

Jeżeli używaliśmy kluczy symetrycznych i mamy null w którymś wierszu może oznaczać brak klucza do odszyfrowania