Re: Stored procedures reacts different to different users

On 21 May 2002 01:06:35 -0700, mrkrister_at_yahoo.se (mrkrister) wrote:

>Hi,
>
>In my application I need to find out which tables the application
>owns. So I wrote a Stored procedure to do this. The essence of the SP
>is as below:
>
>OPEN rsGetAllTables FOR
> SELECT table_name, tablespace_name from all_tables;
> WHERE tablespace_name = 'DEVUSR';
>RETURN '';
>
>The problem is that I have two users, one developer and one for the
>application. When I run this stored proc as the development user, I
>get all the table names. But when the application calls this SP,
>nothing is returned.
>
>Shouldn't the result from an SP always be the same, regardless of
>which user calls the procedure? How come this stored procedure reacts
>differently depending on which user is logged in
>
>The tables are created by the user DEVUSR.
>I am using VB6, Oracle8i and OO40
>
>Hope someone can give me a hint on what the problem is.
>
>Tia
>mrKrister

The other user either
a) has no privilege at all to any of the tables of DEVUSER b) has privilege through a role

The solution in 8i is to run the procedure with authid current_user

This solution must have been posted to all Oracle newsgroups at least a thousand times.
Why do people maintain archives, FAQ resources, and manuals if no one actually uses them?

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue May 21 2002 - 09:47:01 CDT