PL/SQL跨用户执行操作问询：无CREATE ANY TABLE权限时如何实现建表？

因为没有CREATE ANY TABLE权限，核心思路是让production_schema自身执行建表、授权操作，通过授予archive_schema调用这些操作的权限来实现，具体有两种可行方案：

方案一：利用生产模式的存储过程（推荐） #

让production_schema创建包含所有所需操作的存储过程，再给archive_schema授予执行权限，这样archive_schema的包只需调用该存储过程即可，存储过程会以production_schema的权限运行：

1. 以production_schema身份创建存储过程：

CREATE OR REPLACE PROCEDURE production_schema.handle_archive_tasks AS
BEGIN
    -- 1. 创建目标表
    CREATE TABLE production_schema.archive_data (
        record_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        archive_date DATE DEFAULT SYSDATE,
        payload CLOB
    );

    -- 2. 给指定用户授权
    GRANT SELECT, INSERT, UPDATE ON production_schema.archive_data TO target_user;
    GRANT ALTER ON production_schema.archive_data TO target_user; -- 如果需要修改表结构的权限

    -- 3. 其他需要的操作（比如初始化数据、创建索引等）
    CREATE INDEX production_schema.idx_archive_date ON production_schema.archive_data(archive_date);
END;
/

2. 给archive_schema授予该存储过程的执行权限：

GRANT EXECUTE ON production_schema.handle_archive_tasks TO archive_schema;

3. 在archive_schema的包中调用该存储过程：

BEGIN
    production_schema.handle_archive_tasks;
END;
/

方案二：使用归属production_schema的作业 #

如果需要定时执行任务，或者希望通过触发作业来完成操作，可以创建production_schema所有的作业，再让archive_schema拥有启动作业的权限：

1. 以production_schema身份创建作业（以Oracle DBMS_SCHEDULER为例）：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'production_schema.archive_setup_job',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN
                                -- 复制建表、授权逻辑
                                CREATE TABLE production_schema.batch_archive (id NUMBER, data VARCHAR2(200));
                                GRANT SELECT ON production_schema.batch_archive TO read_only_user;
                            END;',
        enabled         => FALSE, -- 默认禁用，由archive_schema触发
        auto_drop       => FALSE,
        comments        => '执行归档相关的建表与授权操作'
    );
END;
/

2. 给archive_schema授予作业操作权限：

-- 授予调度器执行权限
GRANT EXECUTE ON DBMS_SCHEDULER TO archive_schema;
-- 授予作业的修改与执行权限
GRANT ALTER, EXECUTE ON production_schema.archive_setup_job TO archive_schema;

3. 在archive_schema的包中启动作业：

BEGIN
    DBMS_SCHEDULER.RUN_JOB('production_schema.archive_setup_job', use_current_session => FALSE);
END;
/

关键注意事项 #

• 确保production_schema自身拥有足够权限：包括在自己模式下CREATE TABLE、CREATE INDEX，以及对自身对象的GRANT权限（默认情况下用户对自己的对象拥有完全授权权限）。
• 存储过程默认采用定义者权限模型，即运行时使用存储过程创建者（production_schema）的权限，这是绕开跨模式权限限制的核心。
• 作业运行时会使用作业所有者（production_schema）的权限，因此无需额外给archive_schema分配操作权限。

内容的提问来源于stack exchange，提问作者Matěj Alex