This content has been marked as final. Show 8 replies
Can you provide us more details about environment (is it test or production database)?
You have several different scenarios. Every user can have their own schema (user = schema in Oracle), you can authenticate users through OS (users are identified externally), then you can store users in OID or OVD (enterprise user) and you can proxy users connections through one "proxy user".
If you give some more information, I could give you some advice.
Thanks for replying.
I am looking at test and production databases.
We would have small teams working on projects. The objects associated with a project would usually be located in a single schema.
Are you talking about shared schemas with the enterprise users?
This approach I follow most of the time. I find it a reasonable balance of flexibility for developers and quality control for me.
For an application (data-owning) schema, I am OK with developers having that account's password in a development database, and working directly in it. They are responsible for keeping track of what changes they make, and coordinating with each other. I maintain a different password for the application schema in each environment. In other test environments, and in production, the password for that schema is not given to them, so any changes must be scripted and applied in a controlled manner, ensuring everything needed to deploy a change is in place early in the life cycle. In those controlled environments, developers' personal IDs may have read access to the data in the application schema, so they can research and troubleshoot as needed.
Thanks for replying Brian,
So would you move objects into the production schema from the test schema when they are ready for deployment then?
Edited by: user10989416 on 29-Nov-2012 07:30
Not necessarily "move ... from the test schema" (I could interpret that phrase several different ways). More like: repeat the same deployment process in production that we did in test.1 person found this helpful
I create DDL scripts (CREATE TABLE, ALTER TABLE, CREATE INDEX, INSERT INTO, etc...) that we run in the test environment to install the changes there. This not only applies the change to the test database, it tests that the script works as expected.
When it is time to deploy to production, that same (now tested) DDL and/or data change scripts are run in the production database.
When I say move, that's what I mean ie, the DBA would be responsible for running DDL to create objects in the live environment. Nobody accesses the live schemas to make changes but the DBA(s)?
Nobody accesses the live schemas to make changes but the DBA(s)?Correct. In our shop I actually hand off the scripts to a production-level DBA, since we have that additional separation of duties.
If your data is masked in development database, you can give developers all the privileges.1 person found this helpful
If they are working on production system, things are different. I agree with Bryan, that actions on production database should be done in a controlled manner (usually by a dba).
However, if developers need to access the data, I would create separate users for each of them and give them a least privileges.