Um, roles, privileges, etc are administered however you'd like.
The only suggestion I'm making is that rather than having public
synonyms for all objects in your app_owner schema, each user which needs
default access to the objects in the app_owner schema, gets access to
that schema via the logon trigger that sets current_schema. This does
(should) not have any effect on how you manage roles and permissions,
just how Oracle does default object resolution.
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is." --Unknown
-----Original Message-----
Sent: Friday, January 09, 2004 3:35 PM
To: Multiple recipients of list ORACLE-L
How would you handle the case where there are many sets of privileges,
depending on which user you log in as?
The trigger would give everyone the right to do anything the owner
could to a table. There are times when I want create a "read-only"
account in addition to an app user.
we do allow sqlplus access to production by developers -- in a
read-only state so they can investigate end user complaints.
- "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> wrote:
> The other option is a user logon trigger that does execute immediate
> 'alter session set current_schema=appowner';
>
>
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Imagination was given to man to compensate him for what he is not,
> and
> a sense of humor was provided to console him for what he is."
> --Unknown
>
>
> -----Original Message-----
> Sent: Friday, January 09, 2004 2:44 PM
> To: Multiple recipients of list ORACLE-L
>
>
> just to weigh in on the side of use of public synonyms.
>
> If you intend to use roles to simplify privilege management, you are
> almost forced to use public synonyms, as you cannot create a private
> synonym owned by a role. Your other alternative is to hard-code the
> schema owner name in every object access, which somewhat invalidates
> the idea of portability and security between dev/test/production
> environments, as you would have to maintain the same schema owner
> name
> in all environments.
>
> As a general practice, I create a "schema owner", a "schema user" and
> a
> "schema proc_owner" account. I use public synonyms throughout and
> have
> never seen a performance hit because of them.
>
> My (practical) $0.02
>
> Rachel
>
> --- K Gopalakrishnan <kaygopal_at_yahoo.com> wrote:
> > Yong:
> >
> > I have not followed the thread completely. So I may be missing
> > something obvious ;)
> >
> > <BEGIN-NON TECHNICAL>
> >
> > Many applications (for example Oracle Applications) use public
> > synonyms
> > heavily and running with better (or acceptable) performance. We
> > should
> > not really worry about the milli second performance improvements
> > comparing with the coding/application development flexibilities
> > offered
> > by public synonymns.
> >
> > If you look at Steve's test carefully, the improvement or over head
> > in
> > the public synonym to private synonym is around 10% of the CPU time
> > and
> > latch gets. How much performance improvement you can expect in
> > practical systems with the additiona 10% of latch gets/CPU times
> > comparing with the overall system performance. I would expect less
> > than
> > (LESS THAN) 1-2% in total response time.
> >
> > <END NON TECHNICAL>
> >
> > The actual over head is coming from the negative dependency
> tracking
> > or
> > high version count in the Library cache. But this will be an issue
> > only
> > when too many users are connected and accssing the database with
> > too
> > many accounts (different parsing user_id). But this is rarely a
> > situation in many of the packaged application as most of the
> > application connects to the database using a single account (like
> > 'APPS' user in Oracle eBusiness suite) and no negative dependency
> or
> > multi version of SQLs are an issue here (at least in a single
> > instance
> > oracle). The over head can be little higher in RAC environment as
> the
> > Library Cache and Row cache is globally co-ordinated.
> >
> > However I have not really seen major problem (may be I have not
> > observed them too keen) with the public synonyms as most of the
> > packaged applications I have worked are using a single oracle
> account
> > to connect to the database. It is just my personal observation, and
> > may
> > be Jonathan can have a different opinion.
> >
> > Regards,
> > KG
> >
> > PS : Aplogies if some one has already addressed this issue, I have
> > been
> > traveling and didn;t have enough free time to read all the posts.
> > Seen
> > lots of posts flooding on this topic and jumped in out of
> curiosity.
> >
> >
> >
> >
> >
> >
> > --- Yong Huang <yong321_at_yahoo.com> wrote:
> > > > > To the OP: Other people point out common reasons for library
> > > cache latch
> > > > > contention. A less common reason is extensive use of public
> > > synonyms.
> > > > > If that's the reason, you also see row cache objects latch
> > > contention.
> > > >
> > > > I'm not sure that's right. If everyone uses a public synonym,
> > then
> > > > you get one sql text, and one cursor. I think the contention
> > > appears
> > > > because everyone has to have a 'non-existent' reference in
> memory
> > > > to say that they don't own an object with the same name as the
> > > public
> > > > synonym - consequently if you have lots of users who have to
> > check
> > > > long chains of 'non-existent' then the latches get held for
> > longer
> > > > periods of time.
> > >
> > > Hi Jonathan,
> > >
> > > I don't see how your statement contradicts the claim that heavy
> use
> > > of public
> > > synonyms causes contention for not only library cache latches but
> > > also row
> > > cache objects latches. What I had in mind is Steve Adams' test.
> > > Here's the URL
> > > http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I
> > > understand right,
> > > the additional row cache objects latch gets are for synonym
> > > translations,
> > > particularly public synonym translations.
> > >
> > > Yong Huang
> > >
> > >
> >
> > =====
> > Have a nice day !!
> > ------------------------------------------------------------
> > Best Regards,
> > K Gopalakrishnan,
> > Bangalore, INDIA.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: K Gopalakrishnan
> > INET: kaygopal_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
> http://hotjobs.sweepstakes.yahoo.com/signingbonus
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Bobak, Mark
> INET: Mark.Bobak_at_il.proquest.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>
=== message truncated ===
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bobak, Mark
INET: Mark.Bobak_at_il.proquest.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 09 2004 - 15:09:27 CST
