Привилегии Muti-схемы для запуска таблицы в базе данных Oracle

Я пытаюсь написать триггер таблицы, который запрашивает другую таблицу, которая находится за пределами схемы, где будет находиться триггер. Это возможно? Кажется, у меня нет проблем с запросом таблиц в моей схеме, но я получаю:

Error: ORA-00942: table or view does not exist

при попытке запросить таблицы за пределами моей схемы.

РЕДАКТИРОВАТЬ

Приношу свои извинения за то, что не предоставил как можно больше информации в первый раз. У меня сложилось впечатление, что этот вопрос был более простым.

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

Учетная запись пользователя, которую я использую для создания триггера, имеет разрешения на независимый запуск запросов. На самом деле, мой триггер распечатал запрос, который я пытаюсь выполнить, и смог выполнить его самостоятельно.

Следует также отметить, что я строю запрос динамически с помощью оператора EXECUTE IMMEDIATE. Вот пример:

CREATE OR REPLACE TRIGGER MAIN_SCHEMA.EVENTS
BEFORE INSERT
ON MAIN_SCHEMA.EVENTS REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 
    rtn_count NUMBER := 0;
    table_name VARCHAR2(17) := :NEW.SOME_FIELD;
    key_field VARCHAR2(20) := :NEW.ANOTHER_FIELD;
BEGIN
    CASE
        WHEN (key_field = 'condition_a') THEN
            EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_A.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count;
        WHEN (key_field = 'condition_b') THEN
            EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_B.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count;
        WHEN (key_field = 'condition_c') THEN
            EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_C.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count;
    END CASE;

    IF (rtn_count > 0) THEN
        -- change some fields that are to be inserted
    END IF; 
END;

Швы триггера не срабатывают на ИСПОЛНИТЕЛЬНОМ НЕМЕДЛЕННОМ с ранее упомянутой ошибкой.

РЕДАКТИРОВАТЬ

Я провел еще несколько исследований и могу предложить больше разъяснений.

Учетная запись пользователя, которую я использую для создания этого триггера, не является MAIN_SCHEMA или какой-либо из OTHER_SCHEMA_Xs. Используемая мной учетная запись (ME) получает привилегии для соответствующих таблиц через самих пользователей схемы. Например (USER_TAB_PRIVS):

GRANTOR        GRANTEE TABLE_SCHEMA    TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     DELETE    NO        NO
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     INSERT    NO        NO
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     SELECT    NO        NO
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     UPDATE    NO        NO
OTHER_SCHEMA_X ME       OTHER_SCHEMA_X TARGET_TBL SELECT    NO          NO

И у меня есть следующие системные привилегии (USER_SYS_PRIVS):

USERNAME   PRIVILEGE            ADMIN_OPTION
ME         ALTER ANY TRIGGER    NO
ME         CREATE ANY TRIGGER   NO
ME         UNLIMITED TABLESPACE NO

И вот что я нашел в документации Oracle:

Чтобы создать триггер в схеме другого пользователя или ссылаться на таблицу в другой схеме из триггера в вашей схеме, вы должны иметь системную привилегию CREATE ANY TRIGGER. С этой привилегией триггер может быть создан в любой схеме и может быть связан с любой таблицей пользователя. Кроме того, пользователь, создающий триггер, также должен иметь привилегию EXECUTE для указанных процедур, функций или пакетов.

Здесь: Oracle Doc

Так что мне кажется, что это должно работать, но я не уверен насчет "привилегии EXECUTE", на которую он ссылается в документе.

Источник

3 ответа

Решение

Вы должны выполнить это для каждой таблицы и схемы:

grant select on OTHER_SCHEMA_%.table_name to MAIN_SCHEMA;
Источник

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

Синтаксис довольно прост: проблемы со схемой владельца

grant select, insert on my_table to you
/

В качестве альтернативы учетная запись с привилегией GRANT ANY (например, администратор баз данных) может передавать привилегии объектам любого пользователя.

grant select, insert on apc.my_table to you
/

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

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

редактировать

При ссылке на объект в другой схеме нам нужно квалифицировать объект с именем схемы....

insert into apc.whatever_table  values ...

или же нам нужно создать синоним для него

create synonym whatever for apc.whatever_table;
Источник

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

Источник
Другие вопросы по тегам