Re: Help with a Design Question on Security

sean_at_kewi.net (Sean) wrote in message news:<bc264b7c.0110082019.7dc9e46f_at_posting.google.com>...
> Platform: Oracle 8.1.7 on NT
>
> I have a tablespace with a user (let's say "FOO") that has tables,
> stored Procedures, packages etc. All calls to FOO's tables MUST go
> through the packages and procedures we've created (as a means of
> security). I have granted another user "BAR" to execute those
> packages.
>
> Instead of having to call the procedure as
> "FOO.packageName.packageBody", I created a public Synonym so the call
> to the database could just be "packageName.packageBody". This is when
> the problems started.
>
> When the application connects as "BAR" and calls the procedures, over
> a short time, we get the infamous :
>
> ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
> pool","begin pack_login.login(STRUS...","PL/SQL MPCODE","BAMIMA: Bam
> Buffer")
>
> My question is : are Public Synonyms not the way to go? Should I just
> change the code calling the packages as "FOO.packageName.packageBody"
> ?
>
> Thanks for your help,
> Sean

No.

This error occurs because your shared pool is too small and/or too fragmented and could have happened at *any* moment, and has *nothing* to do with synonyms.

Increase or have increase the init.ora parameter shared_pool_size and the database bounced, or flush the shared pool (alter system flush shared_pool) and retry the operation.

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Tue Oct 09 2001 - 07:05:45 CDT