跨Schema删除表存储过程权限不足问题求解

问题原因 #

你的存储过程指定了AUTHID CURRENT_USER，这意味着存储过程执行时会使用调用该过程的用户的权限，而非存储过程所属的DW用户权限。如果调用用户没有SCRATCH Schema下删除表的权限，就会触发权限不足错误。

解决方案 #

方案一：给调用存储过程的用户授权 #

如果希望保留AUTHID CURRENT_USER的设置，需要为执行该存储过程的用户授予删除SCRATCH Schema下表的权限：

-- 授予系统级删除权限（权限范围大，适合信任的用户）
GRANT DROP ANY TABLE TO 调用存储过程的用户名;

-- 或者更细粒度的权限（仅允许操作SCRATCH下的表，Oracle 12c及以上支持）
GRANT DROP ON SCRATCH.* TO 调用存储过程的用户名;

方案二：切换存储过程为DEFINER权限模式 #

将存储过程的权限模式改为AUTHID DEFINER，这样执行时会使用存储过程所属的DW用户权限。操作分两步：

1. 先给DW用户授予删除SCRATCH Schema下表的权限：

GRANT DROP ANY TABLE TO DW;
-- 或细粒度授权
GRANT DROP ON SCRATCH.* TO DW;

2. 修改存储过程的权限声明：

CREATE OR REPLACE PROCEDURE DW.PROC AUTHID DEFINER IS -- 改为DEFINER模式
V_TABLE_NAME  VARCHAR2(255);
V_DELETE_DT NUMBER(33);
V_LIST SYS_REFCURSOR;
BEGIN
    SELECT TO_NUMBER(VALUE) INTO V_DELETE_DT FROM DW.LIST_OF_TABLE; -- 补充分号
    OPEN V_LIST FOR
    SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE= 'TABLE' AND 
    OBJECT_NAME LIKE '%DLY_BKP%' AND CREATED <=SYSDATE - V_DELETE_DT;
     
     LOOP 
        FETCH V_LIST
        INTO V_TABLE_NAME;
        EXIT WHEN V_LIST%NOTFOUND;
        EXECUTE IMMEDIATE 'DROP TABLE SCRATCH.'||V_TABLE_NAME ;
     END LOOP;
    CLOSE V_LIST; 
END;
/

注：原存储过程中SELECT TO_NUMBER(VALUE) INTO V_DELETE_DT FROM DW.LIST_OF_TABLE语句缺少分号，已补充修正。

方案三：代理用户（高权限管控场景可选） #

如果不想授予过大的系统权限，可以创建专门用于删除SCRATCH表的代理用户，通过存储过程切换到该用户执行删除操作。这种方式复杂度较高，适合对权限管控要求极严格的场景。

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