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

One of the DBA's jobs is to manage user privileges. I don't think system is a good idea for a few reasons, not the least being the ability to drop an entire schema which I am sure you don't want. That being said, I think it is perfectly fine to grant all to your users and let the DBAs manage these permissions no matter how many dozens of accounts there may be. Most DBAs will have scripts they can use to manage these permissions anyway.

Listen to your DBAs, they generally know what they are talking about.

Answer 2

If it's just a dev instance; i'd have all users have individual accounts added to the admin role. That way you can still log activity on a per-user basis; but give the devs enough breathing room to do their thing.

Answer 3

I assume that there are a relatively small number of application accounts that own the actual objects. So one or more logical applications are comprised of tables owned by a particular Oracle user. This would not by SYSTEM or SYS, it would not be any of the accounts that Oracle the company delivers. It would be an account that your DBAs created. If you are familiar with the Oracle sample schemas, the HR user owns all the tables in the HR schema which comprise the back end for an HR application.

Starting from the principle of "the simplest thing that could possibly work," my first thought would be to see if the developers could log in directly to those application accounts. This isn't the securest possible configuration, and you are opening up the possibility that a developer accidentally or intentionally does some damage that may be difficult to track or easily resolve. But it can work reasonably well depending on the organization. Privilege management is trivial-- the application owner account already has all the privileges it needs most likely.

The next step up would be giving every developer a separate schema to develop in, presumably in conjunction with a load of public synonyms in the database and an absence of schema qualifiers in the application code, so that any object created in the developer's schema automatically overrides the shared version of that object. This provides much better isolation. Permissions are generally granted by either creating scripts that contain all the grants a developer needs or by creating a script that copies all the privileges from a "known good" account to the new account. Neither is particularly difficult to write-- you just have to make sure that all the developers end up with the same set of privileges, which is generally just another script that gets run when a new privilege is granted.

Answer 4

My group supports about 100 apps with about 20 of them having their own Oracle schema. We have gone down the road of every developer has the password to the schema and it is convenient. However, in hindsight I would recommend that each developer use their own Oracle account to develop. The main reason is auditing.

Answer 5

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.

Is there a way to deal with this, maybe by reducing the amount of data in your personal copies? This seems like the ideal solution, since it would allow you to make any changes you need. Then you could submit them to the DBA when you're ready, and have him update the shared development server.

Answer 6

If you are developing stored PL/SQL objects, then the schema owning those objects needs, as you mentioned, explicit grants on the objects used. If you have a single 'data' schema but are developing code in your own individual schemas then you should get the ability to grant access on the data schema objects to your development schemas. Normally I'd expect username/password for the data schema.

In regards to system privileges (eg CREATE), I'd expect CREATE TABLE, TYPE, VIEW, PROCEDURE TRIGGER, SYNONYM. Others may be appropriate (eg CONTEXT) depending on what you do. The DBA may rule out CREATE DIRECTORY as that could be damaging if mis-used. Ditto for privileges with ANY in them (eg SELECT ANY TABLE, DELETE ANY TABLE)

For performance tuning / system monitoring, on a dev database SELECT_CATALOG_ROLE is good. If the DBA is risk-averse, you may have to negotiate grants on individual views. Go through the REFERENCE guide for your version and ask for any you may use.

Answer 7

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.