Can not create table using Procedure

[dhaval trivedi]

I have created one procedure which creates a table in ohter schema.
Tablename,schema name and tablespace name i m passing through dynamically.(actually I stored those value in table and i am passing this value while creating table). It is executing successfully on my development environment but when i ran same procedure on test environment (other server) than it is giving me error of insufficient previleges. I have made replica of devlopment environment to test environment .

I have given both users dba role. But still it shows me insufficient previleges error.

[adewri]

Does user A who is executing the procedure have quota on user B's tablespace? If yes then try giving 'create any table' grant to user A.

[pando]

obviously a role is not enough for stored procedures, you must grant the privilege directly and not through a role

[TimHall]

This is one of those things that comes up time and time again. Perhaps Oracle should put a slight enhancement into the database, whereby when an insufficient privileges error is detected the server checks if the permission is granted via a role. If it is it displays a different message like,

"insufficient privileges - Your privileges on this object are granted via a role, not directly"

That way the cause of the issue would be more obvious.

Just a thought...

Cheers

Tim...