Wednesday, March 16, 2011

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."

6 comments:

Jeffrey T. Hare, CPA CISA CIA said...

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

Anonymous said...

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.

Unknown said...

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 said...

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.

Anonymous said...

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 said...

Anonymous,

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.