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

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

  • автор:

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

В этой статье описывается, как создать схему в 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).

Схема (shema) в базе данных Oracle

Под термином схема в базе данных Oracle понимается — созданная учетная запись пользователя и объекты, которые ей принадлежат (например, индексы, триггеры, хранимые процедуры).

Посмотреть имеющиеся схемы в базе данных:

SQL> set pagesize 0; SQL> select username from dba_users order by 1; 

Создать новую схему в базе данных

SQL> CREATE USER scott IDENTIFIED BY tiger; 

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

SQL> CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE MY_DATA TEMPORARY TABLESPACE MY_TEMP ACCOUNT UNLOCK; 

Делегировать пользователю возможность подключаться к базе и работать с ней

SQL> grant connect, resource to scott 

Удалить схему можно следующей командой:

SQL> drop user scott cascade; 

Разблокировать схему, можно командой:

SQL> alter user scott account unlock; 

А поменять пароль

SQL> alter user system identified by NewPassword; 

Tags: Oracle Database, shemas

Single DataGuard RAC

Oracle DBA

Собираем также материалы по: SQL & PL/SQL

Лучше потратить какое-то количество времени, чтобы записать успешный опыт, чем потом повторно воспроизводить его по памяти.

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

SQL-Ex blog

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

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

В целом, в стороне от специфических реализаций в реляционных базах данных, «схема» — это концептуальная основа или проект, который определяет структуру, связи и ограничения данных или информации. Она предоставляет способ описания и организации данных в структурированном виде. Такое понятие схемы не уникально для баз данных; например, в GraphQL схема определяет типы, запросы, мутации и связи между ними, ограничивая набор возможных операций, которые могут выполняться с использованием API, и форму возвращаемых данных.

Необычность разных реализаций схемы в базах данных

С точки зрения человека, знакомого с общей идеей схемы, может действительно показаться необычным, что базы данных типа SQL Server, Oracle, MariaDB/MySQL и PostgreSQL каждая слегка (а иногда в значительной степени) по-разному интерпретирует и внедряет схемы. Хотя основная идея схемы как структурированного контейнера или пространства имен для объектов базы данных остается в некоторой степени согласующейся, точная природа, назначение и поведение схем различаются в разных системах.

Почему каждая база данных реализует схему по-разному

  1. Исторические или унаследованные причины: Многие системы баз данных начали свою историю десятилетия назад. Со временем, по мере развития, они опирались на существующую архитектуру, что приводило к вариациям в таких функциях, как схема. Например, понятие схемы в Oracle тесно связана с пользователем, что было обусловлено ранними архитектурными решениями Oracle.
  2. Философия проектирования и целевая аудитория: Некоторые базы данных проектировались под конкретную аудиторию. Oracle, например, был спроектирован для приложений уровня предприятия, что могло оказать влияние на их проект схемы, ориентированный на пользователя. С другой стороны, MySQL был изначально нацелен на веб-приложения, считая схемы синонимами баз данных, возможно, для упрощения.
  3. Соответствие стандарту против практичности: Хотя существуют стандарты ANSI SQL, которые базы данных могут пытаться поддерживать, имеется также стремление соблюсти баланс между поддержкой стандарта и предлагаемыми функциями, которые считаются более практичными или выгодными в первую очередь для пользователей баз данных. Например, PostgreSQL, который нацелен на высокое соответствие стандарту, также вводит продвинутые функции, отсутствующие в стандарте, когда он считает это целесообразным.
  4. Соревновательное разделение: Иногда базы данных вводят или развивают функции, чтобы выделиться на рынке. Эти вариации могут иногда привести к различиям в базовых концепциях, подобных схемам.
  5. Влияние сообщества и управления: Базы данных с открытыми исходными кодами типа PostgreSQL и MariaDB могут подвергаться влиянию своих сообществ разработчиков. Различные сообщества могут оказывать приоритет определенным функциям или философии, что приводит к различным реализациям.
  6. Гибкость и расширяемость: Базы данных могут реализовывать схемы с целью сделать свой подход более гибким или расширяемым для будущих изменений. Это может привести их к принятию нестандартных или уникальных подходов.

Взгляд на схему реляционных баз данных

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

  1. SQL Server:
  2. Определение: В SQL Server схема — это контейнер объектов и может быть назначена конкретным пользователям или ролям в целях безопасности. По умолчанию имеется схема с именем «dbo» (владелец базы данных).
  3. Создание:

CREATE SCHEMA SchemaName AUTHORIZATION OwnerName; 
CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE temp_tablespace_name;
CREATE DATABASE SchemaName;
CREATE SCHEMA SchemaName;
CREATE SCHEMA SchemaName;

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

Схема SQL Server

В SQL Server понятие схемы является многогранным, и понимание ее использования можно разделить на рассмотрение вопросов: «как», «что», «когда» и «где».

Как схема используется в SQL Server?

  1. Организация объектов: Схемы используются для логической группировки и управления объектами базы данных, такими как таблицы, представления, хранимые процедуры и т.д. Например, таблица может быть создана в пределах схемы следующим образом: CREATE TABLE ИмяСхемы.ИмяТаблицы (Столбец1 ТипДанных1, Столбец2 ТипДанных2, . );
  2. Управление пространством имен: Схемы обеспечивают пространства имен, которые позволяют объектам, находящихся в разных схемах в одной и той же базе данных, иметь одинаковые имена.
  3. Управление разрешениями: Схемы дают возможность более тонкого управления разрешениями. Вы можете предоставлять или отменять разрешения на схему, тем самым воздействуя на все объекты в схеме.

Какие объекты могут быть сопоставлены схеме в SQL Server?

  • Таблицы
  • Представления
  • Хранимые процедуры
  • Функции
  • Типы
  • Триггеры
  • Индексы
  • .

Когда в SQL Server используются схемы?

  1. На стадии проектирования: Схемы часто рассматриваются на стадии начального проектирования базы данных. Стратегия хорошо структурированных схем позволит в будущем облегчить модификацию и масштабирование.
  2. Миграция: При переносе или объединении данных из других систем схемы могут использоваться для разделения различных наборов данных.
  3. Установка разрешений: При необходимости установить или изменить разрешения на множество связанных объектов базы данных.
  4. Рефакторинг: При реорганизации объектов для лучшей ясности или производительности.

Где находятся схемы в SQL Server?

В базе данных SQL Server вы можете найти список схем в Object Explorer (SSMS) в узле конкретной базы данных. Они находятся на том же уровне иерархии, что и таблицы, представления и т.д., но действуют как контейнер или пространство имен для этих объектов.

Назначение схем в SQL Server

  1. Логическая организация: Схемы обеспечивают способ логической группировки связанных объектов базы данных, делая базу данных более понятной и управляемой.
  2. Безопасность и управление разрешениями: Контролируя доступ к схеме, администраторы могут опосредованно управлять доступом ко всем объектам в пределах этой схемы.
  3. Гибкость: Схемы обеспечивают гибкость при проектировании и администрировании баз данных. Например, вы можете передать владение схемой (и ее объектами) от одного пользователя другому.
  4. Управление пространством имен: Средство избежать конфликтов при наличии объектов с одинаковыми именами в разных схемах.
  5. Миграция и интеграция: Обеспечение более гладкой миграции или интеграции с данными из другой системы посредством группировки импортированных объектов в конкретные схемы.

Схема в Oracle

В Oracle понятие «схема» тесно переплетается с понятием «пользователь». Схема в Oracle — это фактически коллекция объектов, которыми владеет пользователь. Этот проект отличается от некоторых других систем СУБД, таких как SQL Server, где схема больше чем логический контейнер или пространство имен.

Как схемы используются в Oracle?

  1. Владение объектами: Каждый объект базы данных принадлежит схеме, и по сути имя схемы — это имя учетной записи пользователя, которая ей владеет.
  2. Управление пространством имен: Схемы разделяют объекты с одинаковым именем, но принадлежащих разным пользователям.
  3. Безопасность и разрешения: Предоставляя или отменяя привилегии для конкретного пользователя (схемы), вы можете управлять доступом к объектам в схеме.

Какие объекты могут быть связаны со схемой в Oracle?

  • Таблицы
  • Представления
  • Хранимые процедуры и функции
  • Последовательности
  • Пакеты
  • Синонимы
  • Триггеры
  • Индексы
  • .

Когда в Oracle используются схемы?

  1. Создание и проектирование базы данных: Схемы неявно создаются, когда создается пользователь, и обычно сопоставляются на основе ролей и обязанностей в приложении.
  2. Управление разрешениями: Предоставление и отмена доступа к множеству связанных объектов базы данных осуществляется настройкой привилегий пользователя.
  3. Организационное разделение: Различные отделы или функциональные обязанности в организации могут получить отдельные схемы для лучшей организации и управления.
  4. Внедрение приложений: Различные приложения или модули могут использовать различные схемы.

Где находятся схемы в Oracle?

В Oracle понятие схемы тесно связано с пользователем. Поэтому когда вы видите пользовательские аккаунты в базе данных Oracle (обычно с помощью таких инструментов как Oracle SQL Developer или Oracle Enterprise Manager) вы по сути смотрите на список схем. Объекты каждого пользователя будут находиться в его схеме.

Назначение схем в Oracle

  1. Владение и организация: Каждая схема — это четкий владелец своего множества объектов базы данных, которая гарантирует надлежащую организацию и управление.
  2. Безопасность: Безопасность базы данных реализуется на уровне схемы (пользователя). Предоставление или отмена разрешений на схему влияет на все объекты в этой схеме.
  3. Управление пространством имен: Обеспечивает сосуществование объектов с одинаковыми именами, принадлежащих разным схемам.
  4. Логическое разделение: Обеспечивает логическое разделение объектов базы данных, что может быть полезным для управления большими приложениями или множеством приложений, работающих с одной и той же базой.
  5. Управление ресурсами: Oracle проводит политику управления ресурсами на уровне пользователя/схемы, поэтому определенные схемы могут потреблять больше или меньше ресурсов на основе имеющихся требований.

Схема в MariaDB/MySQL

В MariaDB и MySQL термин «схема» является синонимом «база данных». Слова «схема» и «база данных» используются взаимозаменяемо, и хотя во многих системах схема представляет собой логический контейнер внутри базы данных, в MariaDB/MySQL это, по сути, одно и то же.

Как схемы (базы данных) используются в MariaDB/MySQL?

  1. Контейнер объектов: Схема (или база данных) в MariaDB/MySQL действует как главный контейнер для таблиц, представлений, хранимых процедур и функций.
  2. Управление пространством имен: Каждая схема действует как пространство имен, гарантируя, что объекты типа таблицы имеют уникальные имена в пределах схемы, но могут иметь совпадающие имена в других схемах.
  3. Безопасность: Привилегии могут предоставляться или отменяться на уровне схемы, влияя на доступ ко всем объектам внутри этой схемы.

Какие объекты могут быть связаны со схемой (базой данных) в MariaDB/MySQL?

  • Таблицы
  • Представления
  • Хранимые процедуры и функции
  • Триггеры
  • События
  • Другие реляционные объекты

Когда схемы (базы данных) используются в MariaDB/MySQL?

  1. Создание базы данных: При установке нового приложения или сервиса может быть создана новая схема, чтобы логически разделить данные.
  2. Разделение данных: Для мультитенантных приложений могут использоваться отдельные схемы для каждого арендатора или группы арендаторов.
  3. Резервирование и восстановление: Для резервирования или восстановления данных для конкретного приложения или службы могут быть выполнены операции на уровне схемы.
  4. Развертывание приложения: Различные приложения могут использовать отдельные схемы для лучшего управления и изоляции.

Где находятся схемы (базы данных) в MariaDB/MySQL?

Вы можете просмотреть список схем, используя клиентские инструменты типа MySQL Workbench, phpMyAdmin или даже интерфейс командной строки. В клиенте командной строки команда SHOW DATABASES; выведет список всех схем.

Назначение схем (баз данных) в MariaDB/MySQL:

  1. Логическое разделение: Схемы обеспечивают логическое разделение данных, что существенно для организации данных, особенно в среде с множеством приложений или служб.
  2. Безопасность и управление разрешениями: Обеспечивают уровень, на котором можно управлять разрешениями, позволяя контролировать тех, кто может иметь доступ или модифицировать данные.
  3. Резервирование и обслуживание: Позволяет выполнять операции на уровне схемы, делая более управляемыми такие задачи, как резервирование, восстановление или оптимизация.
  4. Управление пространством имен: Гарантирует, что имена таблиц и других объектов могут быть уникальными в пределах схемы, допуская объекты с одинаковыми именами в различных схемах.
  5. Гибкость: Обеспечивает гибкость в управлении ресурсами, настройках производительности и конфигурировании на уровне схемы.

Схема в PostgreSQL

В PostgreSQL важное значение имеет понятие «роли». В отличие от MariaDB/MySQL, где схема является синонимом базы данных, в PostgreSQL схема является пространством имен в базе данных.

Как в PostgreSQL используются схемы?

  1. Пространство имен для объектов: Схема предоставляет пространство имен, делая возможным множеству объектов (таких как таблицы или функции) иметь одинаковые имена, если они находятся в разных схемах.
  2. Путь поиска: PostgreSQL использует «путь поиска», чтобы определить, какую схему следует искать при ссылке на объект. Путь поиска может быть установлен таким образом, чтобы вы не указывали имя схемы при доступе к ее объектам.
  3. Безопасность: Разрешения доступа огпределяются на уровне схемы. Вы можете управлять тем, какие роли имеют доступ к каким схемам и содержащимся в них объетам.

Какие объекты могут быть связаны со схемой в PostgreSQL?

  • Таблицы
  • Представления
  • Функции
  • Последовательности
  • Типы данных
  • Операторы
  • Индексы
  • Домены
  • И другие объекты базы данных

Когда в PostgreSQL используются схемы?

  1. Начальное проектирование базы данных: На этапе проектирования базы данных могут быть созданы схемы для разделения функциональности или модулей.
  2. Разделение данных: Для мультитенантных приложений для каждого арендатора могут быть созданы отдельные схемы.
  3. Изоляция модулей: Модули разных прилождений могут иметь собственные схемы для лучшей организации.
  4. Миграция: При слиянии баз данных или таблиц, схемы помогут избежать коллизии имен.

Где находятся схемы в PostgreSQL?

В PostgreSQL схемы находятся внутри конкретной базы данных. Вы можете просмотреть их, используя инструменты типа pgAdmin, DBeaver или интерфейса командной строки (psql). В psql список схем выведет команда \dn.

Назначение схем в PostgreSQL

  1. Управление пространством имен: Позволяет сосуществовать объектам базы данных с одинаковыми именами в одной и той же базе, если они находятся в разных схемах.
  2. Логическая организация: Облегчает логическую группировку объектов базы данных на основе функциональности, модулей приложений или других критериев.
  3. Безопасность: Обеспечивает слой безопасности для управления доступом. Устанавливая разрешения на уровне схемы, вы можете контролировать доступ к множеству объектов.
  4. Гибкость: Схемы обеспечивают гибкость управления и организации объектов. Вы можете иметь множество схем в одной базе данных, снижая необходимость создания отдельных баз данных.
  5. Эффективное управление: Облегчает такие задачи, как резервирование, восстановление или миграцию на уровне схемы, предлагая детализацию в операциях с базой данных.

Заключение

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

Напротив, в MariaDB и MySQL «схема» является синонимом «база данных», служа главным образом в качестве контейнера для таблиц, представлений и других связанных объектов. Это определение делает упор на логическом разделении данных и управлении пространством имен на сервере баз данных. Подход PostgreSQL имеет свои нюансы, где схема действует как пространство имен в пределах конкретной базы данных, допуская существование объектов в идентичными именами, если они находятся в разных схемах. Такая структура в PostgreSQL обеспечивает сложные организационные возможности и повышает безопасность и степень детализации управления в рамках одной базы данных.

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

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

Отслеживать
задан 14 окт 2020 в 13:42
585 3 3 серебряных знака 13 13 бронзовых знаков
@Мелкий писал ответ на этот вопрос тут 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

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

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