can't create table from package

#1

Hello,

i am having trouble creating tables from a package using oracle 10g.
i am logged in as user 'testuser' and am working in the users schema
'testuser' where the user is allowed to perform CREATE TABLE
statements, also the EXECUTE right is granted. but when i execute a
package with a statement like EXECUTE IMMEDIATE 'CREATE TABLE ...'; i
receive a ORA-01031 (insufficient privileges). if i execute the create
statement from an editor window the table will be successfully created
since i am granted CREATE TABLE.

it looks like the grants of the users session are changed when i am
inside a package, but i am wondering why this should make any sense
and how i can get the GRANTS to work inside a package too.

any ideas? help would be highly appreciated!
AndiZed

#2

i already solved my little problem:
if a procedure (or a procedure inside a package) is executed roles are
disabled and so are all grants inside these roles... my user got
CREATE TABLE rights through a role, so it was disabled too.

hope this will help anybody with similar problems.

#3

On Sat, 8 Dec 2007 05:22:47 -0800 (PST), andized (AT) gmx (DOT) net wrote:

Quote:

Hello,

i am having trouble creating tables from a package using oracle 10g.
i am logged in as user 'testuser' and am working in the users schema
'testuser' where the user is allowed to perform CREATE TABLE
statements, also the EXECUTE right is granted. but when i execute a
package with a statement like EXECUTE IMMEDIATE 'CREATE TABLE ...'; i
receive a ORA-01031 (insufficient privileges). if i execute the create
statement from an editor window the table will be successfully created
since i am granted CREATE TABLE.

it looks like the grants of the users session are changed when i am
inside a package, but i am wondering why this should make any sense
and how i can get the GRANTS to work inside a package too.

any ideas? help would be highly appreciated!
AndiZed

Creating tables through packages is just plain evil.

--
Sybrand Bakker
Senior Oracle DBA

#4

On Dec 8, 8:22 am, andi... (AT) gmx (DOT) net wrote:

Quote:

Hello,

i am having trouble creating tables from a package using oracle 10g.
i am logged in as user 'testuser' and am working in the users schema
'testuser' where the user is allowed to perform CREATE TABLE
statements, also the EXECUTE right is granted. but when i execute a
package with a statement like EXECUTE IMMEDIATE 'CREATE TABLE ...'; i
receive a ORA-01031 (insufficient privileges). if i execute the create
statement from an editor window the table will be successfully created
since i am granted CREATE TABLE.

it looks like the grants of the users session are changed when i am
inside a package, but i am wondering why this should make any sense
and how i can get the GRANTS to work inside a package too.

any ideas? help would be highly appreciated!
AndiZed

Why would you want to do this in the first place?

this is just dangerous practice. Don't do it.
Ed

#5

it was part of a package i had to port. the original author said
dropping & creating these tables was faster than performing a
truncate. i meanwhile replaced the CREATEs with ALTER statements.
btw, the CREATEs were not the most evil part in the original
package

AndiZed

On 10 Dez., 18:34, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Dec 8, 8:22 am, andi... (AT) gmx (DOT) net wrote:

Hello,

i am having trouble creating tables from a package using oracle 10g.
i am logged in as user 'testuser' and am working in the users schema
'testuser' where the user is allowed to perform CREATE TABLE
statements, also the EXECUTE right is granted. but when i execute a
package with a statement like EXECUTE IMMEDIATE 'CREATE TABLE ...'; i
receive a ORA-01031 (insufficient privileges). if i execute the create
statement from an editor window the table will be successfully created
since i am granted CREATE TABLE.

it looks like the grants of the users session are changed when i am
inside a package, but i am wondering why this should make any sense
and how i can get the GRANTS to work inside a package too.

any ideas? help would be highly appreciated!
AndiZed

Why would you want to do this in the first place?

this is just dangerous practice. Don't do it.
Ed

#6

andized (AT) gmx (DOT) net wrote:

Quote:

it was part of a package i had to port. the original author said
dropping & creating these tables was faster than performing a
truncate.

I hope you, and everyone else, knows that this statement is
unadulterated rubbish. And in a production environment neither
solution is the correct one.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#7

i am wondering whats so wrong about performing a truncate.
what would the correct solution look like in your opinion?

Thanks,
AndiZed

On 12 Dez., 17:24, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

andi... (AT) gmx (DOT) net wrote:
it was part of a package i had to port. the original author said
dropping & creating these tables was faster than performing a
truncate.

I hope you, and everyone else, knows that this statement is
unadulterated rubbish. And in a production environment neither
solution is the correct one.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damor...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org

#8

andized (AT) gmx (DOT) net wrote in news:5c6ac658-06dc-40b1-94d0-13c78d8b9294
@y5g2000hsf.googlegroups.com:

Quote:

i am wondering whats so wrong about performing a truncate.
what would the correct solution look like in your opinion?

Thanks,
AndiZed

Do NOT top post.
Add your comments BELOW those already present.
People read from the top downwards & notthe reverse.

What is evil & wrong is doing DDL from inside PL/SQL.

I wish that Oracle never had created EXECUTE IMMEDIATE to allow such
nonsense.

#9

Quote:

What is evil & wrong is doing DDL from inside PL/SQL.

I wish that Oracle never had created EXECUTE IMMEDIATE to allow such
nonsense.

DDL in PL/SQL has its uses. How else are you going to write an installation
script for database objects which is

a) safely re-runnable
b) is not allowed to throw any exceptions about already existing objects on
re-run

Don't have EXCEPTION or IF in pure SQL...

#10

On Dec 13, 12:51 am, Kai Rode <nik... (AT) web (DOT) de> wrote:

Quote:

What is evil & wrong is doing DDL from inside PL/SQL.

I wish that Oracle never had created EXECUTE IMMEDIATE to allow such
nonsense.

DDL in PL/SQL has its uses. How else are you going to write an installation
script for database objects which is

a) safely re-runnable
b) is not allowed to throw any exceptions about already existing objects on
re-run

Don't have EXCEPTION or IF in pure SQL...

And with 10g and later releases there is CREATE SCHEMA, which would
preclude the use of PL/SQL to create database objects on product
installation, which creates all or nothing should an error occur.
Seems that would make it safe to rerun should difficulties arise.

David Fitzjarrell