SQL-Ex blog
Создание схемы SQL для организации объектов базы данных, предоставления разрешений и упрощения обслуживания
При создании объектов или доступа к ним в 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:
- Создать новую базу данных SQL
- Создать пользователя с именем User1
- Создать схемы для отделов комплектующих (Parts), аренды (Rentals), продаж (Sales) и услуг (Service)
- Создать новую таблицу в каждой схеме
- Создать простую хранимую процедуру, которая будет запрашивать все записи в соответствующей таблице
- Предоставить разрешения на select и execute в схеме Parts пользователю User1
Затем мы подключимся как User1 и выполним хранимую процедуру [Parts].[Proc1] в базе данных SkiShop.
Запрос выполняется и, конечно, не возвращает никаких записей, поскольку таблица пустая, но мы видим, что хранимая процедура выполнилась. Мы увидели, что пользователь USER1 имеет права на select и execute для таблицы Parts.TableA, которые мы предоставили схеме Parts.
Теперь давайте посмотрим, что произойдет с другими схемами и таблицами при попытке выполнить другие три хранимых процедуры или выборки из таблиц в трех других схемах, в которых User1 не имеет разрешений.
Мы видим следующие ошибки, говорящие о том, что мы не имеем разрешений на выполнение хранимых процедур.
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’.
Схема базы данных
Схемы используются в модели безопасности компонента 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, как будет описано в следующей статье (вы можете вернуться к этим примерам позже).
В этом примере создается схема 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 показано в примере ниже:
Здесь изменяется схема 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.
Using database schemas in SQL Server
This article will explain what database schemas are, their advantages and how to create and use schemas in SQL Server.
In a relational database management system such as SQL Server, the database contains various objects. These can be tables, stored procedures, views and functions. In a database, the schema refers to the logical collection of database objects. You can use schemas to segregate objects depending on the application, access rights and security.
SQL Server schemas
SQL Server provides the following built-in logical schemas:
- dbo
- sys
- guest
- INFORMATION_SCHEMA
Every SQL Server schema must have a database user as a schema owner. The schema owner has full control over the schema. You can also change the schema owner or move objects from one schema to another.
SQL Server schemas provide the following benefits:
- Provides more flexibility and control for managing database objects in logical groups
- Allows you to move objects among different schemas quickly
- Enables you to manage object security on the schema level
- Allows users to manage logical groups of objects within a database
- Allows users to transfer ownership among various schemas
Suppose for your organization’s database, you want to group objects based on departments. For example, the tables and stored procedures for the HR department should be logically grouped in the [HR] schema. Similarly, finance department tables should be in the [Fin] schema. Each schema (logical group) contains SQL Server objects such as tables, stored procedures, views, functions, indexes, types and synonyms.
Note: The schema is a database-scoped entity. You can have the same schema in different databases of a SQL Server instance.
By default, SQL Server uses [dbo] schema for all objects in a database. We can query SCHEMA_NAME() to get the default schema for the connected user.
Listing all database schemas in the current database
You can get a list of the schemas using an SSMS or T-SQL query. To do this in SSMS, you would connect to the SQL instance, expand the SQL database and view the schemas under the security folder.
Alternatively, you could use the sys.schemas to get a list of database schemas and their respective owners.
Creating a SQL Server schema using CREATE SCHEMA
To create a new SQL Server schema, we use the CREATE SCHEMA t-SQL statement. Its syntax is shown below.
- Schema_name: This is the schema that we want to create
- Authorization: This is the schema owner name
The script to create schemas [HR], [Admin] and [Fin] with [dbo] schema owner is shown below.
You can refresh the database and view the newly created schema as shown below.
Creating a new table within a schema
To create objects such as a table, we need to specify the schema name in which the object will be created. For example, the following script creates [TableA] in different schemas [HR], [Admin] and [Fin].
You can join sys.tables and sys.schema system tables to list the table name with their schemas. For example, the below query returns [TableA] with its schema.
If you do not specify a schema while creating the object, SQL Server uses the default schema. For example, the below script creates a TableA in the dbo schema.
Note: You can create a table with a similar name in the different schema of a database.
You are required to specify the table schema in order to fetch data from the correct schema. For example, as shown above, we have [TableA] in all schemas. Therefore, if you select records directly without specifying the schema, it looks for an object in the default DBO schema. Therefore, always specify the schema name such as SELECT * FROM HR.TableA for data retrieval or performing any operations.
Creating a stored procedure within a schema
Similarly, you can create objects such as stored procedures in specified schemas. For example, the below script creates a SP in the HR schema.
Transferring the object to a different schema
At some point, you might get a requirement to move the object to a specific schema. For example, suppose you created a new schema [Org], and you want your [HR].[TableA] to move from [HR] schema to [Org] schema.
In this case, you can use the Alter SCHEMA command with the following syntax.
The below script transfers object [HR].[TableA] to the [Org] schema.
Now, let’s transfer the stored procedure [HR].[GetEmpData] to the [Org] schema.
Once you execute the script, it shows the stored procedure in the [Org] schema.
However, inside the stored procedure, the code still refers to the [HR].[TableA] schema.
Therefore, you should not move stored procedures, functions or views using the ALTER SCHEMA function as it might have references for the objects in the definition. Instead, you can drop, create or alter the procedure as shown below.
Dropping a schema
You can drop a schema in a SQL Server database, but the schema should not hold any objects. For example, if I try to drop the [Org] schema, it gives an error that you cannot drop the schema because the object GetEmpData is referencing it.
Therefore, you can either transfer the object to a different schema or drop the objects first. For example, let us drop GetEmpData stored procedures and then try to drop the schema. We again got an error because we have [TableA] in the [Org] schema.
Once we dropped or moved all objects in the database schema, you can then drop the schema.
Note: You cannot drop system schemas such as dbo, information_schema, sys.
Что такое схема в SQL и как ее создать?
Вы когда-нибудь слышали о термине Схема? Что ж, схема в SQL — это набор объектов базы данных, связанных с определенным именем пользователя базы данных. Имя пользователя, о котором я говорю, называется владельцем схемы или, что более уместно, владельцем связанной группы объектов. Давайте копнем эту тему немного глубже и обсудим ее подробно!
Повестка дня этого урока следующая.
- Что такое SQL?
- Что такое схема в SQL?
- Преимущества использования схемы
- Как создать схему?
- Использование SQL Server Management Studio
- Как изменить схему?
- Параметр
- Как сбросить схему?
Что такое SQL?
Как вы все, возможно, знаете, термин SQL означает язык структурированных запросов. SQL является стандартным языком ASI, но существует множество различных версий этого языка. SQL является стандартным языком для системы реляционных баз данных. Это поможет вам в доступе и манипулировании базами данных. Можно выполнить несколько запросов к базе данных. Данные из базы данных могут быть извлечены. Вы можете вставлять, обновлять, удалять записи в базе данных. Это помогает в создании новых баз данных. Также можно создавать новые таблицы и представления.
Перейдём дальше к следующему сегменту.
Что такое схема в SQL Server?
Схема в SQL — это набор объектов базы данных, связанных с базой данных. Имя пользователя базы данных называется владельцем схемы (владельцем логически сгруппированных структур данных). Схема всегда принадлежит одной базе данных, тогда как база данных может иметь одну или несколько схем. Кроме того, это также очень похоже на отдельные пространства имен или контейнеры, в которых хранятся объекты базы данных. Он включает в себя различные объекты базы данных, включая ваши таблицы, представления, процедуры, индекс и т. д.
Давайте двигаться дальше и рассмотрим некоторые преимущества использования Schema в SQL.
Преимущества использования схемы
- Вы можете применять разрешения безопасности для разделения и защиты объектов базы данных на основе прав доступа пользователей.
- Логической группой объектов базы данных можно управлять в базе данных. Схемы играют важную роль в организации объектов базы данных в эти логические группы.
- Схема также помогает в ситуациях, когда имя объекта базы данных совпадает. Но эти объекты попадают в разные логические группы.
- Одну схему можно использовать в нескольких базах данных.
- Схема также помогает повысить безопасность.
- Это помогает в манипулировании объектами и доступе к ним, что в противном случае является сложным методом.
- Вы также можете передать право собственности на несколько схем.
- Объекты, созданные в базе данных, можно перемещать между схемами.
Это было немного плюсов, теперь следующая тема — способ создания схемы.
Как создать схему?
Синтаксис для создания SQL:
Вы можете создать схему, используя студию управления сервером SQL. Следуйте указанным шагам!
Использование SQL Server Management Studio
Следуйте инструкциям, чтобы создать схему.
- В проводнике объектов щелкните папку баз данных.
- Создайте новую схему базы данных в базе данных.
- Щелкните правой кнопкой мыши папку «Безопасность», нажмите «Создать», выберите «Схема».
- Перейдите в диалоговое окно «Схема-Новая», введите конкретное имя, которое вы хотите создать для своей новой схемы.
- В поле владельца схемы введите имя пользователя базы данных, чтобы владеть схемой. Нажмите «Поиск», чтобы открыть диалоговое окно «Поиск ролей и пользователей».
Так создается схема. Теперь давайте посмотрим, как меняется схема.
Как изменить схему?
Схему в базе данных можно изменить с помощью оператора altered schema. Этот оператор специально используется для переименования схемы. Новый владелец должен быть уже существующим пользователем.
Синтаксис для изменения схемы:
Параметр
Поняв, как изменить схему, давайте перейдем к следующему сегменту. Мы собираемся изучить удаление схемы.
Как сбросить схему?
Чтобы удалить схему, мы используем следующий синтаксис:
Если вам нужно удалить всю базу данных, следуйте указанному синтаксису:
Это было все о схеме в SQL. Я надеюсь, что содержание объясняет вышеприведенную ценность ваших знаний. Продолжайте читать, продолжайте исследовать!
Если вы хотите прочитать больше статей о самых популярных на рынке технологиях, таких как искусственный интеллект, DevOps, этический взлом, вы можете обратиться к официальному сайту Edureka.
Обратите внимание на другие статьи из этой серии, в которых объясняются различные другие аспекты SQL.