Execute Immediate within a stored procedure keeps giving insufficient priviliges error
Solution 1
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.
Solution 2
You should use this example with 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;
/
Solution 3
you could use "AUTHID CURRENT_USER" in body of your procedure definition for your requirements.
tundal45
I am an ETL developer/programmer by profession and trying to pick up Ruby on Rails and basics of web development on the side.
Updated on May 18, 2020Comments
-
tundal45 almost 4 years
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.
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:
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.
-
tundal45 almost 15 yearsThanks for the response. I tried
EXECUTE IMMEDIATE('GRANT drop table ON ' || schema_name || '.' || tblToDrop || ' TO ben');
but I am getting invalid privilege error for that line. I looked online for assigning privilege to drop table but I can't seem to find anything. I tried all and "drop table" but I get the same error. Do you know where I can find the right privilege name or what I am doing wrong here? Thank you once again. -
tundal45 almost 15 yearsSteve, I just figured it out. Turns out I could define the privileges within the procedure using AUTHID. If I want the same privileges as the creator, I use AUTHID DEFINER. If I want the current user's privileges, I use AUTHID CURRENT_USER. Here is how the procedure shell looks: CREATE OR REPLACE PROCEDURE some_procedure AUTHID CURRENT_USER IS BEGIN DECLARE BEGIN END; END;
-
David Keener about 9 yearsExcept that sort of invalidates the reason for having privileges in the first place. Also, "DROP_ANY_TABLE" is unlikely to be casually granted in production for any significantly-sized corporation.
-
Md Wasi almost 5 years@SteveBroberg In my question, all the
GRANT
privilege that i have because I am using as user schema. stackoverflow.com/questions/57070212/…