This discussion is archived
5 Replies Latest reply: Oct 12, 2012 9:54 AM by rp0428 RSS

How should devs interact with the db?

896971 Newbie
Currently Being Moderated
Good day. For some time my organization has worked as most small shops do: web and software developers log into the database and create their own views, procs, etc. However we have grown to a much larger staff and have begun the process of transitioning into a more "professional" shop.

In your experience, how should web/software developers (and their products) interact with the production database? In other words, should they send their views to the DBA who will create said views? Or should they not be connecting to views at all? Should they have their own schema on the production database (and this will be the one which holds their views)?

If you say that the developers should have absolutely no access to the production db at all, then how should their products interact with the database?

I appreciate any links, advice, etc.

Thank you.


EDIT: the developers products are about 75% read-only search/reporting apps and 25% data entry.

Edited by: 893968 on Oct 12, 2012 8:39 AM
  • 1. Re: How should devs interact with the db?
    sb92075 Guru
    Currently Being Moderated
    893968 wrote:
    Good day. For some time my organization has worked as most small shops do: web and software developers log into the database and create their own views, procs, etc. However we have grown to a much larger staff and have begun the process of transitioning into a more "professional" shop.

    In your experience, how should web/software developers (and their products) interact with the production database? In other words, should they send their views to the DBA who will create said views? Or should they not be connecting to views at all? Should they have their own schema on the production database (and this will be the one which holds their views)?

    If you say that the developers should have absolutely no access to the production db at all, then how should their products interact with the database?

    I appreciate any links, advice, etc.

    Thank you.


    EDIT: the developers products are about 75% read-only search/reporting apps and 25% data entry.

    Edited by: 893968 on Oct 12, 2012 8:39 AM
    what is OS name & version?

    post results from SQL below

    SELECT * FROM V$VERSION;


    which source code repository holds the source code?
  • 2. Re: How should devs interact with the db?
    EdStevens Guru
    Currently Being Moderated
    893968 wrote:
    Good day. For some time my organization has worked as most small shops do: web and software developers log into the database and create their own views, procs, etc. However we have grown to a much larger staff and have begun the process of transitioning into a more "professional" shop.

    In your experience, how should web/software developers (and their products) interact with the production database? In other words, should they send their views to the DBA who will create said views? Or should they not be connecting to views at all? Should they have their own schema on the production database (and this will be the one which holds their views)?

    If you say that the developers should have absolutely no access to the production db at all, then how should their products interact with the database?

    I appreciate any links, advice, etc.

    Thank you.


    EDIT: the developers products are about 75% read-only search/reporting apps and 25% data entry.

    Edited by: 893968 on Oct 12, 2012 8:39 AM
    Think about this. The earlier in the process that the DBA becomes a reviewer/gatekeeper, the earlier in the process you can eliminate bad design decisions. Bad design decisions can be any of the following and more:
    wrong data types
    badly normalized tables
    use of reserved or key words for object/table/column names
    reinventing wheel (especially common when needing a sequence number)
    object names that make no sense and fit no pattern.
    I'm sure others will chime in with more.

    The point being, if you allow developers the 'create' privilege at any level, they will create things that will become a headache for you further down the road. Let them write queries and procedures ... that's their job. But not tables, indexes, sequences, etc.

    And don't fall for "it's just dev, we can clean it up when we move to QA". No, they won't. At the time it moves to QA it will be "we've got too much invested in this, we can't afford to make those changes now." And regardless of what was agreed to six months earlier, management will side with the developers because they really can't afford to pay for the re-work that would have been avoided in the first place with proper division of responsibility.

    It will be a painful process to bring about. Someone will have to stand firm. And there will always be low-level complaints, but it still comes back to 'if you don't have time to do it right the first time, when will you find time to fix it later?" It's part of the DBA's job to make sure it gets done right the first time, and he will need management support to maintain it.
  • 3. Re: How should devs interact with the db?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    If you say that the developers should have absolutely no access to the production db at all, then how should their products interact with the database?
    - developers should have no or minimal access (for troubleshooting) to the production db.

    - traditionally 3 level landscape should be established.
    1 - development
    2 - test/QA
    3 - production

    - traditionally developers work in Dev Environmanet that includes isolated DB and develop code.
    - depending on a design, DB modeling, and development methodology developer may or may not create DB objects. In some methodologies/environments only a dedicated DB Modeler can model DB objects and a dedicated "developer" DBA creates them. Often this is same person.

    When developers developed code they do unit tests in their DB.
    After all development and unit testing done, the DB and code is deployed in Test/QA environment with using a deployment (installation) procedure.
    Here testers and QA do their tests and stress tests.

    This cycle may repeat number of times until it is decided to go production.

    Same (or very similar) deployment (installation) procedure is used to deploy the system into Prod Env.

    To add. There are many methodologies how to organize development and testing, from very conservative ones like "waterfall" to modern ones like "rapid". You in your org should choose what is suites for you best and follow that.

    But uncontrollably allowing developers create DB objects in Prod DB will create mess and havoc.
  • 4. Re: How should devs interact with the db?
    Mark D Powell Guru
    Currently Being Moderated
    The answer to what level of privileges the developers have depends in part what kind of developement work your employer does.

    Generally speaking you can grant wide object creation and modification privileges in a unit testing environment but need to greatly restrict privileges in production. Where and how tightly this is done depends on if you have separate databases for unit test => production; unit test => system test => production; or unit test => system test => quality control => production, and on how many DBA's your firm employes. Plus if the DBA are segmented in Infrastructure and Application support roles.

    If you are the only DBA then the developers need create privileges. If there are a dozen DBA's then the developers can submit object changes requests and get acceptable turn around.

    Generally speaking I think you want at least 3 databases and you want to control the promotion of object changes into system test and from there into production.


    IMHO -- Mark D Powell --
  • 5. Re: How should devs interact with the db?
    rp0428 Guru
    Currently Being Moderated
    >
    In your experience, how should web/software developers (and their products) interact with the production database?
    >
    If you want to be a 'more "professional" shop' then you should become a 'more "professional" company.

    Policies for the access to and control of production data should be made at the top of the company.

    The considerations involve legal, privacy, protection of intellectual property in addition to the technical issue you are asking about.

    The main question, for each of those areas, is: how much risk does the company want to take:
    1. That private data will fall into unauthorized hands.
    2. That data will be stolen, altered, deleted
    3. That the system will become nonresponsive, unavailable or unrecoverable
    4. That required requirements for retention of data will not be met

    Any of those things can subject the company to serious legal and financial liability. There are stringent federal, state and local requirements regarding certain HIPPA data and also data that falls into the SARBANES-OXLEY realm.

    Your company, CIO, or manager should first set the policy regarding the above issues and then set guidelines that are ensured to meet that policy.

Legend

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