Лекция 2: Инструменты для работы с данными и демонстрационный пример

Данные для дальнейших примеров

Таблицы

В дальнейших примерах чаще всего будут использоваться две таблицы, хранимые в "схеме данных" с именем SCOTT: это EMP и DEPT.

В Oracle и большинстве других подобных систем схемой данных называется специально оформляемое подмножество хранимых объектов, с которыми конкретные прикладные программы имеют право работать в данный момент. Понятие схемы данных задумано ради удобства работы с содержимым БД и защиты доступа. Объектами схемы в Oracle могут быть не только таблицы, но и прочие элементы, такие как индексы, процедуры и другие (в том числе так называемые "ограничения целостности"). Слово "схема" иногда употребляется и применительно к таблице. Под "схемой таблицы" понимают ее структуру вместе с некоторыми элементами описания.

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

Схема с именем SCOTT поставляется в Oracle с давних времен для демонстрационных целей.

Общее описание таблиц:

Данные в таблицах:

Таблица EMP имеет (первичный) ключ: столбец EMPNO; ключ в таблице DEPT — столбец DEPTNO. В таблице EMP столбец DEPTNO образует "внешний ключ", ссылающийся на значения из одноименного столбца таблицы DEPT:

Совпадение имен столбцов внешнего ключа и столбцов адресатов в SQL не обязательно, но в жизни приветствуется.

Таблица DUAL не принадлежит схеме SCOTT, а является "системной" (принадлежит схеме SYS) и доступна для выборки любому пользователю. Состоит из единственной строки и единственного столбца:

Таблица DUAL предназначена играть техническую роль и с этой целью не раз используется в примерах ниже.

Такой характер ее употребления сложился не сразу. Когда она создавалась в старые времена, то имела две, а не одну, как нынче, строки (отсюда название DUAL) и служила для "удвоения" строк системной таблицы выполнением соединения (join) в запросе о распределении памяти на диске в табличном пространстве БД. Впоследствии эта ее задача оказалась неактуальной, и она приобрела нынешнее значение.

В схеме SCOTT присутствует еще пара таблиц, BONUS и SALGRADE, гораздо менее интересных и далее почти не востребованых.

Упражнение. Выясните в SQL*Plus или SQL Developer структуру и содержимое таблиц BONUS и SALGRADE в схеме SCOTT.

Со временем фирма Oracle разработала более правдоподобно и сложно устроенный демонстрационный пример, нежели схема SCOTT, на этот раз из нескольких схем: HR, OE, PM, SH, IX, BI. С ним часто можно столкнуться в нынешних примерах из разных источников. Далее в тексте предпочтение отдано все-таки схеме SCOTT: она небольшого размера, и ее обычно достаточно для решаемых задач.

Пользователи и полномочия

В отличие от схемы, понятие пользователя в базах данных служит для моделирования возможностей прикладной программы совершать те или иные действия в БД. Что касается действий с объектами БД, то специального разрешения на такие действия Oracle не требует в двух случаях:

• когда объект доступа принадлежит самому пользователю,
• когда действие с объектом объявлено "публичным".

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

В БД Oracle самостоятельным является понятие пользователя, а понятие схемы подчинено ему. Так, по команде CREATE USER СУБД создаст в БД пользователя и одновременно автоматически — схему с тем же именем, к которой будет относить все объекты "этого пользователя", и только их. Схема будет удалена из БД также автоматически при удалении пользователя.

Большинство объектов БД, например, таблицы, индексы, процедуры вида SYNONYM, обязаны находиться в одной и ровно одной схеме ("принадлежать одному пользователю"). Однако небольшая часть объектов, например, вида PUBLIC SYNONYM или DIRECTORY, хранятся в БД вне всякой схемы и "не принадлежат никакому конкретному пользователю".

Демонстрационный пользователь SCOTT изначально обладает некоторым набором полномочий, например, создания таблиц и подключения к СУБД. В то же время ему изначально не приданы, скажем, полномочия создавать представления данных или же синонимы. Придать ему эти полномочия способны, например, пользователи SYS и SYSTEM. Эти пользователи могут быть названы "администраторами"; они имеются в любой БД Oracle, и при этом SYS может быть неформально назван "суперпользователем" или "главным администратором", а SYSTEM несколько ограничен в правах по сравнению с SYS.

Полномочия (= привилегии) выдаются командой GRANT и изымаются командой REVOKE.

Пример. Подсоединение в SQL*Plus в качестве SYS, простое создание пользователя YARD вместе со своей схемой и с паролем pass, наделение пользователя YARD некоторыми общими привилегиями:

CONNECT / AS SYSDBA
CREATE USER yard IDENTIFIED BY pass;
GRANT connect, resource, create view TO yard;
Подключение к СУБД под именем YARD и попытка обратиться к таблице EMP:
SQL> CONNECT yard/pass
Connected.
SQL> DESCRIBE emp
ERROR:
ORA-04043: object emp does not exist

Ошибка возникла потому, что пользователь YARD (как и любой другой) работает в собственной схеме БД, а в схеме YARD таблицы EMP нет — она имеется в схеме SCOTT. Разрешить же пользователю YARD обращаться к данным таблицы EMP в схеме SCOTT можно специально выданной командой GRANT, например:

CONNECT scott/tiger
GRANT SELECT ON emp TO yard;

В Oracle понятие "пользователь" естественно связывать не с физическим лицом, обращающимся к БД, а с приложением.