Begin
Execute immediate 'CREATE SEQUENCE seq1 MINVALUE 1 MAXVALUE 99 START WITH 1INCREMENT BY 1 CACHE 20';
End;
Sequence creating successfully.
Create or replace procedure proc_seq as
Begin
Execute immediate 'CREATE SEQUENCE seq2 MINVALUE 1 MAXVALUE 99 START WITH 1INCREMENT BY 1 CACHE 20';
End;
Exec proc_seq();
Error: Insufficient Privilege...
This is a invokers/definers rights issue. By default a stored procedure has definer's right (DR - emphasis added):
During a server call, when a DR unit is pushed onto the call stack, the database stores the currently enabled roles and the current values of
CURRENT_USERandCURRENT_SCHEMA. It then changes both CURRENT_USER and CURRENT_SCHEMA to the owner of the DR unit, and enables only the rolePUBLIC.
If your privilege to create a sequence was granted to you via a role, then as that role will be disabled, the privilege isn't active. You can verify that is the case by running set role none, which will cause your anonymous block to fail with the same error.
As @Littlefoot said you can have the privilege granted directly to you instead of (or as well as) via the role.
Alternatively, you can specify that the procedure should have invoker's rights:
Create or replace procedure proc_seq
authid current_user
as
Begin
Execute immediate 'CREATE SEQUENCE seq2 MINVALUE 1 MAXVALUE 99 START WITH 1INCREMENT BY 1 CACHE 20';
End;
/
exec proc_seq;
PL/SQL procedure successfully completed.
select seq2.nextval from dual;
NEXTVAL
----------
1
Hopefully this is a contrived example; you wouldn't normally create database objects at runtime anyway...
The caveat to this approach is that it might not be suitable for how you intend the procedure to be invoked. If you're running it as yourself then that's fine, but if you will be granting execute permission to other users then they would have to have the relevant privileges (granted either directly or via a role); and would be creating objects in their own schema. That may be exactly what you do want. But if it isn't, and you don't want to grant create sequence to the callers or to have them create their own objects, then you can keep the procedure with definer's rights and grant the privilege to your user directly.
authid current_user to the package specification instead of to the specific procedure. Also make sure you understand all the implications, whether package or standalone procedure, based on how it will be called - and by whom. Granting the create sequence priv directly to the proc/package owner may well be more appropriate - it depends on your circumstances.
Commented
Dec 7, 2018 at 9:07
If you acquired CREATE SEQUENCE privilege via role (apparently, you did), it will work in SQL layer as well is in anonymous PL/SQL blocks (your first code).
However, it won't work in named PL/SQL procedures (your second code) - to do so, you (which means: user that runs that code) will have to be granted that privilege directly, not via role.
