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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
