Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: (newbie) How to get around table name qualification?

Re: (newbie) How to get around table name qualification?

From: Frank van Bortel <>
Date: 19 Feb 2007 05:54:14 -0800
Message-ID: <>

On 19 feb, 14:35, "Jeroen ter Hofstede"
<> wrote:
> 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

as lesser privileged user: "create synonym t100 for creator.t100;", but only after you granted the privileges as creator: grant select, insert, delete, update on t100 to <lesser_user>. Of course, you could create public synonyms.

Don't forget to lock the creator account. Received on Mon Feb 19 2007 - 07:54:14 CST

Original text of this message