Ad hoc SQL statements in Production environments by a developer - ERP Risk Advisors
post-template-default,single,single-post,postid-915,single-format-standard,ajax_fade,page_not_loaded,,qode_grid_1300,footer_responsive_adv,hide_top_bar_on_mobile_header,qode-child-theme-ver-1.0.0,qode-theme-ver-16.7,qode-theme-bridge,wpb-js-composer js-comp-ver-5.5.2,vc_responsive

Ad hoc SQL statements in Production environments by a developer

Ad hoc SQL statements in Production environments by a developer

I had a client email me today the following:
“Is it normal to have IT dev/support group run SQL queries in production to fix errant/stuck data in an Oracle EBS shop? As an example have an data issue that is listing the load weight as 0LB and have IT fix that through an SQL query directly in production?”

Here was my response:
“No. This is not normal for two reasons:

1. Developers should not have access to run scripts in Prod. Any scripts should be developed by the developers, executed by the DBAs in a non-prod environment, tested by an end user then executed by the DBAs in Prod.

2. May or may not be an issue… SQL scripts can either be supported or not supported by Oracle depending on how they are written. If they call a public API, generally they are supported. If there is no public API, Oracle should be providing the data fix script. Otherwise, they’ll not support it and the execution of it may void your support agreement.”

Jeffrey T. Hare

Jeffrey Hare, CPA CIA CISA is the founder and CEO of ERP Risk Advisors. His extensive background includes public accounting (including Big 4 experience), industry, and Oracle Applications consulting experience. Jeffrey has been working in the Oracle Applications space since 1998 with implementation, upgrade, and support experience. Jeffrey is a Certified Public Accountant (CPA), a Certified Information Systems Auditor (CISA), and a Certified Internal Auditor (CIA).

  • Jeffrey T. Hare, CPA CISA CIA
    Posted at 21:30h, 16 March

    And I might add… should be subject to DBA and/or developer peer review.

  • Anonymous
    Posted at 17:22h, 17 March

    At my company, we allow the Oracle developers and a few analysts read-only access to the production database. Only the DBAs get write access. Anything developed by the programmers is tested in a test instance first, signed off on by an end-user, and moved to production by one of the DBAs.

  • Anonymous
    Posted at 13:33h, 18 March

    2 comments – One ask your company how they would explain this action with documentation to an auditor. Secondly – Without documentation of the action that occurred, supposed 6 months from now, someone determines the action taken to make the change was incorrect. How do you get back to the original data? You don't have the code documented to reverse the changes, and with out some documentation of what was changed, you can be sure you have reset the same record IDs.

  • Gordon
    Posted at 13:33h, 18 March

    Not to mention the high risk of data corruption directly in your production instance and as this has potentially been done in an unsupported manner you may not be supported by Oracle.

  • Anonymous
    Posted at 13:28h, 03 November

    I hope its not too late to comment …
    In the environment I work in, we have plenty of customization to the EBS, so we get a more then a fair share of errors and records stuck in interface tables (GL) due to the not so perfect implementation… Until we fix these core design issues, DBAs and sometimes developers are going there and releasing these records one by one… As an Auditor I cannot see how I can mitigate that risk, and hence I just accept it as risky as it might be… any suggestions ??

  • Jeffrey T. Hare, CPA CISA CIA
    Posted at 14:32h, 03 November


    Some thoughts…

    First, the GL interface table has a form in 11i to fix the issues. Users should be correcting the issues themselves.

    Second, mitigating controls – users should review the journal entries after they are fixed – or may be approving them via the journal approval workflow. Apart from that you have all the financial close controls – budget to actual, flux analysis, reconciliations, etc.

    If you feel comfortable with the F/S close controls you are probably ok from a SOX perspective.

    One recommendation you may have is to make the users fix the issues with the GL interface JE correction form. I totally agree that the DBAs and/or developers should not be the ones to make the fixes.

Post A Comment