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?
On Feb 19, 9:25 am, "Jeroen ter Hofstede"
<jeroen.ter.hofst..._at_wanadoo.nl> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
The use of Public synonyms would "map" the objects for all usernames that connect to the database; however, only those users who have been granted object privileges will be able to access the tables.
create table table_a
create public synonym table_a
grant select on table_a to rolename
grant rolename to userA, userB, userC.
would allow userA, userB, userC but not userD to select rows from creator.table_a using the name table_a
For online documentation see http://tahiti.oracle.com
HTH -- Mark D Powell -- Received on Mon Feb 19 2007 - 09:28:42 CST