Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: And more synonyms ...

Re: And more synonyms ...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 13 May 1998 18:46:10 GMT
Message-ID: <355ae9bc.2777563@192.86.155.100>


A copy of this was sent to Alex Vilner <avilner_at_usa.net> (if that email address didn't require changing) On Wed, 13 May 1998 12:20:43 -0400, you wrote:

>Synonyms just keep getting to me more and more ...
>I have user_A in the database, that has all the objects.
>I have user_B that has synonyms that point to objects that user_A owns.
>User_B has a full set of rights to user_A's objects (includes tables,
>functions, procs, sequences, etc.).
>
>When I execute this query
>
> SELECT TABLE_OWNER
> FROM USER_SYNONYMS
> WHERE TABLE_NAME = object_name;
>
>while being connected as user_B, I get the right information:
>the object is indeed being owned by user_A. When I make this
>statement into a function that user_A owns, and give user_B
>execute permission on it, and create a synonym for user_B to
>point to this user_A's function, I get NO_DATA_FOUND
>exception.
>
>It is as though because the function belongs to user_a, it gets
>confused looking at the USER_SYNONYMS...
>

Procedures run as OWNER. the data dictionary supports that. If you look at the view definition for USER_SYNONYMS:

select o.name, s.owner, s.name, s.node
from sys.syn$ s, sys.obj$ o

where o.obj# = s.obj#
  and o.type# = 5
  and o.owner# = userenv('SCHEMAID')


the view selects only those records whose OWNER id is equal to the userenv('SCHEMAID'). userenv('SCHEMAID') returns the 'effective userid', in a procedure/package it returns the userid of the OWNER of the package/procedure.

When user_a created that function on user_synonyms, it fixed its schemaid to be user_a's userid.

If you want to 'get around' this feature, you need to query dba_* views and provide your own filters on the resultant data (eg: makeing sure that user_b sees what user_b should see and user_a sees what user_a should see and so on)

>Any suggestions?
>
>Alex Vilner
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed May 13 1998 - 13:46:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US