SQL & PL/SQL

Greetings all,

When my company's application is deployed, two schema owners are typically created and all database objects divided between the two. I'll call them FIRST and SECOND.

In a standard, vanilla implementation there is never any reason for the two to "talk to each other". No rights to objects in one schema are ever granted to the other.

I am currently charged, however, with writing custom code to roll up data from one of the schemas and update tables in the other with the rollups. I have created a user whose job it is to run this process, and this user has the proper permissions to all necessary objects in both schemas. I'll call this user MRBATCH.

Typically, any custom objects, whether they be additional staging tables, temp tables or stored procedures are saved in the FIRST schema. I tried to save this new stored procedure in the FIRST schema and compile it, but got "Insufficient priviliges" errors whenever the code in the stored procedure tried to access any tables in the SECOND schema. This surprised me a little bit because I had no plans to actually EXECUTE the stored procedure as FIRST, but I guess I can understand it from the point of view of, you ought be able to execute something you own.

So which would be be "better" (assuming there's any difference): Grant FIRST all of the rights it needs in SECOND and save the stored procedure in FIRST, or could I just save the stored procedure in the MRBATCH schema? I'm not sure which would be "better practice".

Is there a third option I'm overlooking perhaps?

Thanks
Joe