SQL, Защита данных

Автоматизация доступов к БД MS SQL по заявкам HPE Service Manager

Время прочтения: 5 мин.

В данной статье я опишу схему автоматизации предоставления доступов сотрудникам для небольшого DWH на MS SQL Server.

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

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

После постановки задачи нужно было придумать архитектурное решение. Данные хранятся на MS SQL серверах. Для автоматизации важно было организовать ролевую модель для баз данных. Поскольку большинство доступов для пользователей требовало режима «чтение» для определенных таблиц, то для существующих и создаваемых баз данных необходимо было завести роль с названием «%Название_БД%_Reader».

Например, ATM_Info_Reader. В эту роль включались самые необходимые таблицы БД.

Для автоматического предоставления доступа используется T-SQL скрипт:

USE {0} CREATE USER [{1}] FOR LOGIN [{1}] EXEC sp_addrolemember '{2}', '{1}'

Здесь:

{0} – имя базы данных

{1} – логин пользователя на MS SQL сервере

{2} – имя роли в БД, к которой предоставляется доступ для пользователя

Если пользователь ранее не был зарегистрирован на сервере, то для него создается автоматически логин другим скриптом:

CREATE LOGIN [{0}] FROM WINDOWS

Здесь:

{0} – доменная учетная запись Active Directory пользователя

Информация о необходимых доступах для пользователя, его логине и другие сопутствующие данные хранятся в заявке в АС HP Service Manager. Получаемая информация позволила создать различные алгоритмы для обработки заявок. Алгоритмы написаны на языке C#.

Разработанная программа представляет собой веб-сайт:

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

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

Например, сайт может посоветовать:

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

Здесь перечислены основные алгоритмы действий операторов.

Сайт сделан на принципах объектно-ориентированного программирования, поэтому добавление новых сценариев не приведет к большим трудозатратам поскольку каждый сценарий реализовывается на шаблонных «блоках» кода.

Особенности программы.

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

var login = System.Web.HttpContext.Current.User.Identity.Name.ToUpper();
if (!Databases.deptStaff.Any(e => e.domainLogin.ToUpper().Contains(login)))
	return RedirectToAction("AccessDenied", "Home");

В случае, если сайт пытается открыть пользователь не из списка, то происходит переадресация на страницу с ошибкой доступа «403 Forbidden».

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

При нажатии кнопки «Исполнить заявки» происходит отправка POST запроса со списком выбранных заявок на бэкенд. Номера заявок хранятся в data-атрибутах заявок на странице. Бэкенд хранит словарь с заявками и подробной информацией о них. По полученному списку можно использовать данные для обработки из словаря.

Обработка происходит в цикле:

foreach (var request in Databases.smRequests.Where(r => selectedIds.Contains(r.number)))

Извлечение выбранных БД из заявок происходит по сопоставлению списка БД из базы данных приложения с полученной пользовательской информации из заявки:

if (request.rationale.ToUpper().Contains(db.Name.ToUpper()) || request.additional_info.ToUpper().Contains(db.Name.ToUpper()))

В базе данных приложения хранится информация о БД на MS SQL серверах. Эта информация включает в себя сведения о:

  • сервере, на котором хранится БД;
  • типе БД (БД отдела/песочница/внешняя БД/другое);
  • электронных адресах ответственных лиц БД;
  • типе доступа к БД (автоматический/ручной/запрещен).

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

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

Алгоритм предоставления доступа для такой БД приведен ниже:

// Проверка на наличие в заявке БД из списка БД отдела
if (entity.deptDBs.Count > 0)
{
	foreach (var db in entity.deptDBs)
	{
		// Проверка типа предоставления доступа к БД - если тип автоматический,
		// то происходит предоставление доступа с добавлением комментария в спискок
		// request.subRequests
		if (db.AccessType == DB.ServersDatabases.DatabaseAccessType.Automatic)
			request.subRequests.Add(execClass.Exec(db, entity.login));
		// Иначе формируется комментарий с последовательностью действия
		// для оператора для выбранной БД
		else
		{
			var at = string.Empty;
			switch (db.AccessType)
			{
				case DB.ServersDatabases.DatabaseAccessType.Deny:
					at = "запрещен";
					break;
				case DB.ServersDatabases.DatabaseAccessType.Manual:
					at = "предоставляется в ручном режиме";
					break;
				case DB.ServersDatabases.DatabaseAccessType.Unknown:
					at = "неопределен. Необходимо уточнить тип доступа к данной БД";
					break;
			}
			var subReq = new SubRequest(false, string.Format("Доступ к БД {0} на сервере {1} {2}. Логин пользователя: {3}",
				db.Name, db.Server, at, entity.login));
			request.subRequests.Add(subReq);
		}
	}
}

Другие сценарии для разных типов БД выглядят схожим образом.

После обработки всех выбранных заявок бэкенд вернёт на страницу результат работы алгоритмов:

Обновленная страница формируется без перезагрузки поскольку используется технология AJAX.

Автоматизированное исполнение заявок позволило повысить скорость обработки заявок и снизить уровень человеческого фактора поскольку программа для каждой БД предлагает однозначное решение.

Советуем почитать