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’.
Ссылки по теме
- Методы авторизации SQL Server, логины и пользователи базы данных
- Безопасность SQL Server — модель безопасности с использованием определяемых пользователем ролей
CREATE SCHEMA (Transact-SQL)
Создает схему в текущей базе данных. При помощи транзакции CREATE SCHEMA также можно создавать таблицы и представления в новой схеме и предоставлять разрешения GRANT, DENY или REVOKE на такие объекты.
Синтаксис
-- Syntax for SQL Server and Azure SQL Database CREATE SCHEMA schema_name_clause [ [ . n ] ] ::= < schema_name | AUTHORIZATION owner_name | schema_name AUTHORIZATION owner_name > ::=
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse CREATE SCHEMA schema_name [ AUTHORIZATION owner_name ] [;] Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
schema_name
Имя, по которому схема идентифицируется в данной базе данных.
AUTHORIZATION owner_name
Указывает имя участника уровня базы данных, который является владельцем схемы. Этому участнику могут принадлежать и другие схемы, при этом текущая схема может не использоваться по умолчанию.
table_definition
Указывает инструкцию CREATE TABLE, которая создает таблицу внутри схемы. У участника, выполняющего эту инструкцию, должно быть разрешение CREATE TABLE в текущей базе данных.
view_definition
Указывает инструкцию CREATE VIEW, которая создает представление внутри схемы. У участника, выполняющего эту инструкцию, должно быть разрешение CREATE VIEW в текущей базе данных.
grant_statement
Указывает инструкцию GRANT, которая предоставляет разрешения на любой защищаемый объект, за исключением новой схемы.
revoke_statement
Указывает инструкцию REVOKE, которая отменяет разрешения на любой защищаемый объект, за исключением новой схемы.
deny_statement
Указывает инструкцию DENY, которая запрещает разрешения на любой защищаемый объект, за исключением новой схемы.
Замечания
Выражения, которые содержат инструкцию CREATE SCHEMA AUTHORIZATION, но не определяют имя, разрешены только для обратной совместимости. Выражение не вызывает ошибку, но не создает схему.
Инструкция CREATE SCHEMA создает схему, содержащиеся в ней таблицы и представления, а также разрешения GRANT, REVOKE или DENY на любой защищаемый объект в одной инструкции. Эта инструкция должна выполняться как отдельный пакет. При помощи инструкции CREATE SCHEMA объекты создаются внутри создаваемой схемы.
Транзакции CREATE SCHEMA являются атомарными. Если в процессе выполнения инструкции CREATE SCHEMA возникают ошибки, ни один из указанных защищаемых объектов не создается и ни одно разрешение не предоставляется.
Защищаемые объекты, которые необходимо создать при помощи инструкции CREATE SCHEMA, могут быть перечислены в любом порядке, за исключением представлений, ссылающихся на другие представления. В этом случае ссылающееся представление должно быть создано после того представления, на которое оно ссылается.
Таким образом, при помощи инструкции GRANT можно предоставлять разрешения на объект еще до того, как он будет создан, а инструкция CREATE VIEW может появляться раньше инструкций CREATE TABLE, создающих таблицы, на которые ссылается представление. Кроме того, инструкции CREATE TABLE могут декларировать внешние ключи к таблицам, определенным позже в инструкции CREATE SCHEMA.
В инструкциях CREATE SCHEMA поддерживаются DENY и REVOKE. Предложения DENY и REVOKE будут исполняться в той последовательности, в которой они появляются в инструкции CREATE SCHEMA.
Участник, выполняющий инструкцию CREATE SCHEMA, может указать другого участника базы данных в качестве владельца создаваемой схемы. Для этого необходимы дополнительные разрешения, описанные в подразделе «Разрешения» далее в этом разделе.
Новая схема принадлежит одному из следующих участников уровня базы данных: пользователю базы данных, роли базы данных или роли приложения. Объекты, создаваемые в схеме, принадлежат владельцу схемы и имеют значение NULL для principal_id в sys.objects. Владение объектами, содержащимися в схеме, можно передать любому участнику уровня базы данных, однако у владельца схемы всегда остается разрешение CONTROL на объекты в схеме.
Начиная с SQL Server 2005 поведение схем изменилось. В результате программный код, предполагающий, что схемы эквивалентны пользователям базы данных, возможно, не будет более возвращать правильные результаты. Старые представления каталогов, включая sysobjects, не должны использоваться в базах данных, где когда-либо выполнялась любая из следующих инструкций DDL: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. В таких базах данных необходимо использовать новые представления каталога. Новые представления каталога учитывают разделение участников и схем, введенное в SQL Server 2005. Дополнительные сведения о представлениях каталога см. в разделе «Представления каталога» (Transact-SQL).
Неявное создание схемы и пользователя
В некоторых случаях пользователь может использовать базу данных без учетной записи пользователя базы данных (субъект базы данных в базе данных). Это может случаться в следующих ситуациях:
- Имя входа имеет привилегии CONTROL SERVER.
- Пользователь Windows не имеет индивидуальной учетной записи пользователя базы данных (субъект базы данных в базе данных), однако получает доступ в базу данных как член группы Windows, которая имеет учетную запись пользователя базы данных (субъект базы данных для группы Windows).
- Пользователь Azure AD не имеет отдельную учетную запись пользователя базы данных (субъект базы данных в базе данных), но обращается к базе данных в качестве члена группы Azure AD, которая имеет учетную запись пользователя базы данных (субъект базы данных для группы Azure AD).
Когда пользователь без учетной записи пользователя базы данных создает объект без определения существующей схемы, субъект базы данных и схема по умолчанию будут автоматически созданы в базе данных для этого пользователя. Созданные субъект и схема базы данных будут иметь то же имя, которым пользовался пользователь при подключении к SQL Server (имя входа в систему для проверки подлинности SQL Server или имя пользователя Windows).
Данное поведение необходимо, чтобы позволить пользователям, базирующимся в группах Windows, создавать и обладать объектами. Однако это может привести к неумышленному созданию схем и пользователей. Во избежание неявного создания пользователей и схем каждый раз, при возможности, явно создавайте субъекты базы данных и назначайте схему по умолчанию. Или явно выражайте существующую схему при создании объектов в базе данных, используя двух- или трехчастные имена объектов.
Неявное создание пользователя Azure Active Directory не поддерживается в Базе данных SQL. Так как создание пользователя Azure AD из внешнего поставщика должно проверка состояние пользователя в Azure AD, создание пользователя завершится ошибкой 2760: указанное имя схемы «» либо не существует, либо у вас нет разрешения на использование. А затем ошибка 2759: сбой CREATE SCHEMA из-за предыдущих ошибок. Попытки создания или изменения схем приводят к ошибке 15151: не удается найти пользователя, так как он не существует или у вас нет разрешений. За ним также следует ошибка 2759. Чтобы обойти эти ошибки, создайте пользователя Azure AD из внешнего поставщика или измените группу Azure AD, чтобы назначить схему по умолчанию. Затем повторно запустите инструкцию, создав объект.
Уведомление об устаревании
Инструкции CREATE SCHEMA, не указывающие имя схемы, поддерживаются в данный момент только для обратной совместимости. Такие инструкции на самом деле не создают схему внутри базы данных, а создают таблицы и представления, а также предоставляют разрешения. Участникам не нужны разрешения CREATE SCHEMA для выполнения этой более ранней формы инструкции CREATE SCHEMA, поскольку схема не создается. Эта функция будет удалена в одном из будущих выпусков SQL Server.
Разрешения
Требует разрешения CREATE SCHEMA в базе данных.
Чтобы создать объект, указанный в инструкции CREATE SCHEMA, у пользователя должно быть соответствующее разрешение CREATE.
Чтобы назначить другого пользователя владельцем создаваемой схемы, у участника должно быть разрешение IMPERSONATE на этого пользователя. Если роль базы данных указана в качестве владельца, то вызывающий объект должен входить в роль или иметь на нее разрешение ALTER.
Для обеспечения обратной совместимости синтаксиса разрешения на CREATE SCHEMA не проверяются, поскольку схема не создается.
Примеры
А. Создание схемы и предоставление разрешений
В следующем примере создается схема Sprockets , принадлежащая Annik , которая содержит таблицу NineProngs . Инструкция предоставляет разрешение SELECT для Mandar и запрещает SELECT для Prasanna . Обратите внимание на то, что Sprockets и NineProngs создаются в одной инструкции.
USE AdventureWorks2022; GO CREATE SCHEMA Sprockets AUTHORIZATION Annik CREATE TABLE NineProngs (source int, cost int, partnumber int) GRANT SELECT ON SCHEMA::Sprockets TO Mandar DENY SELECT ON SCHEMA::Sprockets TO Prasanna; GO Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
B. Создание схемы и таблицы в схеме
В следующем примере создается схема Sales , а затем таблица Sales.Region .
CREATE SCHEMA Sales; GO CREATE TABLE Sales.Region (Region_id INT NOT NULL, Region_Name CHAR(5) NOT NULL) WITH (DISTRIBUTION = REPLICATE); GO C. Задание владельца схемы
В следующем примере создается схема Production , принадлежащая Mary .
CREATE SCHEMA Production AUTHORIZATION [Contoso\Mary]; GO Создание схемы базы данных
Схема базы данных (database diagram) – это визуальное представление таблиц в базе данных. Для создания таблиц и отношений между ними можно использовать инструменты создания схемы баз данных, которые предусмотрены в SQL Server. А для создания схемы баз данных с помощью окна Server Explorer среды Visual Studio .NET выполните перечисленные ниже действия.
РИС. 1.4. Ввод данных в таблицу с помощью команды контекстного меню Retrieve Data from Table
1. Разверните узел базы данных Novelty в окне Server Explorer, щелкните правой кнопкой мыши на узле Database Diagrams и выберите в контекстном меню команду New Diagram (Создать схему).
2. В диалоговом окне Add Table (Добавить таблицу) будет приведен список таблиц базы данных. Выберите созданную ранее таблицу tblCustomer, щелкните на кнопке Add (Добавить), а затем на кнопке Close.
3. В результате будет создана новая схема базы данных с таблицей tblCustomer (рис. 1.5).
4. Для добавления второй таблицы в эту схему щелкните правой кнопкой мыши на пустом пространстве возле таблицы tblCustomer и выберите в контекстном меню команду New Table (Создать таблицу).
5. На экране появится диалоговое окно Choose Name, в которое следует ввести имя новой таблицы tblOrder.
6. После этого в окне схемы появится сетка для определения полей новой таблицы. Создайте в ней поля, показанные на рис. 1.6.
7. Выберите команду меню File?Save для сохранения схемы базы данных, и на экране появится диалоговое окно с просьбой подтвердить создание новой таблицы. Щелкните на кнопке Yes, и в окне Server Explorer появится вновь созданная таблица.
РИС. 1.5. Схема базы данных Novelty, которая содержит все таблицы, выбранные в диалоговом окне Add Table
РИС. 1.6. Схема базы данных Novelty, которая содержит все таблицы, выбранные в диалоговом окне Add Table
После создания таблиц для клиентов и заказов следует установить связь (отношение) между ними. Например, при создании заказа идентификатор клиента ID из таблицы tblCustomer копируется из записи клиента в поле CustomerID таблицы tblOrder. Для указания этого отношения между таблицами на схеме базы данных выполните перечисленные ниже действия.
1. Щелкните на поле ID в таблице tblCustomer и перетащите его к полю CustomerID таблицы tblOrder.
2. На экране появится диалоговое окно Create Relationship (Создать отношение), в котором можно указать свойства отношения между двумя таблицами. После этого уже нельзя создавать заказы для клиентов с идентификаторами, которых нет в таблице клиентов. Это ограничение имеет большое практическое значение и потому с ним следует согласиться.
3. Схема базы данных обновляется для отражения нового отношения, как показано на рис. 1.7.
РИС. 1.7. Схема базы данных с обозначением отношения между табл tblCustomer и tblOrder
Для сохранения созданной схемы базы данных DatabaseDiagram1 выберите команду File?Save DatabaseDiagram1. В диалоговом окне Save New Database Diagram (Сохранить имя новой схемы базы данных) введите имя Relationships (Отношения) для новой схемы базы данных. При этом возможно появление диалогового окна с просьбой подтвердить создание новой таблицы. Щелкните на кнопке Yes для сохранения вновь созданной таблицы tblOrder.
SQL Server может сохранять схему базы данных вместе с самой базой данных. Таким образом, всегда можно получить доступ к схеме, даже с помощью других инструментов, например с помощью программы Enterprise Manager или среды Visual Studio .NET.
Читайте также
Создание базы данных
Создание базы данных Чтобы генерировать содержимое Web-страницы на основе каких-то данных, нужно сначала подготовить сами данные. Поэтому начнем работу с создания базы данных.Наша база данных будет представлять собой три массива, хранящие списки Web-страниц, которые
Создание базы данных
Создание базы данных Чтобы генерировать содержимое Web-страницы на основе каких-то данных, нужно сначала подготовить сами данные. Поэтому начнем работу с создания базы данных.Наша база данных будет представлять собой три массива, хранящие списки Web-страниц, которые
Использование программы Microsoft Visio для просмотра и изменения схемы базы данных
Использование программы Microsoft Visio для просмотра и изменения схемы базы данных Помимо инструментов среды Visual Studio .NET, для создания, просмотра и изменения схем базы данных могут использоваться другие очень удобные средства. Программа Microsoft Visio обладает всеми необходимыми
Создание проекта базы данных
Создание проекта базы данных Проект базы данных Visual Studio.NET предназначен для разработки, тестирования и выполнения сценариев и запросов SQL, а также для управления ими. Этот проект не связан напрямую ни с одним из .NET-совместимых языков программирования, например Visual Basic или
2.2. Создание базы данных
2.2. Создание базы данных В этом разделе вы узнаете, как создать и удалить базу данных, изменить для нее кодировку по умолчанию, выбрать текущую БД, а также просмотреть список всех баз на данном сервере MySQL.Чтобы создать базу данных, выполним командуCREATE DATABASE <Имя базы
4.4. Создание пространственной базы данных MySQL
4.4. Создание пространственной базы данных MySQL Этот раздел описывает типы данных, которые Вы можете использовать для представления пространственных данных в MySQL и функции, доступные для создания и поиска пространственных
Создание базы данных с использованием isql
Создание базы данных с использованием isql Существует более одного способа создания базы данных с использованием isql. Здесь приведен один простой способ интерактивного создания базы данных — тем не менее для работы с серьезной базой данных вы должны создавать и
ГЛАВА 15. Создание и ведение базы данных.
ГЛАВА 15. Создание и ведение базы данных. База данных Firebird — это, прежде всего, файл файловой системы, находящийся под управлением подсистемы ввода/вывода главной машины, на которой выполняется сервер Firebird. Как только сервер создаст этот файл, его система управления
Создание и изменение объектов базы данных
Создание и изменение объектов базы данных В сессии isql вы можете выдавать операторы DDL один за другим для создания (CREATE) или удаления (DROP) баз данных, доменов, генераторов, таблиц, индексов, триггеров и хранимых процедур. Вместе с исключениями генераторов вы также можете
Создание базы данных Employee с помощью скриптов
Создание базы данных Employee с помощью скриптов После выполнения инсталляции Firebird база данных Employee будет создана и будет сделана ее резервная копия из скриптов. Скрипты empddl.sql (который создает метаданные) и empdll.sql (который заполняет базу данных) доступны в области Downloads на
Глава 3 Создание таблиц новой базы данных
Глава 3 Создание таблиц новой базы данных Как уже было сказано в главе 2, разработка новой базы данных «Контрольно-измерительные приборы» производится в программной среде Access 2002.Формирование БД в Access состоит из ряда последовательных этапов, описываемых ниже. Первый этап
12.6. Создание базы данных
12.6. Создание базы данных В Microsoft Access поддерживаются два способа создания базы данных. Имеется возможность создать пустую базу данных, а затем добавить в нее таблицы, формы, отчеты и другие объекты. Такой способ является наиболее гибким, но требует отдельного определения
Создание новой базы данных
Создание новой базы данных Все таблицы и другие объекты базы данных Access 2007 хранятся в одном файле с расширением ACCDB (во всех предыдущих версиях использовались файлы с расширением MDB). Перед созданием таблиц следует создать пустую базу данных следующим способом.1.
Как создать схему данных в sql server management studio
1. В командной строке выполните bat-файл ( thingworxMssqlSchemaSetup.bat ) с соответствующими параметрами (перечисленными в приведенной ниже таблице):
thingworxMssqlSchemaSetup.bat -h -i -p -l -r -d -o
При выполнении этого bat-файла появится запрос пароля для входа в систему (созданного выше вручную) для каждого сценария. Каждый раз вводите пароль и нажимайте клавишу Enter.
При выполнении сценария отображается следующее ожидаемое предупреждение: Warning! The maximum key length for a clustered index is 900 bytes. The index ‘data_table_indexes_pkey’ has maximum length of 902 bytes. For some combination of large values, the insert/update operation will fail.
При выполнении этих сценариев все таблицы, индексы, процедуры и другие объекты, необходимые для настройки ThingWorx Platform, будут созданы в базе данных thingworx со схемой по умолчанию twschema .