Re: Help moving from Application to Database Security

Does anyone know how a large application like Oracle Financials or SAP handles this issue?
I wish Oracle supported a “Default Schema” user parameter that would point users automatically to an application’s schema without this hard coding or synonym stuff. Its seems that the nature of the Oracle security system steers a developer towards a single application user model.

>but i can't see that it
> would be as large as the performance hit you are going to take
> when you migrate from a single "shared" username to separate
> usernames for each individual application user.
I'm not trying to be argumentenive, but what a performace hit is there for a large number of individual users ?

"Spencer" <spencerp_at_swbell.net> wrote in message news:<PsXR6.442$yt.6341_at_nnrp1.sbc.net>...
> one of the biggest downsides to "harcoding" the schema qualifier
> in the application SQL would limit the flexibility and maintainability
> of your application. using synonyms gives you an extra "layer" of
> abstraction.
>
> i've not done actual testing or measurement of the performance
> "penalty" associated with using synonyms, but i can't see that it
> would be as large as the performance hit you are going to take
> when you migrate from a single "shared" username to separate
> usernames for each individual application user.
>
> for our database, i don't believe the use of synonyms even comes
> close to the "top ten" list of performance no-no's. we've got one
> application that connects and disconnects from the database for
> each insert. we've got some applications that don't reuse SQL
> (no prepared or callable statements using bind variables), we've
> got applications for which the primary requirement (apparently)
> was to chew up database resources.
>
> then again, all of these applications use "shared" usernames, some
> of them implement or make use of connection pooling on the middle
> tier, some of them extensively take advantage of SQL reuse (use of
> bind variables in prepared and callable statements... )
>
> other members of the newsgroup may have different experiences with
> using synonyms. i find that the benefits of using synonyms far outweighs
> any potential performance penalty. but then again, we aren't creating
> hundreds of synonyms for hundreds or thousands of users.
>
> we've only a few public synonyms for "shared" packages, the rest are
> private synonyms created in the schema of a single "shared" username
> used by each application.
>
> HTH
>
> now, i prepare to be toasted...
>
> "Steve S" <stevens_at_coloradocustomware.com> wrote in message
> news:bafba412.0106010844.48b6b35e_at_posting.google.com...
> > Sybrand,
> > I have noticed in documentation and other treads, that that seems to
> > be the only way for multiple users to access a common schema without
> > hard coding the owner in all the statements. Isn&#8217;t there some
> > performance overhead to all these public synonyms? We would have over
> > 150. But, the labor involved to hardcode in our application would be
> > astronomical, so I do have a strong motivation to avoid that method.
> > Why wouldn&#8217;t you recommend the hard coding? Thanks again for
> > taking the time to answer!
> >
Received on Tue Jun 05 2001 - 15:12:23 CDT