Daniel,
you are indeed missing something. New ANSI JOIN syntax bypasses Oracle
security *ALTOGETHER*. If you can connect, you can query ANY table or
view by joining it to itself using LEFT OUTER JOIN (or CROSS JOIN or
whatever JOIN) syntax! You can create views on these tables, and you can
DELETE from these views - and you don't need any other privilege than
CREATE SESSION to be able to do so. So it's not easier to protect the
data dictionary in 9i - in fact, it is impossible to protect anything in it.
And that's what this whole thread is about.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3CBC4043.3753A9C6_at_exesolutions.com...
> I must have misread something. Perhaps I still am. The table that contains the
> information is link$. Who has access to link$ in a production system with 9i
> other than sys?
>
> If you are worried about sys compromising security by knowing a password then
> perhaps we need to file a bug report on ALTER USER too.
>
> I'm being a bit tongue-in-cheek here. Obviously there is a security problem and
> I take it seriously. But an unencrypted password has been present in
> user_db_links for a long long time; the source of which was link$. I can log on
> as sys to an 8.1.7 instance and do SELECT password from link$ and see
> unencrypted passwords. I don't know why but it is certainly there and has been
> going back as long as I can remember (perhaps a very short memory). But the
> solution is to make sure no one sees link$ which is easier in 9i than ever
> before as SELECT ANY TABLE no longer gives access to the data dictionary tables
> such as link$.
>
> Ok I submit myself to the court of public opinion. What am I missing? What is it
> in 9.0.1 that isn't present in 8.1.7?
>
> Daniel Morgan
>
>
>
> Jonathan Lewis wrote:
>
> > Daniel,
> >
> > As so often happens, you have put your foot
> > in your mouth without reading the post.
> >
> > Examine the entire script carefully -
> >
> > The user selecting from sys.link$ has
> > been created and granted create session
> > AND NO OTHER PRIVLEGE.
> >
> > --
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> > Next Seminar - Australia - July/August
> > http://www.jlcomp.demon.co.uk/seminar.html
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> > Daniel Morgan wrote in message <3CBB5EFC.43A50425_at_exesolutions.com>...
> > >Why ouch? This is known for database links for a long long time. It is not
> > >present there if you create the link in one of the manners that supports
> > >security.
> > >
> > >And no one other than sys should be looking at sys.link$ anyway. If they
> > are
> > >you have been granting SELECT ANY TABLE to people without regard to its
> > >implications. In 9i access, even with SELECT ANY TABLE goes away. And
> > >hopefully stays that way.
> > >
> > >But if I had my preference, which I don't, Oracle would have encrypted it
> > >back at version 7.x or before. I would be interested in hearing from anyone
> > >inside of Oracle why this is there. Thanks.
> > >
> > >Daniel Morgan
> > >
> > >
> > >Jonathan Lewis wrote:
> > >
> > >> Ouch - 9.0.1.3 on HP-UX
> > >>
> > >> connect / as sysdba
> > >> CREATE USER us1 IDENTIFIED BY us11;
> > >> Grant Create Session To us1;
> > >>
> > >> connect us1/us11
> > >>
> > >> select a.userid, a.password
> > >> from sys.link$ a left outer join sys.link$ b on
> > >> b.name= a.name
> > >> ;
> > >>
> > >> userid password
> > >> --------- --------------
> > >> XXX **********
> > >>
> > >> --
> > >> Jonathan Lewis
> > >> http://www.jlcomp.demon.co.uk
> > >>
> > >> Author of:
> > >> Practical Oracle 8i: Building Efficient Databases
> > >>
> > >> Next Seminar - Australia - July/August
> > >> http://www.jlcomp.demon.co.uk/seminar.html
> > >>
> > >> Host to The Co-Operative Oracle Users' FAQ
> > >> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> > >>
> > >> Vladimir M. Zakharychev wrote in message ...
> > >> >Anyone with 9i can confirm this?
> > >> >
> > >> >
> > >> >
> > >> >This effectively means that LEFT OUTER JOIN allows to create views
> > >> >on tables that are normally not visible (provided that unprivileged user
> > >> >knows table and column names).
> > >
>
Received on Tue Apr 16 2002 - 23:59:19 CDT
