Oracle代理用户执行存储过程报错：ORA-00942，部分过程正常

问题背景 #

在Docker容器内的Oracle XE中，代理用户HR执行OT用户创建的两个存储过程时，其中一个正常，另一个报ORA-00942: 表或视图不存在错误。两个存储过程在OT用户下运行均正常。

存储过程代码 #

执行正常的存储过程：orders_by_product_category_by_year

-- @"/opt/oracle/oradata/Custom Scripts/orders_by_product_category_by_year.sql"
CREATE OR REPLACE PROCEDURE orders_by_product_category_by_year(p_cur OUT sys_refcursor) 
AUTHID CURRENT_USER 
AS 
BEGIN
    OPEN p_cur FOR
        SELECT 
            ROW_NUMBER() OVER (ORDER BY EXTRACT(YEAR FROM orders.order_date) ASC) AS row_num, 
            product_categories.category_name, 
            EXTRACT(YEAR FROM orders.order_date) AS year,
            SUM(order_items.quantity*order_items.unit_price) AS value, 
            COUNT(1) AS count 
        FROM orders 
        LEFT JOIN order_items ON order_items.order_id = orders.order_id 
        LEFT OUTER JOIN products ON products.product_id = order_items.product_id 
        LEFT OUTER JOIN product_categories ON product_categories.category_id = products.category_id 
        GROUP BY product_categories.category_name, EXTRACT(YEAR FROM orders.order_date) 
        ORDER BY year ASC, product_categories.category_name;
END;
/

执行报错的存储过程：orders_for_year

-- @"/opt/oracle/oradata/Custom Scripts/orders_for_year.sql"
CREATE OR REPLACE PROCEDURE orders_for_year(i_year IN NUMBER, o_cursor OUT SYS_REFCURSOR) 
AUTHID CURRENT_USER 
AS 
BEGIN 
    OPEN o_cursor FOR 
        SELECT 
            ROW_NUMBER() OVER (ORDER BY EXTRACT(YEAR FROM orders.order_date) ASC) AS row_num, 
            orders.order_id, 
            customers.name AS customer_name, 
            CONCAT(CONCAT(employees.first_name, ' '), employees.last_name) AS salesrep_name, 
            orders.order_date, 
            (SELECT SUM(order_items.quantity*order_items.unit_price) FROM order_items WHERE order_items.order_id = orders.order_id) AS value 
        FROM orders 
        LEFT OUTER JOIN customers ON customers.customer_id = orders.customer_id 
        LEFT OUTER JOIN employees ON employees.employee_id = orders.salesman_id 
        WHERE EXTRACT(YEAR FROM orders.order_date) = i_year;
END;
/

执行输出 #

SQL> SHOW USER;
USER is "HR"
SQL> VAR cursor REFCURSOR;
SQL> EXEC ot.orders_by_product_category_by_year(:cursor);

PL/SQL procedure successfully completed.

SQL> EXEC ot.orders_for_year(2016, :cursor);
BEGIN ot.orders_for_year(2016, :cursor); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "OT.ORDERS_FOR_YEAR", line 5
ORA-06512: at line 1

SQL> SELECT * FROM user_tab_privs;

权限查询结果 #

SQL> SELECT * FROM user_tab_privs;

GRANTEE    OWNER      TABLE_NAME                     GRANTOR    PRIVILEGE            GRA HIE COM TYPE       INH
---------- ---------- ------------------------------ ---------- -------------------- --- --- --- ---------- ---
HR         OT         REGIONS                        OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         COUNTRIES                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         LOCATIONS                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         WAREHOUSES                     OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         EMPLOYEES                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         PRODUCT_CATEGORIES             OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         PRODUCTS                       OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         CUSTOMERS                      OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         CONTACTS                       OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         ORDERS                         OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         ORDER_ITEMS                    OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         INVENTORIES                    OT         SELECT               NO  NO  NO  TABLE      NO
HR         OT         ORDERS_BY_PRODUCT_CATEGORY_BY_ OT         EXECUTE              NO  NO  NO  PROCEDURE  NO
                      YEAR

HR         OT         ORDERS_FOR_YEAR                OT         EXECUTE              NO  NO  NO  PROCEDURE  NO
PUBLIC     SYS        HR                             HR         INHERIT PRIVILEGES   NO  NO  NO  USER       NO

15 rows selected.

SQL>

问题原因 #

问题出在orders_for_year中的关联子查询。两个存储过程都使用AUTHID CURRENT_USER（即执行存储过程的用户HR的权限），但查询执行逻辑有差异：

• 第一个存储过程通过JOIN关联所有表后进行聚合，Oracle会统一检查HR对所有涉及表的直接权限，由于HR已拥有OT下所有表的SELECT权限，因此能正常执行。
• 第二个存储过程中的(SELECT SUM(...) FROM order_items WHERE ...)是关联子查询，Oracle会将其作为独立查询块进行权限验证。由于HR的schema中不存在order_items表，且查询未指定表的所有者（OT），Oracle无法找到对应表，从而抛出ORA-00942错误。

解决方案 #

方案1：指定表的所有者前缀 #

修改存储过程，在所有表名前加上所有者OT，明确指向OT用户下的表：

CREATE OR REPLACE PROCEDURE orders_for_year(i_year IN NUMBER, o_cursor OUT SYS_REFCURSOR) 
AUTHID CURRENT_USER 
AS 
BEGIN 
    OPEN o_cursor FOR 
        SELECT 
            ROW_NUMBER() OVER (ORDER BY EXTRACT(YEAR FROM ot.orders.order_date) ASC) AS row_num, 
            ot.orders.order_id, 
            ot.customers.name AS customer_name, 
            CONCAT(CONCAT(ot.employees.first_name, ' '), ot.employees.last_name) AS salesrep_name, 
            ot.orders.order_date, 
            (SELECT SUM(ot.order_items.quantity*ot.order_items.unit_price) FROM ot.order_items WHERE ot.order_items.order_id = ot.orders.order_id) AS value 
        FROM ot.orders 
        LEFT OUTER JOIN ot.customers ON ot.customers.customer_id = ot.orders.customer_id 
        LEFT OUTER JOIN ot.employees ON ot.employees.employee_id = ot.orders.salesman_id 
        WHERE EXTRACT(YEAR FROM ot.orders.order_date) = i_year;
END;
/

方案2：创建同义词 #

为HR用户创建OT下表的同义词，让HR访问表名时默认指向OT的表：

-- 为涉及的表创建同义词
CREATE SYNONYM HR.order_items FOR OT.order_items;
CREATE SYNONYM HR.orders FOR OT.orders;
CREATE SYNONYM HR.customers FOR OT.customers;
CREATE SYNONYM HR.employees FOR OT.employees;

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