I am reviewing current security policies in my organisation.
Developers currently log on to schemas using the same username and password and don't have individual accounts.
These accounts were created during an initially open policy but I have pared this back to least privilege.
Is it enough to use these single accounts with least privilege or should I set up individual user accounts for each developer and then use roles to manage collaboration? My understanding is that individual accounts best serve auditing but when I audit I can still see the osuser (we all have our own machines).
What way are other DBA's managing user accounts?
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.
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.
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)?
If your data is masked in development database, you can give developers all the privileges.
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.