SQL-Ex blog

Решение

Цепочка владения - отличный способ предотвратить непосредственный доступ к базовым таблицам. Если вы не знакомы с цепочками владения, когда в SQL Server один объект ссылается на другой объект, и оба объекта имеют одного и того же владельца, то сервер будет следить только за безопасностью первого объекта. Например, если хранимая процедура ссылается на таблицу, SQL Server будет проверять безопасность только хранимой процедуры, а не таблицы, если оба объекта имеют одного владельца.



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

Представления SQL Server - сокрытие столбца с конфиденциальными данными

Возьмем следующий код T-SQL:

CREATE DATABASE TestDB;

GO 

USE TestDB;

GO

CREATE SCHEMA HR;

GO 

CREATE TABLE HR.Employee

(

   EmployeeID INT,

   GivenName VARCHAR(50),

   Surname VARCHAR(50),

   SSN CHAR(9)

);

GO 

У нас есть таблица с очевидно конфиденциальным столбцом SSN. Когда вы проектируете решения, подобные столбцы никогда не должны храниться незашифрованными. Имеется много способов вне и внутри SQL Server надлежащим образом зашифровать данные такого типа. Однако давайте предположим, что вы работаете с существующей системой и тот факт, что HR-стажеры имели возможность выполнять отчеты и запрашивать номера социального страхования (SSN), был отмечен как результат аудита. Как бы вы могли устранить это без нарушения работоспособности приложений, которые используют соответствующую таблицу? Мы можем использовать представление.

CREATE VIEW HR.LookupEmployee

AS

SELECT 

   EmployeeID, GivenName, Surname

FROM HR.Employee;

GO

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

USE TestDB;

GO 

CREATE ROLE HumanResourcesAnalyst;

GO 

GRANT SELECT ON HR.LookupEmployee TO HumanResourcesAnalyst;

GO 

CREATE USER JaneDoe WITHOUT LOGIN;

GO 

ALTER ROLE HumanResourcesAnalyst

ADD MEMBER JaneDoe;

GO 

А теперь выполним несколько тестов. Сначала для представления.

USE TestDB;

GO 

-- Это будет работать

-- JaneDoe имеет разрешение на SELECT к представлению

-- Она не имеет разрешения на SELECT к таблице

-- Цепочка владения делает это возможным

EXECUTE AS USER = 'JaneDoe';

GO 

SELECT * FROM HR.LookupEmployee;

GO 

REVERT;

GO 

Запрос выполняется без ошибок.



А теперь таблица.

-- Это не работает

-- Поскольку JaneDoe не имеет разрешения на SELECT,

-- она не может обращаться к таблице таким образом

EXECUTE AS USER = 'JaneDoe';

GO 

SELECT * FROM HR.Employee;

GO 

REVERT;

GO

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

Изменение данных посредством хранимых процедур SQL Server

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

CREATE PROC HR.InsertNewEmployee

   @EmployeeID INT,

   @GivenName VARCHAR(50),

   @Surname VARCHAR(50),

   @SSN CHAR(9)

AS

BEGIN

   INSERT INTO HR.Employee

   ( EmployeeID, GivenName, Surname, SSN )

   VALUES

   ( @EmployeeID, @GivenName, @Surname, @SSN );

END;

GO 

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

CREATE ROLE HumanResourcesRecruiter;

GO 

GRANT EXECUTE ON SCHEMA::[HR] TO HumanResourcesRecruiter;

GO 

CREATE USER JohnSmith WITHOUT LOGIN;

GO 

ALTER ROLE HumanResourcesRecruiter

ADD MEMBER JohnSmith;

GO

У нас есть хранимая процедура и настроена безопасность; теперь давайте протестируем JohnSmith. John Smith не должен иметь возможность вставки непосредственно в таблицу. Мы не давали никаких разрешений, поэтому по умолчанию он их не имеет.

 -- Это завершится неудачей, т.к. JohnSmith не может

-- выполнять вставку непосредственно в таблицу.

EXECUTE AS USER = 'JohnSmith';

GO 

   INSERT INTO HR.Employee

   ( EmployeeID, GivenName, Surname, SSN )

   VALUES

   ( 557, 'Michael', 'Cooper', '3343343344' );

GO 

REVERT;

GO

Выполняя оператор как JohnSmith, мы получим ожидаемое сообщение об ошибке:



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

 -- Это выполнится успешно, т.к. JohnSmith может выполнять

-- любую хранимую процедуру в схеме HR. Работает цепочка владения

-- допуская вставку.

EXECUTE AS USER = 'JohnSmith';

GO 

EXEC HR.InsertNewEmployee 

      @EmployeeID = 557, 

      @GivenName = 'Michael', 

      @Surname = 'Cooper', 

      @SSN = '3343343344';

GO 

REVERT;

GO

Мы не получаем сообщения об ошибке. На самом деле SQL Server сообщает нам, что одна строка была вставлена.



Давайте проверим, что данные были вставлены:

 -- Проверка вставки

SELECT EmployeeID, GivenName, Surname, SSN 

FROM HR.Employee;

GO 

Как и ожидалось, строка была вставлена в таблицу.

Мощь цепочек владения

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

Помимо этих двух примеров, представления могут использоваться для манипуляции данными. Однако, поскольку представление не ссылается на все столбцы базовых таблиц, оно не может изменять данные в отсутствующих столбцах. В случае INSERT это означает, что эти столбцы должны иметь значения по умолчанию или допускать NULL-значения. Хранимые процедуры могут использоваться для запроса данных аналогично представлениям. Большая разница заключается в том, что хранимые процедуры могут выполнять другие операции, кроме простого оператора SELECT. Поэтому, если вы хотите тщательно контролировать доступ к данным, цепочка владения может существенно помочь в этом начинании.