svētdiena, 2012. gada 5. februāris

Sertifikāti SQL Server

Rakstā par sertifikātiem SQL Server, to izveidi, rezerves kopiju veidošanu un izveidi no faila. TSQL piemēri darbam ar sertifikātiem un vaicājumi sertifikātu informācijas aplūkošanai. Tiek aprakstīts, kam tiek izmantoti sertifikāti, tomēr konkrēti pielietošanas piemēri šajā rakstā netiek doti.

Kas ir sertifikāti
Sertifikāts ir asimetriskā atslēga ar papildus metadatiem. Asimetriskā atslēga ir atslēgu pāris- publiskā un privātā atslēga. Publiskā atslēga tiek izmantota, piemēram, datu šifrēšanai vai paraksta pārbaudei. Kā tās nosaukums norāda- publiskā atslēga nav jāšifrē vai jāslēpj- to droši var ļaut izmantot jebkuram. Privātā atslēga savukārt, tiek izmantota datu atšifrēšanai un, piemēram, datu parakstīšanai.
Metadati, kas nāk līdzi asimetriskai atslēgai ir aprakstīti X.509 standartā. Piemēram, sertifikāta derīguma termiņš un apraksts (subject) ir daļa, no šīs papildus informācijas.

SQL Server piedāvā divus variantus sertifikātu izveidei- var izveidot sertifikātus, kas ir SQL Server parakstīti (self-signed), vai arī var importēt sertifikātus no faila. Veidojot SQL Server parakstītus sertifikātus, vienmēr tiek ģenerēta gan publiskā, gan privātā atslēga. Importējot sertifikātu no faila, var importēt tikai publisko atslēgu un metadatus vai arī abus: gan publisko atslēgu ar metadatiem, gan privāto atslēgu.

Lai kāda būtu sertifikāta izcelsme, SQL Server nodrošina pirvātās atslēgas šifrēšanu. Tas var notikt vairākos veidos- sertifikāta atslēga var tik šifrēta izmantojot Datu bāzes Master Key (DMK) vai izmantojot administratora norādīto paroli. Vēl sertifikāta privāto atslēgu var šifrēt, piemēram, izmantojot citu sertifikātu.

Kam tiek izmantoti sertifikāti
Šajā brīdī vēlos norādīt dažus sertifikātu pielietojumus SQL Server:
- Datu šifrēšanai. Uzreiz jāpiebilst, ka asimetriskā šifrēšana ir resursietilpīgs process, tādēļ sertifikātus parasti neizmanto liela apjoma datu šifrēšanai. Visbiežāk sertifikātus (asimetriskās atslēgas) izmanto simetrisko atslēgu šifrēšanai.
- Datu parakstīšanai. Izmantojot sertifikātus var parakstīt datus (8000 simbolu garuma ierobežojums standarta gadījumā- ja nepieciešams parakstīt garāku datu apjomu, tad nepieciešams veidot savas modifikācijas). Tātad, izmantojot privāto atslēgu var parakstīt datus. Izmantojot sertifikāta publisko atslēgu var pārbaudīt, vai dati tiešām ir parakstīti izmantojot attiecīgo sertifikātu.
- Moduļu parakstīšanai (piemēram, procedūru, funkciju). Principā tas pats iepriekšējais gadījums. Piemēram, tiek parakstīta konkrēta procedūra un tādējādi tiek garantēts, ka tā nav mainījusies kopš parakstīšanas brīža. Izmaiņu gadījumā procedūras paraksts tiek zaudēts un tā ir atkārtoti jāparaksta. Procedūru parakstīšana viennozīmīgi liekas ļoti noderīga funkcija. Pie šīs vēl atgriezīšos. (viens piemērs izmantošanai ir rakstā Truncate Table tiesības)
- Lietotāja izveidei. Var izmantot lietotāja vārda (login name) un lietotāja pieslēguma vārda (user name) izveidei. Šāda iespēja ir ļoti lietderīga komplektā ar moduļu parakstīšanu.

Sertifikātu drošība
Pirms konkrētu TSQL piemēru došanas ir jāatceras jau pieminētā lieta- sertifikāta privātā atslēga vienmēr tiek glabāta šifrētā veidā. Lai līdz galam saprastu privātās atslēgas šifrēšanu ir jāzina, ka tā var tikt aizsargāta ar Datu bāzes Master Key (DMK), ar paroli vai citu sertifikātu (patiesībā tie nav vienīgie varianti).
Ja sertifikātu privātā atslēga tiek aizsargāta ar DMK, tad datu bāzei jābūt ar izveidotu DMK un DMK jābūt atvērtai- tas var notikt automātiski vai izmantojot speciālu TSQL komandu (gadījumā, ja DMK ir aizsargāta ar paroli).
Ja sertifikāts tiek aizsargāts ar paroli, tad datu bāzē nav jābūt DMK.
Vienmēr strādājot ar sertifikātiem jāatceras, ka papildus to izmantošanai ir jārūpējas par sertifikātu rezerves kopiju veidošanu un to drošu uzglabāšanu (izveidojam rezerves kopiju un saglabājam pavisam drošā vietā). Rezerves kopijas jāveido gan sertifikāta publiskajai atslēgai, gan privātajai atslēgai.

Sertifikāti un TSQL
Šajā sadaļā vairāki piemēri piemēri sertifikātu izvedei (tai skaitā no rezerves kopijām un sertifikātu imports), rezerves kopiju veidošanai.

Sertifikātu izveide (Self-signed certificates). Šeit aplūkotajos variantos es nenorādu sertifikātu derīguma termiņa sākuma un beigu datumus. Labās prakses dēļ būtu labāk tomēr norādīt. Bet koda saīsināšanas dēļ un arī tādēļ, ka SQL Server nepārbauda sertifikāta derīgumu termiņu es to šeit nenorādu. Šeit arī netiek aprakstīti visi varianti sertifikātu izveidei (skatīt msdn)
Norādot paroli un aprakstu:
CREATE CERTIFICATE [MansSertifikats]
ENCRYPTION BY PASSWORD = N'DrosaParole'
WITH SUBJECT = N'sqlblog.lv piemērs';
Ja nenorāda paroli, privātā atslēga tiek šifrēta ar Datu bāzes Master Key (DMK):
CREATE CERTIFICATE [MansSertifikats]
WITH SUBJECT = N'Ir DMK tādēļ nav parole!';
Sertifikāta izveide no faila- te jāņem vērā, ka sertifikāts var "nokļūt" failā gan no SQL Server (skatīt nākamo sadaļu- sertifikātu rezerves kopiju veidošana), gan arī sertifikātu var izveidot izmantojot kādu citu metodi un tad importēt SQL Server. No sākuma izveidošu sertifikātu BEZ privātās atslēgas no faila:
-- lai izdzēstu sertifikātu, ja tāds eksistē datu bāzē:
-- DROP CERTIFICATE [MansSertifikats]
CREATE CERTIFICATE [MansSertifikats]
    FROM FILE = 'c:\Sertifikati\MansSertifikats.cer';
Lai izveidotu sertifikātu no failiem ar privāto atslēgu datu bāzē un privātās atslēgas šifrēšanai izmantojot paroli "JaunaPorole", jāizmanto šāds skripts:
-- lai izdzēstu sertifikātu, ja tāds eksistē datu bāzē:
-- DROP CERTIFICATE [MansSertifikats]
CREATE CERTIFICATE [MansSertifikats]
FROM FILE = N'c:\Sertifikati\MansSertifikats.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\Sertifikati\PrivateKey.pvk',
        ENCRYPTION BY PASSWORD = N'JaunaParole',
        DECRYPTION BY PASSWORD = N'DrosaParoleGlabasanaiDiska'
    );
Savukārt, ja datu bāzē ir DMK un ja to izmanto privātās atslēgas šifrēšanai, tad nav jānorāda privātās atslēgas šifrēšanas daļa:
-- lai izdzēstu sertifikātu, ja tāds eksistē datu bāzē:
-- DROP CERTIFICATE [MansSertifikats]
CREATE CERTIFICATE [MansSertifikats]
FROM FILE = N'c:\Sertifikati\MansSertifikats.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\Sertifikati\PrivateKey.pvk',
        DECRYPTION BY PASSWORD = N'DrosaParoleGlabasanaiDiska'
    );
Sertifikāta rezerves kopiju veidošana. (vairāk msdn)
Sertifikātu rezerves kopijas izveide, ja tas šifrēts ar privāto atslēgu. Šajā gadījumā sertifikāta privātā atslēga ir jātšifrē (DECRYPTION BY PASSWORD) un jānorāda, kāda būs parole šifrētajiem datiem uz cietā diska (ENCRYPTION BY PASSWORD):
BACKUP CERTIFICATE [MansSertifikats] TO FILE = N'c:\Sertifikati\MansSertifikats.cer'
    WITH PRIVATE KEY (
    DECRYPTION BY PASSWORD = N'DrosaParole',
    FILE = N'c:\Sertifikati\PrivateKey.pvk',
    ENCRYPTION BY PASSWORD = N'DrosaParoleGlabasanaiDiska');
Gadījumā, ja sertifikāta privātā atslēga tiek šifrēta izmantojot DMK, tad sertifikāta privātās atslēgas atšifrēšana notiek automātiski, līdz ar to nav jānorāda "DECRYPTION BY PASSWORD" daļa:
BACKUP CERTIFICATE [MansSertifikats] TO FILE = N'c:\Sertifikati\MansSertifikats.cer'
    WITH PRIVATE KEY (
    FILE = N'c:\Sertifikati\PrivateKey.pvk',
    ENCRYPTION BY PASSWORD = N'DrosaParoleGlabasanaiDiska');
Sertifikāts var būt arī bez privātās atslēgas, vai arī sertifikāta privāto atslēgai var nebūt nepieciešams veidot rezerves kopiju. Tādā gadījumā izmanto šādu komandu:
BACKUP CERTIFICATE [MansSertifikats] TO FILE = N'c:\Sertifikati\MansSertifikats.cer'
Sertifikāta privātās atslēgas noņemšana. Noteiktās situācijās sertifikāta privāto atslēgu var būt nepieciešams dzēst no datu bāzes (piemēram, gadījumā, ja parakstāt procedūru un nevēlaties, lai citi datu bāzes administratori varētu parakstīt procedūras). Pēc komandas izpildīšanas sertifikāts saturēs tikai publisko atslēgu. (vairāk msdn):
ALTER CERTIFICATE [MansSertifikats] REMOVE PRIVATE KEY;
Atjaunot privāto atslēgu no faila, var izmantojot šādu komandu:
ALTER CERTIFICATE [MansSertifikats]
    WITH PRIVATE KEY (FILE = N'c:\Sertifikati\PrivateKey.pvk',
    DECRYPTION BY PASSWORD = 'DrosaParoleGlabasanaiDiska',
    ENCRYPTION BY PASSWORD = 'DrosaParole');
Lietotāja pieslēguma (User Name) izveide no sertifikāta. Šeit es pieņemu, ka datu bāzē sertifikāts jau ir izveidots izmantojot kādu no iepriekš aprakstītajām metodēm. Konkrētajā datu bāzē jāizpilda:
Create User [CertUser] From Certificate [MansSertifikats]
Lietotāja vārda (Login Name) izveide no sertifikāta- šajā gadījumā sertifikātam jāeksistē master datu bāzē (sertifikātam nav jābūt ar privāto atslēgu). Šeit es pieņemu, ka sertifikāts eksistē uz diska:
USE master;
CREATE CERTIFICATE [MansSertifikats] FROM FILE = N'c:\Sertifikati\MansSertifikats.cer';
CREATE LOGIN [CertLogin] FROM CERTIFICATE [MansSertifikats];
Protams, pēc tam iespējams no lietotāja vārda izveidot tam atbilstošo pieslēguma vārdu datu bāzē (ja tika izveidots pieslēguma vārds no sertifikāta, tad tas no sākuma ir jādzēš, jo gan "CertUser", gan "CertUserFromLogin" ir veidoti no tā paša sertifikāta):
Use MyDB;
Create User [CertUserFromLogin] From Login [CertLogin];
Sertifikāta dzēšana. Ja no sertifikāta ir veidoti pieslēguma vai lietotāja vārdi, tad sākumā tie jādzēš. Tāpat arī, ja sertifikāts ir izmantots moduļu parakstīšanai, tad no sākuma šie paraksti ir jānoņem (par to šeit plašāk netiek aprakstīts). Lai dzēstu sertifikātu jāizpilda komanda:
DROP CERTIFICATE [MansSertifikats];
Sertifikātu izveide izmantojot citas metodes
Šeit parādīšu tikai vienu no iespējamajām metodēm- sertifikātu izveidi izmantojot makecert.exe komandvirknes rīku. Lai to izdarītu, jāpalaiž Visual Studio Comand Promt un jāizpilda, piemēram, šāda komanda:
makecert -sv "c:\Sertifikati\JaunsSertifikats.pvk" -pe -a sha1 -len 1024 -r -n "CN=Generets" c:\Sertifikati\JaunsSertifikats.cer
Komandas izpildes laikā tiks prasīta parole, ko izmantot sertifikāta privātās atslēgas aizsargāšanai. Izskatās apmēram šādi:

Pēc komandas izpildes tiek izveidots sertifikāts un tā privātā atslēga. To var importēt SQL Server datu bāzē gluži tāpat, kā SQL Server izveidotos sertifikātus!

Sertifikātu informācijas apskatīšana
Šo sadaļu papildināšu brīžos, kad būs jauni raksti par sertifikātiem (piemēram, procedūru parakstīšana).

Lai apskatītu datu bāzē izveidotos sertifikātus un to šifrēšanas metodi, var izmantot katalogu skatu sys.certificates:
Select * From sys.certificates
Lai uzzinātu visas procedūras/trigeri datu bāzē, kas ir parakstīti (izmantojot sertifikātu vai asimetrisko atslēgu), var izmantot sys.crypt_properties:
Select
    SCHEMA_NAME(o.schema_id) + '.' + o.name AS object_name,
    cp.crypt_type_desc
From sys.crypt_properties cp
    Inner Join sys.all_objects o on cp.major_id = o.object_id
Order By SCHEMA_NAME(o.schema_id) + '.' + o.name;
Un visas procedūras un trigeri:
Select SCHEMA_NAME(o.schema_id) + '.' + o.name AS object_name,
    cp.crypt_type_desc
From  sys.all_objects o
    Left Join sys.crypt_properties cp on cp.major_id = o.object_id
Where Type in ('P', 'TR') And o.is_ms_shipped = 0
    And o.name not like 'sp_%' -- sp_ ir piem diagrammām. parasti izstrādātāji paši šādus prefiksus neizmanto
Order By SCHEMA_NAME(o.schema_id) + '.' + o.name;

Nav komentāru:

Komentāra publicēšana