Help with a Design Question on Security

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 Received on Mon Oct 08 2001 - 23:19:25 CDT