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;

行使前面提到的错误,触发器在EXECUTE IMMEDIATE上吸取失败.

编辑

我做了更多的研究,我可以提供更多的澄清.

我用来建设此触发器的用户帐户不是MAIN_SCHEMA或OTHER_SCHEMA_X中的任何一个.我正在行使的帐户(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文档中找到的内容：

To create a trigger in another user’s
schema,or to reference a table in
another schema from a trigger in your
schema,you must have the CREATE ANY
TRIGGER system privilege. With this
privilege,the trigger can be created
in any schema and can be associated
with any user’s table. In addition,
the user creating the trigger must
also have EXECUTE privilege on the
referenced procedures,functions,or
packages.

这里：Oracle Doc

因此,在我看来这应该可行,但我不确定它在文档中提到的“EXECUTE特权”.

办理要领

grant select on OTHER_SCHEMA_%.table_name to MAIN_SCHEMA;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!