04 Система безопасности сервера баз данных

1. Управление базами данных

Система безопасности

2. Система безопасности сервера баз данных

Необходимость в системе
безопасности - проблемы:
Кража информации
Злонамеренное повреждение
информации
Случайное повреждение информации

3.

Principal – сущность, которая может запросить ресурс сервера, имеет SID. Уровни: Windows, сервер БД, БД.
Securable – ресурс, к которому может быть предоставлен доступ сервером БД. Контексты доступа: сервер, БД, схема.
Endpoint – точка подключения к MS SQL Server

4. Разграничение прав пользователей СУБД

Контекст
идентификатора
пользователя
Сервер БД
Клиенты
Права
БД

5. Уровни безопасности

Уровень базы данных – доступ к объектам
конкретной базы данных
БД
Сервер БД
Уровень сервера – разрешение или
отклонение доступа к самому серверу

6. Уровень сервера

Аутентификация – проверка подлинности пользователя
Имя пользователя (имя учетной записи) – пользователь
СУБД называет себя
Пароль – пользователь подтверждает, что он тот, за кого себя
выдает
Режимы аутентификации MS SQL Server:
Смешанный (mixed) – SQL Server или Windows (стандартный
или интегрированный в зависимости от имени пользователя)
Только Windows (интегрированный режим)
имя
пароль
БД
Сервер БД

7. Средства аутентификации Windows

Учетная запись:
Имя
Пароль
Членство в группах
Каталог по умолчанию
Уникальный идентификатор (генерируется)
Уникальный идентификатор = login identifier = login ID =
Security Identification = SID
Список прав доступа к ресурсу = Access Control List = ACL

8. Средства аутентификации Windows

Windows
Ресурс
Ресурс
SID, разрешения
SID, разрешения
ACL
ACL
SQL Server
Учетные записи
SQL Server
Учетные записи
Windows
Преимущества:
Более развитая
система
безопасности
Не надо повторно
проходить
аутентификацию

9. Средства аутентификации SQL Server

локальная сеть
Сервер БД
Windows
SQL Server
имя
пароль
Система
аутентификации
Система
аутентификации

10. Средства аутентификации SQL Server

Учетная запись в SQL Server:
Имя / name
Тип / type {Windows User | Windows Group |
Standard}
Доступ к серверу / Server Access
БД по умолчанию / Default Database
Язык по умолчанию / Default Language
Пароль / Password
При установке: SA без пароля и BUILT IN\Administrators

11. Команда создания учетной записи

-- SQL Server Syntax
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }
<option_list1> ::=
PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]
<option_list2> ::=
SID = sid
| DEFAULT_DATABASE =database
| DEFAULT_LANGUAGE =language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
| CREDENTIAL =credential_name
<sources> ::=
WINDOWS [ WITH <windows_options>[ ,... ] ]
| CERTIFICATE certname
| ASYMMETRIC KEY asym_key_name
<windows_options> ::=
DEFAULT_DATABASE =database
| DEFAULT_LANGUAGE =language
CREATE LOGIN p1 WITH PASSWORD ='p1', CHECK_EXPIRATION = OFF,
CHECK_POLICY = off, DEFAULT_DATABASE = master

12. Роли сервера

Предоставление прав по администрированию сервера БД
Фиксированные роли сервера (fixed server roles)
public – это роль любой учетной записи сервера. Если нужно дать разрешение
всем учетным записям сервера, то это можно сделать назначив разрешение
этой роли. Отменить членство в public не возможно.
sysadmin – все права
setupadmin – конфигурирование хранимых процедур для запуска
serveradmin – конфигурирование и выключение сервера
securityadmin – создание и удаление учетных записей
processadmin – управление процессами на сервере
diskadmin – управление файлами баз данных
dbocreator – создание новых БД
Хранимые процедуры для управления ролями:
sp_addsrvrolemember [ @loginame = ] 'login'
, [ @rolename = ] 'role'
sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role’
Пример:
EXEC sp_addsrvrolemember 'p1', 'securityadmin'
В последних версиях SQL имеется команда (with name – переименовать пользовательскую роль сервера)
ALTER SERVER ROLE server_role_name
{
[ ADD MEMBER server_principal ]
| [ DROP MEMBER server_principal ]
ALTER SERVER ROLE server_role_name ADD MEMBER login;
| [ WITH NAME = new_server_role_name ]
ALTER SERVER ROLE server_role_name DROP MEMBER login;
}

13. Уровень базы данных

Пользователь базы данных – административная единица
системы безопасности, через которую осуществляется доступ
учетной записи к объектам данных
Учетная запись
Ivanov
*********
1234321234123 (SID)
Права на уровне
сервера
Сервер БД
DB1
Пользователь базы
данных DB1
IvanovUser
1234321234123 (SID)
Права на уровне
базы данных DB1
DB2
Пользователь базы
данных DB2
Ivn
1234321234123 (SID)
Права на уровне
базы данных DB2

14. Уровень базы данных

Пользователь БД:
Имя / name
Имя учетной записи / login name
(в дальнейшем используется SID)
Роль в БД / role
По умолчанию создаются пользователи БД:
dbo – соответствует учетная запись под которой
создавалась БД. Последнего dbo удалить невозможно.
guest – любая учетная запись отображается в этого
пользователя, если нет доступа к БД (нет сопоставления
пользователя для login). Обычно для guest запрещено
подсоединение к БД.
Примечание для лабораторной работы. Для исследования роли приложения
нужно учетной записи без соответствующего пользователя БД подключиться
к БД. Для этого надо разрешить пользователю guest подключаться к БД –
выполнить команду grant connect to guest

15. Уровень базы данных

Типы ролей на уровне базы данных:
Фиксированные роли БД / fixed database roles
Пользовательские роли / user database roles
Роль приложения / application role

16. Фиксированные роли БД

public – роль любого пользователя БД. Разрешения и запреты для этой
роли наследуются всеми пользователями БД.
db_securityadmin – управление правами доступа к БД
db_owner – любые права, т.к. имеются права владельца
db_denydatawriter – запрещение изменения данных
db_denydatareader – запрещение чтения данных
db_ddladmin – создавать и управлять объектами
db_datawriter – может изменять данные
db_datareader – не может изменять данные
db_backupoperator – выполнение резервного копирования
db_accessadmin – управление пользователями БД

17. Пользовательские роли и роль приложения

DB1
Пользовательские
роли
Standard Role
Фиксированные роли
Роли приложения
Application Role
...
db_securityadmin
db_owner
фиксированные права
Lecturer
набор прав
Student
SmartExam
набор прав
набор прав
Пароль: ********
PetrovUser
IvanovUser
Пользователи БД

18. Создание и удаление пользователей и ролей БД

CREATE USER p1 FOR LOGIN p1
DROP USER p1
CREATE ROLE p1p2
DROP ROLE p1p2
-- только пустая роль (без пользователей) может быть удалена
EXEC sp_addrolemember 'p1p2', 'p1'
EXEC sp_droprolemember 'p1p2', 'p2'
CREATE APPLICATION ROLE approle WITH PASSWORD = 'approle'
DROP APPLICATION ROLE approle
-- Предоставление права чтения из Exemplars роли приложения approle.
-- Команда GRANT будет рассмотрена позже.
GRANT SELECT ON Exemplars TO approle
В последних версиях SQL Server имеется команда (with name – переименование роли, но не фиксированной)
ALTER ROLE role_name
{
ADD MEMBER database_principal
CREATE ROLE Sales;
| DROP MEMBER database_principal
ALTER ROLE Sales ADD MEMBER Barry;
| WITH NAME = new_name
ALTER ROLE Sales DROP MEMBER Barry
}

19. Порядок использования роли приложения

: Application
Server : MS Sql Server
: Actor
Запрос соединения(login, passward)
Аутентификация
Соединение
Права для учетной
записи login
USE DB1
Существующий пользователь БД
или guest (фиксированная роль Public)
sp_setapprole 'SmartExam' 'a_r_passward'
Предоставление прав
Действия в рамках прав приложения
USE library
SELECT * FROM exemplars
PRINT user
EXEC sp_setapprole 'approle', 'approle'
SELECT * FROM exemplars
PRINT user
Права для роли
приложения 'SmartExam'
sp_unsetapprole @cookie;

20. Порядок использования роли приложения

DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM' , @fCreateCookie =
true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- This will return the name of the application role, Sales11.
EXEC sp_unsetapprole @cookie;
-- The application role is no longer active.
-- The original context has now been restored.
GO
SELECT USER_NAME();
-- This will return the name of the original user.
GO

21. Права

Разрешение (предоставление) прав –
команда GRANT
Запрещение прав – команда DENY
Отклонение прав – команда REVOKE
Минимальные права у пользователя БД после создания – Public.
Права выдаются администратором сервера БД, владельцем БД или
владельцем объектов БД.
Набор прав пользователя определяется ролями (фиксированными или
пользовательскими) и правами выданными непосредственно
пользователю БД.

22. Предоставление прав

Категории прав:
Права доступа к объектам базы данных
Права на выполнение команд TransactSQL
Неявные права (роли сервера и
владельца БД)

23. Права доступа к объектам базы данных

GRANT
{ ALL | 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 } ]

24. Права доступа к объектам базы данных

Права (permission)
INSERT – вставка в таблицу или представление
UPDATE – изменение данных таблиц и
представлений, а также столбца
DELETE – для таблицы и представления
SELECT – выборка из таблицы, представления,
столбца
EXECUTE – разрешение запуска хранимой
процедуры. Право на изменение хранимой
процедуры принадлежит ее владельцу и не может
быть предоставлено

25. Права доступа к объектам базы данных

security_account – пользователь БД, пользовательская роль,
пользователь Windows, группа Windows. Если создать
пользователя БД для пользователя или группы Windows, то он
получает указанные права.
WITH GRANT OPTION – пользователю, которому выдаются
права, разрешается предоставлять права
AS { group | role } – группа Windows или роль БД, которой
выдано разрешение предоставлять разрешения и которой
принадлежит пользователь, выдающий разрешение
(выполняющий оператор WITH GRANT OPTION)

26. Права на исполнение команд Transact-SQL

GRANT { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]
Значения statement :
CREATE DATABASE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE – резервное копирование БД
BACKUP LOG – резервное копирование журнала транзакций
Примечание для лабораторной работы. В последних версиях MS SQL Server
если выдать разрешение на создание таблицы пользователю БД, то он не
сможет создать таблицу - надо ему предварительно выдать разрешение на
изменение схемы, например, grant alter schema::dbo to user2. В методических
указаниях об этом не написано.

27. Примеры предоставления прав

-- Materials – таблица, Engineer – роль
-- Администратор выдает разрешение
GRANT SELECT, INSERT ON Materials TO Engineer WITH GRANT
OPTION
-- Valentin – имеет роль Engineer,
-- Liss – не имеет роль Engineer.
-- Valentin выдает разрешение
GRANT SELECT, INSERT ON Materials TO Liss AS Engineer
-- Mary, John – пользователи БД,
-- [Corporate\BobJ] – член группы Windows
GRANT CREATE DATABASE, CREATE TABLE
TO Mary, John, [Corporate\BobJ]

28. Запрещение и отклонение прав

Запрещение прав – запрещение, не зависимо от
уровня, на котором выдано разрешение
Приоритеты значений:
запрещено – высший
разрешено – средний
не разрешено - низший
Предоставлен доступ к таблице Secret
Запрещен доступ к таблице Secret
Stuff
Ivanov
Petrov
NewStuff
Sidorov
Kuznetchov
Таблица Secret не доступна

29. Запрещение прав

Запрещение доступа к объектам базы данных
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 security_account [ ,...n ]
[ CASCADE ]
Запрещение права выполнения команд Transact-SQL
DENY { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]
CASCADE – права отнимаются (и в Revoke тоже отнимаются, но не
запрещаются) еще и у других пользователей,
которым данный дал права

30. Пример запрещения

-- Разрешение
GRANT CREATE TABLE TO Petrov
WITH GRANT OPTION
-- Запрет пользователю БД.
-- Отнять разрешение у тех пользователей,
-- которым он выдал разрешение
DENY CREATE TABLE TO Petrov
CASCADE

31. Отклонение прав

Отклонение (не разрешено) – это состояние отсутствия
явного запрещения и явного разрешения и соответствует
запрещению, если нет разрешения.
Предоставлен доступ к таблице Secret
Отклонен доступ к таблице Secret
Stuff
Ivanov
Petrov
Sidorov
Kuznetchov
Таблица Secret доступна

32. Отклонение прав

Отклонение доступа к объектам базы данных
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 } ]
Отклонение права выполнения команд Transact-SQL
REVOKE { ALL | statement [ ,...n ] }
FROM security_account [ ,...n ]

33. Отклонение прав

GRANT OPTION FOR и CASCADE
используются для того, чтобы
отклонить право выдавать права,
выданное при помощи WITH GRANT
OPTION в команде GRANT без
изменения прав на объекты БД.
FROM, TO – синонимы.

34. Примеры отклонения прав

-- Отклонение запрещения (здесь TO, а не FROM)
REVOKE SELECT ON Budget_Data TO Mary
-- отклонение для Joe выдавать права без изменения
-- его прав доступа (на основании принадлежности
-- текущего пользователя роли SalesManager)
REVOKE GRANT OPTION FOR INSERT ON Customer FROM Joe AS
SalesManager
-- здесь скобки [] нужны для константы со спецсимволом
REVOKE CREATE TABLE FROM Joe, [Corporate\BobJ]
REVOKE CREATE TABLE, CREATE DEFAULT FROM Mary, John

35. Конфликты доступа

Отмена доступа имеет самый низкий
приоритет, а запрещение – самый высокий
Просмотр разрешений – хранимая процедура
sp_helprotect <объект>, <пользователь>, <роль>
Все пользователи имеют разрешение на ее выполнение.

36. Неявные права

Права фиксированных ролей сервера и фиксированных
ролей базы данных не указываются явно (с помощью
GRANT, DENY, REVOKE), поэтому их называют
неявными правами.
Права владельца объекта БД тоже не явные. Если
пользователь создал объект БД, то пользователь
становится владельцем этого объекта: у него,
появляются права выполнения любых действий с этим
объектом и право передачи прав на этот объект другим
пользователям.

37. Хранимые процедуры и представления – инструмент обеспечения безопасности

Представление и хранимая процедура, созданная владельцем БД
CREATE VIEW ReadersView
AS SELECT r.last_name + ' ' + r.first_name AS reader_name FROM Readers r
CREATE PROC [dbo].[GetReaders]
AS
SELECT * FROM Readers
Учетная запись и пользователь БД – без доступа к таблице Readers
CREATE LOGIN test_proc_login WITH PASSWORD ='test_proc_login',
CHECK_EXPIRATION = OFF, CHECK_POLICY = off, DEFAULT_DATABASE = Library
CREATE USER test_proc_login FOR LOGIN test_proc_login
но разрешение на просмотр представления и запуск хранимой процедуры
GRANT SELECT ON [dbo].[ReadersView] TO test_proc_login
GRANT EXECUTE ON [dbo].[GetReaders] TO test_proc_login

38. Хранимые процедуры и представления – инструмент обеспечения безопасности

Безопасно: пользователь может выполнить
хранимую процедуру (представление), но не
может выполнить какие-либо действия, не
предусмотренные в процедуре
(представлении ).