Re: "OWNER.TABLE" problem...

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

Received on Thu Feb 04 1999 - 10:38:07 CST