December 2009 - Posts

Treba vam CSV lista sa kolonama iz tabele?
Koristite CURSOR?
Nemojte više, može mnogo brže :)


USE Northwind
GO

DECLARE @Lista nvarchar(max) = ''

SELECT @Lista = @Lista + ', ' + CustomerID
FROM Customers
ORDER BY CustomerID

-- na kraju malo seckanja stringa i ispis
PRINT SUBSTRING (@Lista, 3, LEN (@Lista)-2)


Rezultat:

ALFKI, ANATR, ANTON, AROUT, BERGS, BLAUS, BLONP,...

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