Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: Mark D Powell <Mark.Powell_at_eds.com>
Date: 19 Feb 2007 07:28:42 -0800
Message-ID: <1171898922.512494.255210@j27g2000cwj.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US