DROP PUBLIC SYNONYM inside of stored procedure

[redbadgers]

I have a stored procedure that accepts object type and object name as inputs then generates a DROP statement and exec immediate as dynamic SQL.
It works fine for all object types except PUBLIC SYNONYM.
When user with DBA role (such as "SYSTEM")invokes this procedure to drop a PUBLIC SYNONYM foo, it will yield insufficent privilege error on the "DROP PUBLIC SYNONYM foo" statement. The user with DBA role can execute the same "DROP PUBLIC SYNONYM foo" statement successfully on the SQL*Plus command line; but not from inside of the stored procedure.
Any Clue? It failed on both Oracle 9i and 10g.
Thanks!
Regards, Nancy

 

[Dagon]

It's probably because your access is through a role and there are restrictions on what you can do in SPs through a role. Try granting the "Drop public synonym" privilege directly to the user who owns the SP.

 

[redbadgers]

The User that I used for testing has DBA role granted, and I checked specifically that DBA role on this Oracle instance still had DROP PUBLIC SYNONYM privilege.
This is what I have tried:
Grant DROP PUBLIC SYNONYM and DROP ANY SYNONYM system privileges to this user, then execute the procedure with attempt to "DROP PUBLIC SYNONYM foo" --- got the same insufficient privilege error. So I changed the DDL to "DROP SYNONYM PUBLIC.foo", still the same error.

Help?!
Regards, Nancy