SQL 在存储过程中立即执行不断提供权限不足错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/996198/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute Immediate within a stored procedure keeps giving insufficient priviliges error
提问by tundal45
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;
Here is the call:
这是电话：
CALL usp_dropTable('SOMESCHEMA', 'SOME_TABLE');
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.
出于某种原因，我不断收到 EXECUTE IMMEDIATE 命令的权限不足错误。我在网上查了一下，发现权限不足的错误通常是指oracle用户帐户对通过的查询中使用的命令没有权限，在这种情况下是DROP。但是，我有删除权限。我真的很困惑，我似乎找不到适合我的解决方案。
Thanks to you in advance.
提前谢谢你。
SOLUTION:
解决方案：
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:
正如史蒂夫在下面提到的，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;
Thank you everyone for responding. This was definitely very annoying problem to get to the solution.
谢谢大家的回复。这绝对是解决问题的非常烦人的问题。
采纳答案by Steve Broberg
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.
Oracle 的安全模型是这样的，当使用 Execute Immediate（在 PL/SQL 块或过程的上下文中）执行动态 SQL 时，用户没有通过角色成员资格授予的对象或命令的权限。您的用户可能具有“DBA”角色或类似角色。您必须明确授予此用户“删除表”权限。如果您尝试从另一个模式（例如 sys 或 system）中的表中进行选择，则同样适用 - 您需要向该用户授予对该表的显式 SELECT 权限。
回答by Chakib Arrama
You should use this example with AUTHID CURRENT_USER:
您应该将此示例与AUTHID CURRENT_USER 一起使用：
CREATE OR REPLACE PROCEDURE Create_sequence_for_tab (VAR_TAB_NAME IN VARCHAR2)
AUTHID CURRENT_USER
IS
SEQ_NAME VARCHAR2 (100);
FINAL_QUERY VARCHAR2 (100);
COUNT_NUMBER NUMBER := 0;
cur_id NUMBER;
BEGIN
SEQ_NAME := 'SEQ_' || VAR_TAB_NAME;
SELECT COUNT (*)
INTO COUNT_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = SEQ_NAME;
DBMS_OUTPUT.PUT_LINE (SEQ_NAME || '>' || COUNT_NUMBER);
IF COUNT_NUMBER = 0
THEN
--DBMS_OUTPUT.PUT_LINE('DROP SEQUENCE ' || SEQ_NAME);
-- EXECUTE IMMEDIATE 'DROP SEQUENCE ' || SEQ_NAME;
-- ELSE
SELECT 'CREATE SEQUENCE COMPTABILITE.' || SEQ_NAME || ' START WITH ' || ROUND (DBMS_RANDOM.VALUE (100000000000, 999999999999), 0) || ' INCREMENT BY 1'
INTO FINAL_QUERY
FROM DUAL;
DBMS_OUTPUT.PUT_LINE (FINAL_QUERY);
cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.parse (cur_id, FINAL_QUERY, DBMS_SQL.v7);
DBMS_SQL.CLOSE_CURSOR (cur_id);
-- EXECUTE IMMEDIATE FINAL_QUERY;
END IF;
COMMIT;
END;
/
回答by adramazany
you could use "AUTHID CURRENT_USER" in body of your procedure definition for your requirements.
您可以根据您的要求在过程定义的主体中使用“AUTHID CURRENT_USER”。
回答by Spellers
Alternatively you can grant the user DROP_ANY_TABLEprivilege if need be and the procedure will run as is without the need for any alteration. Dangerous maybe but depends what you're doing :)
或者，您可以DROP_ANY_TABLE根据需要授予用户权限，该过程将按原样运行，无需任何更改。也许危险，但取决于你在做什么:)
