We are currently working on a development system which also is our testing system .
We had created a number of Roles and assigned these Roles to users. These Roles in turn are Granted Object Priveleges on certain objects in the schema.
Now since this is a development system developers constantly drop, recreate, compile procedures/packages tables etc at the same time when testers are testing the application.
Whenever a table /procedure or any object is dropped the grant on that object to the role is also dropped. i understand that this is logical but it causes us a headache everytime it does so.
Is there any way to avoid doing this where i have to regrant the priveleges again and again.
You can if you think you can.