This discussion is archived
8 Replies Latest reply: Nov 29, 2012 8:07 AM by Zoran Pavlovic RSS

Managing User Accounts

R Merrick Newbie
Currently Being Moderated
Hi,

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?
  • 1. Re: Managing User Accounts
    Zoran Pavlovic Explorer
    Currently Being Moderated
    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.


    Zoran
  • 2. Re: Managing User Accounts
    R Merrick Newbie
    Currently Being Moderated
    hi Zoran,

    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?
  • 3. Re: Managing User Accounts
    Brian Bontrager Expert
    Currently Being Moderated
    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.
  • 4. Re: Managing User Accounts
    R Merrick Newbie
    Currently Being Moderated
    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
  • 5. Re: Managing User Accounts
    Brian Bontrager Expert
    Currently Being Moderated
    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.
  • 6. Re: Managing User Accounts
    R Merrick Newbie
    Currently Being Moderated
    Thanks Brian,

    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)?
  • 7. Re: Managing User Accounts
    Brian Bontrager Expert
    Currently Being Moderated
    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.
  • 8. Re: Managing User Accounts
    Zoran Pavlovic Explorer
    Currently Being Moderated
    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.


    Zoran

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points