Re: Creating a table with the DBMS_SQL package

"Ralf Zwanziger" <goldensurfer_at_gmx.de> wrote in message news:3ba72c33.105470819_at_news.fth.sbs.de...
> On Tue, 18 Sep 2001 12:00:54 +0200, "Marc" <langkamp_at_mailcity.com>
> wrote:
>
> >The user you are using does not have enough rights to execute certain
things
> >out of dbms_sql
> >
> >using a 'bigger' user results in this:
>
> So which rights should the user have?
> The user has the role "resource", which includes the "create table"
> privilege, but it does not work.
> After I did a "grant create table to <username>" in addition to the
> role, I was able to execute the procedure. Why is the "create table"
> from the resource role not enough?
>
> Bye,
> Ralf
>

Because roles are *ignored* during compilation of stored procedures, as roles are volatile.
This question has been answered many times! When you are using 7.3 or 8.0 there is no other solution than to grant create table *directly* to the user.
In 8i you can create the procedure with invokers rights, and you don't need to do that extra grant.

Please search the archives at groups.google.com when you can suspect your question is a FAQ

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Tue Sep 18 2001 - 12:52:24 CDT