SQL-Ex blog

Что такое безопасность на основе роли?

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

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

Начиная с SQL Server 2012, Microsoft предоставляет два различных типа пользовательских ролей: серверные и базы данных. Серверная пользовательская роль обеспечивает безопасный доступ к ресурсам сервера, в то время как пользовательская роль уровня базы данных предоставляет доступ к ресурсам базы данных.

Пользовательские серверные роли

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

Для демонстрации создания пользовательской роли уровня сервера с ограниченными разрешениями я создам роль с именем Junior DBA. Имя предполагает, что эта пользовательская роль будет иметь ограниченный набор серверных прав для младших администраторов. Я хочу, чтобы эта роль имела доступ только к просмотру баз данных и информации о состоянии сервера.

Первым шагом для установки пользовательской роли Junior DBA является её создание. Новую роль можно создать с помощью T-SQL или SSMS. В листинге 1 приведен код T-SQL создания роли.

Листинг 1. Создание пользовательской роли уровня сервера

CREATE SERVER ROLE [Junior DBA] AUTHORIZATION SA;

Код в листинге 1 только создает роль и делает владельцем этой роли аккаунт SA, что обеспечивается ключевым словом AUTHORIZATION с последующим логином SA. Если ключевое слово авторизации и логин опущены, то мой логин будет владельцем этой пользовательской серверной роли. Этот код T-SQL только создал роль, но не присвоил никаких прав этой новой роли.

Я мог бы также создать серверную роль с помощью SSMS, выполнив следующие шаги:

1. Открыть браузер объектов (Object Explorer) и подключиться к серверу, на котором требуется создать новую серверную роль.


2. Раскрыть ветку Безопасность (Security).


3. Щелкнуть правой кнопкой на пункте Роли сервера (Server Role) и выбрать пункт Новая серверная роль...(New Server Role…) из выпадающего меню.

Если я использовал эти шаги в SSMS для создания моей роли Junior DBA, то откроется окно New Server Role, показанное на рис.1.


Рис.1: Окно новой серверной роли

Как можно видеть, SSMS автоматически генерирует имя роли ServerRole-20210403-063456. Поскольку это не то имя, которое я хочу дать моей роли, мне нужно заменить это имя на имя Junior DBA и ввести dbo в поле владельца. Тут я мог бы щелкнуть кнопку ОК, и роль будет создана так же, как если бы я выполнил код T-SQL в листинге 1. Но поскольку я хочу моей новой роли Junior DBA дать некоторые права, позвольте мне продолжить и показать вам, как приписать права новой роли с помощью SSMS.

На рис.1 в разделе Securables в правой части окна имеется список прав уровня сервера, которые могут быть предоставлены пользовательской роли. Для демонстрации я собираюсь дать моей роли младшего администратора только следующие права: просмотр любой базы данных, просмотр всех определений и просмотр информации о состоянии сервера. Чтобы дать эти права, я сначала щелкну правой кнопкой на пункте Servers, а затем прокручу список разрешений, чтобы увидеть те права, которые я хочу дать младшему администратору. После этого окно New Server Role выглядит так, как показано на рис.2.


Рис.2: Пользовательская роль Junior DBA с разрешениями

На последнем шаге создания роли Junior DBA с этими правами осталось щелкнуть кнопку ОК.

Права, которые я предоставил новой роли младшего администратора с помощью SSMS, могут быть даны с помощью T-SQL, выполнив скрипт в листинге 2.

Листинг 2: Предоставление разрешений роли Junior DBA на T-SQL

USE [master];

GO

GRANT VIEW ANY DATABASE TO [Junior DBA];

GRANT VIEW ANY DEFINITION TO [Junior DBA];

GRANT VIEW SERVER STATE TO [Junior DBA];

GO

На этом этапе новая пользовательская серверная роль Junior DBA только что создана и не содержит никаких членов. Прежде, чем добавить членов, мне нужно создать логин для моего младшего администратора. Для этой демонстрации я создам логин JD с авторизацией SQL при помощи кода T-SQL в листинге 3.

Листинг 3: Создание логина JD

USE [master]

GO

CREATE LOGIN [JD] WITH PASSWORD=N'Junior', 

                       DEFAULT_DATABASE=[master], 

                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Чтобы сделать логин JD членом роли Junior DBA, я могу использовать SSMS и свойства логина или роли. Так же я могу использовать код T-SQL в листинге 4.

Листинг 4: Добавление члена в новую серверную роль

ALTER SERVER ROLE [Junior DBA] ADD MEMBER [JD]

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

Определяемые пользователем роли уровня базы данных

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

Чтобы показать, как пользовательские роли могут обеспечить пользователям доступ к ресурсам базы данных, я добавлю трех пользователей в базу данных AdventureWorks2019. Каждый пользователь имеет различный набор прав доступа для поддержки гипотетического приложения по продажам. Приложение SalesForce требует 3 разных профиля безопасности для поддержки требуемой модели безопасности. Каждый профиль безопасности будет иметь различные наборы разрешений. Для каждого профиля безопасности я установлю отличную пользовательскую роль, а затем присвою каждой новой роли требуемые разрешения. Пользовательские роли будут называться: SalesManager, ProductionControl, SalesPerson.

Пользовательской роли SalesManager будет требоваться доступ для управления всеми ресурсами в базе данных AdventureWorks2019. Роли ProductionControl будет необходимо иметь права на вставку, обновление и удаление данных в любых таблицах схемы Production, и иметь доступ на чтение во всех таблицах базы данных AdventureWorks2019. Последнему профилю, SalesPerson будет необходим доступ на вставку, обновление и удаление информации во всех таблицах в схеме Sales и доступ на чтение во всех таблицах базы данных AdventureWorks2019.

Чтобы показать, как добавить пользователей баз данных в каждую из этих различных пользовательских ролей, я создам следующие логины и пользователей базы данных AdventureWorks2019: Tom, Dick, Sally. Эти логины и пользователи будут созданы с помощью кода T-SQL в листинге 5.

Листинг 5: Создание логинов и пользователей базы данных

USE [master]

GO

-- Создание логинов

CREATE LOGIN [Tom] WITH PASSWORD=N'Salesman', 

                       DEFAULT_DATABASE=[AdventureWorks2019], 

                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

CREATE LOGIN [Dick] WITH PASSWORD=N'ProductionControl', 

                       DEFAULT_DATABASE=[AdventureWorks2019], 

                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

CREATE LOGIN [Sally] WITH PASSWORD=N'SalesManager', 

                       DEFAULT_DATABASE=[AdventureWorks2019], 

                       CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

GO

USE AdventureWOrks2019;

GO

-- Создание пользователей базы данных

CREATE USER [Tom] FOR LOGIN [Tom] WITH DEFAULT_SCHEMA=[dbo];

GO

CREATE USER [Dick] FOR LOGIN [Dick] WITH DEFAULT_SCHEMA=[dbo];

GO

CREATE USER [Sally] FOR LOGIN [Sally] WITH DEFAULT_SCHEMA=[dbo];

GO

Поскольку для роли SalesManager будет необходим доступ ко всем ресурсам базы данных, он будет установлен с разрешениями db_owner. Чтобы создать роль SalesManager и предоставить ей разрешения, я выполню следующие шаги, используя SSMS:

1. Открыть обозреватель объектов


2. Развернуть базу данных AdventureWorks2019


3. Развернуть ветку Security


4. Выполнить правый щелчок на пункте Role, переместить мышь на пункт меню New, всплывающего рядом, а затем щелкнуть на пункте меню New Database Role…

После выполнения этих манипуляций появится окно Database Role – New, показанное на рис.3.


Рис.3: Окно новой роли базы данных

Выбрав вкладку General в окне слева, я введу SalesManager в поле Role name (имя роли) в правой части окна. Поскольку я хочу, чтобы новая роль принадлежала dbo, я введу dbo в поле owner (владелец). Чтобы добавить членов к этой роли, я щелкну на кнопке Add. Появится окно Select Database User or Role, где я затем, используя кнопку Browse, выберу Sally, чтобы добавить её как члена новой роли, как это показано на рис.4.


Рис.4: Добавление члена Sally

Чтобы завершить добавление Sally в качестве члена этой новой роли, я щелкну кнопку ОК. Появится окно, изображенное на рис.5.


Рис.5: Новая роль базы данных с Sally в качестве члена роли

Чтобы завершить создание роли, я щелкаю кнопку ОК. Сейчас все, что сделано, это создание новой роли с именем SalesManager, которая включает Sally как единственного члена этой роли. Альтернативно я мог бы использовать скрипт в листинге 6 для создания роли SalesManager.

Листинг 6: Создание роли SalesManager и члена этой роли Sally

USE [AdventureWorks2019];

GO

CREATE ROLE [SalesManager] AUTHORIZATION [dbo];

GO

ALTER ROLE [SalesManager ADD MEMBER [Sally];

GO

Чтобы завершить установку этой роли, мне нужно дать разрешения. Поскольку SalesManager требуется управлять всеми ресурсами в базе данных, я дам этой роли те же разрешения, что и для предопределенной роли db_owner. Самый простой способ выполнить это - сделать роль SalesManager членом роли db_owner, что можно сделать, выполнив код в листинге 7.

Листинг 7: Добавление роли SalesManager как члена роли db_owner

USE [AdventureWorks2019]

GO

ALTER ROLE [db_owner] ADD MEMBER [SalesManager]

GO

Следующим профилем безопасности, который требуется установить для моего гипотетического приложения, является ProductionControl. Пользователь Dick будет членом этой роли, и для этой роли требуются права на SELECT, INSERT, UPDATE и DELETE на все таблицы в схеме Production, а также требуются права на чтение всех таблиц в базе данных AdventureWorks2019. Для создания этой роли добавим Dick как члена и предоставления необходимых разрешений я выполню код в листинге 8.

Листинг 8: Установка пользовательской роли ProductionControl уровня базы данных

USE [AdventureWorks2019];

GO

CREATE ROLE [ProductionControl] AUTHORIZATION [dbo];

GO

ALTER ROLE [ProductionControl] ADD MEMBER [Dick];

GO

GRANT DELETE ON SCHEMA::[Production] TO [ProductionControl];

GRANT INSERT ON SCHEMA::[Production] TO [ProductionControl];

GRANT SELECT ON SCHEMA::[Production] TO [ProductionControl];

GRANT UPDATE ON SCHEMA::[Production] TO [ProductionControl];

GO

ALTER ROLE [db_datareader] ADD MEMBER [ProductionControl];

GO

Последним профилем безопасности является SalesPerson. Для этого профиля я создам пользовательскую роль уровня базы данных с именем SalesPerson. Я добавлю членом этой роли пользователя базы Tom. Эта роль должна иметь разрешения SELECT, INSERT, UPDATE и DELETE на все таблицы в схеме Sales и права на чтение для всех таблиц в базе данных AdventureWorks2019. Для создания этой роли и придания всех необходимых разрешений, я выполню скрипт в листинге 9.

Листинг 9: Создание пользовательской роли SalesPerson

USE [AdventureWorks2019];

GO

CREATE ROLE [SalesPerson] AUTHORIZATION [dbo];

GO

ALTER ROLE [SalesPerson] ADD MEMBER [Tom];

GO

GRANT DELETE ON SCHEMA::[Sales] TO [SalesPerson];

GRANT INSERT ON SCHEMA::[Sales] TO [SalesPerson];

GRANT SELECT ON SCHEMA::[Sales] TO [SalesPerson];

GRANT UPDATE ON SCHEMA::[Sales] TO [SalesPerson];

GO

ALTER ROLE [db_datareader] ADD MEMBER [SalesPerson];

GO

При использовании ролей уровня базы данных для каждого из этих профилей безопасности я теперь могу обеспечить одинаковые разрешения для любых новых пользователей базы данных, которым может быть необходим один из этих профилей безопасности. Чтобы дать новому пользователю те же разрешения, которые имеют Tom, Dick или Sally, все, что мне потребуется, это сделать их членами соответствующей роли. Наличие разрешений на объекты базы данных в пользовательской роли уменьшает объем работы по обеспечению безопасности администратору, которому требуется гарантировать, что два пользователя имеют одинаковые права при условии, что они имеют одну и ту же роль.

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

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