Dec 31, 2015

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:-

  Procedure create_table(v_in IN varchar2) AS
  create_tbl_stmt varchar2(32767);
    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;

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

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.

Location: Hyderabad, Telangana, India

1 comment:

  1. The Escorts who work at Call Girls Booking in Mahipalpur have a raised degree of guidance and are thusly essentially loosening up and capitalizing on their organization. There are many types of Foreigner Call Girls in Mahipalpur. Some of them are called centerfold, beauticians, lap dancers, and beauticians. As these agencies are out of various areas, you can choose one according to your liking.
    Body massage near Airport
    Book Call Girls in Mahipalpur
    Hotels in Mahipalpur For Escorts
    Housewife Escorts in Mahipalpur
    High Profile Escorts in Mahipalpur
    Independent Escorts in Mahipalpur