Главная » Microsoft SQL Server, Базы данных » Обеспечение защиты данных на уровне строк

0

СУБД SQL Server идеально подходит для обеспечения защиты информации по вертикали и в то же время лишена возможности динамически обеспечивать ее на уровне строк. Представления с параметром WITH CHECK OPTION могут обеспечить встроенную в программный код защиту. Однако реализация базы данных на таких представлениях приведет к уменьшению производительности и усложнению поддержки базы.

Базы данных промышленного масштаба часто содержат в строках конфиденциальные данные. Можно сформулировать четыре основных правила обеспечения их защиты.

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

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

?               Корпоративная торговая система должна допускать покупателей пиломатериалов к информации только о пиломатериалах, а покупателей компьютеров — к информации о компьютерах.

?               Любое отделение банка должно быть способно прочитать файл данных любого клиента, но редактировать имеет право только файлы своих клиентов.

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

?               Таблица системы безопасности может содержать список пользователей и их подразделений, а также их права на чтение и запись информации.

?               Процедура системы безопасности проверяет права пользователей относительно запрашиваемых данных и возвращает разрешение или запрет.

?               Процедура перехода между записями проверяет разрешение на возврат данных.

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

Чтобы продемонстрировать описанную конструкцию, мы реализуем ее на примере учебной базы данных OBXKites. Каждый сотрудник из таблицы Contact получит разрешения на чтение, запись или администрирование данных о складских запасах и продажах соответствующего региона. В этой схеме безопасность данных может быть обеспечена с помощью хранимой процедуры, функции, учетной записи NT или триггера.

Таблица безопасности

Таблица безопасности (Security) выступает в роли ассоциативной между таблицами Contact и Location. В ней определены следующие уровни доступа:

?               0 или отсутствие записи — запрет доступа;

?               1 — разрешение чтения;

?               2 — разрешение записи;

?               3 — разрешение администрирования.

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

Создание таблицы

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

USE OBXKites

CREATE TABLE dbo.Security (

SecuritylD UniqueIdentifier NOT NULL Primary Key Nonclustered,

ContactID Uniqueldentifier NOT NULL REFERENCES Contact ON DELETE CASCADE,

LocationID Uniqueldentifier NOT NULL REFERENCES Location ON DELETE CASCADE,

SecurityLevel INT NOT NULL DEFAULT 0 )

Три следующие команды добавляют в таблицу Security ограничения:

CREATE TRIGGER ContactID_RI ON dbo.Security AFTER INSERT, UPDATE AS

SET NoCount ON IF EXISTS(SELECT *

FROM Inserted LEFT OUTER JOIN dbo.Contact

ON Inserted.ContactID = Contact.ContactID WHERE Contact.ContactID IS NULL OR Contact.IsEmployee = 0 )

BEGIN

RAISERROR

(‘Ограничение внешнего ключа: Security.ContactID’, 16, 1)

ROLLBACK TRANSACTION RETURN END

ALTER TABLE dbo.Security

ADD CONSTRAINT ValidSecurityCode CHECK (SecurityLevel IN (0,1,2,3))

ALTER TABLE dbo.Security

ADD CONSTRAINT ContactLocation UNIQUE (ContactID, LocationID)

Так как база данных OBXKites в качестве первичных ключей использует глобаль- В нь,е идентификаторы GUID, для ввода данных лучше использовать хранимые ^^ХСети процедуры. Сценарий, относящийся к настоящей главе (ch24-Advanced T-SQL 7               Solutions . sql) и находящийся на сайте книги (www.SQLServerBible.com),

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

Безопасность переходов

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

CREATE PROCEDURE pSecurity_Fetch(

@LocationCode CHAR(15) = NULL,

@ContactCode CHAR(15) = NULL )

AS

SET NoCount ON

SELECT Contact.ContactCode,

Location.LocationCode,

SecurityLevel FROM dbo.Security JOIN dbo.Contact ON Security.ContactID = Contact.ContactID JOIN dbo.Location ON Security.LocationID = Location.LocationID WHERE (Location.LocationCode = @LocationCode OR @LocationCode IS NULL)

AND (Contact.ContactCode = @ContactCode OR @ContactCode IS NULL)

Назначение уровней безопасности

Разрешения системы безопасности устанавливаются путем занесения данных в таблицу Security, являющуюся связующей между таблицами контактов и регионов. Продолжая тему серверного программного кода, приведем хранимую процедуру, назначающую уровни системы безопасности парам “сотрудник-регион”. В ней вы не найдете для себя ничего нового. Она принимает на входе коды контакта и региона, преобразует их в идентификаторы GUID, а затем выполняет инструкцию вставки:

CREATE PROCEDURE pSecurity_Assign(

@ContactCode VARCHAR(15),

@LocationCode VARCHAR(15),

@SecurityLevel INT )

AS

SET NOCOUNT ON DECLARE

@ContactID UNIQUEIDENTIFIER,

@LocationID UNIQUEIDENTIFIER — Получение идентификатора контакта SELECT @ContactID = ContactID FROM dbo.Contact

WHERE ContactCode = @ContactCode IF @@ERROR <> 0 RETURN -100 IF @ContactID IS NULL BEGIN

RAISERROR

(‘Контакт: ”%s” не найден’, 15,1,@ContactCode)

RETURN -100 END

– Получение идентификатора региона SELECT @LocationID = LocationID FROM dbo.Location

WHERE LocationCode = @LocationCode IF @@ERROR <> 0 RETURN -100 IF @LocationID IS NULL BEGIN

RAISERROR

(‘Регион: ”%s” не найден’, 15,1,@LocationCode)

RETURN -100 END — Вставка

INSERT dbo.Security (ContactID,LocationID, SecurityLevel)

VALUES (@ContactID, @LocationID, @SecurityLevel)

IF @@ERROR <> 0 RETURN -100 RETURN

После создания хранимых процедур pSecurity_Fetch и pSecurity_Assign мы добавим в следующем пакете тестовые данные. Первые два запроса возвращают данные, которые будут использованы для тестирования:

SELECT ContactCode FROM Contact WHERE IsEmployee = 1

Будет получен следующий результат:

ContactCode

118

120

119

Следующий запрос возвращает коды регионов:

SELECT LocationCode FROM Location Результат выполнения запроса:

LocationCode

CH

Clt

ElC

JR

KH

W

На основе этих данных следующие четыре вызова хранимых процедур назначат сотрудникам уровни доступа к данным:

EXEС pSecurity_Assign @ContactCode = 118,

@LocationCode = СН,

@SecurityLevel = 3

EXEC pSecurity_Assign @ContactCode = 118,

@LocationCode = Clt,

@SecurityLevel = 2

EXEC pSecurity_Assign @ContactCode = 118,

@LocationCode = Elc,

@SecurityLevel = 1

EXEC pSecurity_Assign @ContactCode = 12 0,

@LocationCode = W,

@SecurityLevel = 2

Следующие две команды тестируют вставленные данные с помощью процедуры pSecurity_ Fetch. В первой процедуре тестируются установки системы безопасности для региона W: EXEC pSecurity_Fetch @LocationCode = ‘W1 Будет получен следующий результат:

ContactCode LocationCode SecurityLevel

120       W                                   3

Следующий пакет тестирует установки для сотрудника Dave Bos ton с кодом 118:

EXEC pSecurity_Fetch @ContactCode = ‘118’

Будет получен следующий результат:

ContactCode LocationCode SecurityLevel

118          Clt                        2

118          CH                       3

118          E1C                      1

Схема системы безопасности на уровне строк использует ряд ограничений; следующая команда их тестирует.

Тест на уникальность:

EXEС pSecurity_Assign @ContactCode = 120,

@LocationCode = W,

@SecurityLevel = 2

Результат теста:

Server: Msg 2627, Level 14, State 2,

Procedure pSecurity_Assign, Line 3 5

Violation of UNIQUE KEY constraint ‘ContactLocation1.

Cannot insert duplicate key in object ’Security’.

The statement has been terminated.

Тест на ограничение проверки корректности кода доступа:

ЕХЕС pSecurity_Assign @ContactCode = 118,

@LocationCode = W,

@SecurityLevel = 5

Результат теста:

Server: Msg 547, Level 16, State 1,

Procedure pSecurity_Assign, Line 35

INSERT statement conflicted with COLUMN CHECK constraint 1ValidSecurityCode’. The conflict occurred in database ‘OBXKites’, table ‘Security’, column ‘SecurityLevel’.

The statement has been terminated.

Тест триггера проверки правила бизнес-логики на доступность кода доступа только для сотрудников:

Select ContactCode FROM Contact WHERE IsEmployee = 0 EXEC pSecurity_Assign @ContactCode = 102,

@LocationCode = W,

@SecurityLevel = 3

Результат теста:

Ограничение внешнего ключа: Security.ContactID

Тестирование ограничения внешнего ключа контакта, который первый проверяется в хранимой процедуре:

ЕХЕС pSecurity_Assign @ContactCode = 999,

@LocationCode = W,

@SecurityLevel = 3

Результат теста:

Server: Msg 50000, Level 15, State 1, Procedure pSecurity_Assign, Line 19

Контакт: ‘999’ не найден

Тестирование ограничения внешнего ключа региона, используемого в хранимой процедуре: ЕХЕС pSecurity_Assign @ContactCode = 118,

@LocationCode = RDBMS,

@SecurityLevel = 3

Результат теста:

Server: Msg 50000, Level 15, State 1, Procedure pSecurity_Assign, Line 30

Регион: ‘RDBMS’ не найден

Обновление уровней доступа к данным

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

Следующая версия этой же процедуры проверяет, существует ли в таблице Security заданная комбинация кодов контакта и региона, а затем выполняет соответствующую операцию — обновление или вставку. С помощью одних и тех же параметров новой процедуры установки системы безопасности могут быть и созданы, и скорректированы:

ALTER PROCEDURE pSecurity_Assign(

@ContactCode CHAR(15),

@LocationCode CHAR(15),

@SecurityLevel INT )

AS

SET NOCOUNT ON DECLARE

@ContactID UNIQUEIDENTIFIER,

@LocationID UNIQUEIDENTIFIER — Получение идентификатора контакта SELECT @ContactID = ContactID FROM dbo.Contact

WHERE ContactCode = @ContactCode IF @ContactID IS NULL BEGIN

RAISERROR

(‘Контакт: 1‘%s’1 не найден1, 15,1,@ContactCode)

RETURN -100 END

– Получение идентификатора региона SELECT @LocationID = LocationID FROM dbo.Location

WHERE LocationCode = @LocationCode IF @LocationID IS NULL BEGIN

RAISERROR

(‘Регион: 1‘%s11 не найден’, 15,1,@LocationCode)

RETURN -100 END

– Что выполнять: вставку или обновление?

IF EXISTS(SELECT *

FROM dbo.Security

WHERE ContactID = @ContactID

AND LocationID = @LocationID)

– Обновление BEGIN

UPDATE dbo.Security

SET SecurityLevel = @SecurityLevel WHERE ContactID = @ContactID AND LocationID = @LocationID IF @@ERROR <> 0 RETURN -10 0 END — Вставка ELSE BEGIN

INSERT dbo.Security

(ContactID,LocationID, SecurityLevel)

VALUES (@ContactID, @LocationID, @SecurityLevel)

IF @@ERROR <> 0 RETURN -100 END RETURN

Следующий сценарий тестирует способность новой процедуры изменять уровни доступа для контакта с кодом 12 0 и региона W:

EXEС pSecurity_Assign @ContactCode = 12 0,

@LocationCode = W,

@SecurityLevel = 2 EXEC pSecurity_Fetch @ContactCode = 12 0

Будет получен следующий результат:

ContactCode LocationCode SecurityLevel

120     W                     2

В следующем сценарии в таблицу Security вставляется новая запись и тут же модифицируется, после чего результат проверяется:

EXEC pSecurity_Assign @ContactCode = 12 0,

@LocationCode = СН,

@SecurityLevel = 1 EXEC pSecurity_Assign @ContactCode = 120,

@LocationCode = W,

@SecurityLevel = 3 EXEC pSecurity_Fetch @ContactCode = 120

Будет получен следующий результат:

ContactCode LocationCode SecurityLevel

120     W                       3

120      CK                     1

Хранимая процедура проверки полномочий

Хранимая процедура проверю! прав доступа находится в центре системы безопасности строкового уровня. Она возвращает истинное или ложное значение в зависимости от наличия прав на затребованную операцию.

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

CREATE PROCEDURE p_SecurityCheck (

@ContactCode CHAR(15),

@LocationCode CHAR(15),

@SecurityLevel INT,

@Approved BIT OUTPUT )

AS

SET NoCount ON DECLARE @ActualLevel INT SELECT @ActualLevel = SecurityLevel FROM dbo.Security JOIN dbo.Contact

ON Security.ContactID = Contact.ContactID JOIN dbo.Location

ON Security.LocationID = Location.LocationID WHERE ContactCode = @ContactCode AND LocationCode = @LocationCode IF @ActualLevel IS NULL OR

@ActualLevel < @SecurityLevel OR

@ActualLevel = 0

SET @Approved = 0

ELSE

SET (©Approved = 1 RETURN 0

В следующем пакете вызывается хранимая процедура p_SecurityCheck и используется локальная переменная @0К для получения выходного параметра. При тестировании попробуйте использовать несколько разных параметров (чтобы узнать состав допустимых значений, воспользуйтесь процедурой pSecurity_Fetch). В следующем примере проверяется, имеет ли пользователь с кодом 118 административные привилегии на складе в городе Шарлотт:

DECLARE @ОК BIT EXEC p_SecurityCheck

@ContactCode = 118,

@LocationCode = Clt,

@SecurityLevel = 3,

(©Approved = @OK OUTPUT SELECT @OK

Результатом будет значение нуль — признак отсутствия полномочий.

Функция проверки полномочий

В функции проверки полномочий реализована та же логика, что и в аналогичной хранимой процедуре. Основным преимуществом функции является то, что ее можно использовать непосредственно в выражении if, не создавая локальную переменную для хранения выходного параметра. На входе функция принимает те же параметры, что и хранимая процедура; отличается только способ возврата результата — с помощью оператора RETURN, а не выходного параметра.

CREATE FUNCTION dbo.fSecurityCheck (

(©ContactCode CHAR (15),

(©LocationCode CHAR (15),

(©SecurityLevel INT)

RETURNS BIT BEGIN

DECLARE (©ActualLevel INT,

(©Approved BIT SELECT (©ActualLevel = SecurityLevel FROM dbo.Security JOIN dbo.Contact

ON Security.ContactID = Contact.ContactID JOIN dbo.Location

ON Security.LocationID = Location.LocationID WHERE ContactCode = (©ContactCode

AND LocationCode = (©LocationCode IF (©ActualLevel IS NULL

OR (©ActualLevel < (©SecurityLevel OR (©ActualLevel = 0 SET (©Approved = 0

ELSE

SET (©Approved = 1 RETURN (©Approved END

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

– Проверка в теле процедуры IF dbo.fSecurityCheck( 118, ‘Clt’, 3) =0 BEGIN

RAISERROR(1 Недостаточные привилегии1, 16,1)

ROLLBACK TRANSACTION RETURN -100 END

Использование учетной записи NT

Некоторые приложения создаются так, чтобы пользователь выполнял в них регистрацию, а затем эти регистрационные данные передавались в процедуры. В то же время, если экземпляр SQL Server использует аутентификацию NT, процедуры системы безопасности могут использовать и ее.

Вместо того чтобы запрашивать код контакта в качестве параметра, процедура или функция может идентифицировать текущего пользователя с помощью функции suser_sname О и извлекать регистрационные данные NT. Регистрационное имя (доменное или локальное) может быть добавлено в таблицу Contact. В качестве альтернативы может быть создана вторичная таблица, хранящая для каждого пользователя множество регистрационных записей. Некоторые глобальные сети требуют регистрации в разных регионах под разными доменными именами, так что создание отдельной таблицы ContactLogin является отличной альтернативой.

Следующая функция модифицирована для извлечения регистрационных данных NT конкретного пользователя, содержащихся в таблице ContactLogin:

SELECT suser_sname()

Результат получается следующий:

NOLI\Paul

Следующая инструкция создает вторичную таблицу, хранящую регистрационные данные:

CREATE TABLE dbo.ContactLogin(

ContactLogin UNIQUEIDENTIFIER

PRIMARY KEY NONCLUSTERED DEFAULT Newid(),

ContactID Uniqueldentifier NOT NULL

REFERENCES dbo.Contact ON DELETE CASCADE,

NTLogin VARCHAR(10 0) )

Когда таблица создана, всего одна инструкция INSERT может заполнить в ней данными одну строку. После этого мы проверим факт вставки данных.

INSERT CONTACTLOGIN (ContactID, NTLogin)

SELECT ContactID, ‘NOLl\Paul’

FROM dbo.Contact WHERE ContactCode = 118

SELECT ContactCode, NTLogin FROM dbo.Contact JOIN ContactLogin

ON Contact.ContactID = ContactLogin.ContactID

Будет получен следующий результат:

ContactCode NTLogin

118       Paul/NOLI

Функция проверки полномочий была модифицирована так, чтобы использовать таблицу ContactLogin и возвращать список, соответствующий одной регистрационной записи NT. Так как код контакта больше не требуется, в инструкции SELECT отсутствует ссылка на таблицу Contact, и таблица Security объединяется непосредственно с таблицей ContactLogin:

CREATE FUNCTION dbo.fSecurityCheckNT (

@LocationCode CHAR(15),

©SecurityLevel INT)

RETURNS BIT BEGIN

DECLARE @ActualLevel INT,

@Approved BIT

SELECT @ActualLevel = SecurityLevel FROM dbo.Security JOIN dbo.Location

ON Security.LocationID = Location.LocationID JOIN dbo.ContactLogin

ON Security.ContactID = ContactLogin.ContactID WHERE NTLogin = suser_sname()

AND LocationCode = @LocationCode

IF @ActualLevel IS NULL

OR @ActualLevel < @SecurityLevel OR @ActualLevel = 0 SET @Approved = 0

ELSE

SET ©Approved = 1

RETURN @Approved END

Тестирование новой функции ничем не отличается от тестирования предыдущей, однако на этот раз в функцию не передается код контакта — регистрационные данные извлекаются непосредственно из операционной системы:

IF dbo.fSecurityCheckNT(1Clt1, 3) =0

BEGIN

RAISERROR(1 Недостаточные привилегии’, 16,1)

ROLLBACK TRANSACTION RETURN -100

END

Данная функция не вернула ошибку, и я смог завершить выполнение процедуры.

Триггер проверки полномочий

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

Следующий триггер аналогичен описанной ранее функции проверки полномочий. Отличие состоит в том, что он допускает наличие множества заказов из потенциально большого количества регионов. Существующие в нем объединения должны сравнивать строки таблицы [Order] и их регионы с допустимыми для данных регионов полномочиями пользователей. Объединение может непосредственно связывать таблицу ContactLogin с таблицей Security. Так как это триггер вставки и обновления, пользователь с любым уровнем доступа ниже второго для любого заказа будет отвергаться с генерацией ошибки системы безопасности. При этом инструкция ROLLBACK TRANSACTION будет откатывать исходную инструкцию DML, вызвавшую триггер:

CREATE TRIGGER OrderSecurity ON [Order]

AFTER INSERT, UPDATE AS

IF EXISTS (

SELECT *

FROM dbo.Security

JOIN dbo.ContactLogin

ON Security.ContactID = ContactLogin.ContactID JOIN Inserted

ON Inserted.LocationID = Security.LocationID WHERE NTLogin = suser_sname()

AND SecurityLevel < 2 )

BEGIN

RAISERROR(1 Недостаточно полномочий1, 16,1)

ROLLBACK TRANSACTION END

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии