Oracle grant на создание dblink
Database Link - это объект схемы данных. Он позволяет получить доступ из одной базы данных к объектам другой базы данных. Доступ к базам данный не относящимся к системам Oracle Database осуществляется через Oracle Heterogeneous Services.
Для обращения к таблица или представления других БД через SQL запросы необходимо к их имени добавить @dblink . Поддерживаются следующие операторы для объектов других БД: SELECT , INSERT , UPDATE , DELETE и LOCK TABLE .
Необходимо иметь системные привилегии на выполнение операторов в Oracle:
- CREATE DATABASE LINK .
- CREATE PUBLIC DATABASE LINK . Для создание общедоступных ссылок.
В удаленной базе необходимо иметь разрешение выполнение:
Syntax
SHARED
Specify SHARED to use a single network connection to create a public database link that can be shared among multiple users. При использовании SHARED необходимо указывать параметр dblink_authentication .
PUBLIC
Заявление указывает что нужно создать ссылку доступной для всех пользователей базы данных. Если заявление не указано то доступ к ссылке будет иметь только создавший ее пользователь.
dblink
Полная или частичная ссылка на базу данных. Если указать только имя базы, Oracle неявно выберет локальный домен для соединения.
Используйте только ASCII символы для формирования dblink .
GLOBAL_NAMES равное TRUE , требует равенства имен между объектом DATABASE LINK и подключаемой базой данных. Значение FALSE равенства не требует.
Количество открытых соединений dblink устанавливается параметрами инициализации OPEN_LINKS и OPEN_LINKS_PER_INSTANCE .
CONNECT TO
Предложение позволяет подключиться к удаленной базе данных. Предложения CONNECT TO и dblink_authentication можно использовать только при создании общедоступного dblink, использую ключевое слово SHARED .
CURRENT_USER
Пользователь от имени которого создается database link . Этот пользователь должен должен быть глобальным с действующей учетной записью в удаленной базе.
Если использовать ссылку не из хранимого объекта oracle, непосредственно, то текущий пользователь будет назначен в качестве CURRENT_USER .
В случае вызова из хранимого объекта (процедуры, триггера) в качестве CURRENT_USER , будет указан создатель этого объекта.
Однако, если хранимый объект это invoker-rights функция, процедура или пакет, в качестве CURRENT_USER будет выступать вызывающий ее пользователь, а не создатель этого объекта.
user IDENTIFIED BY password
Имя пользователя и пароль для подключения к удаленной базе данных. Указание этого параметра определяет фиксированного пользователя для подключения ( fixed user database link ). Если параметр опущен будет использоваться имя и пароль пользователя который обращается к dblink, подключенный пользователь ( connected user database link ).
dblink_authentication
Укажите имя пользователя и пароль на целевом экземпляре. Это заявление проверяет подлинность пользователя на удаленном сервер и требуется для обеспечения безопасности. Указанное имя пользователя и пароль должен быть действительными на удаленном экземпляре. Имя пользователя и пароль используются только для проверки подлинности. Никакие другие операции не выполняются от имени этого пользователя.
USING 'connect string'
Укажите имя службы удаленной базы данных. Если указать только имя базы данных, то Oracle неявно добавляет домен базы данных в строке соединения, чтобы создать полное имя службы. Поэтому, если домен удаленной базы данных отличается от текущей базы данных, то необходимо указать полное имя службы.
Examples
Примеры используют две базы данных на одном домене с именами: local и remote .
- Создание ссылки на базу данных на одном домене.
- Обновление данных на использую ссылку @remote, предполагается, что пользователь вызывающий обновление имеет соответствующие привилегии в удаленного базе данных.
UPDATE employees@remote
SET salary = salary * 1.1
WHERE last_name = 'Baer' ;
Приходилось ли Вам реализовывать нестандартные решения? А в Oracle? Мне бы хотелось рассмотреть использование техник, позволяющих лучше узнать принципы работы СУБД, а в совокупности предоставляющие удобство для разработчика.
Намного удобнее, выполнять разработку приложений баз данных в едином пространстве, а результаты переносить по ландшафту системы в фоновом режиме, автоматически регистрируя производимые изменения.
Пример выполнения обновления на сервере разработки
Пролог
Вы встречали вредных DBA? А работали с такими? На самом деле, обе стороны (Developer vs. DBA), добиваются одного результата, работоспособности системы, но с разных сторон. Впрочем, когда система расширяется, децентрализуется, но сохраняет целостность в реализации, то поддержка консистентного состояния программной оснастки может начать доставлять серьезные неудобства. Появляются серверы разработки, тестирования, «продуктива» — и все это замечательно, но всех их нужно обновлять.
В Oracle есть инструменты казалось бы похожие на рассматриваемый:
• Audit
• Oracle Streams
• Alert
Но все они выполняют другие функции. Одни, обеспечивают аудит изменений, другие синхронизируют данные. А мне бы хотелось действовать более прозрачно, вот например:
Теперь все мои действия продублированы на сервере ‘prod’. А может быть, даже так:
И, скажем, семь серверов создали таблицу «A». Здорово? Тогда – поехали.
Подготовка
Выполним соединение с базой данных от имени пользователя имеющего достаточные привилегии для последующих действий:
Предполагается что пользователь, выполняющий обновления, не должен иметь доступа к самой системе обновления, впрочем, как и сама система не привязывается к целевой схеме, следовательно, может использоваться универсально. Поэтому создадим нового пользователя:
Поскольку статья не об ограничении прав новых пользователей:
Опустив рассуждения о проводимых изысканиях, скажу, что самым сложным оказалось получение анонимного PL/SQL блока, который приводился в примере выше. Естественно, одни действия в конечном итоге порождают другие, так например, всё тот же блок из примера, выполнит insert, но на самом деле может быть и не выполнит! Ведь выполняться он будет на другом сервере. Поэтому нас будет интересовать именно анонимный PL/SQL блок, а не последствия. Паблик синоним V$SQL или представление V_$SQL на которое он ссылается, хранит все запросы, выполнявшиеся на сервере. Попробуем найти в нём нашу цель:
Действительно, именно мой анонимный блок находится там где положено. Конечно же, SQL_ID выполняя мой пример, будет другой, но принадлежит ли он мне? Проверим:
Выполнившийся блок, удалось найти, но мне бы хотелось узнать, кем он выполнен, а точнее узнать, что именно я выполнял его в определенный момент времени. Другое представление V_$SESSION, сможет мне в этом помочь:
select sql_id, prev_sql_id from v$session;
Тут нужно пояснить, что синоним v$session предоставляет доступ к VIEW, а доступ для пользователя организуется командой:
grant select on v_$session to upd;
Дело тут в том, что тип представление v_$session является FIXED VIEW, поэтому давать права на его синоним – запрещено. Впрочем, если выдавать права на синоним, скажем таблицы, сами права выдаются на таблицу, а НЕ на синоним.
Так что же там с запросом? Ах да, нужно ограничить выборку текущей сессией:
Как это у Вас, не получается? Ни SQL_ID ни PREV_SQL_ID – не содержат найденного ранее идентификатора 753c9f808k8hh? Естественно! SQL_ID содержит идентификатор только что выполненного запроса, а PREV_SQL_ID скорее всего хранит идентификатор запроса:
Я надеюсь, что читатель последовательно выполнял запросы, как я их приводил и поэтому сразу не нашел того что ожидалось. Для того что бы убедиться, что результат будет как и указано, нужно выполнить последовательно анонимный блок и запрос к представлению. Как бы то ни было, считаю, что еще один этап изысканий пройден. Теперь мы имеем исходный текст анонимного блока, и знаем, что он был выполнен именно нами.
К сожалению, решение, которое автоматизирует связывание, мне не нравится, ведь необходимо после определенного момента, запомнить все возможные анонимные блоки выполняющиеся пользователем, а представления хранящего историю сессии пользователя не существует? Или существует? Впрочем я не нашел и на данный момент, предлагаю следующий подход. Создадим таблицу, которая будет хранить идентификатор сессии, которая заинтересована в прослушивании и джоб, опрашивающий эту таблицу и сохраняющий историю сессии.
«Что за названия полей второй таблицы?» — спросил бы я. Несмотря на то, что это не имеет веского оправдания, но пытавшись минимизировать нагрузку создаваемую джобом, я добрался до представления более высокого уровня sys.x_$ksuse которое содержит достаточную информацию о целевой сессии. Делая закладку на будущее, в таблицу будут сохраняться еще несколько полезных полей, помимо необходимых: KSUSENUM (SID) и KSUSESQI (SQL_ID). Хорошо будет вынести тело джоба во внешнюю процедуру, и не добавлять ее в пакет, дабы избежать ошибок, если пакет будет не валиден:
Идея обработки, заключается в том, что бы записывать в историю сессии только тогда, и только то что выполняется пользователем в режиме обновления. Теперь можно создать джоб, прослушать сессию пользователя и проверить результат:
Как видно из результата запроса к V$SQL анонимный блок попал в таблицу лога записанный туда джобом. Для теста, я обращался к столбцу KSUSEPSI лога (предыдущему запросу) ввиду того, что мне приходилось выполнять команды очистки таблицы сессии в момент прослушивания. В дальнейшем, это так же окажется некоторым недостатком, но «обрывание» прослушивания мы исключим из результирующего набора выполняемого на удаленном сервере.
Теперь необходимо собрать DLL команды, которые так же могут выполняться при обновлении. Но здесь происходит противоречие, зачем собирать DDL – если их соберет джоб? К сожалению, он их не соберет, так как DDL не является запросом, а следовательно в v$session не отразится. Для этих целей Oracle предоставляет триггеры уровня СУБД, которыми можно воспользоваться. Выполняемые DDL, запишем в новую таблицу, а по аналогии с джобом, создадим процедуру и триггер выполняющей её:
Дополнительная таблица, и её тип (GLOBAL TEMPORARY хранить данные до дисконнекта), выбраны из следующих соображений: джоб собирающий информацию о сессии, работает в сессии отличной от той, которая выполняет скрипты обновления, следовательно запросы записанные в нее стали бы недоступны сессии исполнителя; предоставить Oracle очистку таблицы после обновления; DDL триггер, срабатывает в той же сессии, в которой выполняется DDL, следовательно в этом случае записывать можно сразу в таблицу буфера; сохранение данных таблицы после коммита обусловлено тем, что DDL выполняет молчаливый коммит.
Важно обратить внимание на то, что процедура объявлена с директивой AUTHID DEFINER, которая позволит записывать действия с правами пользователя UPD, которые могут быть большими, чем у вызывающего. Далее производится определение длинны DDL и сохранение буферов в поле CLOB.
Триггер выполняется после (AFTER) DDL, что подразумевает успешное выполнение команды, до записи в буфер.
Подводя итоги изысканий, теперь имеются все возможные типы операций, подлежащие выполнению на обновляемой базе и можно приступить к завершающему этапу – инструменту выполнения обновлений.
Реализация
Мне не нравятся публикации, которые после длительного рассуждения и подготовки заканчиваются чем то вроде: «А теперь, (если не дурак) тебе должно быть ясно как доделать оставшуюся фигню». Конечно же тут дураков – нет, все давно поняли что нужно сделать дальше. Но я приведу свою текущую реализацию, несмотря на то, что её можно считать бета версией. Теперь много кода, а затем пояснения:
К ранее созданным таблицам, добавились еще две, одна из которых используется для визирования успешно выполненных обновлений, а вторая для настройки соединения с удаленной базой Oracle.
Пакет объявлен с директивой AUTHID CURRENT_USER – что приведет к выполнению процедур пакета, с правами пользователя вызывающего пакет. Теперь, о всех процедурах пакета:
procedure SetSession(u_sid number, u_remove boolean default false) – используя автономную транзакцию, записывает текущий идентификатор сессии в таблицу инициирующую прослушивание.
function JobNumber return number – получает идентификатор джоба прослушивателя.
procedure JobRun – проверяет существование джоба.
procedure SetChannel(u_alias varchar2) – получает настройки удаленного соединения и записывает их в локальные переменные пакета.
procedure CancelUpdate – стирает настройки и очищает временные таблицы.
procedure BeginUpdateChannel(u_alias varchar2) – объединяет вызовы подготовительных процедур и начинает прослушивание.
procedure PrepareUpdateChannel – завершает прослушивание и дописывает в таблицу буфер собранные джобом запросы сессии. Я для собственных нужд, не слишком стараясь, отбрасываю при этом DML, select и встреченные в процессе тестирования служебные команды, а так же вызов процедуры PrepareUpdateChannel который тоже записывается в лог сессии.
procedure DropObject – вспомогательная процедура для очистки.
procedure ExecRemote – выполнение блока на удаленном сервере. Эта процедура реализует один из ключевых моментов механизма. Тут пакет dbms_sql вызывается на удаленном сервере.
procedure EndUpdateChannel – применение обновления. И об этом отдельно.
Для конечного пользователя можно создать процедуры обертки, с директивой AUTHID DEFINER и раздать права вызывать их нужным пользователям:
Не знаю, чем у вас закончилась история с нашим новым пользователем DUMMY, а у меня он все же остался. Если кто-то из вас создал своего пользователя, то можете воспользоваться своим. А, вот сейчас давайте поговорим о том, как могут взаимодействовать разные схемы БД. И как это все возможно осуществить. Запускайте SQL*Plus и подключайтесь пользователем DUMMY (если вы его все-таки пристрелили, реанимируйте его согласно шагу 101). А теперь, находясь в схеме DUMMY дайте такой запрос:
Неудача "ORA-00942: таблица или представление пользователя не существует"! Говорит само за себя. Теперь попробуем:
В чем же причина? Да просто у пользователя DUMMY нет прав производить чтение из таблицы схемы MILLER! Как его предоставить? Очень просто. Подключаемся к схеме MILLER:
А теперь записываем следующее:
Меняем подключение на DUMMY:
Снова повторяем запрос вот так, чтобы было меньше столбцов:
Получаем в результате:
- system_privilege - предоставляемое системное полномочие.
- role - предоставляемая роль.
- TO - определяет пользователей или роли, которым предоставляются системные полномочия.
- PUBLIC - указывает что, системные полномочия определяемые администратором предоставляются всем пользователям.
- WITH ADMIN OPTION - позволяет получившему системные полномочия или роль предоставлять их в дальнейшем другими пользователям или ролям. Такое решение в частности включает и возможность изменение или удаления роли.
Давайте посмотрим какие системные полномочия могут предоставляться. Основных операций в языке DDL три - это CREATE, ALTER, DROP.
- ALTER DATABASE - Позволяет изменять саму БД.
- ALTER USER - Позволяет изменять пользователя и его параметры (пароль, профиль, роль и т.д.)
- ALTER PROFILE - Позволяет изменять профили.
- ALTER TABLESPACE - Позволяет изменять табличные пространства.
- ALTER ANY PROCEDURE - Разрешает изменение любой хранимой функции процедуры или пакета в любой схеме.
- ALTER ANY ROLE - Разрешает изменение любой роли БД.
- ALTER ANY SEQUENCE - Разрешает изменение любой последовательности в БД.
- ALTER ANY TABLE - Разрешает изменение любой таблицы или вида в схеме БД.
- ALTER ANY TRIGGER - Позволяет разрешать, запрещать компилировать любой триггер в любой схеме БД.
- ALTER ANY INDEX - Разрешает изменение любого индекса в любой схеме.
Группа CREATE:
Позволяет создавать в любой схеме соответствующий объект:
Позволяет создавать в конкретной схеме соответствующий объект:
Удаление объектов в любой схеме, а так же очистка таблиц:
Удаление объектов в схеме:
И еще полезные системные привилегии:
Вот далеко не полный список системных привилегий, которые предоставляются оператором GRANT. Для начала я думаю хватит. А дальше все зависит от вас. Давайте теперь рассмотрим предоставление объектных привилегий. Здесь все выглядит вот так:
object_privilege - предоставляемая привилегия - одна из:
COLUMN - определяет столбец таблицы или вида, на который распространяется предоставляемая привилегия.
ON - определяет объект (таблицу, вид, и т.д.)
TO - указывает кому предоставляется привилегия.
WITH ADMIN OPTION - позволяет имеющему эту привилегию предоставлять их в дальнейшем другими пользователям или ролям.
Как с работать с этим типом мы с вами уже пробовали в начале этого шага! Можете, например добавить еще что-нибудь к вышеизложенному примеру. И наконец, давайте рассмотрим как привилегии изымаются или удаляются. Для этого необходимо применять оператор REVOKE. Его синтаксис аналогичен первым двум операторам за небольшим исключением:
Например, чтобы изъять привилегию на выборку из таблицы SALESREPS для схемы DUMMY введите следующее находясь в схеме MILLER:
Получим примерно следующее:
Вот таким образом применяя операторы GRANT и REVOKE, можно строить взаимоотношение схем и строить политику доступа к объектам БД. Попробуйте создать в новом пользователе несколько объектов и разрешить обращаться к ним из схемы MILLER. Если что не получится пишите!
1- Что такое Database Link?
Database Link это объект в SCHEMA в Oracle, похож на мост для соединения с другой базой данных, помогающий вам иметь доступ к объектам другой базы данных.
Вы так же можете создать Database Link чтобы соединить Oracle к другому виду базы данных, как например MySQL, SQL Server, . в данном случае вам нужно использовать сервис Oracle Heterogeneous (Oracle Heterogeneous Service)
2- Создать Database Link
Здесь я иллюстрирую соединение 2 систем баз данных Oracle находящихся на 2 разных компьютерах.
Для начала, вам нужно понять о файле tnsnames.ora в Oracle.
tnsname.ora это файл описывающие базу данных Oracle, вы можете найти этот файл в папке установки Oracle.
- <ORACLE_HOME>\product\12.1.0\dbhome_1\NETWORK\ADMIN
Содержание файла tnsname.ora похож на изображение ниже.
Ниже является изображение иллюстрирующее две системы баз данных Oracle находящиеся на 2 разных компьютерах.
Создать Database Link с названием mydblink чтобы соединить из PROD1 к PROD2.
На PROD1 вы можете тестировать Database Link командой:
Есть некоторые VIEW помогающие вам сделать запрос списка Database Link имеющиеся в базе данных:
Если информация описания Database2 объявлена в файле tnsname.ora в Database1, вы можете создать Database Link из Database1 в Database2 с более кратким синтаксисом.
3- Доступ к объектам через Database Link
После того как у вас есть Database Link, вы можете получить доступ к объектам в Database2 через Database Link.
4- Oracle Synonym
Synonym можно перевести как "Единообразный объект" для определенного объекта. Если вы не хотите добавлять @dblinkName при доступе в объект через Database link, вы можете создать Synonym для того объекта.
Примечание: Вы так же можете создать Synonym для функций, процедур.
Читайте также: