Re: Schemas

"Kevin Frey" <kevin_g_frey_at_hotmail.com> wrote in message news:e5d907ad.0303251418.7567a5b2_at_posting.google.com...
> Hello,
>
> I am working on a multi-RDBMS-platform database project and hope
> someone can clarify a few points concerning Oracle 9i.
>
> It is not possible to create an "arbitrary" schema (eg. "MYSCHEMA"),
> as is possible in DB2 with the CREATE SCHEMA statement?

Correct. A schema is a user, at the end of the day. So you can't have a schema floating in a vaccuum; there has to be an 'arbitrary user' created to own it. Oracle itself does this with the user OUTLN (who owns the OL$ and OL$HINTS tables used to store Stored Outlines). Such 'faux users' usually have their accounts locked, and their default passwords changed, so that no-one ever actually logs on to them (though their permissions tend to be fairly minimal in any case).

>
> Each oracle user has their own schema by default which their own
> tables (if they create any) are created in?

Correct.

>
> Can a user (eg. SYSTEM) create tables in a different schema? Does this
> require the CREATE SCHEMA statement or can it be done independently
> for each CREATE TABLE statement, etc?

Yes, a user can create tables in someone else's schema (and thereby use up that other person's quota, not their own) if they have been granted the 'create any table' system privilege. For a full list of system privileges, select * from system_privilege_map. There you will see lots of privileges with the word 'ANY' in them... and 'ANY' means 'anybody's schema' (so, for example, 'select any table' means I can select from anybody's table, and not just my own. And 'drop any table' means I can drop anybody's tables, not just my own).

>
> This raises the question of what the appropriate "best practice" is
> for creating tables/views etc in an oracle database, when the
> intention is for many users to access the same tables. The points this
> raises are:
>
> 1. Since you can't create an arbitrary schema, should you instead have
> a specific user that "equates" to the schema you want to have. For
> example, I notice some of the Oracle examples refer to a "hr" (human
> resources) schema, which presumes there was a user named hr set-up in
> order to create the database.

Yes, many developments end up creating an 'application user'... that is, a dummy account used to own all the tables that the application makes use of. "HR" is one example. "ACCNTS" is one I've seen for an accounting system.

>
> 2. Is it recommended to create [public] synonyms to map object names
> in the user's schema to objects in another schema?

That's another way to do it, for sure. But there can be performance implications from having many public synonyms. And it doesn't get away from the fact that, at base, your database is comprised of tables in different schemas.

>In our situation
> the user does not access the database directly (eg. with raw SQL) just
> through our application server, so it seems an administrative overhead
> to have to create all these synonyms. The alternatives are ALTER
> SESSION SET CURRENT_SCHEMA = blah or explicit qualification of the
> schema name in every SQL query? Does anyone care to recommend the best
> approach (and why)?

There will be several opinions on this, I suspect. I prefer the 'application schema' approach.

Regards
HJR
>
> Many thanks
>
> Kevin.
Received on Tue Mar 25 2003 - 17:34:46 CST