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

问题描述

这是存储过程的定义:

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 Instant(在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屋！