Управление доступом к данным в Microsoft SQL Server. Роли и привилегии.

Тема: Управление доступом к данным в Microsoft SQL Server. Роли и привилегии.

Цель работы: 1) формирование навыков работы с Microsoft SQL Server.

2) изучение способов управления доступом к данным в Microsoft SQL Server..

Краткие теоретические сведения

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

Однако независимо от конкретной реализации все основные принципы одинаковы.

Управление пользователями в среде MS SQL Server

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

В системе MS SQL Server организована двухуровневая настройка

ограничения доступа к данным. На первом уровне необходимо создать так называемую учетную запись пользователя (login), что позволяет ему подключиться к самому серверу, но не дает автоматического доступа к базам данных.

На втором уровне для каждой базы данных MS SQL Server на основании учетной записи необходимо создать запись пользователя. На основе прав, выданных пользователю как пользователю базы данных (user), его регистрационное имя (login) получает доступ к соответствующей базе данных. В разных базах данных login одного и того же пользователя может иметь одинаковые или разные имена user с разными правами доступа. Иначе говоря, с помощью учетной записи пользователя осуществляется подключение к MS SQL Server, после чего определяются его уровни доступа для каждой базы данных в отдельности.

В системе MS SQL Server существуют дополнительные объекты – роли, которые определяют уровень доступа к объектам MS SQL Server. Они разделены на две группы: назначаемые для учетных записей пользователя сервера и используемые для ограничения доступа к объектам базы данных.

Итак, на уровне сервера система безопасности оперирует следующими понятиями:

122 понятиями:

• аутентификация;

• учетная запись;

• встроенные роли сервера.

На уровне базы данных применяются следующие понятия;

• пользователь базы данных;

• фиксированная роль базы данных;

• пользовательская роль базы данных.

Режимы аутентификации

MS SQL Server предлагает два режима аутентификации пользователей:

• режим аутентификации средствами Windows;

• смешанный режим аутентификации (Windows NT Authentication and SQL Server Authentication).

SQL Server Authentication).

Управление пользователями и правами доступа к данным

инструментальными средствами

1. Запустите утилиту SQL Server Management Studio. На панели

Обозреватель объектов (Object Explorer) в дереве структуры раскройте папку

Базы данных (Databases)

Контрольные вопросы

1. Каким образом в Microsoft SQL Server настроена система управления доступом к базе данных?

2. Как создать нового пользователя базы данных средствами Microsoft SQL Server?

3. Как создать нового пользователя базы данных средствами языка SQL?

4. Что такое роль?

5. Как создать роль средствами Microsoft SQL Server?

6. Как создать роль средствами языка SQL?

7. Что такое привилегии?

8. Как задать привилегии средствами Microsoft SQL Server?

9. Как задать привилегии средствами языка SQL?

10. Как удалить привилегии пользователей из роли?

11. Что означает GRANT?

12. Что означает DENY?

13. Что означает REVOKE?

Методические рекомендации к выполнению лабораторной работы

Управление пользователями и правами доступа к данным инструментальными средствами

1. Запустите утилиту SQL Server Management Studio. На панели Обозреватель объектов (Object Explorer) в дереве структуры раскройте папку Базы данных (Databases).

1. Присоедините базу данных, созданную ранее в лабораторной работе №4, для чего выберите в контекстном меню папки Базы данных (Databases) команду Присоединить базу данных (Attach Database), затем в появившемся окне нажмите кнопку Присоединить (Append) и далее укажите местоположение первичного файла данных (т.е. файла данных с расширением .mdf). Убедитесь, что база данных в папке Базы данных (Databases).

1. На панели Обозреватель объектов (Object Explorer) откройте папку Безопасность (Security) и выберите в ней строку Имена входа (Logins). При этом будет представлен список регистрационных записей MS SQL Server (в котором отображаются, например, те учетные записи домена ОС Windows, которым разрешен доступ к серверу). Создайте для MS SQL Server новую регистрационную запись ComputerName/User1 следующим образом.

Выберите в контекстном меню строки Имена входа (Logins)

команду Создать имя входа (New Login) и откройте окно

Создание имени входа (Login – New). На странице Общие (General) нажмите кнопку Найти (Search) (три точки - справа от поля ввода Имя входа (Login name), затем в окне Пользователь или Группа (User or Group) нажмите кнопку Дополнительно (Additionally), в следующем окне нажмите кнопку Поиск (Search) и выберите из списка существующую учетную запись ОС Windows с именем ComputerName/User1 и вставьте ее в поле ввода Имя входа (Login name). В качестве базы данных по умолчанию выберите созданную ранее базу данных.

На странице Роли сервера (Server Roles) установите флажок dbcreator, предоставляя тем самым пользователю с учетной записью ComputerName/User1 право создавать новые базы данных и их объекты (другими словами, включите данную учетную запись в фиксированную роль сервера dbcreator (Рисунок1).

Рисунок 1 – Создание имени входа

На странице Роли сервера (Server Roles) установите флажок dbcreator, предоставляя тем самым пользователю с учетной записью ComputerName/User1 право создавать новые базы данных и их объекты (другими словами, включите данную учетную запись в фиксированную роль сервера dbcreator

Рисунок 2

На странице Сопоставление пользователей (Database Access) установите в верхнем списке флажок( Например TRADE_ХХХ), а в нижнем списке - флажок db_accessadmin (Рисунок 3), разрешив тем самым пользователю с данной учетной записью управлять добавлением или удалением пользователей в базе данных (например TRADE_ХХХ ) (другими словами, включите данную учетную запись в фиксированную роль db_accessadmin базы данных TRADE_ХХХ).

Рисунок 3 - Включение учетной записи в фиксированную роль db_accessadmin базы данных TRADE_ХХХ.

Завершите создание регистрационной записи ComputerName/User1 щелчком на кнопке OK. Аналогичным образом создайте для SQL Server регистрационные записи ComputerName/User2, ComputerName/User3, ComputerName/User4 при этом, однако, не включайте соответствующие им учетные записи Windows ни в одну из фиксированных ролей сервера и ни в одну из фиксированных ролей базы данных TRADE_ХХХ (однако на странице Database Access флажок TRADE_ХХХ в верхнем списке должен быть установлен).

Примечание. Регистрационные записи SQL Server определяют перечень тех пользователей и групп пользователей операционной системы OC Windows, которые будут иметь доступ к данному SQL Server.

4. На панели Инспектор объектов (Object Explorer) раскройте элемент TRADE_ХХХ и далее подчиненную ему папку Security. Затем выберите появившуюся папку Пользователи (Users) и убедитесь, что сервер создал новых пользователей ComputerName/User2, ComputerName/User3, ComputerName/User4 (другими словами, отобразил учетные записи OC Windows ComputerName/User2, ComputerName/User3, ComputerName/User4 пользователей базы данных (Например, TRADE_ХХХ) с такими же именами.

5. Для базы данных (в примере TRADE_ХХХ ) создайте три пользовательские роли (Рисунок 103) (на уровне роли можно легко менять привилегии пользователей-членов роли). Для этого в контекстном меню папки Безопасность (Security) - Роли (Roles) базы данных TRADE_ХХХ, выберите команду Создать (New) - Создать роль базы данных (New Database Role) и откройте окно Роль базы данных – Создание (Database Role – New).

Рисунок 4 – Создание пользовательской роли

6. Затем введите в поле Имя роли (Role name) имя новой роли как, например Главный бухгалтер, а в поле Владелец (Owner)) - значение dbo (Рисунок 5)

Далее нажмите кнопку Добавить (Add), затем кнопку Поиск (Browse) и установите флажок в строке ComputerName/User1 (Рисунок 105), завершая выбор двумя нажатиями кнопки ОК. После этого закройте окно Роль базы данных – Создание (Database Role – New) с помощью кнопки ОК.

Рисунок 5 - Окно поиска владельца БД

Рисунок 6 – Назначение роли пользователю

Аналогичным образом создайте пользовательскую роль с именем, например Бухгалтер, проставляя флажки в строках членов роли для пользователя ComputerName/User2 и роли Главный бухгалтер

Рисунок 7 – Создание пользовательской роли с именем Бухгалтер

Аналогично можно создать и другие роли пользователей.

Далее создайте пользовательскую роль с именем Экономист, проставляя флажки в строках ComputerName/User3 и Главный бухгалтер.

Примечание1. В SQL Server любой отдельный пользователь (представляющий, в свою очередь, одного или группу пользователей ОС Windows), а также любая отдельная роль может входить в состав нескольких ролей. В свою очередь каждая роль может включать в качестве своих членов несколько пользователей и ролей. В результате пользователи наследуют привилегии, установленные во всех ролях, в которые они входят (прямо, либо косвенно через другие роли)

В нашем случае пользователь ComputerName/User1, помимо привилегий роли Главный бухгалтер, будет обладать также привилегиями ролей Бухгалтер и Экономист, в то время как пользователь ComputerName/User2 будет обладать только привилегиями роли Бухгалтер, а пользователь ComputerName/User3 - только привилегиями роли Экономист

Примечание 2. В любой базе данных автоматически создается пользователь с именем dbo (database owner), являющийся ее владельцем, также, как и все члены фиксированной роли базы данных db_owner. Пользователь dbo имеет абсолютные права по управлению базой данных и его нельзя удалить. Кроме того, пользователь dbo включен в роль db_owner и не может быть удален из нее.

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

7. Задайте привилегии для пользовательских ролей созданной базы данных. Для этого в контекстном меню роли Главный бухгалтер выберите команду Свойства (Properties) (и откройте окно Свойства ролей базы данных (Database Role Properties) – Главный бухгалтер. Перейдите на страницу Защищаемые объекты (Protected objects), выберите через кнопку Поиск (Browse) флажок Определенные объекты..., нажмите кнопку ОК, в окне поставьте галочки около Таблицы и Представления, далее нажмите кнопку Обзор, выделите все таблицы БД (В примере

TRADE_ХХХ и представление OrdersCost)

Рисунок 8 - Определение привилегий для пользовательских ролей БД

Для таблицы Currency установите привилегии Выборка (Select), Вставка (Insert), Обновление (Update), Удаление (Delete).

Для таблицы Orders установите привилегию Обновление (Update), а для представления OrdersCost - привилегию Выборка (Select).

Для каждой из таблиц Clients, Suppliers, Products установите привилегии Обновление (Update) и Удаление (Delete).

Далее, выбрав таблицу Orders, выбрав разрешение Обновление (Update) нажмите кнопку Разрешение на доступ к столбцу (Column Permissions)) и в появившемся окне запретите привилегию Обновление (Update) для столбцов OrderDate и DeliveryDate (Рисунок 9)

Рисунок 9 - Разрешения на доступ к столбцам

По аналогии задайте привилегии для других пользовательских ролей. Для роли Бухгалтер каждая из таблиц Orders, Clients должна иметь привилегии Выборка (Select) и Вставка (Insert). Для роли Экономист каждая из таблиц Suppliers, Products должна иметь привилегии Выборка (Select) и Вставка (Insert).. Кроме того, для роли public таблица Locations должна иметь привилегии Выборка (Select), Вставка (Insert), Обновление (Update), Удаление (Delete).

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

8.Завершите сеанс работы с ОС Windows под текущей учетной записью и стартуйте сеанс под учетной записью ComputerName/User1. При помощи пользовательского меню ОС Windows запустите утилиту SQL Server Management Studio. В этом случае будем говорить, что установлено соединение c сервером базы данных от имени пользователя с учетной записью ComputerName/User1. Убедитесь, что сейчас вы, применительно к базе данных TRADE_ХХХ, обладаете привилегиями роли Главный бухгалтер, т.е. вы можете выполнять все действия с таблицами Currency, Clients, Suppliers, Locations, Products, а также все действия с таблицей Orders за исключением удаления строк и обновления полей OrderDate и DeliveryDate. Также можно просматривать данные представления OrdersCost.

Примечание. Ряд привилегий оказываются доступными благодаря членству роли Главный бухгалтер в ролях Бухгалтера, Экономисты и public. Кроме того, поскольку данная учетная запись является членом фиксированной роли сервера dbcreators, то предоставлено право создавать новые базы данных и их объекты. Также предоставлено право управлять добавлением или удалением пользователей в базе данных, поскольку данная учетная запись включена в фиксированную роль db_accessadmin этой базы данных. Убедитесь также, что отсутствует, например, право редактирования структур таблиц в базе данных и право создавать в ней новые типы данных.

9. Установите соединение c сервером базы данных от имени пользователя с учетной записью ComputerName/User2. Убедитесь, что сейчас вы, применительно к базе данных (в примере TRADE_ХХХ), обладаете привилегиями роли Бухгалтер, т.е. вы можете просматривать данные и вставлять новые строки только в таблицах Orders и Clients, однако обновлять данные и удалять строки в этих таблицах вы не можете. Кроме того, вы можете выполнять все действия с таблицей Locations (соответствующие привилегии доступны через членство в роли public). К остальным таблицам, а также к представлению OrdersCost, доступ оказывается запрещен. Также отсутствуют права на выполнение и многих других действий.

10. Установите соединение c сервером базы данных от имени пользователя с учетной записью ComputerName/User3. Убедитесь, что сейчас вы, применительно к базе данных (в примере TRADE_ХХХ), обладаете привилегиями роли Экономист, т.е. вы можете просматривать данные и вставлять новые строки только в таблицах Suppliers и Products, однако обновлять данные и удалять строки в этих таблицах вы не можете. Кроме того, вы можете выполнять все действия с таблицей Locations (соответствующие привилегии доступны через членство в роли public). К остальным таблицам, а также к представлению OrdersCost, доступ оказывается запрещен. Также отсутствуют права на выполнение и многих других действий.

11. Установите соединение c сервером базы данных от имени пользователя с обычно используемой учетной записью. Сейчас вы обладаете абсолютными правами по управлению базой данных(в примере TRADE_ХХХ )(например, возможна корректировка структур таблиц базы данных, что невозможно в соединениях под учетными записями ComputerName/User1, ComputerName/User2, ComputerName/User3). В этом можно легко убедиться, если выберите на панели Инспектор объектов (Object Explorer) в дереве структуры папку Безопасность (Security) - Пользователи (Users) базы данных ( в примере TRADE_ХХХ) пользователя dbo. Затем в контекстном меню пользователя dbo необходимо выполнить команду Свойства (Properties) и открыть окно Пользователь базы данных – dbo (Database User Properties – dbo). Теперь видно, что текущая учетная запись отображается в пользователя dbo, который в свою очередь, является постоянным членом фиксированной роли базы данных db_owner, которая и предоставляет все имеющиеся привилегии и возможности.

12. Отсоедините базу данных, выбрав в ее контекстном меню команду Задачи (Tasks) - Отсоединить…(Detach Database). Убедитесь, что база данных TRADE_ХХХ исчезла из папки Базы данных (Databases). Теперь файлы этой базы данных можно, при необходимости, переместить в другую папку на жестком диске или копировать на другой носитель данных.

13. Для каждого действия сделать необходимые скрины и объяснить.

Управление пользователями и правами доступа к данным с помощью команд языка SQL

На панели Инспектор объектов (Object Explorer) откройте папку Безопасность (Security) и выберите в ней Имена входа (Logins). При этом будет представлен список регистрационных записей MS SQL Server.

Задание 1. Создание имени входа на основе учетной записи домена Windows Для версий MS SQL Server до 2012 включительно можно использовать системную процедуру:

sp_grantlogin [@loginame =] 'login'

- Пользователь или группа Windows должны быть дополнены именем домена Windows в формате Domain\User. Создайте три новых имени входа на основе существующих в домене Windows.

EXEC sp_grantlogin ’ИмяДомена\User1’

EXEC sp_grantlogin ’ИмяДомена\User2’

EXEC sp_grantlogin ’ИмяДомена\User3’

GO

Отмена доступа к серверу учетной записи выполняется с помощью хранимой процедуры sp_droplogin, имеющей синтаксис:

sp_droplogin [ @loginame = ] 'login'

Использование системной процедуры является устаревшим и не поддерживается в современных версиях MS SQL Server.

Для создания имени входа на основе учетной записи домена Windows используйте следующую команду:

CREATE LOGIN ИмяДомена\User1 FROM WINDOWS;

CREATE LOGIN ИмяДомена\User2 FROM WINDOWS;

CREATE LOGIN ИмяДомена\User3 FROM WINDOWS;

GO

Для удаления существующего имени входа используйте команду:

DROP LOGIN login_name

Задание 2. Добавление учетной записи в фиксированную роль сервера

Синтаксис системной хранимой процедуры:

sp_addsrvrolemember [@loginame=] 'login' , [ @rolename = ] 'role

К предопределенной роли сервера dbcreator добавляется имя входа ОС MS Windows ’ИмяДомена\ User1:

EXEC sp_addsrvrolemember ’ИмяДомена\ User1’, ’dbcreator’

GO

Удаление учетной записи из фиксированной роли сервера выполняется с помощью хранимой процедуры sp_dropsrvrolemember, имеющей синтаксис:

sp_dropsrvrolemember [@loginame=] 'login' , [@rolename =] 'role

Задание 3. Добавление нового пользователя в текущую базу данных

Синтаксис системной хранимой процедуры:

sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name in db' [OUTPUT]]

К текущей базе данных добавляются новые пользователи с именами User1, User2, User3:

EXEC sp grantdbaccess ’ИмяДомена\User1’, 'User1'

EXEC sp grantdbaccess ’ИмяДомена\User2’, 'User2'

EXEC sp grantdbaccess ’ИмяДомена\User3’, 'User3'

Хранимая процедура sp_grantdbaccess вызывает команду CREATE USER. В современных версиях MS SQL Server необходимо пользоваться этой

командой. Для добавления к текущей базе данных пользователя базы данных для имени входа Windows ’ИмяДомена\User1’, используется инструкция CREATE USER. Новому пользователю присваивается имя User1.

CREATE USER User1 FOR LOGIN [ИмяДомена\User1];

GO

Удаление пользователя выполняется с помощью системной хранимой процедуры, имеющей синтаксис:

sp_revokedbaccess [ @name_in_db = ] 'name'

Удаление пользователя из текущей базы данных с помощью команды

DROP USER: DROP USER user_name

Задание 4. Создание пользовательской роли

Синтаксис системной хранимой процедуры создания пользовательской роли:

sp_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ]

Создание трех пользовательских ролей базы данных с помощью системной процедуры и назначение им существующих пользователей базы данных:

EXEC sp_addrole ’Главный бухгалтер’, ’User1’

EXEC sp_addrole ’Бухгалтер’, ’User2’

EXEC sp_addrole ’Экономист’, ’User3’

GO

Создание трех пользовательских ролей базы данных с помощью команды CREATE ROLE и назначение им существующих пользователей базы данных:

CREATE ROLE Главный бухгалтер AUTHORIZATION User1;

CREATE ROLE Бухгалтер AUTHORIZATION User2;

CREATE ROLE Экономист AUTHORIZATION User3;

GO

Удаление пользовательской роли выполняется с помощью системной хранимой процедуры:

sp_droprole [@rolename =] 'role' или команды DROP ROLE role_name

Задание 5. Добавление нового члена в роль (как фиксированную, так и пользовательскую) базы данных

Синтаксис системной хранимой процедуры:

sp_addrolemember [@rolename =] 'role', [@membername =] 'securityaccount

Добавление новых членов в роль:

EXEC sp_ addrolemember ’db_accessadmin’, ’User1’

EXEC sp_ addrolemember ’Главный бухгалтер’, ’User1’

EXEC sp_ addrolemember ’Бухгалтер’, ’User2’

EXEC sp_ addrolemember ’Бухгалтер’, ’Главный бухгалтер’

EXEC sp_ addrolemember ’Экономисты’, ’User3’

EXEC sp_ addrolemember ’Экономист’, ’Главный бухгалтер’

GO

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

sp_droprolemember [@rolename =] 'role', [@membername =] ' securityaccount

Задание 6. Предоставление прав доступа к объектам базы данных Синтаксис системной хранимой процедуры:

GRANT { ALL [ PRIVILEGES ] |permission [ ,...n ]}

{

[( column [ ,...n ] ) ] ON { table | view}

|ON { table | view } [ ( column [ ,...n ])]

|ON { stored_procedure | extended_procedure}

|ON { user_defined_function } }

TO security account [ ,...n ]

[ WITH GRANT OPTION ]

[ AS { group | role } ]

Назначение прав доступа к объектам базы данных:

GRANT SELECT, INSERT, UPDATE, DELETE ON Currency TO [Главный бухгалтер] WITH GRANT OPTION

GRANT UPDATE ON Orders TO [Главный бухгалтер] WITH GRANT OPTION

GRANT SELECT ON Запрос1 TO [Главный бухгалтер] WITH GRANT OPTION GRANT UPDATE, DELETE ON Clients TO [Главный бухгалтер] WITH GRANT OPTION

GRANT UPDATE, DELETE ON Поставщик TO [Главный бухгалтер] WITH GRANT OPTION

GRANT UPDATE, DELETE ON Products TO [Главный бухгалтер] WITH GRANT OPTION

GRANT SELECT, INSERT ON Orders TO Бухгалтер

GRANT SELECT, INSERT ON Clients TO Бухгалтер

GRANT SELECT, INSERT ON Поставщик TO Экономист

GRANT SELECT, INSERT ON Products TO Экономист

GRANT SELECT, INSERT, UPDATE, DELETE ON Locations TO public

GO

Примечание. С помощью ключевых слов WITH GRANT OPTION пользователям, указанным с помощью строки TO security_account [,...n] будет предоставлено право выдавать другим пользователям разрешения доступа, аналогичные выданным им самим.

Задание 7. Запрещение доступа к объектам базы данных

Синтаксис системной хранимой процедуры:

DENY {ALL [PRIVILEGES] |permission [ ,...n ]}

{

[( column [ ,...n ] ) ] ON { table | view} |ON { table | view }

[ ( column [ ,...n ])]

|ON { stored_procedure | extended_procedure}

|ON { user_defined_function }

}

TO securityaccount [ ,...n ]

[CASCADE]

Запрещение права доступа к объектам базы данных:

DENY UPDATE ON Orders (ДатаOrdersа, СрокПоставки) TO [Главный бухгалтер] CASCADЕ

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

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

Неявное отклонение доступа не мешает получить доступ к объекту на другом уровне, например, через членство в некоторой роли.

Для неявного отклонения доступа используется команда REVOKE, имеющая следующий синтаксис:

REVOKE [GRANT OPTION FOR]

{ALL [PRIVILEGES ] | permission [ ,... n ]}

{

[( column [ ,...n ] ) ] ON { table | view}

|ON {table | view} [(column [ ,...n ])]

|ON {stored_procedure | extended_procedure}

} ON {user_defined_function}

{TO|FROM } security account [ ,...n]

[CASCADE]

[ AS { group | role } ]

14. Для каждого действия сделать необходимые скрины и объяснить.

15. Оформить отчет

Содержание отчета

1. Ответы на контрольные вопросы

2. Выполнение работы в соответствии с методическими рекомендациями.

3. Необходимые скрины и пояснения.

4. Вывод по работе