SQL 2008 - kriptovanje na nivou kolone
SQL Server 2008 nudi build-in opcije za rad sa sertifikatima, simetricnim i asimetricnim kljucevima.
TDE (Transparent Data Encryption) nudi mogućnost kriptovanja fajlova baze podataka.
Ovo služi kao odbrana od krađe fajlova baze i ni na koji način ne ograničava sve ostale korisnike da normalno rade sa podacima.
Međutim, moguće je uraditi i kriptovanje na nivou kolone, što može biti jako korisno jer niko (pa ni sa) ne može videti podatke ako nema odgovarajući sertifikat.
Sledi step-by-step TSQL koji ilustruje sve ovo, a na kraju se nalazi clean up segment koji vraća SQL Server u stanje pre pokretanja primera:
-- 1: Kreiramo test bazu
USE master
GO
CREATE DATABASE EncryptTest
--2: Kreiramo test tabelu i ubacujemo malo podataka
USE EncryptTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
VALUES
(1,'First'),
(2,'Second'),
(3,'Third'),
(4,'Fourth')
-- Proverimo sadrzaj tabele
USE EncryptTest
GO
SELECT *
FROM TestTable
-- 3: Kreiramo Database Master Key
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'SQLAuthority'
-- 4: Kreiramo Encryption sertifikat
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
-- 5: Kreiramo simetricni kljuc sa TRIPLE_DES enkripcijom
-- od ostalih simetricnih algoritama na raspolaganju su:
-- DES, TRIPLE_DES, TRIPLE_DES_3KEY, RC2, RC4, RC4_128, DESX, AES_128, AES_192, AES_256
USE EncryptTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE EncryptTestCert
-- Opciono ali vrrrrlo preporucljivo
-- Na fajl sistem snimiti i spremiti na sigurnu lokaciju privatni kljuc i sertifikat
-- i sve to zajedno zastiti passwordom
USE EncryptTest ;
GO
BACKUP CERTIFICATE EncryptTestCert TO FILE =
'c:\EncryptTestCert_Certificate.cer'
WITH PRIVATE KEY ( FILE =
'c:\EncryptTestCert_Cert_Key.pvk',
ENCRYPTION BY PASSWORD =
'User-Provided Password' ) ;
-- RESTORE ovako backupovanog sertifikata bi uradili na sledeci nacin:
-- (ne postoji RESTORE CERTIFICATE naredba, vec se koristi CREATE sa 'FROM FILE' klauzulom)
USE EncryptTest;
CREATE CERTIFICATE EncryptTestCert
FROM FILE = 'c:\EncryptTestCert_Certificate.cer'
WITH PRIVATE KEY (FILE = 'c:\EncryptTestCert_Cert_Key.pvk',
DECRYPTION BY PASSWORD ='User-Provided Password');
--6: Dodajemo kolonu ciji sadrzaj hocemo da kriptijemo (mora biti tipa binary)
USE EncryptTest
GO
ALTER TABLE TestTable
ADD EncryptSecondCol VARBINARY(256)
-- 7: Azuriramo novu kolonu sa kriptovanim podacima iz kolone "SecondCol"
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
-- 8: Pogledamo sadrzaj tabele
USE EncryptTest
GO
SELECT *
FROM TestTable
-- 9: Na kraju kako prikazati dekriptovati podatke iz kolone EncruptSecondCol
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
-- Clean up...
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
USE master
GO
DROP DATABASE EncryptTest
GO