Управление доступом к базам данных SQL Server

Управление ролями приложения

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

Создаем роли приложения

Роль приложения можно создать при помощи инструкции CREATE APPLICATION ROLE. Этот код имеется в файле примеров ApplicationRoles.sql.

USE AdventureWorks;
GO

CREATE APPLICATION ROLE FinancialRole WITH PASSWORD = "Pt86Yu$$R3";

Используем роли приложения

Роли приложений перед использованием необходимо активизировать. Это можно сделать, выполнив системную хранимую процедуру sp_setapprole. После того, как роль приложения активизирована в текущем соединении, она остается активной до тех пор, пока не будет закрыто соединение или выполнена системная хранимая процедура sp_unsetapprole. Хотя роли приложения предназначены для использования из клиентских приложений, их можно также использовать из особых пакетов T-SQL. Следующая процедура (ее можно найти в файлах примеров под именем ApplicationRolesUse.sql ) описывает, как активизировать роль Financial Role и отменить эту активизацию.

USE AdventureWorks;
GO

DECLARE @context varbinary (8000);

EXECUTE sp_setapprole "FinancialRole", "Pt86Yu$$R3",
@fCreateCookie = true, @cookie = @context OUTPUT;

SELECT CURRENT_USER;

EXECUTE sp_unsetapprole @context;
GO

SELECT CURRENT_USER; 
GO

Удаляем роли приложения

Если нужно удалить роль приложения, используйте инструкцию DROP APPLICATION ROLE, как показано в следующем примере (этот код имеется в файле примеров ApplicationRoles.sql ):

USE AdventureWorks;
GO

DROP APPLICATION ROLE FinancialRole;

Управление доступом к схемам

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

<Server>.<Database>.<Schema>.<Object>.

Введение в схемы

Схемы базы данных можно создавать при помощи инструкции CREATE SCHEMA. Создавая схему, можно создать объекты базы данных и назначить разрешения в пределах одной транзакции, которая вызывается инструкцией CREATE SCHEMA. Следующий пример (он есть в файлах примеров под именем ManagingAccessToSchemas01.sql ) создает схему с именем Accounting, назначает пользователя Peter владельцем схемы и создает таблицу с именем Invoices. В этом примере также предоставляется разрешение select роли базы данных public.

USE AdventureWorks;
GO

CREATE SCHEMA Accounting
AUTHORIZATION Peter;
GO

CREATE TABLE Accounting.Invoices (
InvoiceID int,
InvoiceDate smalldatetime,
ClientID int);
GO

GRANT SELECT ON Accounting.Invoices
TO public; GO

INSERT INTO Accounting.Invoices
VALUES (101,getdate(),102);

SELECT *
FROM sys.schemas;

Следующий код (который можно найти в файлах примеров под именем ManagingAccessToSchemas02.sql ) показывает, как удалить существующую схему, выполнив запрос к объектам, которые содержатся в этой схеме, и удалить сначала эти объекты.

USE AdventureWorks;
GO

SELECT s.name AS "Schema",
o.name AS "Object" FROM sys.schemas s INNER JOIN sys.objects o ON s.schema_id=o.schema_id WHERE s.name='Accounting'; GO

DROP TABLE Accounting.Invoices;
GO

DROP SCHEMA Accounting;

Отделяем пользователей от схем

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

Используем схему по умолчанию

Когда приложение ссылается на объект базы данных, не уточняя схемы, SQL Server осуществляет попытку найти объект в схеме, заданной для текущего пользователя по умолчанию. Если объект не содержится в схеме по умолчанию, SQL Server пытается обнаружить объект в схеме dbo. В следующем примере (который можно найти в файлах примеров под именем ManagingAccessToSchemas03.sql ) демонстрирует, как создать схему и назначить ее в качестве схемы по умолчанию для пользователя.

CREATE LOGIN Sara
WITH PASSWORD='TUT87rr$$'; GO

USE AdventureWorks;
GO

CREATE USER Sara
FOR LOGIN Sara; GO

CREATE SCHEMA Marketing
AUTHORIZATION Peter; GO

CREATE TABLE Marketing.Campaigns (
CampaignID int, CampaignDate smalldatetime, Description varchar (max)); GO

GRANT SELECT ON Marketing.Campaigns TO Sara;
GO

ALTER USER Sara
WITH DEFAULT_SCHEMA=Marketing;

Управление доступом к таблицам и столбцам

Таблица и столбцы хранят данные, которые извлекают и создают приложения. Управление доступом к этим данных осуществляется через иерархию разрешений SQL Server 2005. Управлять этой иерархией разрешений можно при помощи инструкций GRANT, DENY и REVOKE.

GRANT. Разрешает роли или пользователю выполнять операции, определенные в момент предоставления разрешения.

DENY. Запрещает пользователю или роли определенные разрешения и предотвращает наследование этих разрешений от других ролей..

REVOKE. Отзывает ранее запрещенные или предоставленные разрешения.

Изменение прав доступа к таблице

Доступ к таблице управляется действующими разрешениями, которые предоставлены пользователю на эту таблицу. Доступом пользователя к таблицам можно управлять при помощи управления разрешениями на таблицу. Разрешения, которыми можно управлять для таблиц, перечислены в табл. 3.2. Эти разрешения можно назначать пользователям базы данных и ролям.

Предоставляем доступ к таблице

Доступ пользователям базы данных и ролям можно предоставить с помощью инструкции GRANT. В следующем примере разрешения SELECT, INSERT и UPDATE на таблицу Sales.Customer предоставляются пользователю Sara (код для управления доступом к таблицам в этом и следующих разделах имеется в файлах примеров под именем ManagingAccessToTables.sql.).

USE AdventureWorks;
GO

GRANT SELECT,INSERT,UPDATE
ON Sales.Customer TO Sara;

Ограничиваем доступ к таблице

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

USE AdventureWorks;
GO

REVOKE SELECT
ON Sales.Customer TO Sara;

Однако пользователь может сохранить отозванное разрешение вследствие принадлежности к роли, которой предоставлено данное разрешение. В этом случае необходимо использовать инструкцию DENY, чтобы запретить доступ этому пользователю. Например:

USE AdventureWorks;
GO

DENY DELETE
ON Sales.Customer TO Sara;

Предоставление доступа к отдельным столбцам

В SQL Server 2005 есть возможность предоставить или отклонить доступ к отдельным столбцам, вместо того, чтобы работать со всей таблицей. Эта возможность предоставляет гибкость в отклонении доступа, например, к конфиденциальным данным в некоторых столбцах. Разрешения, которыми можно управлять для столбцов таблицы, перечислены в табл. 3.3.

Предоставляем доступ к столбцам

Доступ к отдельным столбцам можно предоставить с помощью инструкции GRANT. В следующем примере разрешения SELECT и UPDATE предоставляются пользователю Sara на столбцы Demographics и Modified Date таблицы Sales.Individual. (Код для управления доступом к столбцам таблицы в этом и следующих разделах имеется в файлах примеров под именем ManagingAccessToColumns. sql.)

USE AdventureWorks;
GO

GRANT SELECT,UPDATE (
Demographics, ModifiedDate) ON Sales.Individual TO Sara;

Отзываем доступ к столбцам

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

USE AdventureWorks;
GO

REVOKE UPDATE (Demographics)
ON Sales.Individual TO Sara;

Управление доступом к программируемым объектам

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

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

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

Выполняем хранимую процедуру

Когда приложение совершает вызов для выполнения хранимой процедуры, SQL Server проверяет, имеет ли текущий пользователь базы данных разрешение EXECUTE для этой хранимой процедуры. В следующем примере разрешение EXECUTE для хранимой процедуры dbo.uspGetBillOfMaterials предоставляется пользователю Sara. (Код в этом и следующих разделах можно найти в файлах примеров под именем ManagingAccessToProgrammableObjects.sql.)

USE AdventureWorks;
GO

GRANT EXECUTE On dbo.uspGetBillOfMaterials TO Sara;

Аналогично, если вам нужно не допустить выполнение каким-либо пользователем хранимой процедуры, вы можете отозвать ( REVOKE ) или запретить ( DENY ) разрешение EXECUTE для этого пользователя.

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

Определяемые пользователем функции - это программируемые объекты, как и хранимые процедуры. Существует два основных вида определяемых пользователем функций: функции, возвращающие скалярное значение, которые возвращают одно значение, и функции, которые возвращают табличное значение. В зависимости от типа определяемой пользователем функции, вам придется предоставить одно из двух разрешений - EXECUTE или SELECT (см. табл. 3.5).

Выполняем функции, возвращающие табличное значение

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

USE AdventureWorks;
GO

GRANT SELECT ON dbo.ufnGetContactInformation TO Sara;

Выполняем функцию, которая возвращает скалярное значение

Для выполнения функции, возвращающей скалярное значение, пользователю необходимо иметь разрешение EXECUTE для этой функции. Разрешение EXECUTE для функции, возвращающей скалярное значение, предоставляется тем же способом, что и разрешение EXECUTE для хранимой процедуры. В следующем примере пользователю Sara предоставляется разрешение EXECUTE для функции dbo.ufnGetStock.

USE AdventureWorks;
GO

GRANT EXECUTE ON dbo.ufnGetStock TO Sara;

Управление безопасностью для сборок

SQL Server 2005 предоставляет возможность включать сборки .NET (объекты, которые ссылаются на файлы .dll ) в механизм базы данных и вызывать эти сборки в хранимых процедурах и функциях. Для сборок можно задать те же разрешения, что и для хранимых процедур. Смотрите список разрешений в табл. 3.4.

Определяем набор разрешений

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

SAFE является рекомендуемым набором разрешений для сборок, которые не нуждаются в доступе к внешним ресурсам.

Выполняем сборку

Когда приложение пытается обратиться к объекту внутри сборки, механизм базы данных проверяет, имеет ли текущий пользователь разрешение EXECUTE. Следующий код используется для предоставления разрешения EXECUTE для сборки пользователю Sara.

USE AdventureWorks;
GO

GRANT EXECUTE ON <AssemblyName>
TO Sara;

Управление цепочками владения

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

При обращении к объекту в цепочке, SQL Server сначала сравнивает владельца вызываемого объекта и владельца вызывающего объекта. Если у объектов один владелец, то разрешения вызываемого объекта не оцениваются. Эта функция очень полезна для управления разрешениями объектов. Предположим, например, что Sara создает таблицу с именем Person.SupplierContacts и хранимую процедуру Person.InsertSupplierContacts, c помощью которой Sara добавляет строки в таблицу PersonSupplierContacts. Поскольку у этих объектов один владелец, Sara, то для того, чтобы предоставить другим пользователям разрешение EXECUTE для таблицы PersonSupplierContacts, достаточно предоставить разрешение EXECUTE на хранимую процедуру Person.InsertSupplierContacts. Нет необходимости предоставлять другим пользователям разрешения для таблицы.

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

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

Имя входа и пользователь, подключившиеся на время сеанса или выполняющие процедуру, определяют контекст выполнения. Маркеры имени входа и пользователя предоставляют SQL Server информацию, необходимую для оценки разрешений объектов. SQL Server 2005 предоставляет возможность изменить контекст выполнения при помощи инструкции EXECUTE AS. Эта операция называется переключением контекста.

Выполняем инструкцию EXECUTE AS

Инструкция EXECUTE AS позволяет явно определить контекст выполнения для текущего соединения. EXECUTE AS можно использовать для того, чтобы изменить имя входа пользователя для текущего соединения. Изменение контекста выполнения действительно до тех пор, пока не будет сделано другое изменение контекста, или пока не будет закрыто подключение или выполнена инструкция REVERT. В следующем примере инструкция EXECUTE AS используется для изменения контекста выполнения на пользователя Sara.

USE AdventureWorks;
GO

EXECUTE AS USER='Sara';

TRUNCATE TABLE dbo.ErrorLog;

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

TRUNCATE TABLE dbo.ErrorLog;

Управляем переключением контекста

Кроме управления контекстом выполнения для пакетов (групп инструкций T-SQL, отправленных серверу на выполнение, как в примере TRUNCATE TABLE, рассмотренном выше), можно управлять контекстом выполнения для хранимых процедур и пользовательских функций. Переключая контекст в этих модулях, вы можете управлять тем, какая учетная запись пользователя будет использоваться для доступа к объекту, на который ссылается хранимая процедура или функция. Для выполнения этой задачи можно использовать инструкцию EXECUTE AS со следующими изменениями:

В следующем примере создается хранимая процедура, которая переключает контекст на пользователя dbo. Затем она предоставляет разрешение EXECUTE для хранимой процедуры и изменение контекста выполнения хранимой процедуры пользователю Sara.

CREATE PROCEDURE dbo.usp TruncateErrorLog
WITH EXECUTE AS "dbo"
AS
TRUNCATE TABLE dbo.ErrorLog;
GO

GRANT EXECUTE ON dbo.usp_TruncateErrorLog TO Sara

EXECUTE AS [USER=]'Sara'

EXECUTE dbo.usp_TruncateErrorLog

Заключение

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

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

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

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

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

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

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

Краткий справочник по 2-3 лекции

CREATE LOGIN [<domain>\<user>] FROM WINDOWS;

CREATE LOGIN <user> 
  WITH PASSWORD = "<password>", 
       DEFAULT_DATABASE =<database>;

CREATE LOGIN <user> 
  WITH PASSWORD = "<password>" MUST_CHANGE, 
  CHECK_EXPIRATION = ON, 
  CHECK_POLICY          = ON;

GRANT ALTER <permission> TO <user>;

DROP LOGIN <user>;

EXECUTE sp_change_users_login @Action='Report';

GRANT CONNECT TO Guest;