Transact-SQL | Аутентификация и шифрование данных

Transact-SQL | Аутентификация и шифрование данных Сертификаты

Основы ms sql server шифрования на примере симметричного шифрования / ms sql server- по простому. /

Автор: dbasimple

Для шифрования отдельный строк данных  необходимы функции шифрования – мы будем использовать EncryptByKey DescryptByKey.
Соответственно, не имея одного из компонентов, мы не сможем расшифровать наши данные. Поэтому не забываем делать резервные копии ключей.
Перейдем от теории к практики:
Задача: зашифровать поле  в таблице.
    PASSWORD=‘superpassword#12’
   WITHSUBJECT=‘Certificate for Admin bd’;
    WITHALGORITHM=AES_256
    ENCRYPTIONBYCERTIFICATE cert1;
GO
SELECT * FROMsys.certificatesСоздание резервной копии главного ключа базы данных  ENCRYPTIONBYPASSWORD=‘password@1’;
GOBACKUPCERTIFICATE cert1  TOFILE=‘c:cert1_backup’;
GO
      ENCRYPTIONBYPASSWORD=‘Password@1’Но перед тем как использовать шифрованиедешифрование, необходимо открыть ключ шифрования.
   DECRYPTIONBYCERTIFICATE cert1;           ([login]           ,[srv]                      ,[encrpsw]
           ,[bd]           ,[description]           ,[owner]           )
VALUES           ,‘test_srv’
           ,‘test bd’
           ,‘[description]’
           ,‘[owner]’
           )OPENSYMMETRICKEYSSN_Key_01
   DECRYPTIONBYCERTIFICATE cert1;      ,[srv]
      ,[encrpsw]
      ,convert(char,DecryptByKey([encrpsw]))as p
      ,[bd] ,owner
from [dbo].[servicelogins]
go

    DECRYPTIONBYPASSWORD=‘Password@1’force
  

Certificate trust list (ctl) в powershell – pki extensions

Как вы знаете, .NET не имеет нативной поддержки для X.509 CRL и X.509 CTL (или STL — Security Trust List). Именно поэтому я в своё время написал свой прототип для объекта X509CRL2. Недавно мне потребовалось получить доступ к CTL из PowerShell.

Во-первых, что такое CTL? Это просто список данных (например, хешей сертификатов), который подписан доверенной стороной. Где они применяются? Например, в Microsoft Root Certificate Program, в которой участвуют различные коммерческие и государственные центры сертификации. Эти CA доверены в Windows по умолчанию. Сами сертификаты хранятся в нескольких местах:

  • Контейнер Third-Party Certification Authorities локального хранилища сертификатов;
  • Crypt32.dll (только в Windows Vista );
  • Microsoft Update.

Список доверенных CA достаточно велик (более 300) и его состав контролируется через Certificate Trust List. Клиент Windows периодически скачивает с Windows Update этот самый CTL, в котором хранятся хеши всех доверенных корневых CA и сравнивает их с хешами сертификатов, установленных в Third-Party CAs. Все сертификаты, чьи хеши не представлены в CTL, выпиливаются из хранилища и к этим CA больше автоматического доверия нету. А если в списке есть хеш, но такого сертификата ВНЕЗАПНО нету, тогда клиент ещё раз обращается к Windows Update и скачивает оттуда сертификаты. Следует понимать, что этот CTL *не содержит* сами сертификаты, только их хеши и атрибуты (например, Friendly Name). Как я уже говорил, CTL защищается от подделок цифровой подписью.

И вот, мне приспичило получить вменяемый доступ к этому CTL в PowerShell. Задача оказалась достаточно лёгкой: CertCreateCTLContext. Всё очень похоже на работу с предыдущим скриптом X509CRL:

functionGet-CTL{[CmdletBinding()]param([Parameter(Mandatory=$true)][Byte[]]$RawData)Add-Type@"
using System.Security.Cryptography;

namespace System.Security.Cryptography.X509Certificates {
    public class X509CTL {
        public int Version;
        public OidCollection SubjectUsage;
        public String SequenceNumber;
        public DateTime ThisUpdate;
        public Oid SubjectAlgorithm;
        public Object[] Entries;
        public X509ExtensionCollection Extensions;
        public IntPtr Handle;
        public Byte[] RawData;
    }
    public class X509CTLEntry {
        public String Thumbprint;
        public X509Attribute[] Attributes;
        public IntPtr Certificate;
    }
    public class X509Attribute {
        public Oid OID;
        public Byte[] RawData;
    }
}
"@$cryptsignature=@"
[DllImport("Crypt32.dll", CharSet = CharSet.Auto, SetLastError = true)]
public static extern IntPtr CertCreateCTLContext(
    UInt32 dwMsgAndCertEncodingType,
    Byte[] pbCtlEncoded,
    UInt32 cbCtlEncoded
);
[DllImport("Crypt32.dll", CharSet = CharSet.Auto, SetLastError = true)]
public static extern Boolean CertFreeCTLContext(
    IntPtr pCtlContext
);
[DllImport("Crypt32.dll", CharSet = CharSet.Auto, SetLastError = true)]
public static extern IntPtr CertFindExtension(
    [MarshalAs(UnmanagedType.LPStr)]
    String pszObjId,
    UInt32 cExtensions,
    IntPtr rgExtensions
);
"@$wincryptsignature=@"
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CTL_CONTEXT {
    public UInt32 dwMsgAndCertEncodingType;
    public IntPtr pbCtlEncoded;
    public UInt32 cbCtlEncoded;
    public IntPtr pCtlInfo;
    public IntPtr hCertStore;
    public IntPtr hCryptMsg;
    public IntPtr pbCtlContent;
    public UInt32 cbCtlContent;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CTL_INFO {
    public UInt32 dwVersion;
    public CTL_USAGE SubjectUsage;
    public CRYPTOAPI_BLOB ListIdentifier;
    public CRYPTOAPI_BLOB SequenceNumber;
    public UInt64 ThisUpdate;
    public UInt64 NextUpdate;
    public CRYPT_ALGORITHM_IDENTIFIER SubjectAlgorithm;
    public UInt32 cCTLEntry;
    public IntPtr rgCTLEntry;
    public UInt32 cExtension;
    public IntPtr rgExtension;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CTL_USAGE {
    public UInt32 cUsageIdentifier;
    public IntPtr rgpszUseageIdentifier;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CTL_ENTRY {
    public CRYPTOAPI_BLOB SubjectIdentifier;
    public UInt32 cAttribute;
    public IntPtr rgAttribute;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CRYPT_ATTRIBUTE {
    [MarshalAs(UnmanagedType.LPStr)]
    public String pszObjId;
    public UInt32 cValue;
    public IntPtr rgValue;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CRYPT_ALGORITHM_IDENTIFIER {
    [MarshalAs(UnmanagedType.LPStr)]
    public String pszObjId;
    public CRYPTOAPI_BLOB Parameters;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CRYPTOAPI_BLOB {
    public UInt32 cbData;
    public IntPtr pbData;
}
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
public struct CERT_EXTENSION {
    [MarshalAs(UnmanagedType.LPStr)]
    public String pszObjId;
    public Boolean fCritical;
    public CRYPTOAPI_BLOB Value;
}
"@$WarningPreference="SilentlyContinue"Add-Type-MemberDefinition$cryptsignature-NamespacePKI-NameCrypt32Add-Type-MemberDefinition$wincryptsignature-NamespacePKI.Structs-NameWincrypt[IntPtr]$CTLContext=[IntPtr]::Zero$CTLContext=[PKI.Crypt32]::CertCreateCTLContext(65537,$RawData,$RawData.Length)if(!$CTLContext.Equals([IntPtr]::Zero)){$CTLContextStructure=[Runtime.InteropServices.Marshal]::PtrToStructure($CTLContext,[PKI.Structs.Wincrypt CTL_CONTEXT])$CTLInfo=[Runtime.InteropServices.Marshal]::PtrToStructure($CTLContextStructure.pCtlInfo,[PKI.Structs.Wincrypt CTL_INFO])$rgpszUseageIdentifier=$CTLInfo.SubjectUsage.rgpszUseageIdentifier$oids=New-ObjectSecurity.Cryptography.OidCollectionfor($m=;$m-lt$CTLInfo.SubjectUsage.cUsageIdentifier;$m  ){$pszOid=[Runtime.InteropServices.Marshal]::ReadIntPtr($rgpszUseageIdentifier)[void]$oids.Add([Runtime.InteropServices.Marshal]::PtrToStringAnsi($pszOid))$rgpszUseageIdentifier=[int]$rgpszUseageIdentifier [Runtime.InteropServices.Marshal]::SizeOf([PKI.Structs.Wincrypt CTL_USAGE])}$bytes=New-Objectbyte[]-ArgumentList$CTLInfo.SequenceNumber.cbData[Runtime.InteropServices.Marshal]::Copy($CTLInfo.SequenceNumber.pbData,$bytes,,$CTLInfo.SequenceNumber.cbData)[array]::Reverse($bytes)$SequenceNumber=-join($bytes|%{"{0:x2}"-f$_})$ctl=New-ObjectSystem.Security.Cryptography.X509Certificates.X509CTL-Property@{Version=$CTLInfo.dwVersion 1;SubjectUsage=$oids;SequenceNumber=$SequenceNumberThisUpdate=[datetime]::FromFileTime($CTLInfo.ThisUpdate);SubjectAlgorithm=$CTLInfo.SubjectAlgorithm.pszObjId;Handle=$CTLContext;}$entries=@()if($CTLInfo.cCTLEntry-gt){$rgCTLEntry=$CTLInfo.rgCTLEntryfor($n=;$n-lt$CTLInfo.cCTLEntry;$n  ){$entry=New-ObjectSystem.Security.Cryptography.X509Certificates.X509CTLEntry$EntryStructure=[Runtime.InteropServices.Marshal]::PtrToStructure($rgCTLEntry,[PKI.Structs.Wincrypt CTL_ENTRY])$bytes=New-Objectbyte[]-ArgumentList($EntryStructure.SubjectIdentifier.cbData)[System.Runtime.InteropServices.Marshal]::Copy($EntryStructure.SubjectIdentifier.pbData,$bytes,,$EntryStructure.SubjectIdentifier.cbData)$entry.Thumbprint=-join($bytes|%{"{0:X2}"-f$_})$attribs=@()if($EntryStructure.cAttribute-gt){$rgAttribute=$EntryStructure.rgAttributefor($m=;$m-lt$EntryStructure.cAttribute;$m  ){$attrib=New-ObjectSystem.Security.Cryptography.X509Certificates.X509Attribute$AttribStructure=[Runtime.InteropServices.Marshal]::PtrToStructure($rgAttribute,[PKI.Structs.Wincrypt CRYPT_ATTRIBUTE])$attrib.OID=$AttribStructure.pszObjId$blob=[Runtime.InteropServices.Marshal]::PtrToStructure($AttribStructure.rgValue,[PKI.Structs.Wincrypt CRYPTOAPI_BLOB])$bytes=New-Objectbyte[]-ArgumentList$blob.cbData[Runtime.InteropServices.Marshal]::Copy($blob.pbData,$bytes,,$blob.cbData)$attrib.RawData=$bytes$attribs =$attrib$rgAttribute=[int]$rgAttribute [Runtime.InteropServices.Marshal]::SizeOf([PKI.Structs.Wincrypt CRYPT_ATTRIBUTE])}}$entry.Attributes=$attribs$entries =$entry$rgCTLEntry=[int]$rgCTLEntry [Runtime.InteropServices.Marshal]::SizeOf([PKI.Structs.Wincrypt CTL_ENTRY])}}$rgExtension=$CTLInfo.rgExtensionif($CTLInfo.cExtension-ge1){$Exts=New-ObjectSecurity.Cryptography.X509Certificates.X509ExtensionCollectionfor($n=;$n-lt$CTLInfo.cExtension;$n  ){$ExtEntry=[Runtime.InteropServices.Marshal]::PtrToStructure($rgExtension,[PKI.CRL CERT_EXTENSION])[IntPtr]$rgExtension=[PKI.CRL]::CertFindExtension($ExtEntry.pszObjId,$CTLInfo.cExtension,$CTLInfo.rgExtension)$pByte=$ExtEntry.Value.pbData$bBytes=$nullfor($m=;$m-lt$ExtEntry.Value.cbData;$m  ){[byte[]]$bBytes =[Runtime.InteropServices.Marshal]::ReadByte($pByte)$pByte=[int]$pByte [Runtime.InteropServices.Marshal]::SizeOf([byte])}$ext=New-ObjectSecurity.Cryptography.X509Certificates.X509Extension$ExtEntry.pszObjId,@([Byte[]]$bBytes),$ExtEntry.fCritical[void]$Exts.Add($ext)$rgExtension=[int]$rgExtension [Runtime.InteropServices.Marshal]::SizeOf([PKI.CRL CERT_EXTENSION])}$CTL.Extensions=$Exts}$ctl.Entries=$entries$ctl[void][PKI.Crypt32]::CertFreeCTLContext($CTLContext)}else{Write-Error-CategoryInvalidData-ErrorIdInvalidDataException-Message"The data is not valid CTL."}}

И вот, как примерно выглядит вывод:

[↓] [vPodans] $raw = [io.file]::ReadAllBytes(".Desktopauthroot.stl")
[↓] [vPodans] $ctl = Get-CTL $raw
[↓] [vPodans] $ctl


Version          : 1
SubjectUsage     : {Root List Signer}
SequenceNumber   : 1401ccf1d2032b8314
ThisUpdate       : 23.02.2021 4:22:38
SubjectAlgorithm : System.Security.Cryptography.Oid
Entries          : {System.Security.Cryptography.X509Certificates.X509CTLEntry, System.Security.Cryptography.X509Certif
                   icates.X509CTLEntry, System.Security.Cryptography.X509Certificates.X509CTLEntry, System.Security.Cry
                   ptography.X509Certificates.X509CTLEntry...}
Extensions       :
Handle           : 40415264
RawData          :



[↓] [vPodans]

В свойстве Entries находится массив элементов, каждый из которых представляет собой хеш и набор атрибутов конкретного сертификата:

[↓] [vPodans] $ctl.Entries.Length
347
[↓] [vPodans] $ctl.Entries[0]

Thumbprint                              Attributes                                                          Certificate
----------                              ----------                                                          -----------
CDD4EEAE6000AC7F40C3802C171E30148030... {System.Security.Cryptography.Oid, S...                                       0


[↓] [vPodans] $ctl.Entries[0].Attributes

OID                                                         RawData
---                                                         -------
System.Security.Cryptography.Oid                            {4, 14, 48, 12...}
System.Security.Cryptography.Oid                            {4, 16, 240, 196...}
System.Security.Cryptography.Oid                            {4, 20, 14, 172...}
System.Security.Cryptography.Oid                            {4, 32, 136, 93...}
System.Security.Cryptography.Oid                            {4, 74, 77, 0...}


[↓] [vPodans]

В X509CTLEntry я добвил свойство Certificate, которое в идеале (сейчас там всегда нули) должно содержать ссылку на сертификат (хэндл). Но я пока не придумал, как это лучше сделать. Дело в том, что в Windows Vista и выше, в контейнере Third-Party CAs установлено всего несколько сертификатов. Остальные доставляются в хранилище из Windows Update или crypt32.dll по требованию. Дополнительно, здесь есть ещё 2 проблемы, которые следует решить:

1) В структуре CTL_CONTEXT у нас есть свойство hCertStore, которое содержит хэндл на хранилище сертификатов (враппер — X509Store). Но что-то сертификатов я там не нашёл.

2) Можно распотрошить ресурсы crypt32.dll (сертификаты там хранятся в виде сериализированного хранилища) и достать оттуда список всех доверенных сертификатов (хотя, он может быть не совсем актуальным). Беда в том, что если надёргать хэндлы из этих сертификатов, они сразу станут недействительными, как только закроете serialized store. Т.е. скорее всего для каждого сертификата придётся дуплицировать хэндлы и как-то их потом очищать.

Вобщем, тут есть над чем подумать. Нарпример, можно доставить форматтер, который бы отформатировал атрибуты сертификатов во что-то читабельное (сейчас они хранятся только в виде массива байтов). Можно попробовать прикрутить CryptFormatObject. Но это уже частности, потому что это всё-таки, только прототип для CTL в PowerShell.

Про сертификаты:  Жилищный сертификат на улучшение жилищных условий, покупку квартиры в 2021 году: кому выдается, как его использовать

Куда тратится процессорное время в tde?

Итак, приступим к исследованиям:

Шаг 1. Давайте создадим базу данных, в которой будем проводить эксперименты. Также создадим для этой базы данных DEK (Database Encryption Key), но шифрование пока включать не будем.

Шаг 2. Теперь создадим в этой базе данных хранимую процедуру dbo.spu_TDE_Test1, которую будем использовать для создания тестовой нагрузки на SQL Server. Нагрузка будет представлять собой вставку большого числа строк в таблицу. Это один из самых “неприятных” для TDE сценариев, так как он связан с большим объемом ввода/вывода. Кроме того, процедура измеряет общее время, которое заняло выполнение тестовой нагрузки и отдельно время потребления CPU.

USE TDE_TEST_DB
go
CREATEPROCEDURE dbo.spu_TDE_Test1 ASBEGINSET NOCOUNT ONIF(OBJECT_ID('dbo.TestTable') isnotNULL) DROPTABLE dbo.TestTable
    CREATETABLE dbo.TestTable
    (
        Id int primarykey, 
        Data varchar(8000) notnull
    )
    CHECKPOINTDBCC DROPCLEANBUFFERS 
    DECLARE @time_start datetime = GETDATE(), @cpu_start float = @@CPU_BUSY    
    DECLARE @i int = 0 
    WHILE(@i < 100000) BEGININSERT dbo.TestTable(Id, Data) VALUES (@i, REPLICATE('a', 8000))
        SET @i  = 1
    ENDCHECKPOINTDECLARE @duration float, @cpu_used float, @cpu_count int
    SET @duration = DATEDIFF(ms, @time_start, GETDATE()) / 1000.0
    SET @cpu_count = (SELECT  COUNT(*) FROM sys.dm_os_schedulers 
                                     WHERE scheduler_id < 255 And is_online = 1)
    SET @cpu_used = 
      (@@CPU_BUSY - @cpu_start) * @@TIMETICKS / 1000000.0 / @cpu_count     
    SELECT 
        @duration as duration, 
        @cpu_used as cpu_used,
        CONVERT(decimal(6, 1), 100.0 * @cpu_used / @duration / @cpu_count) 
          as [cpu_used%]
END
go

Шаг 3. Запускаем процедуру и смотрим на результаты, полученные для незашифрованной базы данных (при первом запуске процедуры будет выполняться расширение базы данных, так что результаты нужно “снимать” как минимум со второго запуска).

Получившийся у меня результат:

Шаг 4. Теперь включаем шифрование базы данных.

Шаг 5. Снова запускаем ту же тестовую процедуру, но уже для зашифрованной базы данных. Процедура возвращает нам общее время выполнения теста и потребление времени CPU. C помощью SQLInternalsProfiler’а мы так же получаем список API-функций из динамической библиотеки ADVAPI32.

EXECUTE master..xp_SQLInternalsProfiler_AddSource_IAT 'ADVAPI32.dll', '*', '.', 1
EXECUTE dbo.spu_TDE_Test1
CREATETABLE #Results
(
    source_name nvarchar(128) notnull,
    [count] bigint notnull, 
    second_time decimal(12, 6) notnull,
    percent_time decimal(8, 2) notnull
) 
INSERT #Results 
EXECUTE master..xp_SQLInternalsProfiler_Results 897
EXECUTE master..xp_SQLInternalsProfiler_StopAll 1
SELECT * FROM #Results WHERE percent_time > 0.5 
UNIONALLSELECT'Total', SUM([count]), SUM(second_time), SUM(percent_time) FROM #Results
ORDERBY second_time DESCDROPTABLE #Results

Результат:

Если свести все результаты вместе, получаем:

Надо сказать, что полученные результаты ставят больше вопросов, чем дают ответов. Эти результаты не совсем укладываются в декларируемую логику работы TDE – “при сохранении на диск данные шифруются, а при чтении с диска расшифровываются”. В частности мы видим, что очень много процессорного времени ушло на расшифровку данных, хотя, если следовать логике TDE, то вообще ничего не должно было расшифровываться (мы же только пишем данные).

Обработка исключений с помощью инструкций try, catch и throw

В версиях SQL Server более ранних, чем SQL Server 2005 требовалось наличие кода для обработки ошибок после каждой инструкции Transact-SQL, которая могла бы вызвать ошибку. (Для обработки ошибок можно использовать глобальную переменную @@error.) Начиная с версии SQL Server 2005, исключения можно перехватывать для обработки с помощью инструкций TRY и CATCH. В этом разделе сначала объясняется значение понятия “исключение”, после чего обсуждается работа этих двух инструкций.

Исключением (exception) называется проблема (обычно ошибка), которая не позволяет продолжать выполнение программы. Выполняющаяся программа не может продолжать исполнение по причине недостаточности информации, необходимой для обработки ошибки в данной части программы. Поэтому задача обработки ошибки передается другой части программы.

Роль инструкции TRY заключается в перехвате исключения. (Поскольку для реализации этого процесса обычно требуется несколько инструкций, то обычно применяется термин “блок TRY”, а не “инструкция TRY”.) Если же в блоке TRY возникает исключение, компонент системы, называющийся обработчиком исключений, доставляет это исключение для обработки другой части программы. Эта часть программы обозначается ключевым словом CATCH и поэтому называется блоком CATCH.

Обработка исключений с использованием инструкций TRY и CATCH является общим методом обработки ошибок, применяемым в современных языках программирования, таких как C# и Java.

Обработка исключений с помощью блоков TRY и CATCH предоставляет программисту множество преимуществ, включая следующие:

  • исключения предоставляют аккуратный способ определения ошибок без загромождения кода дополнительными инструкциями;

  • исключения предоставляют механизм прямой индикации ошибок, вместо использования каких-либо побочных признаков;

  • программист может видеть исключения и проверить их в процессе компиляции.

В SQL Server 2021 добавлена еще одна инструкция THROW, имеющая отношение к обработке ошибок. Эта инструкция позволяет вызвать исключение, которое улавливается в блоке обработки исключений. Иными словами, инструкция THROW – это другой механизм возврата, который работает подобно рассмотренной ранее инструкции RAISEERROR.

Использование инструкций TRY, CATCH и THROW для обработки исключений показано в примере ниже:

USE SampleDb;

BEGIN TRY
    BEGIN TRANSACTION
        insert into Employee values(11111, 'Анна', 'Самойленко','d2');
        insert into Employee values(22222, 'Игорь', 'Васин','d4');
        -- Создаем ошибку ссылочной целостности
        insert into Employee values(33333, 'Петр', 'Сидоров', 'd2');
    COMMIT TRANSACTION
    PRINT 'Транзакция выполнена'
END TRY
BEGIN CATCH
    ROLLBACK
        PRINT 'Отмена транзакции';
    THROW
END CATCH

В данном примере показано, как для обработки исключений оформлять инструкции в пакет и выполнять откат результатов исполнения всей группы инструкций при возникновении ошибки. Попытка выполнить пакет, показанный в примере, будет неудачной, а в результате будет выведено следующее сообщение:

Выполнение кода в примере осуществляется следующим образом. После успешного выполнения первой инструкции INSERT попытка исполнения второй инструкции вызывает ошибку нарушения ссылочной целостности. Так как все три инструкции заключены в блок TRY, возникает исключение для всего блока и обработчик исключений начинает исполнение блока CATCH.

Выполнение кода в этом блоке осуществляет откат исполнения всех инструкций в блоке TRY и выводит соответствующее сообщение. После этого инструкция THROW возвращает управление исполнением вызывающему объекту. Вследствие всего этого содержимое таблицы Employee не будет изменено.

Инструкции Transact-SQL – BEGIN TRANSACTION, COMMIT TRANSACTION и ROLLBACK начинают, фиксируют и выполняют откат транзакций соответственно. Предмет транзакций, в общем, и эти инструкции, в частности, мы будем рассматривать в одной из следующих статей.

Общие сведения

Системы баз данных обычно используют индексы для обеспечения быстрого доступа к реляционным данным. Индекс представляет собой отдельную физическую структуру данных, которая позволяет получать быстрый доступ к одной или нескольким строкам данных. Таким образом, правильная настройка индексов является ключевым аспектом улучшения производительности запросов.

Индекс базы данных во многом сходен с индексом (алфавитным указателем) книги. Когда нам нужно быстро найти какую-либо тему в книге, мы сначала смотрим в индексе, на каких страницах книги эта тема рассматривается, а потом сразу же открываем нужную страницу.

Но между индексом книги и индексом базы данных есть две существенные разницы:

Если для таблицы отсутствует подходящий индекс, для выборки строк система использует метод сканирования таблицы. Выражение сканирование таблицы означает, что система последовательно извлекает и исследует каждую строку таблицы (от первой до последней), и помещает строку в результирующий набор, если для нее удовлетворяется условие поиска в предложении WHERE.

Индексы сохраняются в дополнительных структурах базы данных, называющихся страницами индексов. Для каждой индексируемой строки имеется элемент индекса (index entry), который сохраняется на странице индексов. Каждый элемент индекса состоит из ключа индекса и указателя.

Это свойство индексов играет очень важную роль, поскольку количество операций ввода/вывода, требуемых для прохода по страницам индексов, значительно меньше, чем количество операций ввода/вывода, требуемых для прохода по соответствующим страницам данных.

Индексы компонента Database Engine создаются, используя структуру данных сбалансированного дерева B . B -дерево имеет древовидную структуру, в которой все самые нижние узлы находятся на расстоянии одинакового количества уровней от вершины (корневого узла) дерева. Это свойство поддерживается даже тогда, когда в индексированный столбец добавляются или удаляются данные.

На рисунке ниже показана структура B -дерева для таблицы Employee и прямой доступ к строке в этой таблице со значением 25348 для столбца Id. (Предполагается, что таблица Employee проиндексирована по столбцу Id.) На этом рисунке можно также видеть, что B -дерево состоит из корневого узла, узлов дерева и промежуточных узлов, количество которых может быть от нуля и больше:

Поиск в этом дереве значения 25348 можно выполнить следующим образом. Начиная с корня дерева, выполняется поиск наименьшего значения ключа, большего или равного требуемому значению. Таким образом, в корневом узле таким значением будет 29346, поэтому делается переход на промежуточный узел, связанный с этим значением.

В этом узле заданным требованиям отвечает значение 28559, вследствие чего выполняется переход на узел дерева, связанный с этим значением. Этот узел и содержит искомое значение 25348. Определив требуемый индекс, мы можем извлечь его строку из таблицы данных с помощью соответствующих указателей. (Альтернативным эквивалентным подходом будет поиск меньшего или равного значения индекса.)

Индексированный поиск обычно является предпочтительным методом поиска в таблицах с большим количеством строк по причине его очевидного преимущества. Используя индексированный поиск, мы можем найти любую строку в таблице за очень короткое время, применив лишь несколько операций ввода/вывода.

Про сертификаты:  сертификация котлов | сертификация сосудов под давлением | сертификация трубопроводов | сертификация тр тс 032 2013

В следующих разделах мы рассмотрим два существующих типа индексов, кластеризованные и некластеризованные, а также научимся создавать индексы.

Создание индексов

Индекс для таблицы создается с помощью инструкции CREATE INDEX. Эта инструкция имеет следующий синтаксис:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
    ON table_name (column1 [ASC | DESC] ,...)
        [ INCLUDE ( column_name [ ,... ] ) ]
        
[WITH
    [FILLFACTOR=n]
    [[, ] PAD_INDEX = {ON | OFF}]
    [[, ] DROP_EXISTING = {ON | OFF}]
    [[, ] SORT_IN_TEMPDB = {ON | OFF}]
    [[, ] IGNORE_DUP_KEY = {ON | OFF}]
    [[, ] ALLOW_ROW_LOCKS = {ON | OFF}]
    [[, ] ALLOW_PAGE_LOCKS = {ON | OFF}]
    [[, ] STATISTICS_NORECOMPUTE = {ON | OFF}]
    [[, ] ONLINE = {ON | OFF}]]
    [ON file_group | "default"]Соглашения по синтаксису

Параметр index_name задает имя создаваемого индекса. Индекс можно создать для одного или больше столбцов одной таблицы, обозначаемой параметром table_name. Столбец, для которого создается индекс, указывается параметром column1. Числовой суффикс этого параметра указывает на то, что индекс можно создать для нескольких столбцов таблицы. Компонент Database Engine также поддерживает создание индексов для представлений.

Можно проиндексировать любой столбец таблицы. Это означает, что столбцы, содержащие значения типа данных VARBINARY(max), BIGINT и SQL_VARIANT, также могут быть индексированы.

Индекс может быть простым или составным. Простой индекс создается по одному столбцу, а составной индекс – по нескольким столбцам. Для составного индекса существуют определенные ограничения, связанные с его размером и количеством столбцов. Индекс может иметь максимум 900 байтов и не более 16 столбцов.

Параметр UNIQUE указывает, что проиндексированный столбец может содержать только однозначные (т.е. неповторяющиеся) значения. В однозначном составном индексе однозначной должна быть комбинация значений всех столбцов каждой строки. Если ключевое слово UNIQUE не указывается, то повторяющиеся значения в проиндексированном столбце (столбцах) разрешаются.

Параметр CLUSTERED задает кластеризованный индекс, а параметр NONCLUSTERED (применяется по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Компонент Database Engine разрешает для таблицы максимум 249 некластеризованных индексов.

Возможности компонента Database Engine были расширены, позволяя создать поддержку индексов с убывающим порядком значений столбцов. Параметр ASC после имени столбца указывает, что индекс создается с возрастающим порядком значений столбца, а параметр DESC означает убывающий порядок значений столбца индекса.

Параметр INCLUDE позволяет указать неключевые столбцы, которые добавляются к страницам узлов некластеризованного индекса. Имена столбцов в списке INCLUDE не должны повторяться, и столбец нельзя использовать одновременно как ключевой и неключевой.

Чтобы по-настоящему понять полезность параметра INCLUDE, нужно понимать, что собой представляет покрывающий индекс (covering index). Если все столбцы запроса включены в индекс, то можно получить значительное повышение производительности, т.к. оптимизатор запросов может определить местонахождение всех значений столбцов по страницам индекса, не обращаясь к данным в таблице.

Такая возможность называется покрывающим индексом или покрывающим запросом. Поэтому включение в страницы узлов некластеризованного индекса дополнительных неключевых столбцов позволит получить больше покрывающих запросов, при этом их производительность будет значительно повышена.

Параметр FILLFACTOR задает заполнение в процентах каждой страницы индекса во время его создания. Значение параметра FILLFACTOR можно установить в диапазоне от 1 до 100. При значении n=100 каждая страница индекса заполняется на 100%, т.е. существующая страница узла так же, как страница, не относящаяся к узлу, не будет иметь свободного места для вставки новых строк.

При значении параметра FILLFACTOR между 1 и 99 страницы узлов создаваемой структуры индекса будут содержать свободное место. Чем больше значение n, тем меньше свободного места в страницах узлов индекса. Например, при значении n=60 каждая страница узлов индекса будет иметь 40% свободного места для вставки строк индекса в дальнейшем.

(Строки индекса вставляются посредством инструкции INSERT или UPDATE.) Таким образом, значение n=60 будет разумным для таблиц, данные которых подвергаются довольно частым изменениям. При значениях параметра FILLFACTOR между 1 и 99 промежуточные страницы индекса содержат свободное место для одной записи каждая.

После создания индекса в процессе его использования значение FILLFACTOR не поддерживается. Иными словами, оно только указывает объем зарезервированного места с имеющимися данными при задании процентного соотношения для свободного места. Для восстановления исходного значения параметра FILLFACTOR применяется инструкция ALTER INDEX.

Параметр PAD_INDEX тесно связан с параметром FILLFACTOR. Параметр FILLFACTOR в основном задает объем свободного пространства в процентах от общего объема страниц узлов индекса. А параметр PAD_INDEX указывает, что значение параметра FILLFACTOR применяется как к страницам индекса, так и к страницам данных в индексе.

Параметр DROP_EXISTING позволяет повысить производительность при воспроизведении кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Более подробную информацию смотрите далее в разделе “Пересоздание индекса”.

Параметр SORT_IN_TEMPDB применяется для помещения в системную базу данных tempdb данных промежуточных операций сортировки, применяющихся при создании индекса. Это может повысить производительность, если база данных tempdb размещена на другом диске, чем данные.

Параметр IGNORE_DUP_KEY разрешает системе игнорировать попытку вставки повторяющихся значений в индексированные столбцы. Этот параметр следует применять только для того, чтобы избежать прекращения выполнения длительной транзакции, когда инструкция INSERT вставляет дубликат данных в индексированный столбец.

Если этот параметр активирован, то при попытке инструкции INSERT вставить в таблицу строки, нарушающие однозначность индекса, система базы данных вместо аварийного завершения выполнения всей инструкции просто выдает предупреждение. При этом компонент Database Engine не вставляет строки с дубликатами значений ключа, а просто игнорирует их и добавляет правильные строки. Если же этот параметр не установлен, то выполнение всей инструкции будет аварийно завершено.

Когда параметр ALLOW_ROW_LOCKS активирован (имеет значение on), система применяет блокировку строк. Подобным образом, когда активирован параметр ALLOW_PAGE_LOCKS, система применяет блокировку страниц при параллельном доступе.

Активированный параметр ONLINE позволяет создавать, пересоздавать и удалять индекс в диалоговом режиме. Данный параметр позволяет в процессе изменения индекса одновременно изменять данные основной таблицы или кластеризованного индекса и любых связанных индексов.

Параметр ON создает указанный индекс или на файловой группе по умолчанию (значение default), или на указанной файловой группе (значение file_group).

В примере ниже показано создание некластеризованного индекса для столбца Id таблицы Employee:

Создание однозначного составного индекса показано в примере ниже:

В этом примере значения в каждом столбце должны быть однозначными. При создании индекса заполняется 80% пространства каждой страницы узлов индекса.

Создание однозначного индекса для столбца невозможно, если этот столбец содержит повторяющиеся значения. Такой индекс можно создать лишь в том случае, если каждое значение (включая значение NULL) встречается в столбце только один раз. Кроме этого, любая попытка вставить или изменить существующее значение данных в столбец, включенный в существующий уникальный индекс, будет отвергнута системой в случае дублирования значения.

Создание представления

Представление создается посредством инструкции CREATE VIEW, синтаксис которой выглядит следующим образом:

Инструкция CREATE VIEW должна быть единственной инструкцией пакета. (Это означает, что эту инструкцию следует отделять от других инструкций группы посредством инструкции GO.)

Параметр view_name задает имя определяемого представления, а в параметре column_list указывается список имен, которые будут использоваться в качестве имен столбцов представления. Если этот необязательный параметр опущен, то используются имена столбцов таблиц, по которым создается представление.

Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.

Предложение SCHEMABINDING привязывает представление к схеме таблицы, по которой оно создается. Когда это предложение указывается, имена объектов баз данных в инструкции SELECT должны состоять из двух частей, т.е. в виде schema.db_object, где schema – владелец, а db_object может быть таблицей, представлением или определяемой пользователем функцией.

Любая попытка модифицировать структуру представлений или таблиц, на которые ссылается созданное таким образом представление, будет неудачной. Чтобы такие таблицы или представления можно было модифицировать (инструкцией ALTER) или удалять (инструкцией DROP), нужно удалить это представление или убрать из него предложение SCHEMABINDING.

Когда при создании представления указывается параметр VIEW_METADATA, все его столбцы можно обновлять (за исключением столбцов с типом данных timestamp), если представление имеет триггеры INSERT или UPDATE INSTEAD OF.

Инструкция SELECT в представлении не может содержать предложение ORDER BY или параметр INTO. Кроме этого, по временным таблицам нельзя выполнять запросы.

Представления можно использовать для разных целей:

  • Для ограничения использования определенных столбцов и/или строк таблиц. Таким образом, представления можно использовать для управления доступом к определенной части одной или нескольких таблиц.

  • Для скрытия подробностей сложных запросов. Если для приложения базы данных требуются запросы со сложными операциями соединения, создание соответствующих представлений может упростить такие запросы.

  • Для ограничения вставляемых или обновляемых значений некоторым диапазоном.

В примере ниже показано создание представления:

Запрос в этом примере выбирает из таблицы Works_on строки, удовлетворяющие условию Job=’Консультант’. Представление view_Consultant определяется строками и столбцами, возвращаемыми этим запросом. На рисунке ниже отображена таблица Works_on, в которой строки, выбранные в представлении view_Consultant, выделены красным цветом:

Запрос в этом примере задает выборку строк, т.е. он создает горизонтальное подмножество базовой таблицы Works_on. Возможно также создание представления с ограничениями на включаемые в него столбцы и строки. Создание такого представления показано в примере ниже:

Запрос в этом примере выбирает для включения в представление view_WithoutBudget все столбцы таблицы Project, за исключением столбца Budget.

Про сертификаты:  Настройка КЭП для работы на MacOS | ITCOM удостоверяющий центр

Как уже упоминалось ранее, в общем формате инструкции CREATE VIEW не обязательно указывать имена столбцов представления. Однако, с другой стороны, в приведенных далее двух случаях обязательно требуется явно указывать имена столбцов:

  • если столбец представления создается из выражения или агрегатной функции;

  • если два или больше столбцов представления имеют одинаковое имя в базовой таблице.

В примере ниже показано создание представления, для которого явно указываются имена столбцов:

Здесь имена столбцов представления view_Count должны быть указаны явно по той причине, что инструкция SELECT содержит агрегатную функцию count(*), которая требует, чтобы все столбцы представления были именованы.

Не требуется явно указывать список столбцов в инструкции CREATE VIEW, если применить заголовки столбцов, как это показано в примере ниже:

Представление можно создать из другого представления, как показано в примере:

Представление view_project_p2 в примере ниже создается из представления view_Consultant. Все запросы, использующие представление view_project_p2, преобразовываются в эквивалентные запросы к базовой таблице Works_on.

Представления можно также создавать посредством среды Management Studio. Для этого выберите в обозревателе объектов базу данных, в которой требуется создать представление, щелкните в ней правой кнопкой мыши узел Views и в открывшемся контекстном меню выберите пункт New View. Откроется редактор представлений, в котором можно выполнять следующие действия:

  • выбрать базовые таблицы и строки в этих таблицах для создания представления;

  • присвоить представлению имя и определить условия в предложении WHERE соответствующего запроса.

Хранимые процедуры и среда clr

SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.

Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure, которая запускается на выполнение инструкцией RECONFIGURE. В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:

Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:

  1. Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.

  2. Используя инструкцию CREATE ASSEMBLY, создать соответствующий выполняемый файл.

  3. Сохранить процедуру в виде объекта сервера, используя инструкцию CREATE PROCEDURE.

  4. Выполнить процедуру, используя инструкцию EXECUTE.

На рисунке ниже показана графическая схема ранее изложенных шагов. Далее приводится более подробное описание этого процесса.

Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код.

В примере ниже показан исходный код хранимой процедуры на языке C#:

В этой процедуре реализуется запрос для подсчета числа строк в таблице Employee. В директивах using в начале программы указываются пространства имен, требуемые для ее выполнения. Применение этих директив позволяет указывать в исходном коде имена классов без явного указания соответствующих пространств имен.

Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure, который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection.

В следующем фрагменте кода:

используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand.

Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода.

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:

В параметре assembly_name указывается имя сборки. В необязательном предложении AUTHORIZATION указывается имя роли в качестве владельца этой сборки. В предложении FROM указывается путь, где находится загружаемая сборка.

Предложение WITH PERMISSION_SET является очень важным предложением инструкции CREATE ASSEMBLY и всегда должно указываться. В нем определяется набор прав доступа, предоставляемых коду сборки. Набор прав SAFE является наиболее ограничивающим.

Код сборки, имеющий эти права, не может обращаться к внешним системным ресурсам, таким как файлы. Набор прав EXTERNAL_ACCESS позволяет коду сборки обращаться к определенным внешним системным ресурсам, а набор прав UNSAFE предоставляет неограниченный доступ к ресурсам, как внутри, так и вне системы базы данных.

Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.

Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.

В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:

Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME. Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:

assembly_name.class_name.method_name

где:

  • assembly_name – указывает имя сборки;

  • class_name – указывает имя общего класса;

  • method_name – необязательная часть, указывает имя метода, который задается внутри класса.

Выполнение процедуры CountEmployees показано в примере ниже:

Инструкция PRINT возвращает текущее количество строк в таблице Employee.

Часто задаваемые вопросы

Типичные ошибки Континент-АП при установке связи с сервером доступа

1. Ошибка «Client cert not found»

Для решения данной проблемы необходимо:

· Проверить, запущен ли процесс eapsigner161.exe;

· Если процесс не запущен, зайти в папку с установленной программой и запустить процесс вручную.

2. Ошибка «Неизвестная ошибка импорта сертификатов»

Для решения данной проблемы необходимо:

· удалить все сертификаты с истекшим сроком действия из хранилища «Личные» локального хранилища сертификатов системы (для этого можно воспользоваться оснасткой «mmc» (выбрав в меню Файл -> Добавить или удалить оснастку -> Сертификаты -> Добавить -> Готово -> Ок) или воспользоваться функционалом браузера Internet Explorer (выбрать в меню Сервис -> Свойства браузера -> Содержание -> Сертификаты));

· удалить из хранилища «Личные» локального хранилища сертификатов системы все сертификаты, которые в своем составе, в поле «Субъект», содержат следующие символы: , ; “ ” « ».

3. Ошибка «Не совпадает подпись открытого эфемерного ключа»

Для решения данной ошибки необходимо при установке личного сертификата выбирать правильный контейнер закрытых ключей. Для этого можете воспользоваться функционалом оснастки КриптоПРО CSP, используя функцию «установить личный сертификат» во вкладке «сервис», которая в своем составе имеет возможность, путем проставления галочки, автоматического поиска соответствия между контейнером закрытых ключей (значением закрытого ключа) и значением открытого ключа, содержащегося в сертификате пользователя.

4. Ошибка «Сервер отказал в доступе пользователю не найден корневой сертификат»

Для решения данной ошибки необходимо:

· проверить издателя сертификата, который используется для установления соединения (сертификат для Континент-АП имеет в качестве издателя Корневой сертификат сервера доступа «ЦС СД Интернет», «4800-sd-01.roskazna.ru» или «4800-sd-02roskazna.ru»);

· проверить наличие в хранилище «Доверенные корневые центры сертификации» локального хранилища сертификатов системы сертификата Корневой сертификат сервера доступа «ЦС СД Интернет», «4800-sd-01.roskazna.ru» или «4800-sd-02roskazna.ru», в случае его отсутствия необходимо заново произвести установку сертификата пользователя.

5. Ошибка «Сервер доступа отказал пользователю в подключении. Причина отказа: Неизвестный клиент»

Для решения данной ошибки необходимо проверить правильность, указанных в Континент-АП, адресов серверов доступа. В УФК по Московской области используются следующие адреса серверов доступа:

4800-sd-01.roskazna.ru или 4800-sd-02.roskazna.ru.

Если в процессе подключения к одному из серверов доступа возникает подобная ошибка, необходимо произвести подключение на другой сервер доступа. В случае, если описанное выше не помогает решить проблему, необходимо позвонить в Управление Федерального казначейства по Московской области (по месту получения сертификата, необходимые контактные данные опубликованы на сайте mo.roskazna.ru в разделе ГИС > Удостоверяющий центр > Континент АП > Контакты).

6. Если после установки Континент-АП произошла потеря интернет соединения необходимо в настройках сетевого адаптера, который используется для выхода в интернет, снять галочку в пункте «Continent 3 MSE Filter».

7. Ошибка «Сервер отказал в доступе пользователю. Причина отказа: многократный вход пользователя запрещен»

Для решения данной ошибки необходимо обратится в УФК по Московской области по месту получения сертификата, необходимые контактные данные опубликованы на сайте mo.roskazna.ru в разделе ГИС > Удостоверяющий центр > Континент АП > Контакты).

8. Ошибка 721 либо 628

· Проверить, работает ли подключение к интернету на АРМ.

· Отключить МСЭ, брандмауэр либо другое ПО которое может блокировать служебные порты/протоколы Континент-АП.

· Исключить проблему в канале провайдера, попробовать использовать другого, к примеру, через 3-g модем. Если с другим провайдером работает – нужно обратиться к своему с запросом на открытие служебных портовпротоколов.

Оцените статью
Мой сертификат
Добавить комментарий