| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Privileges of DBMS_SQL in a stored procedure
On Sat, 04 Oct 1997 18:48:17 +0000, Tobias Hitzfeld <Hitzfeld_at_Schoepflin.de> wrote:
>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 !!!!!
>
No, this is not right. The caller of the procedure needs no priveleges. The procedure executes exclusively with the priveleges that were granted to the owner of the procedure. the only thing the caller needs is create session and execute on the procedure.
thats one of the purposes of procedures -- to encapsulate application with a set of privs so that you don't have to give those privs to the end users.
In the case below, the owner of the procedure probably have the create view privelege via a role (and roles are never enabled in stored procedures). to test a pl/sql block in SQL*Plus and see if it will work as a procedure one should:
SQL> set role none; SQL> begin > ..... > /
If the block runs with no roles you can put it in a procedure and be assured it will run there (for any user who can run it) as well.
>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
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |