在Oracle包内执行CTAS建表遇ORA-01031权限不足问题求助

问题背景 #

需要创建PL/SQL包，通过CTAS（Create Table As Select）语句删除并重建表。该表需频繁刷新，且底层数据列经常增删，维护合并/更新查询过于繁琐。原通过外部脚本实现删建，现需迁移到数据库包中，遇到权限错误。

测试验证 #

可正常运行的匿名块 #

create table my_test_table as select * from dual; --创建测试表

declare
v_count int;
begin
   select count(*) into v_count from all_tab_columns where table_name = upper('my_test_table');
   if v_count >= 1 then
      execute immediate 'drop table my_test_table';
   end if;
   
execute immediate q'[
create table my_test_table as
select * from dual
]';
end;

select * from my_test_table; --返回预期结果

封装为包后出现错误 #

包定义：

CREATE OR REPLACE PACKAGE test_pkg AS
  PROCEDURE test_procedure;
END test_pkg;

CREATE OR REPLACE package body test_pkg as
procedure test_procedure
is
    v_count int;
begin
        select count(*) into v_count from all_tab_columns where table_name = upper('my_test_table');
        if v_count >= 1 then
            execute immediate 'drop table my_test_table';
        end if;
           
        execute immediate q'[
        create table my_test_table as
        select * from dual
        ]';
end test_procedure;
end test_pkg;
/

执行测试：

create table my_test_table as select * from dual; --确保表存在

execute TEST_PKG.TEST_PROCEDURE; --触发错误

select * from my_test_table; --表已被删除，但未重建

收到错误：

ORA-01031: insufficient privileges
ORA-06512: at test_pkg, line 15

解决方案 #

权限核心原因 #

PL/SQL包默认使用定义者权限，即执行时继承包创建者的权限，而非调用者的权限。匿名块能运行是因为使用当前用户的直接权限，但角色授予的权限在PL/SQL存储对象（包、存储过程）中不生效，且如果CREATE TABLE权限是通过角色获得的，包内无法使用。

所需权限及操作 #

1. 确保用户拥有直接的CREATE TABLE权限
   先检查当前用户的直接系统权限：

   SELECT privilege FROM user_sys_privs WHERE privilege = 'CREATE TABLE';
   

   若无结果，使用SYS用户授予直接权限：

   GRANT CREATE TABLE TO your_username;
   

2. 跨Schema创建表的情况
   如果需要在其他Schema下创建表，需授予直接的CREATE ANY TABLE权限：

   GRANT CREATE ANY TABLE TO your_username;
   

3. 允许其他用户调用包的情况
   给调用用户授予包的执行权限：

   GRANT EXECUTE ON test_pkg TO target_username;
   

关键注意点 #

• 角色授予的权限在PL/SQL存储对象中不生效，必须使用直接授予的系统权限。
• 若包由其他用户创建，需确保包创建者拥有足够的直接权限，否则即使调用者有权限，执行时仍会失败。

内容的提问来源于stack exchange，提问作者tlknoor