Re: Creating Tables

Comments embedded

"Marc Blum" <marc_at_marcblum.de> wrote in message news:3cd52d5d.11940589_at_news.online.de...
> hi,
>
> ok, here I am, ready to get burned down to the ground :-)
>
> Our applications (and there are many in one db) are designed the
> hopefully "right" way:
>
> APP_OWNER: holds tables, packages views etc, CREATE SESSION revoked
>
> APP_ROLE: bundles SELECT, EXECUTE etc privs
>
> APP_USER: no objects, just the APP_ROLE granted to it, used by the
> application to connect to the db and do the work

Nothing wrong so far
>
> Now for the ugly(?) part!
>
> The problem:
> Everywhere in packages and triggers and SELECTs and bla ... we need to
> know the OS-user, which differs from the ORACLE-user. (In that
> context, it's not applicable to create an individual ORACLE-User for
> every person working with the application.)
>
> The solution:
> A packaged function, which returns the OS-User. The package is owned
> by SYS, the package gets GRANT EXECUTE TO PUBLIC and a PUBLIC SYNONYM.
> This one is the single only synonym in our whole application suite.
>
> The question:
> Am I evil? Do I have to go to church and pray to god to forgive me?

Everyone needs to.

> Have I missed a new function in 8.x.x?
>

SYS_CONTEXT in 8i

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address




>

> On Fri, 03 May 2002 01:13:09 GMT, "RSH" <RSH_Oracle_at_worldnet.att.net>

> wrote:

>

> >Apart from  the emphatic warnings, so eloquently posted,

> >

> >"NOOOOOOOOO".

> >if that adds any useful emphasis.

> >

> >Hypothetically, say, you are building an enterprise or multi-unit system.

> >

> >SST might be State Sales Tax.

> >IRS might be payroll deductions for your national tax authority.

> >

> >And so forth.

> >

> >SVAT might be for Value-Added Tax, an idea that has fortunately not

caught


> >on among us barbarians, at least in these parts. We do not even

understand


> >the taxes we are already paying, which I understand is not uncommon

> >elsewhere.

> >

> >In any case:

> >

> >to echo my colleague again,

> >

> >"NOOOOO".

> >

> >Never create, change, touch, or even make funny faces at anything owned

by


> >SYS.

> >

> >I would advise against any meaningful amount of humor similarly directed

at


> >SYSTEM.

> >

> >Nowadays with CTXSYS and God only knows what else, it generally is a good

> >policy to leave untouched, places that Oracle has set aside for some

> >purpose, unless you are sure beyond doubt, what you are getting into.

> >

> >Create usernames with default tablespaces for objects and for TEMP that

are


> >not SYSTEM. (Create the tablespaces first... sigh.)

> >

> >It would not be unusual to have a layout such as:

> >

> >AR_TBL

> >AR_IDX

> >PAY_TBL

> >PAY_IDX

> >

> >..

> >etc

> >to store application specific data and the indices that relate to the

data.


> >

> >That was given just as an academic example. And there are numerous people

in


> >this newsgroup who would fervently argue a completely different strategy

for


> >you.

> >

> >In any case, if there is one single issue I would risk money on wagering,

it


> >would be DO NOT EVER put anything that Oracle does not own, or create, in

> >the SYSTEM tablespace. The data dictionary lives there, everybody's

PL/SQL


> >lives there, etc.

> >

> >Given the volatility of the group lately, I wonder whether we can even

gain


> >consensus on that issue.

> >

> >Sybrand is completely correct by starting off with "ARRRRGGGGHhhh..."

etc.


> >(Correction.)

> >

> >I would have prefaced it with AIIIGH, then the NOOOO.

> >

> >Among the reasons this is the root of all evil, is that you are not free

to


> >do automatic or manual 'garbage collection', in SYSTEM; every other

> >tablespace can be crunched, if it comes to the worst, and performance is

> >dreadful, by EXPORTing and then IMPORTing the entire application's

objects.


> >

> >In the case of  the SYSTEM tablespace, however, it generally means

> >rebuilding the entire database (since SYSTEM holds things like all the

> >dictionary structures, permissions and security, etc).

> >

> >But there are ways even to get through that, if that is what you have to

do.


> >

> >Please keep us  apprised of your progress.

> >

> >Finally, if you absolutely, positively are in a mess due to this, the

> >problem can be corrected, but it is a procedure that Oracle documents,

but


> >does not brag loudly or widely about.

> >

> >RSH.

> >

> >"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message

> >news:ud3jv9ppdne8b_at_corp.supernews.com...

> >> Comments embedded

> >> "mtwalla" <mtwalla_at_bluefishgroup.com> wrote in message

> >> news:Q_jA8.4781$VQ6.602987_at_news.uswest.net...

> >> > If I am creating tables/database objects specific to an application

who


> >> > should be the owner of those tables? Should I create them to be owned

by


> >> SYS

> >>

> >> AAARRRRRRRRRRRGGGGGGGGGGGGGGGGGGGGGGGGGG

> >> NEVER EVER do this. SYS is the owner of the datadictionary and is not

> >> exported. You should your hands have chopped off for even considering

it.


> >>

> >>

> >> > and then create public synonymns on the tables/objects? Or should I

have


> >> an

> >> > application user who owns the tables/objects

> >> YES and if so, should I create

> >> > Synonyms on them?

> >>

> >> YES. In an ideal world the owner should have revoked create session

> >> privilege after the deed or the account locked,. as you can't protect

the


> >> owner of the tables against itself. Too many people are always connect

as


> >> application owner in Oracle, and as the user Oracle on their O/S. One

slip


> >> of the finger and you have havoc.

> >> Or is there some other approach altogether?

> >> Hardcode the owner of the object in front of every object name in

selects


> >> etc.

> >> Bad idea (tm)

> >> Thanks! I know

> >> > how to create tables, write the DDL, etc. I'm just fuzzy on the

> >ownership

> >> of

> >> > the objects.

> >> >

> >> >

> >> >

> >> >

> >>

> >> Please do not crosspost and multipost.

> >>

> >> Regards

> >>

> >>

> >> --

> >> Sybrand Bakker

> >> Senior Oracle DBA

> >>

> >> to reply remove '-verwijderdit' from my e-mail address

> >>

> >>

> >

> >

>

> regards

> Marc Blum

> mailto:marc_at_marcblum.de

> http://www.marcblum.de