Always encrypted
Эта технология позволяет хранить шифрованные данные в SQL Server без передачи ключей шифрования самому SQL Server. Always Encrypted так же как и TDE шифрует данные в базе данных, но не на уровне базы, а на уровне столбца.
Для шифрования Always Encrypted использует 2 ключа:
- Column Encryption Key (CEK)
- Column Master Key (CMK)
Все процессы шифрования и дешифрования данных происходят на клиенте, в базе данных хранятся только зашифрованное значение ключа шифрования (CEK).
Always Encrypted так же позволяет ограничить доступ к данным даже для DBA, таким образом давая возможность не беспокоиться о том, что администратор получит доступ к данным, к которым не должен.
Авторизация в sql server
Для авторизации SQL Server использует безопасность на основе ролей, которая позволяет назначать разрешения для роли или группы Windows/домена, а не отдельным пользователям. В SQL Server есть встроенные роли сервера и баз данных, у которых есть предопределенный набор разрешений.
В SQL Server есть 3 уровня безопасности, их можно представить, как иерархию от высшего к низшему:
- Уровень сервера – на этом уровне можно раздать права на базы данных, учетные записи, роли сервера и группы доступности;
- Уровень базы данных включают в себя схемы, пользователи базы данных, роли базы данных и полнотекстовые каталоги;
- Уровень схемы включают такие объекты, как таблицы, представления, функции и хранимые процедуры.
Архив обновлен 24.02.2022г.
Внимание:
Если вы используете данный скрипт на локальном сервере типа
DENWER XAMPP
, то не
стоит ждать писем на свой почтовый ящик. Письма лежат в заглушке
sendmail
. В
Denwer
вы их можете
найти по пути
Z:tmp!sendmail
открыть данные файлы вы сможете в любом почтовом клиенте.
Аудит активности в sql server
SQL Server предоставляет возможность вести аудит любой пользовательской активности в экземпляре сервера.
Это очень мощный инструмент, который позволяет полностью контролировать действия ваших пользователей/разработчиков.
Рассмотрим базовую настройку аудита:
В SSMS, во вкладке Security -> Audits создайте новый аудит.
Затем, для аудита нужно создать Спецификацию (Audit Specification), для указания событий, которые будут отслеживаться.
После того как вы создадите и активируете аудит, в журнале аудита можно будет посмотреть события, которые зафиксированы процедурой аудита.
Аутентификация в sql server
Аккаунт SQL Server можно разделить на 2 части: Имя входа и Пользователь.
Встроенные роли базы данных
Так же стоит отдельно выделить специальные роли в базе данных msdb.
Встроенные роли сервера
Схема ролей SQL Server:
Встроенные схемы
В SQL Server есть встроенные системные схемы:
- dbo
- guest
- sys
- INFORMATION_SCHEMA
Схема dbo является схемой по умолчанию для новых баз данных, а пользователь dbo является владельцем схемы dbo. По умолчанию, новые пользователи в базе данных имеют схему dbo в качестве схемы по умолчанию. Другие встроенные схемы нужны для системных объектов SQL Server.
Далее создаем таблицу
bez_role
, где напишим названия наших ролей:
- Администратор
- Модератор
- Пользователь
И так, создадим таблицы в нашей базе данных. у меня это таблицы:
- Bez_reg
- Bez_role
- Bez_content
Использование group managed service accounts для sql server
Групповые управляемые учетные записи службы или gMSA – это специальная учетная запись, которая автоматически управляется Active Directory. gMSA это развитие технологии MSA, так как MSA было невозможно использовать в кластерных сценариях.
gMSA исключает необходимость вручную менять пароли для учетной записи. При настройке gMSA вы указываете на каких серверах будет работать gMSA аккаунт, как часто Active Directory будет менять пароль, и кто имеет право на просмотр пароля. На серверах, на которых будет установлен gMSA не нужно указывать пароль при указании соответствующей учетной записи gMSA.
Имейте в виду, что версия Windows Server для работы с gMSA должна быть не ниже 2022.
Когда стоит использовать шифрование sql server?
Шифрование данных это одна из важных мер обеспечения безопасности, но шифрование может быть требовательно к ресурсам сервера и иногда может быть лишним.
Если пользователи обращаются к данным через общедоступную сеть, то для обеспечения безопасности может потребоваться шифрование, но если данные передаются по защищенной интрасети или ВПН, то необходимости в шифровании данных нет. Так же стоит рассмотреть возможность шифрования данных, если есть угроза кражи физического носителя с базами данных.
Внедрение шифрования должно быть хорошо спланировано: нужно учесть дополнительную нагрузку на сервер, могут ли приложения, которые работают с вашим сервером внедрить поддержку шифрования данного типа на своей стороне и многие другие нюансы.
Общие рекомендации по безопасности sql server
Всегда следуйте принципу наименьших привилегий. В том числе настройте аккаунт службы SQL Server с помощью gMSA. Ни в коем случае не используйте доменный аккаунт с привилегиями администратора домена.
Оценка уязвимостей sql server через ssms
В SQL Server Management Studio есть функция оценки уязвимостей для базы данных.
Выберите базу данных -> Tasks -> Vulnerability Assessment -> Scan For Vulnerabilities.
Сканнер оценит базу данных на предмет популярных ошибок в конфигурации безопасности и даст соответствующие рекомендации.
Обязательно стоит пройтись этим сканнером по вашим базам данных. Он может выявить скрытые проблемы, которых не видно на первый взгляд.
Предоставление прав ролям, а не пользователям
Когда пользователей становится много, управлять их разрешениями становится сложнее, и также становится сложнее не допустить ошибки в предоставлении прав.
Рекомендуется предоставлять разрешения ролям, а пользователей добавлять в роли. Таким образом вы добьетесь большей прозрачности, так как все пользователи той или иной роли будут иметь одинаковые права. Добавить или удалить пользователей из роли проще, чем воссоздать отдельные наборы разрешений для отдельных пользователей.
Можно предоставить права пользователю на схему. В этом случае пользователи сразу смогут работать с вновь созданными объектами в этой схеме, в отличии от ролей, когда при создании нового объекта, роли нужно будет раздать на него права.
Прозрачное шифрование данных
Прозрачное шифрование данных или Transparent Data Encryption шифрует всю базу целиком. При краже физического носителя или .mdf/.ldf файла, злоумышленник не сможет получить доступ к информации в базе данных.
Диаграмма, для того чтобы представить весь процесс
Базовое шифрование базы данных через T-SQL:
USE master;GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’;goCREATE CERTIFICATE ServerCert WITH SUBJECT = ‘DEK Certificate’;goUSE AdventureWorks2022;GOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE ServerCert;GOALTER DATABASE AdventureWorks2022SET ENCRYPTION ON;GO
Роли приложений
Роль приложения – это объект базы данных (такой же, как и обычная роль базы данных), который позволяет с помощью аутентификации через пароль менять контекст безопасности в базе данных. В отличие от ролей баз данных, роли приложений по умолчанию находятся в неактивном состоянии и активируются, когда приложение выполняет процедуру sp_setapprole и вводит соответствующий пароль.
В отличие от обычных ролей, роли приложений практически никогда не используются. Как исключение, их применение можно найти в multi-layer приложениях.
Самые распространённые роли это:
- Администратор
- Модератор
- Пользователь
- Гость
Создание формы авторизации
Теперь перейдем непосредственно к самой авторизации. Создайте файл с названием mylogin.html со следующим содержанием:
Создание формы регистрации
Для того чтобы пользователь мог сам зарегистрироваться, сделайте форму, данные с которой будут посылаться на файл обработки регистрации, т.е. записываться в базу данных. Например, вот самый простой способ:
Структура базы данных авторизации через соц. сети и сайт
Я бы посоветовал идти немного другим путем.
У вас есть свой пользователь [user], а попасть в него – авторизироваться под ним – можно разными путями. Списком путей будут все варианты авторизации у вас на сайте, и он может пополняться, например
- Прямая авторизация на сайте через почту/телефон и пароль
- VK
- OK
- Yandex
- …
- StackEchange
При этом когда человек входит на ваш сайт, у него есть выбор:
- Ввести почту и пароль -ИЛИ- ввести телефон и пароль (смотря как у вас принято)
- Воспользоваться сторонней авторизацией на других сайтах – и список принятых у вас авторизаций
Пользователь выбирает удобный для себя способ, вы знаете этот выбор, и проверяете его вход по набору источников авторизации из первого списка. Все источники фактически будут отличаться только способом авторизации, реализацией в коде и URL для запроса авторизации. По сути результат авторизации может иметь три исхода:
- Успешная. Почта/пароль подходят, ответ от сайта внешней авторизации успешен и так далее, пользователь найден. Все понятно, авторизируем
- Не успешная. Почта/пароль не подходят, ответ от сайта авторизации отрицательный, все плохо. Что делать тоже понятно – введите пароль еще раз или восстановление пароля
- Успешный, но не до конца. Ответ от сайта авторизации успешный, но у вас нет пользователя, связанного с таким аккаунтом. Здесь тоже все понятно, автоматически создаем у себя пользователя с таким источником авторизации и даем ему вход
Структура базы в данном случае довольно проста:
Таблица пользователей:
Таблица источников авторизации:
Таблица текущих произведенных авторизаций:
- [user_id] – уникальный ID пользователя, для которого произведена авторизация
- [expire] – метка окончания авторизации, может быть либо 0/1, либо метка времени, либо вообще может не быть (вы можете просто удалять авторизацию при ее окончании, если вам не надо знать, кто когда через что заходил)
- [cookies] – некое поле или поля, которые содержат уникальный ключ/токен или что-то еще, что точно идентифицирует именно этот единичный вход пользователя. Это может быть кука браузера, которую отдали пользователю при авторизации через почту/пароль, или токен авторизации через VK
- [stats] – любые поля, описывающие именно этот вход пользователя, для статистики или безопасности. Сюда можно записывать браузер, машину, клиента, IP-адрес, все что угодно, если оно вам нужно. Обычно это сборная строка из нескольких параметров, но ее может вообще не быть, если она вам не нужна
Вот в целом и вся логика.
Алгоритм:
- Пользователь заходит на сайт
- Вынимаем сразу у него куки/ключи/токены/что-то еще, что может говорить о его авторизованности
- Проверяем их на валидность и работоспособность. Если они еще действуют – просто авторизуем пользователя. Конец
- Предлагаем пользователю выбрать вариант авторизации
- По выбранному варианту получаем источник авторизации (поле [type] в таблице источников авторизации), проводим попытку авторизации этим способом.
- Если успешно – получаем [user_id] и авторизуем. Конец
- Если успешно, но такого пользователя у нас еще нет, молча создаем его и авторизуем. Конец
- Если не успешно – просим повторить вход с выбором варианта или восстановить пароль. Можно начислить штраф на вход, паузу или показать капчу для продолжения, полезно. Только не переборщите с защитой, вводить капчу каждый раз на каждый чих – это перебор.
Структура таблицы: bez_reg
--
-- Структура таблицы `bez_reg`
--
CREATE TABLE IF NOT EXISTS `bez_reg` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login` varchar(200) NOT NULL,
`pass` varchar(32) NOT NULL,
`salt` varchar(32) NOT NULL,
`active_hex` varchar(32) NOT NULL,
`status` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Схемы в sql server
У некоторых объектов SQL Server (таблицы, процедуры, представления, функции) есть схема. Схемы можно представить, как контейнеры для различных объектов (или пространство имён/namespace, если вы знакомы с программированием).
Например, если у пользователя есть права на select из схемы, то пользователь так же может делать select со всех объектов этой схемы. То есть объекты, принадлежащие схеме, наследуют её разрешения. Когда пользователи создают объекты в схеме, объекты принадлежат владельцу схемы, а не пользователю.
Главное отличие схем от ролей в том, что разрешения на схемы могут быть предоставлены ролям. Например, у роли testrole могут быть разрешения select со схемы schema1 и разрешения на select/update на схеме schema2. Объект может принадлежать всего одной схеме, но права на него могут быть у нескольких ролей.
Файл reg_form.html
Так как регистрация пользователей у нас готова, самое время написать авторизацию. Создадим форму для авторизации
пользователей, далее напишем обработчик формы авторизации и на последок сделаем скрипт show.php который
будет показывать нам авторизированны мы в системе или нет.
Фильтрация данных в sql server
Фильтрация данных в SQL Server через хранимые процедур/представления/функции можно отнести к реализации принципу наименьших привилегий, так как вы предоставляете доступ не ко всем данным в таблице, а лишь к некоторой их части.
Например, можно предоставить пользователю права только на SELECT из представления и запретить прямой доступ к таблицам, которые используются в представлении. Таким образом вы предоставите доступ только к части данных из таблицы, задав фильтр where в представлении.
Фильтрация данных через row-level security
Безопасность на уровне строк или Row-Level Security (RLS) позволяет фильтровать данные таблицы для разных пользователей по настраиваемому фильтру. Это осуществляется через SECURITY POLICY в T-SQL
На данном скриншоте политика настраивается таким образом, что пользователь Sales1 будет видеть строки таблицы, в которых значение столбца Sales равняется имени пользователя (Sales1), а пользователь Manager будет видеть все строки.
Шифрование данных средствами sql server
SQL Server может шифровать данные, процедуры и соединения с сервером. Шифрование возможно с использованием сертификата, асимметричного или симметричного ключа. В SQL Server используется иерархичная модель шифрования, то есть каждый слой иерархии шифрует слой под ним.
Самыми распространенными типами шифрования являются TDE (Прозрачное шифрование данных) и Always Encrypted.