Лабораторная работа №4 Управление ролями и разрешениями в ms sql Server

Создание ролей программно

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

Создадим для нашего примера роли библиотекаря (LIBRAR) и читателя (READER).

Пример создания роли библиотекаря:

USE Demo_DataBase --сделать текущей БД Demo_DataBase

EXEC sp_addrole 'LIBRAR'

Эти операторы набрать на странице, вызванной нажатием кнопки <New Query> (<Новый запрос>) или через пункт менюFile/ New/ Query in current connection или нажатием комбинации клавиш <CTRL>+ <N>.

Для запуска команд на выполнение нажать <F5>.

Повторный запуск тех же команд сгенерирует ошибки типа «В БД уже существует роль LIBRAR».

Пример создания роли читателя:

USEDemo_DataBase--сделать текущей БДDemo_DataBase

EXEC sp_addrole 'READER'

Библиотекарь должен обладать правами на чтение, удаление, изменение, добавление во все таблицы БД Demo_DataBase, а также должен иметь возможность запускать на исполнение процедуры и функции БДDemo_DataBase. Поэтому роли библиотекаря из системных привилегий назначаемEXECUTE, а из привилегий доступа к объектам назначаем DELETE, INSERT, UPDATE, SELECT.

Читатель должен обладать правами на чтение из таблиц ADMIN_BOOKS.AUTHORS, ADMIN_BOOKS.BOOKS, ADMIN_BOOKS.PUBLISHING_HOUSE. Поэтому роли читателя из привилегий доступа к объектам назначаем SELECT.

Оператор представления привилегий

Синтаксис:

grant<привилегия>,...

ON < объект >, …

TO <имя>

[with grant option];

Атрибут with grant optionдает право пользователю самому раздавать права, которые он получил.

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

Пример: grant select, update (Sales, num) ON Sales_data TO user1

with grant option

Пользователь, предоставивший привилегию другому называется грантор (grantor — предоставитель). Привилегия является предоставляемой, если право на нее можно предоставить другим пользователям.

PUBLIC— имя роли, которую получает пользователь при добавлении в список пользователей конкретной БД, включает в себя минимальный набор прав на чтение данных из таблиц и представлений в БД.

Роль библиотекаря названа LIBRAR. Операторы назначения прав доступа для этой роли представлены ниже:

GRANT DELETE, INSERT, UPDATE, SELECT ON BOOKS TO LIBRAR

GRANT DELETE, INSERT, UPDATE, SELECT ON AUTHORS TO LIBRAR

GRANT DELETE, INSERT, UPDATE, SELECT ON DELIVERIES TO LIBRAR

GRANT EXECUTE TO LIBRAR

 

Роль читателя названа READER. Операторы назначения прав доступа для этой роли представлены ниже:

GRANT SELECT ON BOOKS TO READER

GRANT SELECT ON AUTHORS TO READER

GRANT SELECT ON PUBLISHING_HOUSE TO READER

Создание пользователей с определенной ролью

Пример создания библиотекаря Ivanov_Lib и присвоения ему роли:

Exec sp_addlogin 'Ivanov_Lib','Ivanov', 'Demo_DataBase'

use Demo_DataBase

Exec sp_adduser 'Ivanov_Lib','Ivanov_Lib'

EXEC sp_addrolemember 'LIBRAR', 'Ivanov_Lib'

 

Пример создания читателя Petrov_Read и присвоения роли:

Exec sp_addlogin 'Petrov_Read','Petrov', 'Demo_DataBase'

use Demo_DataBase

Exec sp_adduser 'Petrov_Read','Petrov_Read'

EXEC sp_addrolemember 'READER', 'Petrov_Read'

Оператор отмены привилегий

Синтаксис отмены привилегий:

Revoke[with grant option]

< привилегии >,…

ON < объект >,…

FROM <имя_пользователя>;

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

Пример:

REVOKE SELECT ON AUTHORS FROM READER

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

Revoke <список ролей> from <список пользователей>.

Пример:

use Demo_DataBase

EXEC sp_droprolemember 'READER', 'Petrov_Read'

Варианты заданий к лабораторной работе №4

Общие положения

В утилите SQL Server Management Studio выполнить примеры, которые даны по ходу работы.

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

В утилите SQL Server Management Studio создать под каждое должностное лицо соответствующую роль, наделить эту роль определенными привилегиями. Далее создать по одному пользователю на каждую должность и присвоить им соответствующие роли.

Сохранить последовательно операторы. Операторы создания ролей, привилегий и пользователей сохранить в сетевой папке.

Справочная информация

Для просмотра служебной информации можно обратиться к системным представлениям, как показано на рис. 4.5. в разделе Object Explorer. Также на рис. 4.5. показаны три команды и результат их выполнения, которые позволяют увидеть списки созданных пользователей и ролей всех БД; объектов, созданных на сервере; учетных записей сSQLServerаутентификацией.

Лабораторная работа №4 Управление ролями и разрешениями в ms sql Server

Список системных процедур и команд, которые позволяют реализовать политику разделения прав между пользователями БД.

Название встроенной процедуры	Описание
sp_grantlogin	– позволяет использовать пользователей или группы ОС для соединения с Microsoft SQL Server™, используя Windows Authentication. Этот пример позволяет пользователю WindowsNTCorporate\BobJсоединяться сSQLServer. EXEC sp_grantlogin 'Corporate\BobJ'
sp_defaultdb	Изменяет для пользователя БД по умолчанию Этот пример настраит БД по умолчанию pubsдля пользователяVictoria. EXEC sp_defaultdb 'Victoria', 'pubs'
sp_grantdbaccess	Добавляет учетную запись из раздела securityв текущую БД, для учетных записейMicrosoftWindowsтакже дает разрешение на доступ к текущей БД. Синтаксис: EXEC sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name_in_db' [OUTPUT]] Этот пример добавляет учетную запись Corporate\GeorgeWв текущую БД и присваивает псевдоним внутри БДGeorgie. EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie'
sp_revokedbaccess	Удаляет информацию об учетной записи из текущей БД. Синтаксис: EXEC sp_revokedbaccess [ @name_in_db = ] 'name' Этот пример удаляет учетную запись Corporate\GeorgeWиз текущей БД. EXEC sp_revokedbaccess 'Corporate\GeorgeW'
sp_addrole	Создает новую роль в текущей БД. Этот пример создает новую роль в текущей БД с названием Managers. EXEC sp_addrole 'Managers'
sp_addrolemember	В текущей БД назначает роль конкретному пользователю. Пример A. Этот пример добавляет учетную запись Corporate\JeffL из Windows NT в БД Sales как пользователя Jeff. Jeff затем получает роль Sales_Managers в БД Sales. USE Sales --сделать текущей БД Sales GO –выполнить команду, а потом запустить следующую EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff' GO EXEC sp_addrolemember 'Sales_Managers', 'Jeff' Пример B. Этот пример добавляет пользователя SQLServerс именемMichaelк ролиEngineeringв текущей БД. EXEC sp_addrolemember 'Engineering', 'Michael'
sp_helprotect	Показывает список привилегий, ассоциированных с ролью.
sp_helprolemember	Показывает список пользователей БД, входящих в указанную роль
sp_addsrvrolemember	Присвоение встроенной серверной роли для существующей учетной записи sp_addsrvrolemember [ @loginame = ] 'login', [ @rolename = ] 'role' Например: sp_addsrvrolemember 'Admin_DB', 'sysadmin'
sp_dropsrvrolemember	Удаление встроенной серверной роли для учетной записи или группы sp_dropsrvrolemember [ @loginame = ] 'login', [ @rolename = ] 'role' Например: sp_dropsrvrolemember 'Admin_DB', 'sysadmin'
sp_helpsrvrole	Описание только встроенных ролей в SQL Server sp_helpsrvrole [ [ @srvrolename = ] 'role' ] Например: sp_helpsrvrole 'sysadmin'
sp_helpsrvrolemember	Возвращает список ролей и учетных записей, которым присвоены эти роли sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ] Например: sp_helpsrvrolemember 'sysadmin'
sp_srvrolepermission	Возвращает список ролей и разрешений, которые присвоены этим ролям sp_srvrolepermission [[@srvrolename =] 'role'] Например: sp_srvrolepermission 'sysadmin'
sp_addlogin sp_adduser	Создание новой учетной записи в SQL Server в разделе Sequrity: sp_addlogin [ @loginame = ] 'login' [, [ @passwd = ] 'password' ] [, [ @defdb = ] 'database' ] [, [ @deflanguage = ] 'language' ] [, [ @sid = ] sid ] [, [ @encryptopt = ] 'encryption_option' ] Например: sp_addlogin 'login1',sysname, 'DB_Books' Создает пользователя в SQL Server без PUBLIC в БД ' DB_Books'. Нужно еще использовать sp_adduser [ @loginame = ] 'login' [, [ @name_in_db = ] 'user' ] [, [ @grpname = ] 'group' ], Пример: Создана база данных DB_Books. В ней создан пользователь Admin_DB с серверной ролью sysadmin, с ролью в БД db_owner. Cоздать в QueryAnalyzer нового пользователя с именем Public_ и паролем Public_1 (пароль не должен совпадать с именем пользователя) с помощью следующих команд (не забудьте нажать F5 для запуска команд на выполнение): Exec sp_addlogin 'Public_','Public_1', 'DB_Books' use DB_Books Execsp_adduser 'Public_','Public_' В БД DB_Books создан пользователь Public_ с ролью в БД DB_Books public.
Deny (отрицание)	Этот пример запрещает несколько системных привилегий для нескольких пользователей. Пользователи не могут использовать системные привилегии CREATEDATABASEorCREATETABLE, если они не наделены имим через командуGRANT. Пример: DENY CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] DENY SELECT, INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom
Grant (предоставлять)	This example grants multiple statement permissions to the users Mary and John, and the Corporate\BobJ Windows NT group. GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] Назначение разрешения на выборку (SELECT) для ролиPUBLICв таблицеAuthors: GRANT SELECT ON Authors TO public
Revoke (отменять)	This example revokes multiple statement permissions from multiple users. REVOKE CREATE TABLE, CREATE DEFAULT FROM Mary, John This example removes the denied permission from Mary and, through the SELECT permissions applied to the Budget role, allows Mary to use the SELECT statement on the table. REVOKE SELECT ON Budget_Data TO Mary

 

Создание пользователей на доступ к серверу через утилиту Microsoft SQL Server Management Studio/

Рис. 1. Раздел Security для работы с пользователями и создание нового пользователя (при SQL Server аутентификации нужно снять галочки сEnforce password policy)

Рис. 2. Настройка серверной роли для нового пользователя (весь список серверных ролей с их привилегиями в конце работы)

Рис. 3. Настройка роли базы данных для нового пользователя (весь список ролей баз данных с их привилегиями ниже)

 

Перечень ролей БД:

Public – минимальные права доступа к БД (на просмотр)

Db_owner– может выполнять любые действия с БД

Db_accessadmin– добавляет и удаляет пользователей БД

Db_sequrityadmin– управляет ролями в БL и разрешениями на запуск команд и работу с объектами БД

Db_ddladmin– добавляет, изменяет и удаляет объекты БД

Db_backupoperator– осуществляет резервное копирования БД

Db_datareader– может просматривать все данные в каждой таблице в БД

Db_datawriter- может добавлять, удалять и изменять данные в каждой таблице в БД

Db_denydatareader– запрет на просмотр всех данных в каждой таблице в БД

Db_denydatawriter- запрет на добавление, удаление и изменение всех данных в каждой таблице в БД

Рис. 4. Разблокирование создаваемой учетной записи

 

После нажатия на <OK> в БД появится пользовательProbaс правами собственника БД, который может выполнять все манипуляции с БДDemo_DataBase.

Создание ролей программно

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

Создадим для нашего примера роли библиотекаря (LIBRAR) и читателя (READER).

Пример создания роли библиотекаря:

USE Demo_DataBase --сделать текущей БД Demo_DataBase

EXEC sp_addrole 'LIBRAR'

Эти операторы набрать на странице, вызванной нажатием кнопки <New Query> (<Новый запрос>) или через пункт менюFile/ New/ Query in current connection или нажатием комбинации клавиш <CTRL>+ <N>.

Для запуска команд на выполнение нажать <F5>.

Повторный запуск тех же команд сгенерирует ошибки типа «В БД уже существует роль LIBRAR».

Пример создания роли читателя:

USEDemo_DataBase--сделать текущей БДDemo_DataBase

EXEC sp_addrole 'READER'

Библиотекарь должен обладать правами на чтение, удаление, изменение, добавление во все таблицы БД Demo_DataBase, а также должен иметь возможность запускать на исполнение процедуры и функции БДDemo_DataBase. Поэтому роли библиотекаря из системных привилегий назначаемEXECUTE, а из привилегий доступа к объектам назначаем DELETE, INSERT, UPDATE, SELECT.

Читатель должен обладать правами на чтение из таблиц ADMIN_BOOKS.AUTHORS, ADMIN_BOOKS.BOOKS, ADMIN_BOOKS.PUBLISHING_HOUSE. Поэтому роли читателя из привилегий доступа к объектам назначаем SELECT.