Re: Creating Tables

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

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? Have I missed a new function in 8.x.x?  

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 Received on Sun May 05 2002 - 08:19:14 CDT