Oracle database : Can't create table from package - ORA-01031: insufficient privileges

A database user having DBA role not able to create a table from package, it is hard to believe first but this is how it works.

If a procedure (or a procedure inside a package) is executed roles are disabled automatically and so all grants associated with the role. If role is disabled, CREATE TABLE privilege is revoked too that's why it will throw insufficient privilege error. 

Consider the following example,
Package specification:-

create or replace package pl_sql_examples as 
  Procedure create_table(v_in IN  varchar2);
end pl_sql_examples;

Package body:-

create or replace PACKAGE BODY PL_SQL_EXAMPLES AS

  Procedure create_table(v_in IN varchar2) AS
  create_tbl_stmt varchar2(32767);
  BEGIN
    create_tbl_stmt := 'CREATE TABLE customers_dump( customer_id number(10) NOT NULL,
                            customer_name varchar2(50) NOT NULL,city varchar2(50))';
    execute immediate create_tbl_stmt;
  END create_table;

END PL_SQL_EXAMPLES;

Now execute above procedure from hr user (HR User has DBA role assigned) from sql developer/sql plus client :

execute PL_SQL_EXAMPLES.create_table('dummy');

Error starting at line : 1 in command -
execute PL_SQL_EXAMPLES.create_table('111') Error report -
ORA-01031: insufficient privileges
ORA-06512: at "HR.PL_SQL_EXAMPLES", line 8
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Error states that, this user does not have sufficient privilege to create table.(It's strange, Hr user is DBA).Reason behind is- If a procedure (or a procedure inside a package) is executed roles are disabled automatically and so all grants associated with the role (i.e CREATE TABLE privilege is missing for hr since it is given through role)

Solution:- GRANT CREATE TABLE privilege to hr manually instead of through role.Execute following command from system user context

GRANT CREATE ANY TABLE TO HR;

Now execute the above procedure to create table, it should create table customers_dump successfully.

execute PL_SQL_EXAMPLES.create_table('dummy');

anonymous block completed

Note:- It is not recommended to create table from package, if possible it is suggested to avoid it. Sometimes it is inevitable.

---