Re: Create view multiple schemes

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Sun, 02 Mar 2003 21:38:43 GMT
Message-ID: <3E627D86.3090600@adelphia.net>

Karsten Farrell wrote:

> gerard_at_HateSpam.kiwi-ict.nl said...
> 

>>Hi All,
>>
>>A company I work for has several schemes in an Oracle 8i database. I'm
>>trying to create a view over several schemes, but only get errors.
>>Maybe one of you can help me.
>>
>>Asume:
>>Scheme1 with table Employee
>>
>>Scheme2 with table Address
>>
>>I log into Scheme3, Where I want to create the view:
>>
>>SQL statement:
>>CREATE OR REPLACE VIEW TEST (or should this be Scheme3.TEST ?) AS
>>SELECT
>> E.Name,
>> A.Address
>>FROM
>> Scheme1.Employee E,
>> Scheme2.Address A
>>WHERE
>>etc. etc.
>>
>>I get the error that Table Scheme2.Address doesn't exist. When I log
>>into Scheme2, and try to create the view here, I get the error that
>>Scheme1.Employee doesn't exist.
>>
>>I'm pretty familiar with SQL-Server, and I've done this several times
>>without a problem. New to Oracle.... So, probably I'm forgetting
>>something here ?
>>
>>Thx,
>>
>>Gerard Verbruggen
>>
>>P.S. I also tried to login as System (the topmost user) and get the
>>same problems.
>>
>>

> 
> 
> 1. Connect to scheme1 and grant select on employee to scheme3 (or better 
> yet, to a role, which you assign to scheme3).
> 2. Connect to scheme2 and grant select on address to scheme3 (or better 
> yet, to a role, which you assign to scheme3).

Not thru a ROLE. Procedures do not get the privileges granted thru ROLEs.

(Or has that changed in newer version of ORACLE?)

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Sun Mar 02 2003 - 15:38:43 CST