"OWNER.TABLE" problem...

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 - 09:54:41 CST