CREATE OR REPLACE PROCEDURE schema1.proc1 AS
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE schema1.add_ins_seq';
EXECUTE IMMEDIATE 'CREATE SEQUENCE schema1.add_ins_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 1000 NOORDERÂ NOCYCLE';
END;
This procedure is created in schema1 by schema1.
schema1 has been granted CREATE SEQUENCE privilege.
When I run this procedure through SQL Developer after logging into schema1, an error insufficient privilege is thrown at CREATE SEQUENCE, however, DROP SEQUENCE is executed. I can create the sequence without calling the procedure.
If I add AUTHID CURRENT_USER then I don't get the insufficient privilege error.
Why it is giving this error when the owner and the invoker of the procedure is schema1?