Re: Privileges of DBMS_SQL in a stored procedure

From: Tobias Hitzfeld <Hitzfeld_at_Schoepflin.de>
Date: 1997/10/04
Message-ID: <34368F94.B5C59A07@Schoepflin.de>#1/1

--------------FFD6F4BBEDB59346842F4C1C
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi Michael!
Let me quess: You'r calling your procedure, while you're connected with another schema-sure, 'cause
you'll copy the view !!!

Make sure while using dynamic sql that
1) the user which is calling your procedure has the system privileges to create a view
2) the user which is calling your procedure has received the object privileges to access to the base tables/views of the view,

you wish to create!

Even if the proc-caller will temporarely receive the same rights as the proc-owner !!! The proc-caller is executing dynamic sql and so, his own system- and object-priviliges will be checked !!!!!

Scincerly yours
T.Hitzfeld
Oracle DBA

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



--------------FFD6F4BBEDB59346842F4C1C

Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

<HTML>

Hi Michael!
 Let me quess: You'r calling your procedure, while you're connected
with another schema-sure, 'cause
 you'll copy the view !!!

Make sure while using dynamic sql that
 1) the user which is calling your procedure has the system privileges
to create a view
 2) the user which is calling your procedure has received the object
privileges to access to the base tables/views of the view,
     you wish to create!

Even if the proc-caller will temporarely receive the same rights as
the proc-owner !!! The proc-caller is executing dynamic sql and so, his own system- and object-priviliges will be checked !!!!!

Scincerly yours
 T.Hitzfeld
 Oracle DBA

Michael Cretan wrote:
<BLOCKQUOTE TYPE=CITE>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</BLOCKQUOTE>

  </HTML>

--------------FFD6F4BBEDB59346842F4C1C-- Received on Sat Oct 04 1997 - 00:00:00 CDT