Re: Privileges of DBMS_SQL in a stored procedure

Michael Cretan wrote:
>
> Hi,
>
> I have created a procedure that creates views in a schema. It does this
> by extracting the view definitions from tables into a varchar2 field,
> and then calls the dbms_sql package to create the view.
>
> Whilst the PL/SQL works quite happily when executed at the sql prompt,
> when I store it in the database as a procedure and execute it, I get
> insufficient privilege errors being raised by the DBMS_SQL package.
>
> This strikes me as a little strange as the procedure is stored in the
> same schema as the views, and their underlying base tables. DBMS_SQL is
> supposed to inherit the privileges of the schema where the procedure is
> stored.
>
> Has anyone had this problem, and maybe found a workaround for it ?
>
> We are running Oracle v7.3.2.3.0 on and AIX box.
>
> Thanks,
>
> Mike Cretan

grant the create view priviledge to the owner of the package. Roles are disabled during execution of the dynamic sql, so you need the explicit priviledge of creation of view.

Vijay Darekar Received on Fri Oct 03 1997 - 00:00:00 CDT