Consider the following example,
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.