SQL-Ex blog

Что такое схема?

Схемой в SQL Server является просто группа объектов в текущей базе данных. Не следует ее путать с определением схемы в Oracle, которая аналогична базе данных в SQL Server.

История схем

До SQL Server 2000 включительно владельцем объекта являлся пользователь, который его создал. Это означало, что при удалении пользователя в базе данных требовалось переприсвоить объекты, им созданные, другому пользователю. Начиная с SQL Server 2005, схемы есть способ разделить создателя объекта и сам объект.

Встроенные схемы

Имеется четыре предопределенных встроенных схем, которые создаются, когда вы устанавливаете SQL Server:

• dbo
  
  
  • Схема по умолчанию
  
  
  • Предполагается, если не указано имя схемы. В запросах используется [ИмяТаблицы] или [ИмяСхемы].[ИмяТаблицы]


• guest
  
  
  • Владельцем является пользователь Guest (гость). Отключено по умолчанию.
  
  
  • Если когда и используется, то редко.


• INFORMATION_SCHEMA
  
  
  • Схема для представлений метаданных SQL Server


• sys
  
  
  • Информация об объекте
  
  
  • Информация о выполняющемся запросе
  
  
  • Динамические административные представления (DMV) в памяти

Зачем использовать схемы?

• Гибкость в организации объектов баз данных


• Множество пользователей могут иметь разрешения на схему


• Пользователей можно удалять, не оказывая влияния на объекты или схемы


• Позволяют нескольким объектам иметь одинаковые имена, например, в хранилище данных с данными из нескольких источников данных с одинаковыми именами таблиц, схема добавляет уровень различия.

Что может послужить примером использования схем?

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

• Комплектующие


• Аренда


• Продажи


• Услуги

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

Оператор создания схемы

Вот полный синтаксис T-SQL CREATE SCHEMA:

CREATE SCHEMA schema_name_clause [  [ ...n ] ]  

 ::=  

    {  

    schema_name  

    | AUTHORIZATION owner_name  

    | schema_name AUTHORIZATION owner_name  

    }  

 ::=   

    {   

        table_definition | view_definition | grant_statement |   

        revoke_statement | deny_statement   

    }

Используя нашу базу данных лыжного магазина, мы собираемся:

• Создать новую базу данных SQL


• Создать пользователя с именем User1


• Создать схемы для отделов комплектующих (Parts), аренды (Rentals), продаж (Sales) и услуг (Service)


• Создать новую таблицу в каждой схеме


• Создать простую хранимую процедуру, которая будет запрашивать все записи в соответствующей таблице


• Предоставить разрешения на select и execute в схеме Parts пользователю User1

Вот операторы SQL:

-- создаем базу данных

CREATE DATABASE [SkiShop];

GO

-- используем новую базу данных

USE [SkiShop];

GO

-- создаем пользователя базы данных

CREATE USER [User1] FOR LOGIN [User1];

GO

-- создаем схемы

CREATE SCHEMA [Parts];

GO

CREATE SCHEMA [Rentals];

GO

CREATE SCHEMA [Sales];

GO

CREATE SCHEMA [Service];

GO

-- создаем таблицы

CREATE TABLE [Parts].[TableA]

(

    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)

);

GO

CREATE TABLE [Rentals].[TableA]

(

    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)

);

GO

CREATE TABLE [Sales].[TableA]

(

    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)

)

CREATE TABLE [Service].[TableA]

(

    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)

);

GO

-- создаем процедуры

CREATE PROCEDURE [Parts].[Proc1]

AS

SELECT * FROM [Parts].[TableA];

GO

CREATE PROCEDURE [Service].[Proc1]

AS

SELECT * FROM [Service].[TableA];

GO

CREATE PROCEDURE [Rentals].[Proc1]

AS

SELECT * FROM [Rentals].[TableA];

GO

CREATE PROCEDURE [Sales].[Proc1]

AS

SELECT * FROM [Sales].[TableA];

GO

-- предоставляем разрешения select и execute пользователю

GRANT SELECT ON SCHEMA::[Parts] TO [User1];

GRANT EXECUTE ON SCHEMA::[Parts] TO [User1];

GO

Затем мы подключимся как User1 и выполним хранимую процедуру [Parts].[Proc1] в базе данных SkiShop.

USE [SkiShop];

GO

EXEC [Parts].[Proc1];

Запрос выполняется и, конечно, не возвращает никаких записей, поскольку таблица пустая, но мы видим, что хранимая процедура выполнилась. Мы увидели, что пользователь USER1 имеет права на select и execute для таблицы Parts.TableA, которые мы предоставили схеме Parts.

Теперь давайте посмотрим, что произойдет с другими схемами и таблицами при попытке выполнить другие три хранимых процедуры или выборки из таблиц в трех других схемах, в которых User1 не имеет разрешений.

USE [SkiShop];

GO

 EXEC [Rentals].[Proc1];

EXEC [Sales].[Proc1];

EXEC [Service].[Proc1];

SELECT * FROM [Rentals].[TableA]

SELECT * FROM [Sales].[TableA];

SELECT * FROM [Service].[TableA];

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

Msg 229, Level 14, State 5, Procedure Rentals.Proc1, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object 'Proc1', database 'SkiShop', schema 'Rentals'.

Msg 229, Level 14, State 5, Procedure Sales.Proc1, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object 'Proc1', database 'SkiShop', schema 'Sales'.

Msg 229, Level 14, State 5, Procedure Service.Proc1, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object 'Proc1', database 'SkiShop', schema 'Service'.

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

Msg 229, Level 14, State 5, Line 8
The SELECT permission was denied on the object 'TableA', database 'SkiShop', schema 'Rentals'.

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'TableA', database 'SkiShop', schema 'Sales'.

Msg 229, Level 14, State 5, Line 10
The SELECT permission was denied on the object 'TableA', database 'SkiShop', schema 'Service'.

Ссылки по теме

1. Методы авторизации SQL Server, логины и пользователи базы данных


2. Безопасность SQL Server - модель безопасности с использованием определяемых пользователем ролей