在存储过程中立即执行不断提供权限不足错误 [英] Execute Immediate within a stored procedure keeps giving insufficient priviliges error

问题描述

这里是存储过程的定义:

Here is the definition of the stored procedure:

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.但是，我有删除权限.我真的很困惑，我似乎找不到适合我的解决方案.

For some reason, I keep getting insufficient privileges error for the EXECUTE IMMEDIATE command. I looked online and found out that the insufficient privileges error usually means the oracle user account does not have privileges for the command used in the query that is passes, which in this case is DROP. However, I have drop privileges. I am really confused and I can't seem to find a solution that works for me.

在此先感谢您.

解决方案:

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

As Steve mentioned below, Oracle security model is weird in that it needs to know explicitly somewhere in the procedure what kind of privileges to use. The way to let Oracle know that is to use AUTHID keyword in the CREATE OR REPLACE statement. If you want the same level of privileges as the creator of the procedure, you use AUTHID DEFINER. If you want Oracle to use the privileges of the user currently running the stored procedure, you want to use AUTHID CURRENT_USER. The procedure declaration looks as follows:

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;

谢谢大家的回复.这绝对是解决问题的非常烦人的问题.

Thank you everyone for responding. This was definitely very annoying problem to get to the solution.

推荐答案

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

Oracle's security model is such that when executing dynamic SQL using Execute Immediate (inside the context of a PL/SQL block or procedure), the user does not have privileges to objects or commands that are granted via role membership. Your user likely has "DBA" role or something similar. You must explicitly grant "drop table" permissions to this user. The same would apply if you were trying to select from tables in another schema (such as sys or system) - you would need to grant explicit SELECT privileges on that table to this user.

这篇关于在存储过程中立即执行不断提供权限不足错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！