Забезпечення захисту даних на рівні рядків

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

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

■ Дані про складський вартості товарів, графіку їх випуску та утриманні в них різних матеріалів не повинні виходити за межі відповідного виробничого відділу У той же час програми MRP зазвичай використовують системи відстеження цих даних в межах всього підприємства (всіх його підрозділів і філій)

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

■ Корпоративна торгова система повинна допускати покупців пиломатеріалів до інформації тільки про пиломатеріали, а покупців компютерів – до інформації про компютери

■ Будь-яке відділення банку має бути здатне прочитати файл даних будь-якого клієнта, але редагувати має право тільки файли своїх клієнтів

Вирішення цих та інших завдань захисту інформації на рівні рядка передбачає створення серверного програмного коду, і на те є ряд причин

■ Таблиця системи безпеки може містити список користувачів і їх підрозділів, а також їх права на читання і запис інформації

■ Процедура системи безпеки перевіряє права користувачів стосовно запитуваних даних і повертає дозвіл або заборону

■ Процедура переходу між записами перевіряє дозвіл на повернення даних

■ Тригери викликають процедуру системи безпеки для перевірки прав користувача на виконання конкретної інструкції DML на запитуваних рядках

Щоб продемонструвати описану конструкцію, ми реалізуємо її на прикладі навчальної бази даних OBXKites Кожен співробітник з таблиці Contact отримає дозволу на читання, запис або адміністрування даних про складські запаси і продажах відповідного регіону У цій схемі безпеку даних може бути забезпечена за допомогою збереженої процедури, функції, облікового запису NT або тригера

Таблиця безпеки

Таблиця безпеки (Security) виступає в ролі асоціативної між таблицями Contact і Location У ній визначено такі рівні доступу:

■ 0 або відсутність запису – заборона доступу

■ 1 – дозвіл читання

■ 2 – дозвіл запису

■ 3 – дозвіл адміністрування

Як альтернативу можна було б використовувати трехбітовий стовпець, однак привілеї мають властивість накопичення, тому більше підходить цілочисельний стовпець

Створення таблиці

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

USE OBXKites

CREATE TABLE dboSecurity (

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 dboSecurity AFTER INSERT, UPDATE AS

SET NoCount ON IF EXISTS(SELECT *

FROM Inserted LEFT OUTER JOIN dboContact

ON InsertedContactID = ContactContactID WHERE ContactContactID IS NULL OR ContactIsEmployee = 0 )

BEGIN

RAISERROR

(Обмеження зовнішнього ключа: SecurityContactID, 16, 1)

ROLLBACK TRANSACTION RETURN END

ALTER TABLE dboSecurity

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

ALTER TABLE dboSecurity

ADD CONSTRAINT ContactLocation UNIQUE (ContactID, LocationID)

Так як база даних OBXKites в якості первинних ключів використовує глобаль-В нь, е ідентифікатори GUID, для введення даних краще використовувати збережені ^ ^ ХСеті процедури Сценарій, що відноситься до цієї чолі (ch24-Advanced T-SQL 7Solutions sql) і знаходиться на сайті книги (wwwSQLServerBiblecom),

містить збережені процедури, аналогічні наведеним у книзі, і призначені для введення даних в таблицю безпеки У цьому сценарії також містяться і навчальні дані

Безпека переходів

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

CREATE PROCEDURE pSecurity_Fetch(

@LocationCode CHAR(15) = NULL,

@ContactCode CHAR(15) = NULL )

AS

SET NoCount ON

SELECT ContactContactCode,

LocationLocationCode,

SecurityLevel FROM dboSecurity JOIN dboContact ON SecurityContactID = ContactContactID JOIN dboLocation ON SecurityLocationID = LocationLocationID WHERE (LocationLocationCode = @LocationCode OR @LocationCode IS NULL)

AND (ContactContactCode = @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 dboContact

WHERE ContactCode = @ContactCode IF @@ERROR &lt&gt 0 RETURN -100 IF @ContactID IS NULL BEGIN

RAISERROR

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

RETURN -100 END

– Отримання ідентифікатора регіону SELECT @ LocationID = LocationID FROM dboLocation

WHERE LocationCode = @LocationCode IF @@ERROR &lt&gt 0 RETURN -100 IF @LocationID IS NULL BEGIN

RAISERROR

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

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

INSERT dboSecurity (ContactID,LocationID, SecurityLevel)

VALUES (@ContactID, @LocationID, @SecurityLevel)

IF @@ERROR &lt&gt 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

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

Обмеження зовнішнього ключа: SecurityContactID

Тестування обмеження зовнішнього ключа контакту, який першим перевіряється в збереженій процедурі:

Ехес 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 dboContact

WHERE ContactCode = @ContactCode IF @ContactID IS NULL BEGIN

RAISERROR

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

RETURN -100 END

– Отримання ідентифікатора регіону SELECT @ LocationID = LocationID FROM dboLocation

WHERE LocationCode = @LocationCode IF @LocationID IS NULL BEGIN

RAISERROR

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

RETURN -100 END

– Що виконувати: вставку або оновлення

IF EXISTS(SELECT *

FROM dboSecurity

WHERE ContactID = @ContactID

AND LocationID = @LocationID)

– Оновлення BEGIN

UPDATE dboSecurity

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

INSERT dboSecurity

(ContactID,LocationID, SecurityLevel)

VALUES (@ContactID, @LocationID, @SecurityLevel)

IF @@ERROR &lt&gt 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&nbsp&nbsp&nbsp&nbsp&nbsp 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 dboSecurity JOIN dboContact

ON SecurityContactID = ContactContactID JOIN dboLocation

ON SecurityLocationID = LocationLocationID WHERE ContactCode = @ContactCode AND LocationCode = @LocationCode IF @ActualLevel IS NULL OR

@ActualLevel &lt @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 dbofSecurityCheck (

(©ContactCode CHAR (15),

(©LocationCode CHAR (15),

(©SecurityLevel INT)

RETURNS BIT BEGIN

DECLARE (©ActualLevel INT,

(©Approved BIT SELECT (©ActualLevel = SecurityLevel FROM dboSecurity JOIN dboContact

ON SecurityContactID = ContactContactID JOIN dboLocation

ON SecurityLocationID = LocationLocationID WHERE ContactCode = (©ContactCode

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

OR (©ActualLevel &lt (©SecurityLevel OR (©ActualLevel = 0 SET (©Approved = 0

ELSE

SET (©Approved = 1 RETURN (©Approved END

У наступному пакеті продемонстрований виклик функції для перевірки прав доступу в тілі збереженої процедури Якщо функція повертає нуль, значить, у користувача немає достатніх прав, і виконання процедури припиняється:

– Перевірка в тілі процедури IF dbofSecurityCheck (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 dboContactLogin(

ContactLogin UNIQUEIDENTIFIER

PRIMARY KEY NONCLUSTERED DEFAULT Newid(),

ContactID Uniqueldentifier NOT NULL

REFERENCES dboContact ON DELETE CASCADE,

NTLogin VARCHAR(10 0) )

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

INSERT CONTACTLOGIN (ContactID, NTLogin)

SELECT ContactID, NOLl\Paul

FROM dboContact WHERE ContactCode = 118

SELECT ContactCode, NTLogin FROM dboContact JOIN ContactLogin

ON ContactContactID = ContactLoginContactID

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

ContactCode NTLogin

118       Paul/NOLI

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

CREATE FUNCTION dbofSecurityCheckNT (

@LocationCode CHAR(15),

©SecurityLevel INT)

RETURNS BIT BEGIN

DECLARE @ActualLevel INT,

@Approved BIT

SELECT @ActualLevel = SecurityLevel FROM dboSecurity JOIN dboLocation

ON SecurityLocationID = LocationLocationID JOIN dboContactLogin

ON SecurityContactID = ContactLoginContactID WHERE NTLogin = suser_sname()

AND LocationCode = @LocationCode

IF @ActualLevel IS NULL

OR @ActualLevel &lt @SecurityLevel OR @ActualLevel = 0 SET @Approved = 0

ELSE

SET ©Approved = 1

RETURN @Approved END

Тестування нової функції нічим не відрізняється від тестування попередньої, проте цього разу у функцію не передається код контакту – реєстраційні дані витягуються безпосередньо з операційної системи:

IF dbofSecurityCheckNT(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 dboSecurity

JOIN dboContactLogin

ON SecurityContactID = ContactLoginContactID JOIN Inserted

ON InsertedLocationID = SecurityLocationID WHERE NTLogin = suser_sname()

AND SecurityLevel &lt 2 )

BEGIN

RAISERROR(1 Недостатньо повноважень1, 16,1)

ROLLBACK TRANSACTION END

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*