Re: Create view multiple schemes

Ed Prochak wrote:

> 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

With respect to privileges such as selecting from or referencing tables you are correct.

Daniel Morgan Received on Sun Mar 02 2003 - 17:00:50 CST