Re: Help moving from Application to Database Security

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 Fri Jun 01 2001 - 20:40:35 CDT