What permissions should Developers have in the Dev database instance

Question

...and how should those permissions be granted. I work in a large IT dept with 70+ applications, some in SQL server and most in oracle. Each system has a prod, QA and Dev instance. We (I'm a developer) have readonly access to prod/qa, which I'm fine with. In SQL server development instances devs are given db_owner, which works totally fine. The debate is over what permissions I should have in the DEV oracle databases.

I recognize that best case would be to have each dev run their own instance on their workstation for development, but because of the size of the databases this has not been considered an option.

I'm also interested in HOW these permissions should be applied. In oracle permissions granted via a role are not active during PL/SQL execution so roles (even the "dba" role) are not useful. That leaves using a built in account (system) or creating dozens of users accross dozens of database and directly granting dozens of permissions to each. In my mind just letting the devs login as system is making a lot of sense, but our DBAs claim that's a bad idea.

Answer 1

We used to just give developers access to the application account. This works for small shops but rapidly gets out of hand as the number of developers increase.

Here's what we do now:

1. the Application has it's own account (aka schema).
2. Developers have their own accounts
3. Data resides in the application schema
4. We have an ant build script to build code into whatever schema you want.
   • code includes views, packages, objects etc..
   • the build script includes a step to run a stored procedure to grant explicit rights to developers to the application data
5. Developers make changes in their own schema
6. When happy they check that into subversion
7. The Application's dev schema is built from the new subversion build.
8. Developers can check out and rebuild their own environments.
9. DDL changes to table structures are done via the DBA
   • these can be scripted as well

This has the benefit of ensure any front end application is not broken by database developers constantly rebuilding everything.