Re: "OWNER.TABLE" problem...

In article <36B9CCEE.9C8104DD_at_sybrandb.demon.nl>, postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote:

> This is a multi-part message in MIME format.
> --------------5130701D8ECA8D99926DF8A8
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hi Chris
>
> create public synonyms for all individual objects
>
> create public synonym X for XYZDBA.X;
>
> and it should work without problem.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Chris Totten wrote:
>
> > OK we've hit another "minor" problem - coming from SQL-Server all of
> > our
> > users are "aliased" to the dbo - we handle the security in our system
> > ourselves and all tables are created using the "sa" login.
> >
> > Up until now our (one) Oracle site has been running using the "XYZDBA"
> > schema/user and this account has been used to create all the tables
> > in the
> > system.
> >
> > Now when we create *other* users (joe, fred etc) and try to run the
> > system
> > we don't get any results - even if we think we've assigned all the
> > correct
> > permissions to the "role" that each user is given...
> >
> > It seems to be that if "joe" wants to access the table, he has to
> > prefix
> > the table name with the table owner, e.g.
> >
> > SELECT * FROM X
> >
> > ...doesn't work...
> >
> > SELECT * FROM OWNER.X
> >
> > ...does work...!
> >
> > The problem is that our application (being designed initially with
> > SQLS in
> > mind), doesn't *have* the prefix and the SQL fails with "table doesn't
> > exist".
> >
> > Is there any way to save us from prefixing the table names in this
> > way? Is
> > there any way to say use "OWNER" by default for example?
> >
> > Failing that, is there some combination of permissions that you can
> > assign
> > to "joe" that allows him to act as if he was an owner of the table
> > also
> > and again save us from having to prefix the table name?
> >
> > The consequence of having this restriction is pretty bad for us - we'd
> > either have to fudge some horrible behind the scenes logging on, or
> > if we
> > could rely on *all* the queries in our system being based on our own
> > objects we could do something about it - parse the SQL or something
> > (this
> > is already being done where required), but we can't rely on *all* the
> > queries being of this type... :(
> >
> > Help!...
> >
> > //chris
>
> --------------5130701D8ECA8D99926DF8A8
> Content-Type: text/x-vcard; charset=us-ascii;
> name="postbus.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Sybrand Bakker
> Content-Disposition: attachment;
> filename="postbus.vcf"
>
> begin:vcard
> n:Bakker;Sybrand
> x-mozilla-html:FALSE
> version:2.1
> email;internet:postbus_at_sybrandb.demon.nl
> x-mozilla-cpt:;0
> fn:Sybrand Bakker
> end:vcard
>
> --------------5130701D8ECA8D99926DF8A8--
>

A million thanks to everyone that replied via e-mail - this looks like the way to do it - we've all breathed a big sigh of relief!!! :))

Thanks again,

//chris Received on Mon Feb 08 1999 - 04:45:42 CST