Схема Schema с точки зрения базы данных представляет собой контейнер объектов типа таблиц, триггеров, хранимых процедур и т.п. В данной статье будут рассмотрены вопросы создания и удаления схемы БД следующих СУБД:
- Oracle: Schema привязывается к пользователю, т.е. наименование схемы, как правило, является учетной записью пользователя. Схема создается при создании пользователем первого объекта, и все последующие объекты созданные этим пользователем становятся частью этой схемы. Кроме этого Oracle позволяет создавать схему как контейнер одновременно с объектами базы данных.
- MSSQL: в Microsoft SQL Server начиная с версии 2005 жесткая связь между пользователями и схемами была отменена. Пользователи могут получить доступ на выполнение определенных операций с объектами схемы: чтение, запись, обновление или выполнение.
- PostgreSQL: Schema создается внутри объекта базы данных. Сервер может управлять несколькими базами данных, каждая из которых может включать несколько схем. То есть, как и в MSSQL, схема не связана с учетной записью пользователя.
- MySQL: понятие Schema имеет тождественный смысл с Database. База данных Database является контейнером объектов, к которым пользователь получает доступ.
- Derby: Schema не имеет жесткой связи с пользователем и является контейнером объектов, для доступа к которым пользователь должен иметь соответствующие привилегии.
Создание схемы, CREATE SCHEMA
Для создания схемы необходимо использовать SQL скрипт CREATE SCHEMA . Разные схемы могут включать одноименные объекты. При обращении к объектам разных схем необходимо указывать наименование схемы как префикс. Для создания схемы пользователь должен иметь соответствующие привилегии. Конечно же, superuser"ы данной привилегией владеют.
Создание схемы Oracle
Oracle относится к тем платформам СУБД, которые не имеют явной поддержки команды CREATE SCHEMA . Однако он все же неявно создаёт схему, когда пользователь создаёт свой первый объект базы данных. Данная СУБД использует команду "CREATE SCHEMA" для создания за одну транзакцию таблиц и представлений вместе с предоставлением доступа к ним.
Необходимо отметить, что Oracle разрешает дополнительно использовать в инструкции CREATE SCHEMA стандартные скрипты CREATE TABLE, CREATE VIEW и GRANT. Нельзя использовать любые расширения этих команд, имеющиеся в Oracle, если эти команды включены в инструкцию CREATE SCHEMA. Синтаксис создания объектов со схемой.
CREATE SCHEMA AUTHORIZATION schema options CREATE TABLE CREATE VIEW GRANT
В следующем примере для схемы "painter"" создаются таблица и представление. Коме этого в инструкции CREATE SCHEMA определен доступ к объектам.
CREATE SCHEMA AUTHORIZATION painter CREATE TABLE paint (paint_id NUMBER PRIMARY KEY, paint_size NUMBER, colour VARCHAR2(10)) CREATE VIEW large_paints AS SELECT paint_id, colour FROM paint WHERE paint_size = 100 GRANT select ON large_paints TO scott;
Порядок команд создания объектов и предоставления прав доступа в инструкции CREATE SCHEMA не критичен, но все же следует соблюдать синтаксис. Oracle выполняет инструкцию CREATE SCHEMA только в том случае, если все входящие в нее инструкции CREATE и GRANT были выполнены успешно.
Создание схемы MS SQL
В СУБД MS SQL при помощи транзакции CREATE SCHEMA можно создать схему одновременно с созданием в ней таблиц, представлений и предоставить или запретить доступ на эти объекты с использованием операторов GRANT, DENY или REVOKE.
CREATE SCHEMA schema_name_clause [
Параметры скрипта:
- schema_name
Идентификационного наименование схемы. - AUTHORIZATION owner_name
Учетная запись пользователя базы данных, который является владельцем схемы. - table_definition
SQL скрипт создания таблицы внутри схемы. Пользователь должен иметь права CREATE TABLE в текущей базе данных. - view_definition
SQL скрипт создания представления внутри схемы. Пользователь должен иметь права CREATE VIEW в текущей базе данных. - grant_statement
Предоставление разрешения на любой объект, за исключением новой схемы. - revoke_statement
Отмена разрешения на любой объект, за исключением новой схемы. - deny_statement
Запрещение разрешения на любой объект, за исключением новой схемы.
Транзакция CREATE SCHEMA являются атомарной. Если в процессе выполнения инструкции CREATE SCHEMA возникают ошибки, то ни один из указанных объектов не создается и ни одно разрешение не предоставляется.
Объекты, которые необходимо создать при помощи инструкции CREATE SCHEMA, могут быть перечислены в любом порядке, за исключением представлений, ссылающихся на другие представления. В этом случае ссылающееся представление должно быть создано после того представления, на которое оно ссылается.
При помощи инструкции GRANT можно предоставлять разрешения на объект еще до того, как он будет создан, а инструкция CREATE VIEW может появляться раньше инструкций CREATE TABLE, создающих таблицы, на которые ссылается представление. Кроме того, инструкции CREATE TABLE могут декларировать внешние ключи к таблицам, определенным позже в инструкции CREATE SCHEMA.
Создание схемы PostgreSQL
Новая схема создается в текущей базе данных сервера, с которым установлено соединение. Наименование схемы должно быть уникально для данной Database.
CREATE SCHEMA schema_name [ AUTHORIZATION username ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]
Параметры скрипта:
- username
имя пользователя (role name) владельца схемы. Если username отсутствует, то будет использоваться имя пользователя, исполняющего данный скрипт. Чтобы создать схему для владельца с другой ролью (role) необходимо иметь права данной роли или быть superuser"ом. - schema_element
определяет SQL скрипт одновременного создания внутри схемы объектов. Вместе со схемой можно создать TABLE, VIEW, INDEX, SEQUENCE, TRIGGER с получением на них соответствующих прав. Если в скрипте присутствует AUTHORIZATION username то все права будут принадлежать username. После создания схемы можно также создать в ней другие объекты.
Примеры создания схемы в PostgreSQL:
CREATE SCHEMA orders; CREATE SCHEMA AUTHORIZATION alex; CREATE SCHEMA customers; CREATE TABLE customers.companies (id name not null, name varchar(64) not null, address varchar(128), registered date); CREATE VIEW customers.v_companies AS SELECT id, name, address FROM customers.companies WHERE address IS NOT NULL;
Примечание: Согласно SQL стандарту, владелец схемы всегда является "хозяином" всех находящихся внутри объектов. PostgreSQL, также как и MSSQL, разрешает создание внутри схем объектов, "хозяином" которых может быть не владелец схемы, но имеющий соответствующие привилегии данной схемы.
Создание базы данных MySQL
В MySQL понятие базы данных database равносильно понятию schema в других СУБД типа MSSQL, PostgreSQL, Derby и имеет тот же смысл - контейнер не привязанных к пользователю объектов. Создание базы данных выполняется с помощью оператора CREATE DATABASE .
CREATE DATABASE db_name ;
Параметры скрипта:
- db_name
Имя создаваемой базы данных. - IF NOT EXISTS
Если данный паарметр не указать, то при создании базы данных с уже существующим именем, возникнет ошибка выполнения команды. - CHARACTER SET
Определение кодировки таблиц базы данных. - COLLATE
Определение порядка сортировки данных.
Если при создании таблицы эти параметры CHARACTER SET и COLLATE не указываются, то кодировка и порядок сортировки вновь создаваемой таблицы берутся из значений, указанных для текущей базы данных.
Примеры использования CREATE DATABASE
CREATE DATABASE forum CREATE DATABASE forum CHARACTER SET utf8 COLLATE utf8_general_ci;
Создание схемы Derby
CREATE SCHEMA { [ schemaName AUTHORIZATION username ] | [ schemaName ] | [ AUTHORIZATION username ] }
Наименование схемы не должно содержать более 128 символов и быть уникальным внутри базы данных. Также наименование не должно начинаться с префикса SYS.
Только владелец базы данных может создавать схему с наименованием, отличным от имени/логина пользователя, и только владелец базы данных может определять AUTHORIZATION username с именем/логином пользователя, отличным от текущего логина.
Примечание: username может принадлежать только пользователю, а не role.
CREATE SCHEMA CUSTOMERS AUTHORIZATION serg; CREATE SCHEMA AUTHORIZATION loran;
Удаление схемы, DROP SCHEMA
Для удаления схемы необходимо использовать SQL скрипт drop schema .
Удаление схемы Oracle
Для удаление схемы СУБД Oracle необходимо удалить пользователя; объекты схемы удаляются автоматически:
DROP USER user_name ;
Ключевое слово CASCADE означает удалить все связанное со схемой (пользователем) объекты.
Удаление схемы MSSQL
DROP SCHEMA schema_name
Удаляемая схема не должна содержать никаких объектов. Если схема содержит объекты, выполнение инструкции DROP заканчивается сбоем. Сведения о схемах можно увидеть в представлении каталога sys.schemas.
Удаление схемы PostgreSQL
Схема может быть удалена только её владельцем или superuser"ом. Необходимо помнить, что владелец owner может удалить схему и все содержащиеся в ней объекты даже если они ему не принадлежат.
При удалении схемы в PostgreSQL можно дополнительно включить параметры:
DROP SCHEMA [ IF EXISTS ] schema_name [, ...] [ CASCADE | RESTRICT ]
Параметры скрипта:
- IF EXISTS
Проверка наличия схемы. Если схемы нет, то исключительная ситуация не возникнет. - CASCADE
Автоматически удалять объекты, содержащиеся в схеме. - RESTRICT
Не удалять схему, если она содержит объекты. Этот параметр используется по умолчанию.
Пример удаления схемы orders вместе с содержащимися в ней объектами:
DROP SCHEMA orders CASCADE;
Удаление базы данных MySQL
В СУБД MySQL удалить можно не только пустую базу данных.
DROP DATABASE db_name
Если не указать параметр IF EXISTS, то при попытке удаления не существующей базы данных, возникнет ошибка выполнения команды. Данный параметр доступен в MySQL 3.22 и более поздних версиях. При выполнении команды DROP DATABASE удаляется как сама база данных, так и все объекты, которые в ней находятся.
В следующем примере удаляется база данных "forum" :
DROP DATABASE forum
Удаление схемы Derby
В СУБД Derby удалить можно только пустую схему. Схемы SYS и APP (схема пользователя по умолчанию) не могут быть удалены.
DROP SCHEMA schema_name RESTRICT;
Ключевое слово RESTRICT является обязательным и обязывает выполнение проверки наличия объектов в удаляемой схеме.
Обновление схемы, ALTER SCHEMA
В SQL стандарте скрипт ALTER SCHEMA не определен.
В PostgreSQL владельца или наименование схемы можно изменить скриптом ALTER SCHEMA.
ALTER SCHEMA name OWNER TO newowner; ALTER SCHEMA name RENAME TO newname;
Чтобы использовать ALTER SCHEMA необходимо быть владельцем схемы и иметь соответствующие привилегии. При изменении наименования схемы нужно иметь привилегии CREATE для текущей базы данных. Чтобы сменить владельца, необходимо быть членом соответствующей роли и иметь в ней привилегии CREATE.
В СУБД MSSQL с помощью скрипта ALTER SCHEMA можно перенести объекты из одной схемы в другую.
ALTER SCHEMA schema_name TRANSFER [
Пользователи и схемы в MSSQL полностью разделены. Инструкция ALTER SCHEMA применяется только для перемещения объектов между схемами в пределах одной базы данных. В следующем примере схема Customers изменяется путем перемещения в нее таблицы Cities из схемы Persons.
ALTER SCHEMA Customers TRANSFER Persons.Cities;
Создание пользователя мы с вами освоили. Давайте рассмотрим как уже созданного пользователя можно видоизменять. Допустим, вы хотите поменять ему пароль. Например, при создании БД пользователи SYS и SYSTEM получают пароли по умолчанию, которые просто необходимо заменить сразу же после создания БД!!! Иначе любой злоумышленник знакомый с БД Oracle может нанести вам большой ущерб!!! Для того, чтобы изменить пароль необходимо использовать команду ALTER - это так же довольно обширная команда и рассматривать все, что она может мы пока не будем. Сразу стоит отметить, что Oracle не допускает пароли типа 123456 или 543kolobok . Например, попробуйте следующее:
ALTER USER DUMMY IDENTIFIED BY 123456 /
Получаем:
ALTER USER DUMMY IDENTIFIED BY 123456 * ошибка в строке 1: ORA-00988: отсутствует или неверен пароль (пароли)
ALTER USER DUMMY IDENTIFIED BY 543kolobok /
Получаем:
ALTER USER DUMMY IDENTIFIED BY 543kolobok * ошибка в строке 1: ORA-00988: отсутствует или неверен пароль (пароли)
То есть пароли в Oracle должны начинаться с буквы, а не с цифры! Но в середине или в конце цифры применять можно! Я к стати до сих пор не могу понять, почему они так сделали? Хотя оставим это на усмотрение Oracle Inc. :) Давайте изменим пароль для схемы DUMMY с DUMB на PIONER4 :
ALTER USER DUMMY IDENTIFIED BY PIONER4 /
Получаем:
SQL> ALTER USER DUMMY IDENTIFIED BY PIONER4 2 / Пользователь изменен.
Вот теперь все верно! Пароль нашего пользователя изменен и мы можем в этом убедиться:
CONNECT DUMMY/PIONER4@PROBA
Получаем:
SQL> CONNECT DUMMY/PIONER4@PROBA Соединено.
SQL> CONNECT SYSTEM/MANAGER@PROBA Соединено.
С помощью команды ALTER - можно так же менять квоты пользователя в табличных пространствах. Например, урезать DUMMY за плохое поведение квоту на табличном пространстве USERS до 50 Мб:
ALTER USER DUMMY QUOTA 50M ON USERS /
Получаем:
SQL> ALTER USER DUMMY 2 QUOTA 50M ON USERS 3 / Пользователь изменен.
Теперь DUMMY не выйдет за пределы 50 Мб. Чего ему вполне достаточно для нашего примера. Рассмотрим следующее понятие применимое к схеме БД, а именно профиль (Profile ). С помощью профилей можно ограничить количество ресурсов системы и БД доступных для пользователя, а так же управлять ограничениями налагаемыми паролями. Если пользователю не назначен профиль по умолчанию, то будет использовать профиль DEFAULT (почти каламбур!).
Давайте создадим свой профиль и назначим его нашему пользователю:
CREATE PROFILE TODUMMY LIMIT PASSWORD_LIFE_TIME 180; /
Получаем:
SQL> CREATE PROFILE TODUMMY LIMIT 2 PASSWORD_LIFE_TIME 180; Профиль создан.
Созданный нами профиль ограничивает срок действия пароля до 180 дней. Давайте, определим этот профиль для пользователя DUMMY :
ALTER USER DUMMY PROFILE TODUMMY /
Получаем:
SQL> ALTER USER DUMMY 2 PROFILE TODUMMY 3 / Пользователь изменен.
Теперь срок действия пароля PIONER4 в схеме DUMMY будет всего 180 дней. А, после этого БД не будет принимать регистрации с данным паролем. При создании профилей используется ряд ограничивающих ресурсов. Чуть позже мы их опишем. Допустим, если у вас есть необходимость заблокировать определенного пользователя системы, это возможно сделать, применив все тот же ALTER USER :
ALTER USER DUMMY ACCOUNT LOCK /
SQL> ALTER USER DUMMY ACCOUNT LOCK 2 / Пользователь изменен.
И соответственно разблокировать пользователя:
ALTER USER DUMMY ACCOUNT UNLOCK /
Получим заблокированного пользователя:
SQL> ALTER USER DUMMY ACCOUNT UNLOCK 2 / Пользователь изменен.
Давайте рассмотрим так же такое понятие неразрывно связанное с пользователями БД, как роль (role ). По своей сути роль это некая группа, в которой объединяются несколько привилегий. При использовании ролей можно изменять уровни привилегий для нескольких пользователей одновременно, что упрощает процесс администрирования в БД имеющей несколько сот пользователей. Для примера создадим роль DMROLE :
CREATE ROLE DMROLE /
Получаем:
SQL> CREATE ROLE DMROLE 2 / Роль создана.
Пока это "пустая" роль. Теперь назначаем ей ряд привилегий с помощью оператора GRANT , например ALTER SESSION :
GRANT ALTER SESSION TO DMROLE /
Получаем:
SQL> GRANT ALTER SESSION TO DMROLE 2 / Привилегии предоставлены.
А вот теперь отпишем эту роль для нашего пользователя DUMMY конечно же с помощью оператора GRANT :
GRANT DMROLE TO DUMMY /
Получаем:
SQL> GRANT DMROLE TO DUMMY 2 / Привилегии предоставлены.
Вот теперь схема DUMMY может использовать привилегию ALTER SESSION . И конечно же, самое главное - "Удаление пользователя"! Удалить пользователя и все объекты его схемы достаточно просто:
SQL> DROP USER DUMMY CASCADE 2 / Пользователь удален.
Ключевое слово CASCADE означает удалить все связанное со схемой (пользователем). Что то, как то грустно все у нас кончается. :) Таким образом, теперь для вас я думаю, стала яснее картина создания схемы (пользователя) и работа с ней. Стоит отметить к слову, что назначение ролей пользователю БД не всегда оправдано, но как я уже сказал значительно облегчает администрирование. Думаю, вы уже заметили, что мы с вами работаем пока только через SQL*Plus . Есть и более продвинутые инструменты для администрирования БД Oracle , такое мощное средство как, например Enterprise Manager . Но это тема для отдельного рассказа. Да и пока его использование нам не столь необходимо. А вот вам задание создайте собственного пользователя и несколько объектов в нем и расскажите мне, что у вас получилось! Дерзайте! :)
СУБД Oracle, как и все ее реальные конкуренты - старая система. Такое долголетие было бы невозможно без ряда технических решений, удачно (с этой точки зрения) предложенных еще в стародавние времена. Но наряду с этим в системе есть и примеры дефектов начального проектирования. Когда-то они не казались таковыми, а потом исправлять их стало очень сложно, так что только в самых последних версиях, стали прощупываться пути решения нечаянно запрограммированной проблемы. Такой является проблема "пользователей" и "схем".
Проблема
В Oracle понятия "схема" и "пользователь" нераздельно слились воедино. Формально два разных слова "user" и "schema" используются в Oracle для обозначения одного и того же: "схемы-пользователя". Документация на этот счет стыдливо говорит, что "при заведении пользователя (команда CREATE USER) автоматически создается схема с таким же именем". С другой стороны, отдельных манипуляций со схемами в Oracle не предусмотрено (команда CREATE SCHEMA в Oracle обманчива; она не создает схему, как можно было бы подумать), вот и выходит, в системе понятий Oracle "схема" = "пользователь".
Для разработчика же эти два понятия не идентичны. Так, схема представляет собой своего рода контейнер хранимых в БД объектов, несущий традиционно двойную функциональную нагрузку: как средства организации данных (объектов в базе много, но не всем приложениям все они интересны) и как средство защиты данных от посторонних приложений. Пользователь же, по своей изначальной идее - это конкретное лицо, которое может подключаться к СУБД для работы с теми или иными данными, проверяться на наличие полномочий, контролироваться на предмет совершаемых действий и т. д.
Данные принадлежат информационной системе, предприятию, а пользователи могут наниматься и увольняться. Как сымитировать такой способ работы в Oracle?
Решение
Возможно, кто-то уже придумал свое решение этой проблемы. Если нет - можно воспользоваться предлагаемым ниже.
(1)
Для хранения объектов "отдела кадров" создаем схему/пользователя HR:
CREATE USER hr IDENTIFIED BY you_password;Далее по мере необходимого уточняем все свойства этой схемы, например:
ALTER USER hr DEFAULT TABLESPACE hr_ts DEFAULT TABLESPACE temp;(2)
Для юзеров создаем пользователей Oracle, например:
CREATE USER pete IDENTIFIED BY password;
CREATE USER mary IDENTIFIED BY 12345;
…
Далее пользователям нужно приписать необходимые свойства. В типичном случае все они одинаковы, так что имеет смысл написать один-единственный сценарий, который наделял бы каждого нового "Петю" или "Машу" требуемыми полномочиями. Что туда должно входить? Как минимум, системная привилегия CREATE SESSION. Но кроме этого, для доступа к своим таблицам от имени HR следует выдать что-то вроде
GRANT SELECT, INSERT, UPDATE, DELETE ON main_hr_table TO pete;(3)
Это еще не все. Пользователь PETE действительно теперь сможет подключаться к СУБД от своего имени и работать с таблицей MAIN_HR_TABLE, однако ссылаться на нее он будет вынужден по полному имени: HR.MAIN_HR_TABLE, так как это не его таблица. Можно ли избежать этого и заставить его чувствовать себя "как дома"? Можно. Ему достаточно выдать:
ALTER SESSION SET CURRENT_SCHEMA=hr;Удобнее, однако, эту команду "завернуть" в триггер, срабатывающий при подключении к "схеме" PETE, то есть выдать, например, от имени SYS:
CREATE OR REPLACE TRIGGER set_hr_schema_for_pete
AFTER LOGON ON pete.SCHEMA
BEGIN
EXECUTE IMMEDIATE "ALTER SESSION SET
CURRENT_SCHEMA=hr";
END;
/
Теперь, подключаясь к СУБД, пользователь PETE будет видеть объекты HR "как свои", по короткому имени, правда свои собственные таблицы он вынужден будет называть "целиком", например PETE.MY_PETE_TABLE, но нам, кажется, это и не важно.
Особенности предложенного решения
- его надо автоматизировать
- невозможно создавать и удалять объекты
- можно использовать системный аудит
координатор Евро-Азиатской Группы Пользователей Oracle,
преподаватель УКЦ Interface Ltd.