Реализуйте безопасность на уровне строк с контекстом сеанса в конструкторе API данных.

Используйте функцию контекста сеанса SQL для реализации безопасности на уровне строк в построителе API данных.

Предпосылки

• Существующий СЕРВЕР SQL и база данных.
• CLI инструмента построения API данных. Установка интерфейса командной строки

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

Создайте таблицу с вымышленными данными для использования в этом примере.

1. Подключитесь к базе данных SQL с помощью предпочтительного клиента или средства.

2. Создайте таблицу с именем Revenues с колонками id, category, revenue и accessible_role.

   DROP TABLE IF EXISTS dbo.Revenues;
   
   CREATE TABLE dbo.Revenues(
       id int PRIMARY KEY,  
       category varchar(max) NOT NULL,  
       revenue int,  
       accessible_role varchar(max) NOT NULL  
   );
   GO
   

3. Вставьте четыре примера строк в таблицу Revenues .

   INSERT INTO dbo.Revenues VALUES
       (1, 'Book', 5000, 'Oscar'),  
       (2, 'Comics', 10000, 'Oscar'),  
       (3, 'Journals', 20000, 'Hannah'),  
       (4, 'Series', 40000, 'Hannah')
   GO
   

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

1. Протестируйте данные с помощью простого SELECT * запроса.

   SELECT * FROM dbo.Revenues
   

2. Создайте функцию с именем RevenuesPredicate. Эта функция фильтрует результаты на основе текущего контекста сеанса.

   CREATE FUNCTION dbo.RevenuesPredicate(@accessible_role varchar(max))
   RETURNS TABLE
   WITH SCHEMABINDING
   AS RETURN SELECT 1 AS fn_securitypredicate_result
   WHERE @accessible_role = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   

3. Создайте политику безопасности с именем RevenuesSecurityPolicy с помощью функции.

   CREATE SECURITY POLICY dbo.RevenuesSecurityPolicy
   ADD FILTER PREDICATE dbo.RevenuesPredicate(accessible_role)
   ON dbo.Revenues;
   

(Необязательно) Создание хранимой процедуры

В этом разделе показан простой шаблон "hello world" для использования значений контекста сеанса непосредственно в T-SQL.

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

   CREATE OR ALTER PROCEDURE dbo.GetRevenuesForCurrentRole
   AS
   BEGIN
       SET NOCOUNT ON;
   
       DECLARE @role varchar(max) = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   
       SELECT id, category, revenue, accessible_role
       FROM dbo.Revenues
       WHERE accessible_role = @role;
   END
   GO
   

Инструмент запуска

Запустите инструмент построения данных (DAB), чтобы создать файл конфигурации и одну сущность.

1. Создайте новую конфигурацию, установив для --set-session-context значение true.

   dab init \
       --database-type mssql \
       --connection-string "<sql-connection-string>" \
       --set-session-context true \
       --auth.provider Simulator
   

   Если контекст сеанса включен для SQL Server, построитель API данных отправляет утверждения пользователей, прошедшие проверку подлинности, в SQL путем вызова sp_set_session_context (например, roles). Включение контекста сеанса также отключает кэширование ответов для этого источника данных.

1. Добавьте новую сущность с именем revenue для dbo.Revenues таблицы.

   dab add revenue \
       --source "dbo.Revenues" \
       --permissions "Authenticated:read"
   

2. Запустите средство построителя API данных.

   dab start
   

3. Запрос конечной точки без указания эффективной роли. Обратите внимание, что данные не возвращаются, так как:

   • По умолчанию эффективная роль — это Authenticated.
   • Нет строк, имеющих accessible_role = 'Authenticated'.
   • Политика безопасности фильтрует результаты, если роль не соответствует.

   curl http://localhost:5000/api/revenue
   

4. Выполните запрос к конечной точке, установив эффективную роль на Oscar. Обратите внимание, что отфильтрованные результаты включают только Oscar строки.

   curl -H "X-MS-API-ROLE: Oscar" http://localhost:5000/api/revenue
   

5. Повторно используйте роль Hannah.

   curl -H "X-MS-API-ROLE: Hannah" http://localhost:5000/api/revenue
   

Тестирование с помощью GraphQL

Контекст сеанса также работает с запросами GraphQL.

query {
    revenues {
        items {
            id
            category
            revenue
            accessible_role
        }
    }
}

Передайте заголовок роли:

curl -X POST http://localhost:5000/graphql \
    -H "Content-Type: application/json" \
    -H "X-MS-API-ROLE: Oscar" \
    -d '{"query": "{ revenues { items { id category revenue accessible_role } } }"}'

Что построитель API данных отправляет в SQL Server

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

EXEC sp_set_session_context 'roles', 'Oscar', @read_only = 0;
-- Then executes your query
SELECT * FROM dbo.Revenues;

Все утверждения пользователей, прошедшие проверку подлинности, отправляются в виде пар "ключ-значение". Распространенные утверждения включают roles, sub или oid, и любые пользовательские утверждения от поставщика удостоверений.

Тестирование в SQL

Проверьте фильтр и предикат в SQL напрямую, чтобы убедиться, что он работает.

1. Снова подключитесь к СЕРВЕРУ SQL Server с помощью предпочтительного клиента или средства.

2. Запустите sp_set_session_context, чтобы вручную задать требование контекста сеанса roles статическим значением Oscar.

   EXEC sp_set_session_context 'roles', 'Oscar';
   

3. Выполните типичный SELECT * запрос. Обратите внимание, что результаты автоматически фильтруются с помощью предиката.

   SELECT * FROM dbo.Revenues;  
   

4. (Необязательно) Запросите таблицу с помощью хранимой процедуры.

   EXEC dbo.GetRevenuesForCurrentRole;
   

Очистите ресурсы

Если вы хотите удалить примеры объектов, выполните следующую команду:

DROP SECURITY POLICY IF EXISTS dbo.RevenuesSecurityPolicy;
DROP FUNCTION IF EXISTS dbo.RevenuesPredicate;
DROP PROCEDURE IF EXISTS dbo.GetRevenuesForCurrentRole;
DROP TABLE IF EXISTS dbo.Revenues;

Устранение неполадок

• Нет возвращаемых результатов: убедитесь, что политика безопасности активна (SELECT * FROM sys.security_policies), проверьте значение контекста сеанса (SELECT SESSION_CONTEXT(N'roles')) и убедитесь, что --set-session-context true задано в конфигурации построителя данных API.
• Все возвращенные строки: убедитесь, что политика безопасности не отключена (WITH STATE = OFF) и что предикат возвращается 1 только для авторизованных строк.
• Проблемы с производительностью: индексируйте столбец предиката (accessible_role) и рассмотрите возможность временного отключения политики для изоляции эффекта производительности.

Связанный контент

• Функции, связанные с базой данных
• Использование сред