Использование разрешений SQL Server

Версия SQL Server 2005 была первой крупной версией SQL Server, выпущенной после появления достопамятного документа о Trustworthy Computing Билла Гейтса в 2002 году (http://www.computerbytesman.com/security/billsmemo.htm). После этого документа подход и отношение компании к безопасности продуктов изменились, как и компоненты безопасности SQL Server 2005. В результате SQL Server 2012 и 2014 отличаются изначально чрезвычайно высокой защищенностью, достигнутой благодаря развитию этих компонентов и их усилению в каждом новом выпуске. Но сохранять высокий уровень защиты по мере добавления баз данных и предоставления доступа пользователям — трудная задача.

Одним из самых значительных изменений в 2005 году была новая инфраструктура разрешений при проверке подлинности серверов. Ушли в прошлое времена, когда требовалось назначать пользователям системные роли, чтобы предоставить им единственное разрешение. Сегодня система разрешений отличается глубокой детализацией и позволяет назначать минимальные разрешения, необходимые субъекту безопасности для выполнения работы, в соответствии с принципом предоставления минимальных прав.

При использовании разрешений следует учитывать три основных фактора:

• Кто должен получить разрешение?
• На какой объект должно распространяться разрешение?
• Разрешения какого вида должны быть выданы?

Основная идея разрешений состоит в предоставлении разрешения определенного типа для какого-нибудь объекта или объектов в экземпляре SQL Server некоторому субъекту безопасности.

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

Субъекты безопасности: кто должен получить разрешение?

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

Субъекты безопасности уровня Windows

• Имя входа домена или локальное имя входа Windows;
• Группа Windows.

Субъекты безопасности уровня SQL Server

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

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

Объект разрешения

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

Рисунок. Иерархия защищаемых объектов в экземпляре SQL Server

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

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

Инструкции разрешения

После знакомства с субъектами безопасности и защищаемыми объектами самое время рассмотреть собственно разрешения и способы их назначения. Эти действия можно выполнить с помощью замечательного инструментария в среде SQL Server Management Studio (SSMS) или инструкций T-SQL. Я подробно рассмотрю последний вариант, в основном потому, что таким образом можно полностью использовать весь набор разрешений.

Можно воспользоваться тремя инструкциями разрешения. Инструкция GRANT предоставляет разрешение для защищаемого объекта субъекту безопасности. Инструкция REVOKE отменяет результат инструкции GRANT: удаляет разрешения для объекта, ранее назначенные субъекту безопасности. Если в момент выполнения инструкции REVOKE у субъекта безопасности нет соответствующего разрешения, то оно и дальше будет отсутствовать; ошибки не произойдет. Важно понимать, что отозванное разрешение может быть унаследовано через членство в роли, которое имеет разрешение. Это может привести к сложным ситуациям, поэтому оптимальный подход — не назначать слишком часто разрешений отдельным пользователям, именам входа или иным одиночным субъектам, только ролям.

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

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

Использование разрешения на уровне экземпляра сервера

Рассмотрим практический пример использования разрешений и код T-SQL для выполнения различных действий. Весь приведенный программный код есть в файле Permissions Code.sql (см. листинг A), наряду с программным кодом для очистки и вспомогательных функций, а также комментариями. Программный код анализирует разрешения как на уровне сервера, так и на уровне базы данных.

Сначала создается имя входа Bonsai (с достаточно надежным паролем!) для сервера и пользователь Bonsai сопоставляется имени входа в базе данных AdventureWorks2012. Используйте любую базу данных по своему желанию, но в программном коде используются объекты и данные именно из этой базы данных.

CREATE LOGIN Bonsai WITH PASSWORD = 'EDxQk!R209*:ZJ5';
GO
USE AdventureWorks2012;
GO
CREATE USER Bonsai FOR LOGIN Bonsai WITH DEFAULT_SCHEMA = Production;
GO

Затем в программном коде создается определяемая пользователем серверная роль, LimitedAdmin, которая получает большую часть прав доступа sysadmin на экземпляре SQL Server. Сначала роли присваивается разрешение CONTROL SERVER, благодаря которому она, в сущности, становится ролью sysadmin. После этого производится отмена некоторых разрешений, в том числе возможность создавать или изменять другие серверные роли и имена входа. Вероятно, самый значительный ущерб возможностям наносит отмена разрешения изменять любые базы данных, что не позволяет и создавать новые базы данных. Поэтому в действительности роль будет иметь разрешения только на уровне экземпляра сервера.

USE master;
GO
CREATE SERVER ROLE LimitedAdmin;
GO
GRANT CONTROL SERVER TO LimitedAdmin;
GO
DENY ALTER ANY SERVER ROLE TO LimitedAdmin;
DENY ALTER ANY LOGIN TO LimitedAdmin;
DENY ALTER ANY DATABASE TO LimitedAdmin;

Теперь нужно протестировать права роли LimitedAdmin. Для этого используется программный код, приведенный в листинге 1, в котором выполняются четыре действия: создание серверной роли, создание имени входа, создание базы данных и включение кода SQLCLR в экземпляре. Для первых трех действий разрешения отменены, но четвертое должно быть выполнено, так как системные администраторы имеют право изменять системные параметры, и данное разрешение не было отменено для роли.

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

EXECUTE AS LOGIN = 'Bonsai';

Экран. Тестирование разрешений Bonsai до назначения ему роли LimitedAdmin

Затем мы возвращаемся к разрешениям sysadmin с помощью следующей инструкции.

REVERT;

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

ALTER SERVER ROLE LimitedAdmin ADD MEMBER Bonsai;

Теперь, когда контекст выполнения изменен на Bonsai, первые три операции по-прежнему завершаются неудачей, но включение кода SQLCLR работает успешно. Мы назначили роли LimitedAdmin разрешение CONTROL SERVER, которое охватывает почти все действия, совершаемые на экземпляре сервера, но при этом отменили некоторые разрешения. Если вы намерены запретить роли LimitedAdmin изменять параметры сервера, например включением кода SQLCLR, то можно запретить разрешение SETTINGS с помощью следующей инструкции:

DENY ALTER SETTINGS TO LimitedAdmin;

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

Использование разрешений на уровне базы данных

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

В этом случае сначала выполняется перемещение базы данных AdventureWorks2012, а затем создается определяемая пользователем роль ProdDataEntry и к роли добавляется Bonsai.

USE AdventureWorks2012;
GO
CREATE ROLE ProdDataEntry AUTHORIZATION dbo;
ALTER ROLE ProdDataEntry ADD MEMBER Bonsai;
GO

Затем нужно назначить роли какие-нибудь разрешения и одно разрешение будет удалено. Идея состоит в том, что членам роли нужны права для вставки и обновления записей в двух таблицах в схеме Production. На данном этапе важно управлять разрешениями для действий. Следующие инструкции назначают разрешения INSERT и UPDATE для таблиц Production.UnitMeasure и Production.ProductCategory, а также инструкция SELECT для таблицы ProductCategory. С ее помощью будет показано, как можно управлять действиями с данными на детальном уровне.

GRANT INSERT ON Production.UnitMeasure TO ProdDataEntry;
GRANT UPDATE ON Production.UnitMeasure TO ProdDataEntry;
GRANT INSERT ON Production.ProductCategory TO ProdDataEntry;
GRANT UPDATE ON Production.ProductCategory TO ProdDataEntry;
GRANT SELECT ON Production.ProductCategory TO ProdDataEntry;

Затем разрешение EXECUTE назначается для хранимой процедуры dbo.uspGetEmployeeManagers, но такое же разрешение удаляется для процедуры dbo.uspGetManagerEmployees. Это означает, что у различных объектов имеются разрешения разных типов. Помните, что если Bonsai имеет разрешение EXECUTE для uspGetManagerEmployees через членство в другой роли, то она сможет выполнить процедуру. В этом отличие REVOKE от DENY.

GRANT EXECUTE ON dbo.uspGetEmployeeManagers TO ProdDataEntry;
REVOKE EXECUTE ON dbo.uspGetManagerEmployees TO ProdDataEntry;

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

Фрагмент 1 завершается успешно, так как Bonsai имеет разрешение INSERT для таблицы UnitMeasure, но фрагмент 2 завершается неудачно из-за отсутствия разрешения SELECT. Фрагменты 3 и 4 завершаются успешно, так как роль имеет разрешения INSERT и SELECT для таблицы ProductCategory. Фрагмент 5 завершается неудачей, поскольку роль вообще не имеет разрешений для таблицы HumanResources.Department. Фрагмент 6 успешен, так как роль имеет разрешение EXECUTE для хранимой процедуры uspGetEmployeeManagers, но фрагмент 7 завершается неудачей, потому что разрешения для процедуры uspGetManagerEmployees были отозваны ранее.

SQL Server предоставляет значительный объем метаданных о разрешениях, и в листинге 3 показано несколько способов доступа к метаданным. Первая инструкция перечисляет разрешения, которые имеются у роли ProdDataEntry в базе данных AdventureWorks2012 после выполнения предшествующего программного кода. Три другие инструкции показывают, как использовать sys.fn_builtin_permissions для получения списка всех встроенных разрешений в экземпляре сервера, всех разрешений на уровне сервера и всех на уровне базы данных. Это превосходный способ проанализировать полный набор доступных разрешений. Вы почерпнете массу полезной информации!

Пользуйтесь разрешениями

Меры безопасности SQL Server 2005 были значительно пересмотрены, и совершенствование продолжалось в каждой новой версии. Разрешения стали гораздо детальнее, чем в предшествующих версиях SQL Server, и администратору предоставляются широкие возможности по определению круга лиц, получающих доступ к различным объектам на экземпляре сервера. При огромном разнообразии субъектов безопасности, защищаемых объектов и разрешений весьма трудно найти оптимальный способ назначения, отмены и отзыва разрешений. Но усилия не пропадут даром: вы получите куда более защищенный сервер. И никогда не поддавайтесь соблазну назначать пользователям и ролям разрешения административного уровня, просто ради того чтобы упростить задачу.

— SQL Server 2012/2014 Permissions Code
— donkiely@computer.org
— *** SQL Server Logins ***
— *************************
CREATE LOGIN Bonsai WITH PASSWORD = 'EDxQk!R209*:ZJ5';
GO
USE AdventureWorks2012;
GO
CREATE USER Bonsai FOR LOGIN Bonsai WITH DEFAULT_SCHEMA = Production;
— User name doesn't need to be the same as login name, but here it is.
GO
— *** User-Defined Server Role and Permissions ***
— ************************************************
USE master;
GO
— Create a user-defined server role
CREATE SERVER ROLE LimitedAdmin;
GO
— Grant sysadmin privileges
GRANT CONTROL SERVER TO LimitedAdmin;
GO
— Members of LimitedAdmin role now have omnipotent powers over the server instance and all its objects
— Restrict that to some extent
DENY ALTER ANY SERVER ROLE TO LimitedAdmin;
DENY ALTER ANY LOGIN TO LimitedAdmin;
DENY ALTER ANY DATABASE TO LimitedAdmin;
— Statements that exercise server-level actions — don't run these yet!
— ** Start statements
CREATE SERVER ROLE TempRole;
CREATE LOGIN TempLogin WITH PASSWORD = 'AK8l*9%fwy/xvH';
CREATE DATABASE TempDatabase;
EXEC SP_CONFIGURE 'show advanced options', '1';
GO
RECONFIGURE;
GO
EXEC SP_CONFIGURE 'clr enabled', '1'
GO
RECONFIGURE;
GO
— ** End statements
— Statement cleanup — run as sysadmin
— ** Start statements
DROP SERVER ROLE TempRole;
DROP LOGIN TempLogin;
DROP DATABASE TempDatabase;
— ** End statements
— Test the statements, logged in as sysadmin
— Go execute statements. Should all succeed.
— Go execute cleanup statements.
— Test permissions
— Test 1: Can Bonsai do these things?
EXECUTE AS LOGIN = 'Bonsai';
— go execute the block of statements
REVERT;
— Test 2: Add Bonsai to LimitedAdmin
ALTER SERVER ROLE LimitedAdmin ADD MEMBER Bonsai;
GO
EXECUTE AS LOGIN = 'Bonsai';
— go execute the block of statements
— still can't execute first four operations, but can the fifth
REVERT;
— Test 3: Deny ALTER SERVER STATE permission, which won't allow DBCC FREPROCCACHE
DENY ALTER SETTINGS TO LimitedAdmin;
GO
EXECUTE AS LOGIN = 'Bonsai';
— go execute the block of statements
— still can't execute first four operations, but can the fifth
REVERT;
— *** User-defined Database Role and Permissions ***
— **************************************************
USE AdventureWorks2012;
GO
— Create a user-defined data entry role in the production schema
CREATE ROLE ProdDataEntry AUTHORIZATION dbo;
— Assign Bonsai to the role
ALTER ROLE ProdDataEntry ADD MEMBER Bonsai;
GO
— Assign permissions to the ProdDataEntry role
GRANT INSERT ON Production.UnitMeasure TO ProdDataEntry;
GRANT UPDATE ON Production.UnitMeasure TO ProdDataEntry;
GRANT INSERT ON Production.ProductCategory TO ProdDataEntry;
GRANT UPDATE ON Production.ProductCategory TO ProdDataEntry;
GRANT SELECT ON Production.ProductCategory TO ProdDataEntry;
GRANT EXECUTE ON dbo.uspGetEmployeeManagers TO ProdDataEntry;
REVOKE EXECUTE ON dbo.uspGetManagerEmployees TO ProdDataEntry;
— See what Bonsai can do
EXECUTE AS USER = 'Bonsai';
— Succeeds — has permission
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES ('BAR', 'Standard Bar');
— Fails
SELECT * FROM Production.UnitMeasure WHERE UnitMeasureCode = 'BAR';
— Succeeds — has permission
INSERT INTO Production.ProductCategory (Name)
VALUES ('Navigation');
— Succeeds
SELECT * FROM Production.ProductCategory WHERE Name = 'Navigation';
— Fails
INSERT INTO HumanResources.Department
(Name, GroupName)
VALUES
('Advertising', 'Sales and Marketing');
GO
— Succeeds
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetEmployeeManagers 113;
GO
— Fails
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetManagerEmployees 113;
GO
REVERT;
— *** Permissions metadata ***
— ****************************
— View the permissions for the ProdDataEntry database role
USE AdventureWorks2012;
GO
SELECT DB_NAME() AS 'Database', p.name, p.type_desc, dbp.state_desc,
dbp.permission_name, so.name, so.type_desc
FROM sys.database_permissions dbp
LEFT JOIN sys.objects so ON dbp.major_id = so.object_id
LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_id
WHERE p.name = 'ProdDataEntry'
ORDER BY so.name, dbp.permission_name;
— Get a list of all built-in permissions
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);
— Get a list of server-level permissions
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
— Get a list of database-level permissions
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
— *** Clean Up ***
— ****************
— Be sure to run this as sysadmin!
— Run earlier cleanup statements, if necessary
USE AdventureWorks2012;
GO
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode = 'BAR';
DELETE FROM Production.ProductCategory WHERE Name = 'Navigation';
GO
DROP USER Bonsai;
DROP ROLE ProdDataEntry;
GO
USE master;
GO
DROP LOGIN Bonsai;
DROP SERVER ROLE LimitedAdmin;
GO

CREATE SERVER ROLE TempRole;
CREATE LOGIN TempLogin WITH PASSWORD = 'AK8l*9%fwy/xvH';
CREATE DATABASE TempDatabase;
EXEC SP_CONFIGURE 'show advanced options', '1';
GO
RECONFIGURE;
GO
EXEC SP_CONFIGURE 'clr enabled', '1'
GO
RECONFIGURE;
GO

EXECUTE AS USER = 'Bonsai';
— Фрагмент 1
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES ('BAR', 'Standard Bar');
— Фрагмент 2
SELECT * FROM Production.UnitMeasure WHERE UnitMeasureCode = 'BAR';
— Фрагмент 3
INSERT INTO Production.ProductCategory (Name)
VALUES ('Navigation');
— Фрагмент 4
SELECT * FROM Production.ProductCategory WHERE Name = 'Navigation';
— Фрагмент 5
INSERT INTO HumanResources.Department
(Name, GroupName)
VALUES
('Advertising', 'Sales and Marketing');
GO
— Фрагмент 6
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetEmployeeManagers 113;
GO
— Фрагмент 7
DECLARE @rc INT;
EXECUTE @rc = dbo.uspGetManagerEmployees 113;
GO
REVERT;

USE AdventureWorks2012;
GO
SELECT DB_NAME() AS 'Database', p.name, p.type_desc, dbp.state_desc,
dbp.permission_name, so.name, so.type_desc
FROM sys.database_permissions dbp
LEFT JOIN sys.objects so ON dbp.major_id = so.object_id
LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_id
WHERE p.name = 'ProdDataEntry'
ORDER BY so.name, dbp.permission_name;
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;