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

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

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

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

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

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Создаем роль приложения FinancialRole в текущей базе данных

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

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

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

• Изменяем контекст соединения на базу данных AdventureWorks.

  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 ):

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• удаляем роль приложения FinancialRole из текущей базы данных

  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.

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Создаем схему Accounting с владельцем Peter.

  CREATE SCHEMA Accounting
  AUTHORIZATION Peter;
  GO
• Создаем таблицу Invoices в схеме Accounting.

  CREATE TABLE Accounting.Invoices (
  InvoiceID int,
  InvoiceDate smalldatetime,
  ClientID int);
  GO
• Предоставляем разрешение SELECT на новую таблицу роли public.

  GRANT SELECT ON Accounting.Invoices
  TO public; GO
• Добавляем строку данных в новую таблицу.
• Обратите внимание на двухкомпонентное имя, которое мы используем для обращения к таблице в текущей базе данных.

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

  Удалить схему можно при помощи инструкции DROP SCHEMA. В SQL Server 2005 не допускается удаление схемы, если в схеме есть объекты. Информацию о схемах можно получить, выполнив запрос к представлению каталога sys.schemas. Следующий пример выполняет запрос к представлению каталога sys.schemas, чтобы получить информацию о схеме:

  SELECT *
  FROM sys.schemas;

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

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Извлекаем информацию о схеме Accounting.

  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
• Удаляем таблицу Invoices из схемы Accounting.

  DROP TABLE Accounting.Invoices;
  GO
• Удаляем схему Accounting.

  DROP SCHEMA Accounting;

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

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

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

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

• Создаем имя входа SQL Server в данном экземпляре SQL Server.

  CREATE LOGIN Sara
  WITH PASSWORD='TUT87rr$$'; GO
• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Создаем пользователя Sara в базе данных AdventureWorks и сопоставляем этого пользователя имени входа Sara

  CREATE USER Sara
  FOR LOGIN Sara; GO
• Создаем схему Marketing, владелец - пользователь Peter.

  CREATE SCHEMA Marketing
  AUTHORIZATION Peter; GO
• Создаем таблицу Campaigns в только что созданной схеме.

  CREATE TABLE Marketing.Campaigns (
  CampaignID int, CampaignDate smalldatetime, Description varchar (max)); GO
• Предоставляем разрешение SELECT пользователю Sara на новую таблицу.

  GRANT SELECT ON Marketing.Campaigns TO Sara;
  GO
• Объявляем схему Marketing схемой по умолчанию для пользователя Sara

  ALTER USER Sara
  WITH DEFAULT_SCHEMA=Marketing;