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

On 19 feb, 14:35, "Jeroen ter Hofstede"
<jeroen.ter.hofst...@wanadoo.nl> 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.

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

> <jeroen.ter.hofst...@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.

On Feb 19, 9:25 am, "Jeroen ter Hofstede"
<jeroen.ter.hofst...@wanadoo.nl> wrote:
> On 19 feb, 14:54, "Frank van Bortel" <frank.van.bor...@gmail.com>
> wrote:
>
> > On 19 feb, 14:35, "Jeroen ter Hofstede"
> > <jeroen.ter.hofst...@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 --

On 19 feb, 16:28, "Mark D Powell" <Mark.Pow...@eds.com> wrote:

> 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

Okay, I think that would be a possible solution. The code (that knows
all the tables) could perform the
create table table_a
create public synonym table_a
grant select on table_a to rolename
and we could have the DBA perform the
grant rolename to userA, userB, userC.

In this way, the code would not have to know the user names. This
assumes that it can use a predefined role with standard access rights
(select, update, insert, delete); such a role does not seem to exist.

It starts to look like I'll have to write a batch of custom code to
address Oracle, and have the user provide a number of extra settings.
Well, so be it.

> For online documentation see http://tahiti.oracle.com

Unfortunately, one needs at least an idea for what one is looking to
be able to find anything in that enormous pile - I lack too much basic
Oracle knowledge for that, and I don't have the time to just start
reading from the beginning.


Anyway, thanks for your help.

On Feb 20, 3:12 am, "Jeroen ter Hofstede"
<jeroen.ter.hofst...@wanadoo.nl> wrote:
> On 19 feb, 16:28, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
>
> > 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
>
> Okay, I think that would be a possible solution. The code (that knows
> all the tables) could perform the
> create table table_a
> create public synonym table_a
> grant select on table_a to rolename
> and we could have the DBA perform the
> grant rolename to userA, userB, userC.
>
> In this way, the code would not have to know the user names. This
> assumes that it can use a predefined role with standard access rights
> (select, update, insert, delete); such a role does not seem to exist.
>
> It starts to look like I'll have to write a batch of custom code to
> address Oracle, and have the user provide a number of extra settings.
> Well, so be it.
>
> > For online documentation see http://tahiti.oracle.com
>
> Unfortunately, one needs at least an idea for what one is looking to
> be able to find anything in that enormous pile - I lack too much basic
> Oracle knowledge for that, and I don't have the time to just start
> reading from the beginning.
>
> Anyway, thanks for your help.

Scan the table of contents in the Concepts and DBA Administration
Guides.

Much of what a DBA needs to know is contained in these two manuals.

Then the SQL manual can provide the full options and information on
the use of DDL to create and manage objects.

The Oracle version# Reference manual provides a place to look up all
database parameters, rdbms dictionary view definitions, and basic info
on the dynamic performance views, v$.

If you are more of a developer then see the Application Developers
Guide - Fundamentals. This manual covers the topics in Concepts and
DBA Administration of most interest to a developer.

The most flexible way to manage security is via roles. CREATE ROLE
rolename [IDENTIFIED BY password]. Just have objects for specific
applications or even better job functions issue the appropriate grant
to the role. Then the role can be granted to users OR set by an
application at run time.

HTH -- Mark D Powell --

On 20 feb, 15:16, "Mark D Powell" <Mark.Pow...@eds.com> wrote:

> Scan the table of contents in the Concepts and DBA Administration
> Guides.
[snip rest "reading guide"]


Thanks - that'll indeed help me find what I need.


Jeroen