Re: dynamic SQL question

"Jac. @ xs4all" wrote:
>
> this error occurs, when you don't have the privileges for the sql-command
> you are trying to execute with the dbms_sql package. Note that you must have

Actually the creator of the stored procedure doesn't have the privileges. It is possible for you to have the privileges to create a table and not the user who created the stored procedure. In this case it will still fail.

> been given the privileges DIRECTLY, so NOT via a database role. There are
> reasons why this is implemented this way in Oracle, but that's a different
> story...
>
> solution:
> - connect as sys or system
> - execute the command 'grant create table to <user_name>', replace
> <user_name> by the name of the user which creates the procedure containing
> the dynamic sql.

When I first read this I thought you had gotten it wrong. On second reading I believe you got it right but it isn't clear (at least to me). The way I remember it is, a stored procedure is executed with the current non-role privileges as the user who created the stored procedure. Also the dynamic SQL will be executed as the user who created the stored procedure.

later,
Ed Bruce Received on Mon Jan 11 1999 - 00:00:00 CST