CREATE/DROP synonyms in another user's schema without ANY privilege
What's the best way to enable a user to create/drop synonyms in another user's schema without doing any of the following?:
1) Granting CREATE/DROP ANY SYNONYM or CREATE/DROP PUBLIC SYNONYM to the synonym creator.
2) Logging in as the user that will own the synonym to create the synonym.
* Although option #2 might be ideal, it would require reworking a lot of code in our environment.
I thought about creating a stored procedure in the syn-owner's schema that issues the "CREATE SYNONYM" DDL command and then granting EXECUTE on this proc to the syn-creator -- but, it's my understanding that it's best to avoid putting DDL inside stored code.
Any ideas?
Oracle version is 10.2.0.4