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

On 19 feb, 14:54, "Frank van Bortel" <frank.van.bor..._at_gmail.com> wrote:
> On 19 feb, 14:35, "Jeroen ter Hofstede"

> <jeroen.ter.hofst..._at_wanadoo.nl> wrote:
[...]

> > 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;",

Yes, but that would
- either require that the code knows the user account name for the creator account (I used "creator" as an example, actually I'd like to leave the customer free in it). This is doable, but I was wondering whether a more elegant solution existed; - or that the user/dba performs these steps himself, which is a bit painful for some seventy tables.

> 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.

II have looked into synonyms. What would solve my problem is if there is a single command or configuration option that would map everything from "creator" into the "user" name space; however, it looks it can only be done per table, right?

> Don't forget to lock the creator account.

Probably, I'll leave that to the customer's DBA.

Thanks for your help. Received on Mon Feb 19 2007 - 08:25:09 CST