Что такое схема в sql
Перейти к содержимому

Что такое схема в sql

  • автор:

SQL-Ex blog

Создание схемы SQL для организации объектов базы данных, предоставления разрешений и упрощения обслуживания

Добавил Sergey Moiseenko on Суббота, 6 мая. 2023

При создании объектов или доступа к ним в SQL Server вы можете также указывать имя схемы объекта. Что такое схема, и как она используется в Microsoft SQL Server?

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

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

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

История схем

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

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

  • dbo
    • Схема по умолчанию
    • Предполагается, если не указано имя схемы. В запросах используется [ИмяТаблицы] или [ИмяСхемы].[ИмяТаблицы]
    • Владельцем является пользователь Guest (гость). Отключено по умолчанию.
    • Если когда и используется, то редко.
    • Схема для представлений метаданных SQL Server
    • Информация об объекте
    • Информация о выполняющемся запросе
    • Динамические административные представления (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
    -- создаем базу данных 
    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 — модель безопасности с использованием определяемых пользователем ролей

    Создание схемы базы данных

    В этой статье описывается, как создать схему в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

    Ограничения

    • Новая схема принадлежит одному из следующих участников уровня базы данных: пользователю базы данных, роли базы данных или роли приложения. Объекты, создаваемые в схеме, принадлежат владельцу схемы и имеют значение NULL для principal_id в sys.objects. Владение объектами, содержащимися в схеме, можно передать любому участнику уровня базы данных, однако у владельца схемы всегда остается разрешение CONTROL на объекты в схеме.
    • Если при создании объекта базы данных указать допустимый субъект домена (пользователя или группу) в качестве владельца объекта, то этот субъект добавляется в базу данных в качестве схемы. Новая схема принадлежит этому субъекту домена.

    Разрешения

    • Требует разрешения CREATE SCHEMA в базе данных.
    • Чтобы назначить другого пользователя владельцем создаваемой схемы, у участника должно быть разрешение IMPERSONATE на этого пользователя. Если роль базы данных указана в качестве владельца, то вызывающий объект должен входить в роль или иметь на нее разрешение ALTER.

    Создание схемы с помощью SQL Server Management Studio

    1. В обозревателе объектов раскройте папку Базы данных .
    2. Разверните базу данных, в которой создается новая схема базы данных.
    3. Щелкните правой кнопкой мыши папку Безопасность , укажите на пункт Создатьи выберите Схема.
    4. В диалоговом окне Схема — создать на странице Общие введите имя новой схемы в поле Имя схемы .
    5. В поле Владелец схемы введите имя пользователя или роли базы данных, которые будут владельцем схемы. Кроме того, выберите «Поиск», чтобы открыть диалоговое окно «Роли поиска» и «Пользователи«.
    6. Нажмите ОК.

    Диалоговое окно не будет отображаться, если вы создаете схему с помощью SSMS для Базы данных SQL Azure или Azure Synapse Analytics. Потребуется создать схему шаблона T-SQL.

    Дополнительные параметры

    Диалоговое окно «Схема — новая» также предлагает параметры на двух дополнительных страницах: разрешения и расширенные свойства.

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

    Создание схемы с помощью Transact-SQL

    1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.
    2. На стандартной панели выберите пункт Создать запрос.
    3. В следующем примере создается схема Chains , а затем таблица Sizes .
    CREATE SCHEMA Chains; GO CREATE TABLE Chains.Sizes (ChainID int, width dec(10,2)); 
    CREATE SCHEMA Sprockets AUTHORIZATION Joe CREATE TABLE NineProngs (source int, cost int, partnumber int) GRANT SELECT ON SCHEMA::Sprockets TO Bob DENY SELECT ON SCHEMA::Sprockets TO John; GO 
    SELECT * FROM sys.schemas; 

    Следующие шаги

    Дополнительные сведения см. в статье CREATE SCHEMA (Transact-SQL).

    Что такое schema в БД?

    Что такое schema в postgreSQL? Её надо создавать сразу после создания БД? Это логическое устройство таблиц, наполнения, прав и т.д. И тогда может быть много схем и они могут использовать общие таблицы?

    Отслеживать
    задан 14 окт 2020 в 13:42
    595 3 3 серебряных знака 14 14 бронзовых знаков
    @Мелкий писал ответ на этот вопрос тут qna.habr.com/answer?answer_id=1423551#answers_list_answer
    14 окт 2020 в 14:49
    и тут теперь будет
    14 окт 2020 в 14:54
    @Мелкий может ли в одной схеме быть таблица из другой?
    14 окт 2020 в 18:38

    @Venot таблица всегда относится к одной схеме. в другой схеме может быть другая таблица с тем же именем, что в первой и это будут разные таблицы

    14 окт 2020 в 20:10

    1 ответ 1

    Сортировка: Сброс на вариант по умолчанию

    Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

    Схемы — это дополнительный уровень структурирования объектов базы. Похоже на директории в файловой системе или пространства имён ( namespace ) в программировании. Но не могут быть вложенными.

    Пользуясь аналогией с файловой системой и вебом: есть файлы стилей CSS, какие-то JS. Ничто не мешает их все размещать в корневой директории веб-сервера. Но обычно их размещают всё-таки в поддиректориях для собственного удобства.

    После создания новой базы у вас будет предопределённая схема public с правами для создания новых объектов для всех пользователей. Что делать дальше — решение разработчика схемы этой базы, проигнорировать схемы и размещать всё в public , структурировать как-либо по схемам, можно удалить public схему даже.

    • users
    • user_settings
    • user_favorites
    • blog_posts
    • blog_comments
    • users
    • users.settings
    • users.favorites
    • blog.posts
    • blog.comments

    Самой базе без разницы. Схемы — это логический уровень, как названия таблиц.

    Большинство проектов схемы не используют.

    Права: у схем есть права create — кто может создавать новые объекты, и usage — кто может обращаться к объектам в той схеме. Поэтому может быть удобно для разработчиков сделать отдельную схему user_tmp и исключить её из бекапов, а в остальные схемы не давать прав create — тем самым форсируя, что таблицы приложения проходят через обычную принятую у вас процедуру миграций.

    Для полноты картины: схемы public может и не быть, если она была удалена в базе, которая указанна в template опции create database

    Схема базы данных

    Схемы используются в модели безопасности компонента Database Engine для упрощения взаимоотношений между пользователями и объектами, и, следовательно, схемы имеют очень большое влияние на взаимодействие пользователя с компонентом Database Engine. В этом разделе рассматривается роль схем в безопасности компонента Database Engine. В первом подразделе описывается взаимодействие между схемами и пользователями, а во втором обсуждаются все три инструкции языка Transact-SQL, применяемые для создания и модификации схем.

    Разделение пользователей и схем

    — это коллекция объектов базы данных, имеющая одного владельца и формирующая одно пространство имен. (Две таблицы в одной и той же схеме не могут иметь одно и то же имя.) Компонент Database Engine поддерживает именованные схемы с использованием понятия принципала (principal). Как уже упоминалось, принципалом может быть индивидуальный принципал и групповой принципал.

    Индивидуальный принципал представляет одного пользователя, например, в виде регистрационного имени или учетной записи пользователя Windows. Групповым принципалом может быть группа пользователей, например, роль или группа Windows. Принципалы владеют схемами, но владение схемой может быть с легкостью передано другому принципалу без изменения имени схемы.

    Отделение пользователей базы данных от схем дает значительные преимущества, такие как:

    • один принципал может быть владельцем нескольких схем;
    • несколько индивидуальных принципалов могут владеть одной схемой посредством членства в ролях или группах Windows;
    • удаление пользователя базы данных не требует переименования объектов, содержащихся в схеме этого пользователя.

    Каждая база данных имеет схему по умолчанию, которая используется для определения имен объектов, ссылки на которые делаются без указания их полных уточненных имен. В схеме по умолчанию указывается первая схема, в которой сервер базы данных будет выполнять поиск для разрешения имен объектов. Для настройки и изменения схемы по умолчанию применяется параметр DEFAULT_SCHEMA инструкции CREATE USER или ALTER USER. Если схема по умолчанию DEFAULT_SCHEMA не определена, в качестве схемы по умолчанию пользователю базы данных назначается схема dbo.

    Инструкция CREATE SCHEMA

    В примере ниже показано создание схемы и ее использование для управления безопасностью базы данных. Прежде чем выполнять этот пример, необходимо создать пользователей базы данных Alex и Vasya, как будет описано в следующей статье (вы можете вернуться к этим примерам позже).

    USE SampleDb; GO CREATE SCHEMA poco AUTHORIZATION Vasya GO CREATE TABLE Product ( Number CHAR(10) NOT NULL UNIQUE, Name CHAR(20) NULL, Price MONEY NULL); GO CREATE VIEW view_Product AS SELECT Number, Name FROM Product; GO GRANT SELECT TO Alex; DENY UPDATE TO Alex;

    В этом примере создается схема poco, содержащая таблицу Product и представление view_Product. Пользователь базы данных Vasya является принципалом уровня базы данных, а также владельцем схемы. (Владелец схемы указывается посредством параметра AUTHORIZATION. Принципал может быть владельцем других схем и не может использовать текущую схему в качестве схемы по умолчанию.)

    Две другие инструкции, применяемые для работы с разрешениями для объектов базы данных, GRANT и DENY, подробно рассматриваются позже. В этом примере инструкция GRANT предоставляет инструкции SELECT разрешения для всех создаваемых в схеме объектов, тогда как инструкция DENY запрещает инструкции UPDATE разрешения для всех объектов схемы.

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

    Инструкция CREATE SCHEMA является атомарной. Иными словами, если в процессе выполнения этой инструкции происходит ошибка, не выполняется ни одна из содержащихся в ней подынструкций.

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

    Принципалом уровня базы данных может быть пользователь базы данных, роль или роль приложения. (Роли и роли приложения рассматриваются в одной из следующих статей.) Принципал, указанный в предложении AUTHORIZATION инструкции CREATE SCHEMA, является владельцем всех объектов, созданных в этой схеме. Владение содержащихся в схеме объектов можно передавать любому принципалу уровня базы данных посредством инструкции ALTER AUTHORIZATION.

    Для исполнения инструкции CREATE SCHEMA пользователь должен обладать правами базы данных CREATE SCHEMA. Кроме этого, для создания объектов, указанных в инструкции CREATE SCHEMA, пользователь должен иметь соответствующие разрешения CREATE.

    Инструкция ALTER SCHEMA

    Инструкция ALTER SCHEMA перемещает объекты между разными схемами одной и той же базы данных. Инструкция ALTER SCHEMA имеет следующий синтаксис:

    ALTER SCHEMA schema_name TRANSFER object_name 

    Использование инструкции ALTER SCHEMA показано в примере ниже:

    USE AdventureWorks2012; ALTER SCHEMA HumanResources TRANSFER Person.ContactType;

    Здесь изменяется схема HumanResources базы данных AdventureWorks2012, перемещая в нее таблицу ContactType из схемы Person этой же базы данных. Инструкцию ALTER SCHEMA можно использовать для перемещения объектов между разными схемами только одной и той же базы данных. (Отдельные объекты в схеме можно изменить посредством инструкции ALTER TABLE или ALTER VIEW.)

    Инструкция DROP SCHEMA

    Для удаления схемы из базы данных применяется инструкция DROP SCHEMA. Схему можно удалить только при условии, что она не содержит никаких объектов. Если схема содержит объекты, попытка выполнить инструкцию DROP SCHEMA будет неуспешной.

    Как указывалось ранее, владельца схемы можно изменить посредством инструкции ALTER AUTHORIZATION, которая изменяет владение сущностью. Язык Transact-SOL не поддерживает инструкции CREATE AUTHORIZATION и DROP AUTHORIZATION. Владелец схемы указывается с помощью инструкции CREATE SCHEMA.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *