oops. my bad. I really should double-check in the docs before I
pronounce things :)
you are, of course, right. it merely removes the necessity to preface
an object reference with the owner.
- "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> wrote:
> 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
>
=== 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).
Received on Fri Jan 09 2004 - 19:09:26 CST
