Re: Another user populates my local tables using a "procedure" under my account.

In article <33233DB5.1504_at_worldnet.att.net>, Gina-Oliver_at_worldnet.att.net wrote:
>I've developed a procedure under my local Oracle user account which
>performs certain calculations and sets a few local variables. Also it
>populates/updates some small local temporary tables with the data
>queried from a main database. I've given another user the "execute"
>privilege on this procedure and she has identical local temporary tables
>as of mine.
>
>When she runs this procedure the plain calculation part works fine and
>her local variables are set correctly. But, the problem is: my local
>tables get popualted/updated instead of her own! even though she has no
>granted privileges
>on my local tables.
>
>Is there a way this could be avoided so she can run the program without
>effecting the tables in my user account. Thanks for any hint.

One suggestion is for you to compile the procedure into her user account/schema. What's happening, I believe, is that the procedure, residing in your schema, attempts to access tables in the same schema. Doing this, she can use the procedure without affecting your tablespace at all...assuming, of course, that you haven't created public synonyms for these tables that her account can see.

The other suggestion is for you to explicitly name the tables under her account/schema in your (recompiled) procedure, and make sure that she gives you select/insert/update privledges (not to mention possibly needing to create synonyms that you can see) for those tables.

In this case, the SQL code would look like:

UPDATE thatuser.table1...

as opposed to

UPDATE table1.

Within your schema, "table1" means *your* copy of table1, not hers.

Good luck,

Loren

--
Loren Davidson      
loren@wombat.net         http://www.batnet.com/beauty/
"If war is the violent resolution of conflict, then peace is not the absence of conflict, but rather, the ability to resolve conflict without violence."
    -- C.T. Butler, "A Guide to Formal Consensus", http://www.ic.org/pnp/ocac/