database - Oracle 数据库中表触发器的多模式权限

Question

我正在尝试编写一个表触发器，它查询触发器所在架构之外的另一个表。这可能吗？看起来我在我的模式中查询表没有问题，但我得到:

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

所以在我看来这应该可行，但我不确定它在文档中提到的“执行权限”。

Answer 1

您遇到的是 Oracle 安全模型的一个特性。使用模式的全部意义在于控制对数据的访问。我的架构中的表是我的，在我授予您对它们的权限之前您甚至看不到它们。

语法很简单:所有者架构问题

grant select, insert on my_table to you
/

或者，具有 GRANT ANY 权限的帐户(例如 DBA)可以传递对任何用户对象的权限。

grant select, insert on apc.my_table to you
/

被授权者可以是用户或角色。但是，请注意，我们只能使用直接授予用户的权限来构建程序单元——存储过程、 View 、触发器。

因此，如果您让其他架构所有者授予您必要的权限，您将能够构建触发器。

编辑

当引用另一个模式中的对象时，我们需要使用模式名称限定对象......

insert into apc.whatever_table  values ...

否则我们需要为它创建一个同义词

create synonym whatever for apc.whatever_table;