(newbie) How to get around table name qualification?

An apology in advance: I'm only sideways involved in Oracle databases and know very little about them. If it seems that I'm not knowing what I'm talking about, that may very well be true.

I'm writing an application that uses a database, via ODBC, for its own private storage. It itself creates the necessary tables, columns and indices, as it knows best what it needs to store and does not want to bother the users with it. This table-creating is part of a separate step in set-up, afterwards the application only needs to write and read data.

Some of our customers want to run the application on their Oracle database (ranging from 8i to 10i, currently). I don't have any DBA skill worth mentioning, let alone Oracle DBA skills, and I'm stuck on the following.

Due to security concerns, it is mandatory that the Oracle user account that is used to access the database during normal operation has no more than read and write rights. Another, much more privileged, user account is to be used during set-up to create the tables.

It appears however that tables created by this more privileged account cannot be "seen" by the normal account. If I create tables with a user called "creator", table names appear to be prefixed with this name, e.g. table "T100" becomes "creator.T100". If the account "user" attempts to access "T100" it gets the error message that no such table exists. (As an interesting side note: the table /does/ show up if user
"user" performs a SQLTables() call in ODBC, however it is not found
when executing SQL statements.)

Of course, I could add an option to prefix each table name with a configurable name; or I could require that the user account to create the tables has a specific known name so I can hard-code it - these
"solutions" are very ugly however, and I suspect that there are much
better ways to handle this.
With MS SQL Server it is sufficient to map the user account doing the creation to 'dbo,' all tables created by this account seem to be accessible from all other users with no need to prefix the table names. Does Oracle has a comparable feature, or perhaps an incomparable one that solves the issue?

Thanks in advance,
Jeroen Received on Mon Feb 19 2007 - 07:35:57 CST