Роли уровня базы данных

Управление сетевыми протоколами клиента сервера &

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

SQL Server Диспетчер конфигурации позволяет управлять серверными и клиентскими сетевыми протоколами, в том числе применять шифрование протокола, просматривать свойства псевдонима, а также включать и отключать протокол.

Диспетчер конфигурации SQL Server позволяет создавать или удалять псевдонимы, изменять порядок использования протоколов и просматривать свойства псевдонима сервера, включая:

• псевдонимы сервера— псевдонимы сервера, используемый для компьютера, с которым соединяется клиент;

• протокол — сетевой протокол, используемый для данной конфигурации;

• параметры соединения — параметры, связанные с адресом соединения для конфигурации сетевого протокола.

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

Доступные сетевые протоколы

SQL Server поддерживает протоколы общей памяти, TCP/IP и именованных каналов. Сведения о выборе сетевых протоколов см. в разделе Configure Client Protocols. SQL Server не поддерживает сетевые протоколы VIA, Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk и NWLink IPX/SPX. Клиенты, подключенные ранее с помощью этих протоколов, для соединения с SQL Serverдолжны выбрать другой протокол. Диспетчер конфигурации SQL Server нельзя использовать для настройки прокси-сервера WinSock. Чтобы настроить прокси-сервер WinSock, см. документацию по ISA Server.

Как мы делали ядро инвестиционного бизнеса «Альфа-Банка» на базе Tarantool

Кадр из фильма «Our Secret Universe: The Hidden Life of the Cell»

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

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

Кроме того, растут и потребности регуляторов, как российских, так и зарубежных.

Чтобы соответствовать текущим потребностям и заложить фундамент для будущих модернизаций, мы разработали ядро инвест-бизнеса на основе Tarantool.

Средства для начала работы

Основные средства

• SQL Server Management Studio (SSMS) — это основной инструмент для администрирования ядра СУБД и написания кода Transact-SQL. Он размещается в оболочке Visual Studio. Решение SSMS доступно бесплатно для скачивания. Последнюю версию можно использовать с более старыми версиями ядра СУБД.

• диспетчер конфигурации SQL Server устанавливается как с SQL Server, так и с клиентскими средствами. Он позволяет включать серверные протоколы, настраивать параметры протокола, например порты TCP, настраивать автоматический запуск служб сервера и настраивать соединение клиентских компьютеров. Это средство может настроить дополнительные элементы подключения, но не активирует компоненты.

Пример базы данных

Примеры баз данных и примеров не включены в SQL Server. Большинство примеров, описанных в SQL Server электронной документации, используют примеры баз данных AdventureWorks.

Начало работы в среде SQL Server Management Studio

• В текущих версиях Windows в меню Пуск введите «SSMS», а затем выберите Microsoft SQL Server Management Studio.
• При использовании более старых версий Windows в меню «Пуск» наведите указатель мыши на все программы, наведите указатель на Microsoft SQL Server и выберите SQL Server Management Studio.

Запуск диспетчера конфигурации SQL Server

• В текущих версиях Windows на Пуск странице введите диспетчер конфигурациии выберите пункт Диспетчер конфигурации SQL Server версия диспетчер конфигурации.
• При использовании более старых версий Windows в меню «Пуск» наведите указатель мыши на все программы, наведите указатель на Microsoft SQL Server, наведите указатель на средства настройки и нажмите кнопку диспетчер конфигурации SQL Server.

Отделение пользователей от схем

Отделение пользователей от схем обеспечивает дополнительную гибкость в управлении разрешениями объектов базы данных. Схема представляет собой именованный контейнер для объектов базы данных, позволяющий группировать объекты по отдельным пространствам имен. Например, образец базы данных AdventureWorks содержит схемы для Production, Sales и HumanResources.

Четырехкомпонентный синтаксис ссылок на объекты указывает имя схемы.

Владельцы схем и разрешения

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

По умолчанию, если разработчик создает объект в схеме, он принадлежит участнику безопасности, являющемуся владельцем схемы, а не разработчику. Владение объектом можно передать с помощью инструкции Transact-SQL ALTER AUTHORIZATION. Схема может также содержать объекты, принадлежащие другим пользователям и иметь более детализированные разрешения, чем назначенные схеме, хотя это не рекомендуется из-за увеличения сложности управления разрешениями. Объекты можно перемещать из одной схемы в другую, а принадлежность схемы передавать от одного участника другому. Пользователей базы данных можно удалять, не влияя этим на схемы.

Встроенные схемы для обратной совместимости

В состав SQL Server входят девять предварительно определенных схем, имена которых совпадают с именами встроенных пользователей и ролей базы данных: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin. Они существуют ради обратной совместимости. Рекомендуется не использовать их для объектов-пользователей. Схемы, имеющие те же имена, что и предопределенные роли базы данных, можно удалить, если они еще не используются. В этом случае команда drop просто возвратит ошибку и заблокирует удаление используемой схемы.

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

Невозможно удалить следующие схемы:

Примечание

Схемы и зарезервированы для системных объектов. В этих схемах нельзя создавать и удалять объекты.

Схема dbo

Схема является схемой по умолчанию для каждой базы данных. Схемой по умолчанию для пользователей, созданных с помощью команды Transact-SQL CREATE USER, является . Владельцем схемы является учетная запись пользователя .

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

Примечание

Если на объект базы данных ссылаются с помощью однокомпонентного имени, SQL Server вначале ищет этот объект в схеме пользователя по умолчанию. Если объект не найден, SQL Server ищет его в схеме . Если этот объект не найден в схеме , возвращается ошибка.

Предопределенных ролей на уровне сервера

Примечание

Эти роли уровня сервера, появившиеся до SQL Server 2022 г. (16.x), недоступны в базе данных Azure SQL или Azure Synapse Analytics. Существуют специальные Azure SQL роли сервера базы данных для управления разрешениями, эквивалентные ролям уровня сервера, представленным в SQL Server 2022 (16.x). Дополнительные сведения о База данных SQL см. в статье Управление и предоставление доступа к базе данных.

В следующей таблице представлены предопределенные роли уровня сервера и их возможности.

Предопределенная роль уровня сервера
Описание
sysadmin
Члены предопределенной роли сервера sysadmin могут выполнять любые действия на сервере.
serveradmin
Элементы предопределенной роли сервера serveradmin могут изменять параметры конфигурации на уровне сервера, а также выключать сервер.
securityadmin
Элементы предопределенной роли сервера securityadmin управляют именами входа и их свойствами. Это могут быть разрешения на уровне сервера , и . Они также могут предоставлять, запрещать и отменять разрешения на уровне базы данных (инструкции , и ) при наличии доступа к базе данных

Кроме того, они могут сбрасывать пароли для имен входа SQL Server .Важно! Возможность предоставления доступа к компоненту Компонент Database Engine и настройки разрешений пользователей позволяет администратору безопасности назначать большинство разрешений сервера. Роль securityadmin должна считаться эквивалентной роли sysadmin

В качестве альтернативы, начиная с SQL Server 2022 (16.x), рассмотрите возможность использования новой предопределяемой роли сервера ##MS_LoginManager##.
processadmin
Члены предопределенной роли сервера processadmin могут завершать процессы, работающие на экземпляре SQL Server.
setupadmin
Члены предопределенной роли сервера setupadmin могут добавлять и удалять связанные серверы с помощью инструкций Transact-SQL. (Членство в роли sysadmin необходимо при использовании службы Среда Management Studio.)
bulkadmin
Элементы предопределенной роли сервера bulkadmin могут выполнять инструкцию . Роль bulkadmin или разрешения ADMINISTER BULK OPERATIONS не поддерживаются для SQL Server на Linux. Операции массовой вставки для SQL Server на Linux может выполнять только sysadmin.
diskadmin
Предопределенная роль сервера diskadmin используется для управления файлами на диске.
dbcreator
Члены предопределенной роли сервера dbcreator могут создавать, изменять, удалять и восстанавливать любые базы данных.
public
Каждое имя для входа SQL Server принадлежит к роли сервера public. Если субъекту сервера не предоставлены или запрещены определенные разрешения на защищаемый объект, пользователь наследует разрешения, предоставленные для этого объекта. Разрешения роли public следует назначать только тому объекту, который будет доступен всем пользователям. Вы не можете изменить членство в public.Примечание. Роль public реализуется не так, как другие роли. В разрешениях может быть отказано, они могут предоставляться либо отменяться для предопределенных ролей public.

Важно!

Большинство разрешений, предоставляемых следующими ролями сервера, неприменимы к Azure Synapse Analytics: processadmin, serveradmin, setupadmin и diskadmin.

Устанавливаем SQL Server

Алгоритм действий следующий:
1. Запускаем ранее скачанный пакет установки. Должен открыться SQL Server Installation Server:

2. Выбираем пункт «Установка».
3. После изменения экрана кликаем «Новая установка изолированного экземпляра SQL Server». Произойдёт запуск установки. Жмём «Далее».

4

Обратите внимание, чтобы на этапе «правил установки» в окне отсутствовали красные крестики. В обратном случае нажимайте на выделенные строки предупреждений и следуйте инструкциям, чтобы их устранить

Потом жмём «Далее», окно установки опять поменяется:

5. Выбираем «Выполнить новую установку SQL Server 2016», открывается окно регистрации продукта:

6. Вводим лицензионный ключ продукта при наличии. В обратном случае выбираем Evaluation, что активирует 180-дневную копию.
7. Принимаем лицензионное соглашение.
8. Открывается окно компонентов. Устанавливаем галочки напротив:
— службы ядра СУБД;
— соединение с клиентскими средствами;
— компоненты документации.
Снова нажимаем «Далее».

9. Если есть установленная копия SQL Server, выбираем «экземпляр по умолчанию», если нет (устанавливаете впервые) — именованный экземпляр. Вводим в поле имя Экземпляра, жмём «Далее».

10. Проверяем, хватает ли места на диске. Если не хватает, освобождаем его и жмём «Далее».
11. На этапе «Настройка Ядра СУБД» убеждаемся, что выбрали строку «Проверка подлинности Windows». Если не выбрали, выбираем. Потом добавляем в нижнее поле пользователя, которого мы создавали перед установкой (либо добавляем текущего соответствующей кнопкой). Жмём «Далее».

12. Перепроверяем все настройки установки системы управления БД SQL Server.
13. Наблюдаем за установкой до её завершения.

История

Пользователь является субъектом безопасности уровня базы данных. Для соединения с базой данных имя входа должно быть сопоставлено с пользователем базы данных. Имя входа может быть сопоставлено с различными базами данных в качестве разных пользователей, но в каждой базе данных ему может быть сопоставлен только один пользователь. В частично автономной базе данных можно создать пользователя, не имеющего имени входа. Дополнительные сведения о пользователях автономной базы данных см. в разделе CREATE USER (Transact-SQL). Если в базе данных включена учетная запись гостя, то под именем входа, не сопоставленным с пользователем базы данных, можно войти от пользователя guest.

Важно!

Как правило, пользователь guest отключен. Не используйте пользователя guest без крайней необходимости.

Как субъектам безопасности, пользователям могут предоставляться разрешения. Областью действия пользователя является база данных. Для подключения к определенной базе данных на экземпляре SQL Server имя входа должно быть сопоставлено с пользователем базы данных. Разрешения внутри базы данных предоставляются и запрещаются для пользователя базы данных, а не имени входа.

Совместимость с разными компонентами

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

• DBCC SHOW_STATISTICS предоставляет статистику по нефильтрованным данным и может вызвать утечку информации, которая должна быть защищена политикой безопасности. По этой причине доступ к просмотру объектов статистики для таблицы с политикой безопасности на уровне строк ограничен. Пользователь должен быть владельцем таблицы, членом предопределенной роли сервера sysadmin, предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin.

• Filestream. Безопасность на уровне строк не совместима с компонентом Filestream.

• PolyBase: безопасность на уровне строк поддерживается для внешних таблиц в Azure Synapse и SQL Server 2019 CU7 или более поздней версии.

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

• Индексированные представления. Как правило, политики безопасности можно создавать на основе представлений, а представления — на основе таблиц, связанных политиками безопасности. Тем не менее индексированные представления нельзя создать на основе таблиц с политикой безопасности, так как операции поиска строк через индекс будут обходить политику.

• Отслеживание измененных данных. Система отслеживания измененных данных может вызвать утечку целых строк, которые должны быть отфильтрованы для доступа членов db_owner или пользователей, являющихся членами «шлюзовой» роли, указанной при включении этой системы для таблицы. (Примечание. Для этой функции можно явно задать значение NULL, чтобы все пользователи имели доступ к измененным данным.) В результате параметр db_owner и члены такой шлюзовой роли могут просматривать все изменения данных в таблице даже при наличии политики безопасности для таблицы.

• Отслеживание изменений. Отслеживание изменений может вызвать утечку первичного ключа строк, которые должны быть отфильтрованы для доступа пользователей с разрешениями SELECT и VIEW CHANGE TRACKING. Доступ к фактическим значениям данных не предоставляется, становится известно только то, что столбец A был обновлен (вставлен или удален) для строки с первичным ключом B. Это создает проблему, если первичный ключ содержит конфиденциальные элементы, например номер социального страхования. Тем не менее на практике для получения последних данных инструкция CHANGETABLE почти всегда объединена с исходной таблицей.

• Полнотекстовый поиск. Запросы, использующие следующие функции полнотекстового и семантического поиска, будут выполняться со сниженной производительностью из-за введения дополнительного соединения для применения безопасности на уровне строк и блокирования утечки первичных ключей строк, которые должны быть отфильтрованы: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

• Индексы Columnstore. Безопасность на уровне строк совместима с кластеризованными и некластеризованными индексами columnstore. Тем не менее, поскольку безопасность на уровне строк применяет функцию, оптимизатор может изменить план запроса таким образом, чтобы пакетный режим не использовался.

• Секционированные представления. Предикаты блокировки нельзя определить в секционированных представлениях, а секционированные представления нельзя создавать на основе таблиц, использующих предикаты блокировки. Предикаты фильтров совместимы с секционированными представлениями.

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

Немного теории: о СУБД и БД

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

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

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

Тут следует заметить, что серверы бывают разные и различаются тем, как они:
• предоставляют доступ;
• хранят данные;
• предоставляют данные пользователю.

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

И вот как раз для взаимодействия программных компонентов и необходим ещё один компонент, называемый СУБД, то есть речь идёт о системе управления базами данных.
По сути, СУБД представляет собой программную прослойку между сервером и пользователем. СУБД даёт возможность абстрагировать пользователя от системного видения базы данных, самой же системе предоставляется способ непосредственного взаимодействия с пользователем.

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

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

Кроме того, по способу доступа к БД системы бывают клиент-серверные, файл-серверные и встраиваемые.

Что касается СУБД SQL Server, то она является реляционной и клиент-серверной. Эта СУБД довольно популярна на системах, построенных на базе ОС Windows. Давайте установим и настроим её, взяв для примера SQL Server 2016 Enterprise.

Разрешения

SQL Server Express LocalDB — это экземпляр, созданный пользователем для самостоятельного использования. Любой пользователь компьютера может создать базу данных с помощью экземпляра LocalDB, сохранив файлы в своем пользовательском профиле и запустив процесс со своими учетными данными. По умолчанию доступ к экземпляру LocalDB имеет только его владелец. Данные, содержащиеся в LocalDB, защищены средствами файловой системы. Если файлы пользовательской базы данных хранятся в общей папке, то базу данных может открыть любой пользователь, имеющий доступ к этой папке, с помощью собственного экземпляра LocalDB. Если файлы базы данных хранятся в защищенном месте, например в папке пользовательских данных, то базу данных может открыть только сам пользователь, а также администратор, обладающий правами доступа к данной папке. Файлы LocalDB могут быть одновременно открыты только одним экземпляром LocalDB.

Примечание

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

Предоставление минимального разрешения

Первое указанное выше разрешение () — наиболее гранулярное, то есть эта инструкция предоставляет минимально возможное разрешение . Вместе с ним не предоставляются разрешения для каких-либо вложенных объектов. Рекомендуется всегда предоставлять минимально возможное разрешение (вы можете подробнее ознакомиться с принципом минимальных привилегий), но в то же время (противоречит этому) попытаться предоставить разрешения на более высоких уровнях, чтобы упростить систему предоставления. Поэтому, если Jae требуются разрешения для всей схемы, предоставьте один раз на уровне схемы, а не на уровне таблицы или представления много раз. Структура базы данных может существенно повлиять на то, насколько успешной может быть эта стратегия. Стратегия наиболее эффективна, когда объекты в базе данных, которым требуются одинаковые разрешения, включаются в одну схему.

Совет

При разработке базы данных и объектов для нее следует с самого начала определить, какие пользователи или приложения будут обращаться к каким объектам. На основе этих данных сами объекты (таблицы, представления, функции и хранимые процедуры) распределяются по схемам и контейнерам с доступом разного типа. Подробные сведения об этом подходе см. в записи блога Андреаса Вольтера (Andreas Wolter) Schema-design for SQL Server: recommendations for Schema design with security in mind (Разработка схемы для SQL Server: рекомендации по разработке схемы с учетом безопасности).