Создание схемы hr oracle
Думаю, настало время разобраться с тем как же создаются "схемы" в БД Oracle. Под понятием схема имеется ввиду сам созданный аккаунт или говоря прямо - пользователь БД! Целью создания пользователя как раз и является получение схемы БД, с определенными правами и привилегиями. Создание пользователя в БД Oracle достаточно не сложный, но в тоже время довольно концептуальный момент. Для создания пользователя (схемы), применяется команда DDL - CREATE USER. Она имеет следующий синтаксис:
- [пользователь] [Username] - Имя пользователя (название схемы).
- [пароль] [Password] - Пароль для учетной записи.
- DEFAULT TABLESPACE - Табличное пространство в котором будут находиться создаваемые в данной схеме объекты. Эта настройка не дает пользователю права создавать объекты - здесь устанавливается только значение по умолчанию.
- TEMPORARY TABLESPACE - Табличное пространство, в котором находятся временные сегменты, используемые в процессе сортировки транзакций.
- QUOTA - Позволяет пользователю сохранять объекты в указанном табличном пространстве, занимая там место вплоть до определенного в квоте общего размера.
К слову сказать, в чем мы далее и убедимся. Для того, чтобы запросы пользователей могли создавать временные сегменты в табличном пространстве TEMP, им не нужны квоты на дисковое пространство. Попробуем создать пользователя! Запускайте SQL*Plus с пользователем SYS или SYSTEM пароли администраторов смотрите в шаге 5! Из всего выше сказанного, запишем вот такую конструкцию:
Здесь мы создаем пользователя (схему) DUMMY с паролем DUMB и позволяем ему резвится на 100 Мб пространства USERS и еще немного выделяем из пространства TEMP. Получаем в результате:
Ок! Пользователь (схема) создан. Наверное, можно уже подключится и начать создавать объекты! Пробуем!
Именное такой синтаксис подключения можно использовать, он еще называется строка коннекта и расписывается вот так:
Опа! Не повезло! Создание пользователя - это еще не все! Теперь ему нужно разрешить самое основное - создавать сессию с сервером. Сделать это можно командой GRANT. Она достаточно объемная и мы ей займемся чуть позже, а пока восстановим подключение:
Даем пользователю право создавать сессию с сервером:
Вот теперь можно немного перевести дух. Итак, мы создали пользователя, определили ему табличные пространства, назначили квоты на них. И даже позволили создавать сессию с сервером. Давайте убедимся, что пользователь создан и чувствует себя нормально. Производим переконнект на админа БД:
Дадим такой запрос к представлению DBA_USERS:
Кто знаком с криптографией, может на досуге раскусить - E888ADB4D5FFE1B2 или хотя бы провести аналогию с DUMB! Итак, все с нашей схемой в порядке! Осталось только разрешить пользователю создавать объекты БД.
Да, так как оператор GRANT это DDL, то COMMIT вызывается не явно! В данном случае мы разрешили пользователю, создавать такие основные объекты БД как - TABLE, PROCEDURE, TRIGGER, VIEW, SEQUENCE. Для начала этого достаточно. А что делать, если пользователю будет необходимо изменять эти объекты? Тогда нужно добавить еще немного прав, на изменение (ALTER) вот так:
Вот теперь он может не только создавать эти объекты, но и изменять их! А, что если пользователю необходимо будет удалить какой-либо объект или удалить записи из таблиц? Тогда нужно добавить права на удаление объектов БД вот так:
Уфф! Ну вот теперь кажется все! Пользователь действительно полноценный и может работать! Помните в шаге 6 мы с вами это уже проделывали, но тогда я не вдавался в подробности, так как было не до того! А, вот теперь давайте разберемся более детально и продолжим далее.
Мы будем использовать для примеров две схемы. Встроенную схему HR, которая представляет из себя пример приложения для управления человеческими ресурсами и схема WEBSTORE – пример приложения для обработки заказов. Схема HR может быть создана при создании базы данных либо быть создана позднее выполнив скрипты из Oracle Home.
Схемы HR и WEBSTORE
Схема HR состоит из семи таблиц, связанных первичными и внешними ключами. На рисунке 9-3 отображены отношения между таблицами как на диаграмме сущность-связь.
Два из отношений на рисунке 9-3 могут быть сразу не совсем понятны. Первое, это отношение один-ко-многим таблицы EMPLOYEE к таблице EMPLOYEE. Это self-referencing внешний ключ. Это значит что много сотрудников может быть связано с одним сотрудником, и это основано на том факте что у многих сотрудников может быть один менеджер, но менеджер это также сотрудник. Эта связь реализована за счёт столбца внешнего ключа manager_id и первичного ключа employee_id.
Вторая связь которая возможно требует дополнительного пояснения это двунаправленная связь между DEPARTMENTS и EMPLOYEES. Многие сотрудники могут работать в одном департаменте, и один сотрудник может быть менеджером многих департаментов.
Таблицы в схеме
REGIONS содержит строки для георафических областей
COUNTRIES содержит строки для каждой страны, которые могут закрепляться за регионом
LOCATIONS содержат адреса, которые могут принадлежать стране
DEPARTAMENTS хранит информацию об отделах, с необязательным адресом и необязательным полем менеджера (менеджер должен существовать как сотрудник)
EMPLOYEES хранит строки для каждого сотрудника, каждому из которых должна быть назначена должность и необязательно менеджер и департамент. Менеджер в свою очередь должен быть в таблице сотрудников
JOBS содержит информацию о должностях в компании. Много сотрудников могут иметь одинаковую должность
JOB_HISTORY хранит информацию о предыдущих должностях сотрудника, уникально определяемую полями employee_id и start_date; сотруднику не может быть назначено несколько должностей в один момент времени. Каждая запись в истории ссылается на сотрудника с одной должностью в определённый момент и возможной принадлежности какому-либо отделу. Мы будем использовать HR схему для примеров и упражнений, так что она должна быть создана.
Схема WEBSTORE уже была создана если вы выполняли лабораторные работы. Если нет, мы определим отношения и связи и создадим схему и необходимые объекты. Схема WEBSTORE состоит из четырёх таблиц, связанных первичными и внешними ключами. На рисунке 9-4 покаданы связи между таблицами, как диаграмма сущность-связь.
Магазин управляет продуктами, покупателями и заказами в соответсвенно названных таблицах. Каждый заказ может состоять из нескольких продуктов с разным количеством – эти данные хранятся в таблице ORDER_ITEMS. Столбец order_item_id хранит номер для каждого уникального продукта в каждом заказе. Каждый заказ связан с одной или несколькими строками таблицы ORDER_ITEMS .
Таблицы схемы WEBSTORE
PRODUCTS хранит информацию о продуктах: описание, цена, статус и доступное количество.
CUSTOMERS содержит информацию о покупателях
ORDERS хранит информацию о заказах. Один покупатель может совершить много заказов. Заказ не может быть создан без существующего покупателя.
ORDER_ITEMS хранит детальную информацию какие продукты были заказаны в каком заказе.
Создание демонстрационных схем
Если вы создавали БД используя лабораторное пособие то схема HR уже создана. Доступна опция создания демонстрационной схемы при создании базы с помощью DBCA.
Если схема не была создана в момент создания БД вы можете создать её выполнив скрипт установленный в домашнюю директорию БД. Этот скрип необходимо выполнить используя SQL *Plus иди SQL Developer с правами пользователя SYSDBA. Во время выполнения будут запрошены некоторые значения. Например в среде Linux вначале запустим SQL *Plus из командной строки ОС
root@> sqlplus / as sysdba
Доступны разные параметры для подключения к базе данных, но такая команда подключит вас к базе данных если вы работаете на той жемашине на которой установлен Oracle.
Затем выполните команду в SQL *Plus
Символ “?” это переменная которую SQL *Plus заменит на путь к домашней директории Oracle. Выведется запрос на пароль пользователя HR, табличное и временное пространство, пароль аккаунта SYS и путь куда записывать лог о выполнении скрипта. Обычно значения табличного пространства по умолчанию и временного пространтсва это USERS и TEMP, но они должны быть созданы перед запуском скрипта. После выполнения скрипта вы будете подключены к БД как пользователь HR. Чтобы убедиться в этом выполните команду
Вы увидите что подключен аккаунт HR; затем выполните
select table_name from user_tables;
И вы увидите семть таблиц схемы HR.
Для создания схемы WEBSTORE (если она не создана в процессе выполнения лабораторных работ) выполните следующие запросы для создания необходимых объектов и добавления данных которые будут использоваться далее.
root@> sqlplus / as sysdba
SQL> create user webstore identified by oracle default tablespace users temporary tablespace temp quota unlimited on users;
SQL> grant create session, create table, create sequence to webstore;
SQL> ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;
SQL> connect webstore/oracle
SQL> create table customers(
customer_id number(8) not null constraint pk_customer_id primary key,
join_date date default sysdate not null,
customer_status varchar2(8) not null, customer_name varchar2(20) not null,
creditrating varchar2(10) not null, email varchar2(50) not null);
SQL> create table products(
product_id number(8) not null constraint pk_product_id primary key,
product_description varchar2(20) not null,
product_status varchar2(8) not null, price number(10,2) not null,
price_date date not null, stock_count number(8) not null);
SQL> create table orders(
order_id number(8) not null constraint pk_order_id primary key,
order_date date not null, order_status varchar2(8) not null,
order_amount number(10,2) not null,
customer_id number(8) constraint fk_customer_id references customers (customer_id));
SQL> create table order_items(
order_item_id number(8) not null,
order_id number(8) constraint fk_order_id references orders(order_id),
product_id number(8) constraint fk_prod_id references products(product_id),
SQL> create sequence cust_seq;
SQL> create sequence order_seq;
SQL> create sequence prod_seq;
После того как объекты созданы используйте следующие запросы которые запросят ввод данных для добавления строк в таблице, основываясь на данных в таблице 9-2.
insert into customers (customer_id, customer_status, customer_name, creditrating, email)
insert into products(product_id, product_description, product_status, price, price_date, stock_count)
insert into orders(order_id, order_date, order_status, order_amount, customer_id)
insert into order_items values (&item_id, &order_id, &product_id, &quantity);
Создание схемы в Oracle, поначалу может показаться немного запутанной. Вы можете подумать, что оператор CREATE SCHEMA создаст вашу схему, но это не так. Оператор CREATE SCHEMA используется только для создания объектов (т.е. таблиц, представлений и т.д.) в вашей схеме в одном SQL - предложении, но фактически не создает саму схему.
Чтобы создать схему в Oracle, вам необходимо выполнить следующие действия:
1. Создать нового пользователя в Oracle.
По сути, схема создается в Oracle при создании пользователя. (Изучите синтаксис оператора CREATE USER).
Мы можем создать нового пользователя с оператором CREATE USER следующим образом:
Этот оператор CREATE USER создаст нового пользователя samvel в базе данных Oracle, чей пароль pass4samvel , табличное пространство по умолчанию будет tbs_01 с квотой в 20 МБ, а временным табличным пространством будет tbs_01 .
2. Назначение привилегий SYSTEM новому пользователю в Oracle
Следующим шагом в настройке вашей схемы является назначение «системных привилегий» новому пользователю samvel .
Эти «системные привилегии» позволят нашему новому пользователю создавать сессию в Oracle, а также создавать таблицы, представления, триггеры, процедуры, последовательности и синонимы в новой схеме. Вот пример того, как мы можем предоставить эти системные привилегии:
Эти новые привилегии теперь предоставляются пользователю, называемому samvel .
3. Создание объектов в схеме
Теперь, когда схема (называемая samvel ) была создана с необходимыми привилегиями, вы можете создавать объекты в схеме. Это можно сделать одним из двух способов:
- Выполнение отдельных операторов SQL для создания каждого объекта. Это будет сделано с помощью операторов CREATE TABLE и CREATE VIEW.
- Выполнение оператора CREATE SCHEMA для создания нескольких объектов в одном выражении SQL.
4. Привилегии Grant объекта
После того, как вы создали свои объекты в схеме, вам необходимо предоставить привилегии, чтобы другие схемы/пользователи могли обращаться к вашим объектам базы данных (т.е. таблицам).
5. Создание синонимов для объектов
В качестве последнего шага вам может понадобиться создать синонимы, чтобы другие схемы могли обращаться к новым объектам базы данных (т.е. таблицам) без необходимости префикса имен объектов с именем схемы.
Например, если бы вы были другим пользователем с именем samvel и хотели выбрать из таблицы suppliers в схеме new_schema , вам нужно было бы запустить следующий оператор SELECT (до создания любых синонимов):
В посте рассматривается способ разблокировки и доступа к учебному и тестовому пользователю (схемы) HR в базе данных Oracle Database 18c Express Edition. Рассмотрены следующие вопросы:
- Краткий обзор Multitenant архитектуры
- Разблокировка пользователя HR
- Multitenant
- Flashback Table
- Flashback Database
- Oracle Partitioning
- In-Memory Column Store и Aggregation
- Advanced Analytics и Security
- Online Index Rebuild
- Online Table Redefinition
- Query Results Cache и PL/SQL Function Result Cache
- Oracle Advanced Compression
- Materialized View Query Rewrite
- Oracle Spatial and Graph
- Bitmap Indexes
Разблокировка пользователя (схемы) HR
Предполагается, что есть успешно установленная Oracle Database 18c Express Edition. При необходимости, можно установить Oracle Database 18c Express Edition используя следующие материалы: установка Oracle Database 18c Express Edition на Linux и установка Oracle Database 18c Express Edition на Windows. Нижеописанные шаги будут работать с Oracle Database 18c Express Edition, установленной, как на операционную систему Linux, так и на Windows.
Вариант разблокировки с помощью SQL*Plus.
Шаг 1. Подключение к CDB
Выполняется подключение к CDB с помощью пользователя sys с ролью as sysdba:
Подключение успешно прошло к CDB. Далее проверяется имя и идентификатор CDB.
Результат запроса показывает, что CDB имеет имя XE и ее уникальный идентификатор = 0. По умолчанию, после установки Oracle Database 18c Express Edition есть одна PDB с именем XEPDB1. Следующий запрос покажет существующие PDB.
Проверяется наличие пользователя HR в CDB.
Запрос не вернул данные. Это означает, что пользователя HR нет в CDB. Далее необходимо подключиться к PDB и найти там HR.
Шаг 2. Подключение к PDB
Есть два способа подключиться к PDB с использованием SQL*Plus.
Способ 1. Находясь в CDB, подключиться к PDB используя команду alter session. В примере ниже происходит переключение из сеанса CDB к PDB с именем XEPDB1:
Переключение прошло успешно. Для того, чтобы удостовериться в корректности подключения, проверяется имя и идентификатор PDB базы:
Запросы показывают характеристики существующей PDB (Шаг 1.).
Способ 2. Можно подключиться к PDB с консоли операционной системы, указав параметры подключения.
Ниже выполняется подключение к PDB под пользователем sys с указанием IP адреса сервера БД, порта и имени PDB (по умолчанию для созданной PDB (XEPDB1) используется порт 1539):
Подключение прошло успешно.
Для информации: Администраторы баз данных временами выполняют подключение к БД используя аутентификацию на уровне операционной системы с помощью команды sqlplus / as sysdba и без указания пароля. При запуске этой команды в среде с Multitenant архитектурой будет осуществлено подключение к CDB. Для того, чтобы напрямую подключиться к PDB минуя CDB, используется sqlplus / as sysdba и без указания пароля, также необходимо в переменную среду операционной системы добавить новый системный параметр ORACLE_PDB_SID и в его значении указать название PDB. Этот параметр для подключения к PDB без указания пароля могут осуществлять только пользователи sys и system. Остальные пользователи будут автоматически подключены к CDB, если не укажут параметры подключения к PDB. Ниже описываются шаги подключения к PDB для пользователя sys с применением параметра ORACLE_PDB_SID в переменной среде операционной системы. Это очень удобный способ для администраторов баз данных:
Подключение к PDB прошло успешно напрямую из операционной системы без указания пароля и параметров подключения PDB. Далее проверяется имя и идентификатор PDB.
После успешного подключения к PDB c использованием одного из двух способов определяется наличие пользователя HR, а также его статус.
Запускается запрос поиска пользователя HR среди всех существующих пользователей в XEPDB1:
Получен результат, подтверждающий наличие пользователя HR в PDB.
При помощи запроса определяется имя, статус и дата блокировки пользователя HR:
Шаг 3. Разблокировка пользователя HR
После установки Oracle Database 18c Express Edition учетная запись HR заблокирована и пароль у нее просрочен (необходимо задать новый пароль) (см. предыдущий шаг – Шаг 2.). В этом случае, система позволяет сделать запросы к объектам HR (таблицам, представлениям, функциям и т.п.) от имени других пользователей при наличии соответствующих привилегий. Например, при выполнении запроса на определение количества строк в таблице EMPLOYEES пользователя HR под пользователем SYS система успешно выдаст следующий результат:
Для пользователя HR назначается новый пароль:
При попытке подключения к PDB, не разблокировав пользователя, можно получить следующую ошибку:
Необходимо заново подключиться к PDB под пользователем sys:
и разблокировать пользователя HR следующей командой:
Операции назначения пароля и разблокировки пользователя HR прошли успешно. Проверяется статус пользователя:
Пользователь HR разблокирован и новый пароль активен. Это означает, что теперь можно подключиться к PDB с именем XEPDB1 под учебным тестовым пользователем HR и начать работу.
Шаг 4. Подключение к PDB с учетной записью HR.
Используя данные для подключения к PDB, выполняется вход систему под учетной записью HR и запускается запрос для определения количества строк в его таблице EMPLOYEES.
На этом завершается определение наличия пользователя, назначение ему пароля и разблокировка HR в PDB Oracle Database 18c Express Edition, а также выполнение запроса к его объекту с помощью SQL*Plus.
Вариант разблокировки с помощью SQL Developer.
Шаг 1. Подключение к CDB
Для этого создается новое подключение в SQL Developer и указываются необходимые параметры подключения к CDB, такие как:
Name: XE_18c
Указывается имя соединения, которое позволяет однозначно идентифицировать CDB при подключении.
IP: 192.168.0.1
IP адрес сервера БД.
Port: 1539
Порт подключения к БД.
SID: XE
SID или имя CDB.
Username: sys
Указывается имя пользователя для подключения к БД.
Role: SYSDBA
Подключение к БД осуществляется пользователем sys. Данный пользователь может подключиться только с ролью SYSDBA.
Password:
Пароль пользователя sys, который был назначен во время установки базы данных.
После нажатия Connect произойдет успешное подключение к CDB с именем XE. Далее проверяется имя, идентификатор и версия CDB, а также выводятся существующие PDB.
Как и ожидалось, выведенные выше данные идентичны полученным с помощью SQL*Plus.
Далее проверяется наличие пользователя HR в CDB.
Запрос не вернул данные, это означает, что пользователя HR нет в CDB. Теперь необходимо подключиться к PDB и проверить наличие HR в PDB.
Шаг 2. Подключение к PDB
Создается новое подключение в SQL Developer и указываются необходимые параметры подключения к подключаемой базе данных XEPDB1, такие как:
Name: XEPDB1_18c
Указывается имя соединения, которое позволяет однозначно идентифицировать PDB при подключении.
IP: 192.168.0.1
IP адрес сервера БД.
Port: 1539
Порт подключения к БД.
SID: XEPDB1
SID или имя PDB.
Username: sys
Указывается имя пользователя для подключения к БД.
Role: SYSDBA
Подключение к БД осуществляется пользователем sys. Данный пользователь может подключиться только с ролью SYSDBA.
Password:
Пароль пользователя sys, который был назначен во время установки базы данных. Пользователи sys и system могут подключиться с одним и тем же паролем и к CDB и к PDB.
После нажатия Connect произойдет успешное подключение к подключаемой БД XEPDB1. Далее проверяется имя и идентификатор.
Результаты показывают, что было подключение к PDB с именем XEPDB1 и идентификатором 3. Определяется наличие пользователя HR в этой PDB. В иерархии дерева надо выбрать «Other Users» в соединении с именем XEPDB1_18c как показано на скриншоте:
В списке пользователей необходимо найти пользователя HR и нажать на правую кнопку. Из контекстного меню выбрать «Edit User». Откроется новое модальное окно «Edit User» как показано на скриншоте. Как видно на скриншоте учетная запись HR заблокирована (Account is Locked) и пароль у нее просрочен (Password Expired):
Шаг 3. Разблокировка пользователя HR:
В продолжение предыдущего шага необходимо:
- Задать идентичный пароль в полях New Password (новый пароль) и Confirm Password (подтвердить пароль).
- Снять галочку из пункта Password Expired (user must change next login).
- Снять галочку из пункта Account is Locked для разблокировки пользователя.
- Нажать Apply.
Пользователь HR разблокирован и ему назначен пароль. Это означает, что теперь можно подключиться к PDB с именем XEPDB1 под учебным тестовым пользователем HR и начать работу.
Шаг 4. Подключение к PDB с учетной записью HR.
Создается новое подключение в SQL Developer и указываются необходимые параметры подключения к подключаемой базе данных XEPDB1 с пользователем HR, такие как:
Name: XEPDB1_18c_hr
Указывается имя соединения, которое позволяет однозначно идентифицировать PDB при подключении с пользователем HR.
IP: 192.168.0.1
IP адрес сервера БД.
Port: 1539
Порт подключения к БД.
SID: XEPDB1
SID или имя PDB.
Username: HR
Указывается имя пользователя для подключения к БД.
Role: default
Подключение к БД осуществляется пользователем HR. Данный пользователь не может использовать роль SYSDBA.
Password:
Пароль, который был назначен пользователю HR на третьем шаге, то есть hr.
После нажатия Connect произойдет успешное подключение к PDB с именем XEPDB1 под пользователем HR. Выполняется запрос для определения количества строк в таблице EMPLOYEES:
На этом завершается определение наличия пользователя, назначение ему пароля и разблокировка HR в PDB Oracle Database 18c Express Edition, а также выполнение запроса к его объекту с помощью SQL Developer.
Читайте также: