Oracle гранты на схему

Обновлено: 02.07.2024

В данном посте описываются способы добавления функционала автоматической нумерации к таблице в базах данных Oracle.

Автоматическая нумерация или автоинкремент (в некоторых системах управления базами данных, например, в MySQL, для автоматической нумерации используется понятие автоинкремент) – это автоматическое увеличение значения колонки в таблице, чаще всего колонки, содержащей значения первичных ключей – Primary Key (обязательны к заполнению и должны быть уникальны). Можно прописывать эти значения вручную, но в промышленной среде, когда в таблицы записывается много данных, запоминать предыдущее значение и сохранять уникальность значений трудно, что скажется на скорости заполнения таблиц. Для промышленной среды такая ситуация недопустима, и в целом – очень неудобна.

Ручная нумерация

Рассматривается пример с ручным вводом данных для значений колонки с primary key.

Шаг 1. Создается таблица test с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

Шаг 2. Добавляются данные в таблицу test.

Данные (2 строки) успешно добавлены. Далее будет выполнена попытка добавления третьей строки со значением NULL и без указания данных для колонки id.

Добавление третьей строки с указанием NULL:

и добавление строки без указания данных для колонки id:

и в обоих случаях система выдаст следующую ошибку:

Данная ошибка (ORA-01400) возникает из-за того, что новая добавляемая строка не содержит значение для колонки id. Колонка id таблицы test является первичным ключом (primary key) и требуется обязательное указание уникального значения.

Если добавить следующую строку,

то система выдаст ошибку:

Данная ошибка (ORA-00001) возникает из-за того, что нарушено условие уникальности значений в колонке id. В таблице уже есть строка со значением 2 в колонке id. Данный тип ошибки возникает при попытке выполнить команды INSERT или UPDATE, которые пытаются вставить дублирующее значение в колонку с ограничением по уникальности значений.

Шаг 3. Проверка данных в таблице.

В таблице test после всех вышеперечисленных операций будут следующие 2 строки данных:

Автоматическая нумерация.

Общий принцип механизма автоматической нумерации можно описать следующим образом: внутри базы к каждой таблице создается и привязывается отдельный счетчик, который увеличивается на единицу при вставке новой строки, а получившееся значение записывается в ту колонку, которое помечено как автонумерация (автоинкремент).

У различных СУБД этот механизм реализован по-своему. В данном посте рассматривается применение данного механизма применительно к СУБД Oracle.

В Oracle автонумерацию можно реализовать несколькими способами. Например, с помощью:

Последовательности (Sequence),
Комбинации последовательности и триггера (Trigger),
Использования колонки с IDENTITY.

Ниже рассматривается каждый способ с примерами.

1. Автоматическая нумерация с помощью последовательности (Sequence).

Для баз данных, использующих Oracle Database версии до 12c одним из способов реализации является sequence (последовательность). Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда нужно создать уникальный номер в качестве значения первичного ключа.

Шаг 1. Создается таблица test_sequence с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

Шаг 2. Ниже создается последовательность с названием t_sequence. Значение данной последовательности начинается с единицы и каждое новое сгенерированное число будет увеличиваться с шагом 1.

Последовательность создана. Синтаксис создания последовательности позволяет, кроме start with и increment by, задать также и другие параметры. Например, maxvalue, minvalue, cycle, cache и т.д. Но в данном посте для упрощения задачи они опускаются.

Шаг 3. Добавляются данные в таблицу test_sequence. Вместо значения id прописывается параметр nextval последовательности t_sequence (t_sequence.nextval).

Все данные успешно добавлены.

Шаг 4. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3, то есть значения уникальны и параметр nextval последовательности вставил их вместо ручного ввода пользователем.

2. Автоматическая нумерация с помощью комбинации последовательности и тригерра.

Данный способ можно применить к любой версии Oracle. Также как и в предыдущем способе, используется последовательность. Однако, в данном способе последовательность применяется в комбинации с другим объектом Oracle – триггером.

Шаг 1. Создается таблица test_sequence_trigger с колонками id и city_name, где ограничение primary key используется для колонки id и not null для колонки city_name.

Шаг 2. Ниже создается последовательность с названием tt_sequence. Значение последовательности начинается с единицы и каждое новое сгенерированное число будет увеличиваться с шагом 1.

Шаг 3. Создается триггер tt_trigger для таблицы test_sequence_trigger. Каждый раз, перед тем как данные будут добавляться во время выполнения оператора INSERT в таблицу test_sequence_trigger, триггер с именем tt_trigger будет запрашивать число из созданной последовательности tt_sequence и подставлять его в колонку id.

Шаг 4. Выполняется вставка данных в таблицу test_sequence_trigger.

Шаг 5. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3.

Применение комбинации последовательности и триггера, позволило строкам автоматически получить уникальные числовые значения в колонке id.

3. Автоматическая нумерация с помощью использования колонки с IDENTITY.

Начиная с версии Oracle Database 12c есть возможность создать колонку с IDENTITY для автоматической нумерации.

Колонку с типом IDENTITY можно объявить при создании таблицы, либо изменить существующую таблицу, добавив в неё колонку с типом IDENTITY используя DDL команду ALTER TABLE.

Общий синтаксис создания таблицы с колонкой IDENTITY приведен ниже:

GENERATED (ALWAYS | (BY DEFAULT [ON NULL])) AS IDENTITY [sequence_options,…]

Колонку IDENTITY можно создать с одной из трех опций генерации значений:

GENERATED BY DEFAULT AS IDENTITY – Генератор последовательностей добавляет значение в колонку IDENTITY каждый раз, когда значение не предоставлено вручную.
GENERATED BY DEFAULT ON NULL AS IDENTITY – Генератор последовательностей добавляет следующее значение колонки IDENTITY, если будет вручную указано значение NULL для колонки.
GENERATED ALWAYS AS IDENTITY – Генератор последовательностей всегда добавляет значение в колонку IDENTITY. Не предоставляется возможности вручную указать значение.

У каждой колонки с IDENTITY есть генератор последовательности – Sequence Generator. У данного генератора есть различные атрибуты, которые по умолчанию применяют определенные значения. Но при необходимости можно самому задать эти значения. Ниже приведены некоторые из атрибутов генератора последовательности. В разделе 3.3.3 они применены при создании новой таблицы.

START WITH initial_value – эта опция контролирует первое (начальное) значение (число) для колонки IDENTITY. По умолчанию, это значение равно 1.
INCREMENT BY internval_value – эта опция определяет с каким шагом будут генерироваться числа последовательности. По умолчанию, это значение равно 1.

Ниже в примерах описывается применение IDENTITY с различными опциями.

3.1. Опция GENERATED BY DEFAULT AS IDENTITY.

Шаг 1. Создание таблицы test_identity с опцией GENERATED BY DEFAULT AS IDENTITY для колонки id.

Шаг 2. Добавление данных в таблицу test_identity.

Данные успешно добавлены.

Если попытаться вставить в колонку с типом IDENTITY значение NULL:

то система выдаст следующую ошибку:

Опция GENERATED BY DEFAULT AS IDENTITY не позволяет вставлять значения типа NULL в колонку с типом IDENTITY. Если значение для колонки не указано, то генератор последовательностей (Sequence Generator) будет использовать свое значение.

Шаг 3. Проверяется содержимое таблицы. Как видно из выборки, в колонку id автоматически вставились значения от 1 до 3.

Шаг 4. Удаляется таблица test_identity для выполнения теста со следующей опцией IDENTITY (п. 3.2).

3.2. Опция GENERATED BY DEFAULT ON NULL AS IDENTITY.

Шаг 1. Создание таблицы test_identity с опцией GENERATED BY DEFAULT ON NULL AS IDENTITY для колонки id.

Шаг 2. Добавление данных в таблицу test_identity.

Данные успешно добавлены.

Выше были вставлены значения NULL в колонку с типом IDENTITY и не было ошибки (в отличие от опции пункта 3.1). Опция GENERATED BY DEFAULT ON NULL AS IDENTITY позволяет вставлять значения типа NULL в колонку IDENTITY.

Шаг 3. Проверяется содержимое таблицы.

Шаг 4. Удаляется таблица test_identity для выполнения теста со следующей опцией IDENTITY (п. 3.3).

3.3. Опция GENERATED ALWAYS AS IDENTITY.

Шаг 1. Создание таблицы с опцией GENERATED ALWAYS AS IDENTITY для колонки id.

Шаг 2. Добавление данных в таблицу test_identity.

Шаг 3. Проверяется содержимое таблицы. Как видно из выборки, в колонке id автоматически вставились значения для двух успешно добавленных строк.

Далее выполняется ввод данных из двух сессий без выполнения команды commit для проверки принципа работы генератора последовательности с опцией GENERATED ALWAYS AS IDENTITY.

3.3.1 Первая сессия:

Выполняется вставка новой строки:

Строка добавлена. Выполняется выборка данных из таблицы:

Как видно, пользователь первой сессии после вставки новой строки видит 3 строки данных.

3.3.2. Ввод и выборка данных второй сессии:

Выполняется вставка новой строки второй сессии:

Строка успешно добавлена. Выполняется выборка данных второй сессии. Пользователь данной сессии для новой строки получает значение 4 для колонки id с учетом того, что генератор последовательности (Sequence Generator) уже выдал значение 3 для пользователя первой сессии.

После выполнения commit в каждой сессии можно получить такой результат из сессии любого пользователя:

Это означает, что данные, добавленные в первой сессии и данные, добавленные во второй сессии, после совершения commit объединились в одной таблице, сохранив лишь уникальные значения для колонки id.

3.3.3. Применение атрибутов Sequence Generator.

Как было описано ранее в данном посте, при использовании IDENTITY можно задать значение параметров генератора последовательностей (Sequence Generator). Приводится пример создания таблицы, где определяются значения двух параметров Sequence Generator – start with и increment by. В приведенном ниже примере генератор последовательности будет генерировать числа для колонки id начиная с числа 5 и каждое следующее число будет увеличиваться с шагом 5:

Далее выполняется вставка данных для проверки генерируемых значений.

Выполняется выборка данных из таблицы:

Результат запроса показывает, что генератор последовательности для колонки id с IDENTITY сгенерировал числа начиная с пяти и каждое последующее число увечил на пять.

На этом завершается описание трех способов реализации автоматической нумерации (автоинкремент) в базах данных Oracle.

Как предоставить привилегии Create Table в Schema2 для Schema1?

Я оборачивался, и я смутился.
Я пробовал:

Из Schema2 я пробовал,

Но похоже, что это дает создание таблицы Schema1 в ее собственной схеме, а не в схеме 2.

Любые идеи, пожалуйста?

Единственный другой способ, позволяющий пользователю, не являющемуся DBA, создать таблицу в другой схеме, - предоставить пользователю системную привилегию CREATE ANY TABLE .

Эта привилегия может быть предоставлена только SCHEMA1 пользователем с привилегиями администратора.

Вы хотите предоставить create ANY table :

Модификатор any "позволяет создавать таблицы в других, чем собственные схемы.

Лучшее решение (минимизирует угрозу безопасности, предоставляемую привилегией CREATE ANY TABLE.

Создайте процедуру на схеме2, которая принимает определение таблицы как параметр "in" (p_tab_def в varchar2 (4000)).
Внутри поставлен execute_immediate (p_tab_def); выражение.
Вы должны сначала проверить p_tab_def, чтобы защитить себя от других операторов DDL, чем "CREATE TABLE [. ]". (можно использовать простую проверку, проверив первые два слова → он должен быть "CREATE TABLE" ).

Предоставить выполнение для schema2.procedure_name в schema1;

Это простая концепция. Я использовал такие конценции в своей предыдущей работе.

Репутация: нет
Всего: 1

А вот еще одна проблема. Есть пользователь-владелец, скажем, FADMIN. В его схеме создаю два объекта: таблицу TABLE1 и представление VIEW1. Один нюанс - VIEW1 основана на таблице из другой схемы, скажем SYSADM.DOC. Далее, создаю пользователя, скажем, FUSER, который просто должен просматривать эти два объекта. Даю ему права на просмотр

Код

GRANT SELECT ON TABLE1 TO FUSER;
GRANT SELECT ON VIEW1 TO FUSER;

Думал, дело в том, что у FUSER нет прав на образующую представление таблицу, т.е. на SYSADM.DOC. Даю права

Пробую. Получается, что FUSER даже SYSADM.DOC теперь может просматривать, а VIEW1 все равно нет - та же ошибка. Ради интереса попробовал дать FUSER права на все таблицы

И, о чудо! FUSER, наконец-то получил доступ к VIEW1. В связи с этим два вопроса: что это за ошибка, т.е. каких прав и на что ему не хватало? И как быть, т.к. давать этому юзеру такие полномочия ну совсем не резон?

Репутация: нет
Всего: 1

Репутация: 37
Всего: 161

Репутация: нет
Всего: 1

Получилось, спасибо. Вот только вопрос: SYSADM передает права FADMIN'у с WITH GRANT OPTION. Тот дает права на свое представление (основанное на таблице SYSADM.DOC) пользователю FUSER'у и тот получает доступ. Но почему это не работало раньше хоть и без WITH GRANT OPTION, но при этом FUSER имел права отдельно на представление от FADMIN и права на таблицу DOC от SYSADM'а?

И еще, как это загнать в роль, если с ролью это не работает?

Репутация: 37
Всего: 161

Цитата(Dumm @ 28.5.2013, 14:11 )

Но почему это не работало раньше

Я не смог воспроизвести указанную вами ситуацию у себя. Дать грант на вью, выбирающую из таблицы другой схемы, с правами без грант опшин, мне оракл не дал 11.2.03

Цитата(Dumm @ 28.5.2013, 14:11 )

И еще, как это загнать в роль, если с ролью это не работает?

Объекты схемы (в вашем случае - вью) работают от прав владельца, не роли. Т.е. для того, чтобы создать вью надо дать пользователю(не роли) грант на табицу. Соответственно чтобы грант был передаваем, он должен отдаваться с грант опшн.

Репутация: нет
Всего: 1

Цитата

Странно, у меня жонглирование с правами проходило весьма филантропно. 11.2.0.1.0. Ладно, спишем на железную логику Oracle.

Цитата

А если у меня таких вьюшек много? Конечно, пропишу ручками. А вот если еще их (права) надо дать не одному пользователю, а нескольким? Этак может получится о-о-очень много строк кода. Вот и вопрос, в роль это загнать никак? А потом пользователю(ям) задавать эту роль. Или здесь уже ограничение Oracle?

Репутация: 37
Всего: 161

Я не понимаю что ставит вас в тупик.
Пользователям давайте через роль. А объектам схемы нужен прямой грант (если он не authid current user).

Ваши пользователи же не будут создавать объекты схемы - так? Прав, данных через роль им должно хватить.

Репутация: 37
Всего: 161

Цитата(Zloxa @ 28.5.2013, 16:47 )

Я не понимаю что ставит вас в тупик.

Подумав, кажется я начал догадываться

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

Репутация: нет
Всего: 1

Это я понимаю. И вообще по теме, данной в заголовке, у меня все с вашей помощью разрешилось. Но раз уж мы затронули текущий вопрос, то не вижу причин нам не выяснить его до победного конца. Приведу придуманный пример, поправьте меня, если я где ошибусь. Есть пользователь-владелец SYSADM. У него куча таблиц в схеме. Есть пользователь-владелец MASTER1, в схеме которого только представления, основанные на таблицах SYSADM'а. Вот чтобы он их создал ему нужны права на таблицы:

Далее, допустим есть пользователь мелкого пошиба, который ничем не владеет, а только и делает, что просматривает представления MASTER1'а, например, USER1. Тогда права надо давать так:
(блок 2)

Код

GRANT SELECT ON TO MASTER1 WITH GRANT OPTION;
GRANT SELECT ON TO USER1;

Допустим, что представлений 100 штук и каждое основано на своей таблице, т.е. таблиц задействовано тоже 100 штук. Тогда надо 100 раз повторить строки в блоке 2. А теперь представим, что есть еще MASTER2, у которого есть тоже в своей схеме представления, но базируются они на тех же самых таблицах SYSADM'а. Получается, что ему нужны точно такие же права на те же самые таблицы:

Код

GRANT SELECT ON TO MASTER2 WITH GRANT OPTION;
GRANT SELECT ON TO USER2;

Значит и еще 100 раз прописать блок 2, но для другого пользователя. А если таких владельцев 10 штук - MASTER1, MASTER2, . MASTER10. Получается всего 1000 строк. Вот и вопрос почему бы блок 2 не загнать в роль. А потом просто присвоить роль MASTERn?

Код

CREATE ROLE ROLE_EXAMPLE;
GRANT SELECT ON TO ROLE_EXAMPLE WITH GRANT OPTION;
GRANT ROLE_EXAMPLE TO MASTER1;
.
.
.
GRANT ROLE_EXAMPLE TO MASTER10;

Но такая конструкция неработоспособна, нельзя создать роль с атрибутом WITH GRANT OPTION. Вот и возникает вопрос, а как? Думаю, что никак.

Репутация: 37
Всего: 161

Еще раз повторяю, для пользователя - владельца, отдавайте прямой грант виз грант опшин.
Пользователям-пользователям, назначайте привелегию на вью через роль

Вероятно вы видите не логичным, т.к. отдаете гранты из под sysdba и у вас котлеты мешаются с мухами.
Если вы будете отдавать привелегии из под пользоватя SYSADM, вы не сможете отдать привелегию на вью пользователя MASTER1 пока он вам не даст привелегию ее читать. Вы можете отдать лишь привлегию на чтение своих таблиц.
Пользователь же MASTER1 может определить роль и наделить ее привелегиями, необходимыми для того, чтобы пользователи могли получить доступ к объектам его схемы. Если эти пользователи не будут создавать свои объекты схемы, этого будет достаточно.

Репутация: нет
Всего: 1

Цитата

Еще раз повторяю, для пользователя - владельца, отдавайте прямой грант виз грант опшин.

На каждую таблицу? Т.е. всего 100 раз (в примере). А для другого владельца, который использует эти же таблицы другой схемы? Тоже давать прямой грант? Хорошо, еще 100 строк. А для третьего, . десятого?

Репутация: 37
Всего: 161

Цитата(Dumm @ 29.5.2013, 11:28 )

На каждую таблицу? Т.е. всего 100 раз (в примере)

Цитата(Dumm @ 29.5.2013, 11:28 )

А для другого владельца, который использует эти же таблицы другой схемы? Тоже давать прямой грант?

Цитата(Dumm @ 29.5.2013, 11:28 )

А для третьего, . десятого?

У вас что - несчетное количество схем? Обычно это не так и проблемы, в общем, не составляет.

Репутация: нет
Всего: 1

Верно. На практике это редкость. Интерес был чисто гипотетический. Просто попытавшись загнать в роль гранты с директивой WITH GRANT OPTION, я понял, что не смогу этого сделать. Теперь ясно, что сделать это через роль не удастся никак. Вопросов больше нет.

Данный раздел предназначен для обсуждения проблем с Oracle Database, другие продукты Oracle здесь не обсуждаются. Просьба при создании темы, придерживаться следующих правил:

Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, Zloxa, LSD.

[ Время генерации скрипта: 0.1643 ] [ Использовано запросов: 21 ] [ GZIP включён ]

Периодически Oracle разработчики сталкиваются с проблемой отладки PL/SQL кода, когда код вызывается из веба или среднего слоя(т.е. когда сессия разработчика не совпадает с сессией в которой возникает проблема).

Особенно актуально, если какие-либо проблемы возникают на стороне Web при двухзвенных и трехзвенная схемах взаимодействия БД и Web(ниже пример трехзвенной архитектуры взаимодействия):

Рисунок 1 — Трехзвенная архитектура взаимодействия БД и Web.

Метод решения проблем:

Ниже код метода, который мы будем отлаживать при помощи DBMS_PIPE и DBMS_ALERT одновременно:

При отсутствие грантов на DBMS_PIPE и DBMS_ALERT раздадим их:

Рисунок 2 — Раздача грантов c Oracle сервера схемы SYS на рабочую схему

Рисунок 3 — Настройка окна с Pipe

Рисунок 4 — Настройка окна с Alert

После запуска метода checkout_with_pipe_and_alert из веба/среднего слоя(в нашем случае из другой сессии):

В окнах Pipe и Alert получим следующие результаты:

Рисунок 5 — Результат получения информации от Pipe

Рисунок 6 — Результат получения информации от Alert

Читайте также: