Problem with privileges when creating sequences in procedure

Question

I have a problem with the execution of the procedure, exactly with the creation of the sequence. I am getting the error ORA-01031 - "insufficient privileges" even though the operation is being performed by the user "system". Code procedure:

CREATE OR REPLACE PROCEDURE my_proc_1
IS
   l_seq_start_with number := 7;
begin
   begin
      execute immediate 'DROP SEQUENCE MY_SEQ';
   exception
      when others then null;
   end;
   execute immediate
      'CREATE SEQUENCE MY_SEQ START WITH '
         ||to_char(l_seq_start_with)
         ||' INCREMENT BY 1 NOCACHE';
end;
/

Calling:

BEGIN
   my_proc_1;
END;

The same operation in the anonymous block is performed correctly - a sequence is formed.

Answer 1

That's because SYSTEM was granted the DBA role which allows it to create a sequence (among other things). But, privileges acquired via roles will work in anonymous PL/SQL blocks (and at SQL level, of course), but will not work in named stored procedures - which is what you have.

Solution? Grant privilege directly to user (system in this case).

Demo:

SQL> show user
USER is "SYSTEM"
SQL> CREATE OR REPLACE PROCEDURE my_proc_1
  2  IS
  3     l_seq_start_with number := 7;
  4  begin
  5     begin
  6        execute immediate 'DROP SEQUENCE MY_SEQ';
  7     exception
  8        when others then null;
  9     end;
 10     execute immediate
 11        'CREATE SEQUENCE MY_SEQ START WITH '
 12           ||to_char(l_seq_start_with)
 13           ||' INCREMENT BY 1 NOCACHE';
 14  end;
 15  /

Procedure created.

SQL> exec my_proc_1;
BEGIN my_proc_1; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.MY_PROC_1", line 10
ORA-06512: at line 1

Failed, as you already know. Grant the privilege explicitly:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant create sequence to system;

Grant succeeded.

Back to system:

SQL> connect system
Enter password:
Connected.
SQL> exec my_proc_1;

PL/SQL procedure successfully completed.

SQL> select my_seq.nextval from dual;

   NEXTVAL
----------
         7

SQL>

Now it works.