Ora-01031 when execute immediate 'create table ...'


Dear advisor !

i have user leaseline with connect, resource, DBA role.

I have a 'CreateData' procedure and i could run it well before. Now, i run it with error.

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "LEASELINE.CreateData", line 3
ORA-06512: at line 1

the CreateData procedure

As
Begin
    Execute Immediate 'Create table aaaaaaaaaaa (a varchar2(1))'      ;
end ;

Please show me how to corecct it. Maybe, i have change some configure at Oracle , but i do not remember.

Why error

Thank for all consider
namcit99Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
baonguyen1Connect With a Mentor Commented:
You may need to grant CREATE TABLE privilege to the user. By defaut users are granted via role:

SQL>Grant CREATE TABLE to <user>

the try again

0
 
annamalai77Commented:
dear friend

once again grant all the roles to the user and try it.

regards
annamalai
0
 
namcit99Author Commented:

the 'leaseline' user has DBA, connect, resource Role. So the Leaseline user has 'Create Table' priviledge

Why ? i has modify some Role before, but i just test and the 'Create tbel ' still exist on connect and DBA role
0
 
annamalai77Commented:
hi

even though u give the resource its for the unlimited quota on the tablespace. and dba role is for performing certain dba privilieges.

try this for the user.
grant create session to <user>;

regards
annamalai
0
 
ishandoCommented:
Privileges granted through roles are not recognised in PL/SQL, only eplicitly granted privileges.

As baonguyen1 said - grant the create table privilege to the user and you should be ok.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.