sql - 在存储过程中立即执行始终给出权限不足的错误

Question

以下是存储过程的定义:

CREATE OR REPLACE PROCEDURE usp_dropTable(schema VARCHAR, tblToDrop VARCHAR) IS
BEGIN
  DECLARE v_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) 
      INTO v_cnt 
      FROM all_tables 
     WHERE owner = schema
       AND table_name = tblToDrop;

     IF v_cnt > 0 THEN 
        EXECUTE IMMEDIATE('DROP TABLE someschema.some_table PURGE');
     END IF;
   END;
END;

这是电话:

CALL usp_dropTable('SOMESCHEMA', 'SOME_TABLE');

出于某种原因，我不断收到 EXECUTE IMMEDIATE 命令的权限不足错误。我在网上查了一下，发现权限不足错误通常意味着oracle用户帐户没有在查询中使用的命令的权限，在本例中是DROP。不过，我有放弃特权。我真的很困惑，我似乎找不到适合我的解决方案。

预先感谢您。

解决方案:

正如 Steve 在下面提到的，Oracle 安全模型很奇怪，因为它需要在过程中的某个地方明确地知道要使用哪种权限。让 Oracle 知道这一点的方法是在 CREATE OR REPLACE 语句中使用 AUTHID 关键字。如果您想要与过程创建者具有相同级别的权限，则可以使用 AUTHID DEFINER。如果希望 Oracle 使用当前运行存储过程的用户的权限，则需要使用 AUTHID CURRENT_USER。过程声明如下:

CREATE OR REPLACE PROCEDURE usp_dropTable(schema VARCHAR, tblToDrop VARCHAR) 
AUTHID CURRENT_USER IS
BEGIN
  DECLARE v_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) 
      INTO v_cnt 
      FROM all_tables 
     WHERE owner = schema
       AND table_name = tblToDrop;

     IF v_cnt > 0 THEN 
        EXECUTE IMMEDIATE('DROP TABLE someschema.some_table PURGE');
     END IF;
   END;
END;

谢谢大家的回复。这绝对是一个非常烦人的问题，需要解决。

Answer 1

Oracle 的安全模型是这样的，当使用“立即执行”(在 PL/SQL block 或过程的上下文中)执行动态 SQL 时，用户没有通过角色成员资格授予的对象或命令的权限。您的用户可能具有“DBA”角色或类似角色。您必须明确向该用户授予“删除表”权限。如果您尝试从另一个架构(例如 sys 或 system)中的表中进行选择，同样的情况也适用 - 您需要向该用户授予对该表的显式 SELECT 权限。