Thursday, January 05, 2006
DBA as User
In this thread over on AskTom, Tom makes a thought-provoking observation namely
DBAs should not be allowed to directly modify application tables, they have no
clue what the ramifications could be.
No more than they would update the data dictionary!
The argument is that DBAs, just like anyone else, should use the appropriate API or application to modify application tables. I rather like this approach in theory. I'm not sure however how well it would work in practice. The biggest problem for me is what to do with applications or APIs that are imperfect. These can leave corporate data in an incorrect or even perhaps illegal state and especially in the case of third party supplied applications waiting for the supplier to resolve a bug (and also provide a regression tested api call/application for fixing duff records) is probably not an option. This may of course also apply to internally developed applications!
I think therefore I'd argue that DBAs should never directly modify application data unless there is no available timely alternative and the incorrect data cannot be left as is and also that it is a core duty of a dba to understand as deeply as possible how the applications that interact with the data for which they are responsible are designed and implemented.
8 Comments
DBAs should not be allowed to directly modify application tables, they have no
clue what the ramifications could be.
No more than they would update the data dictionary!
The argument is that DBAs, just like anyone else, should use the appropriate API or application to modify application tables. I rather like this approach in theory. I'm not sure however how well it would work in practice. The biggest problem for me is what to do with applications or APIs that are imperfect. These can leave corporate data in an incorrect or even perhaps illegal state and especially in the case of third party supplied applications waiting for the supplier to resolve a bug (and also provide a regression tested api call/application for fixing duff records) is probably not an option. This may of course also apply to internally developed applications!
I think therefore I'd argue that DBAs should never directly modify application data unless there is no available timely alternative and the incorrect data cannot be left as is and also that it is a core duty of a dba to understand as deeply as possible how the applications that interact with the data for which they are responsible are designed and implemented.
8 Comments:
I think therefore I'd argue that DBAs should never directly modify application data unless there is no available timely alternative and the incorrect data cannot be left as is
Yeah, like the time we couldn't close the books in Oracle Financials because a bug in the GL put duplicate rows in one of the tables? Oracle Support's solution - apply patch XYZ (about 3 days worth of work). Two hours of surgery later (after consulting the data dictionary and metalink, of course) we closed the books. Right thing to do? Probably not. Did the books get closed? Yup.
Yeah, like the time we couldn't close the books in Oracle Financials because a bug in the GL put duplicate rows in one of the tables? Oracle Support's solution - apply patch XYZ (about 3 days worth of work). Two hours of surgery later (after consulting the data dictionary and metalink, of course) we closed the books. Right thing to do? Probably not. Did the books get closed? Yup.
Well, with things like SOX compliance and HIPAA this becomes even more important. Since auditing and such is typically/many times wrapped in application API's - doing what Jeff just described above could be... well, an issue.
But DBA's should not be "willy nilly" going around and modifying tables - it would be the exception to the exception (if an API exists) - so the thread on asktom you refer to does point out an issue - if the DBA's are frequently going around the api's and playing with the tables (as seemed to be indicated) there is a huge huge problem there.
It would be the path of last restort - and only with the help of the application team itself (eg: if support says "run this DML against the sys schema - that is one thing, if you read a web page that says to do it - DON'T)
But DBA's should not be "willy nilly" going around and modifying tables - it would be the exception to the exception (if an API exists) - so the thread on asktom you refer to does point out an issue - if the DBA's are frequently going around the api's and playing with the tables (as seemed to be indicated) there is a huge huge problem there.
It would be the path of last restort - and only with the help of the application team itself (eg: if support says "run this DML against the sys schema - that is one thing, if you read a web page that says to do it - DON'T)
This sounds way too familiar.
request:
"Can you delete this workfile that is stuck? We can't post it and can't delete it".
There's like 15 tables hanging off of it that have child records - that doesn't scare me as the RI clearly states the relationships. Its the possibility that RI is missing from some other table, and purging the parent just left some orphans out there ... somewhere in the app schema.
Here's where naming conventions for tables and columns really comes in handy.
I'd have to agree that dbas don't belong running ad hoc dml to cleanup app data - but what other choices are there? If one backs up all of the data that will be affected, spools the entire task complete with exact times that statements are run, switches the logfile beforehand and afterwards and notes which archived redo logs were generated - they're prepared to reverse the changes either via logminer or a PITR of a backup set.
An export of the target tables wouldn't hurt either.
If the changes are documented and you're prepared to back them out - I don't see a problem.
We're still better off having a dba perform such actions than a developer - at least the dba will be able to properly record and will be prepared to undo such actions. Certainly a developer more familiar with the app schema should be consulted on it, perhaps even "teamed up" with the dba to perform the repairs - if the dba lacks familiarity with the app.
Now, if you're talking about making changes to audit trails ...
request:
"Can you delete this workfile that is stuck? We can't post it and can't delete it".
There's like 15 tables hanging off of it that have child records - that doesn't scare me as the RI clearly states the relationships. Its the possibility that RI is missing from some other table, and purging the parent just left some orphans out there ... somewhere in the app schema.
Here's where naming conventions for tables and columns really comes in handy.
I'd have to agree that dbas don't belong running ad hoc dml to cleanup app data - but what other choices are there? If one backs up all of the data that will be affected, spools the entire task complete with exact times that statements are run, switches the logfile beforehand and afterwards and notes which archived redo logs were generated - they're prepared to reverse the changes either via logminer or a PITR of a backup set.
An export of the target tables wouldn't hurt either.
If the changes are documented and you're prepared to back them out - I don't see a problem.
We're still better off having a dba perform such actions than a developer - at least the dba will be able to properly record and will be prepared to undo such actions. Certainly a developer more familiar with the app schema should be consulted on it, perhaps even "teamed up" with the dba to perform the repairs - if the dba lacks familiarity with the app.
Now, if you're talking about making changes to audit trails ...
# posted by 
: 4:42 PM, January 05, 2006
: 4:42 PM, January 05, 2006
I would be very worried if any of my guys were asked to change data in a third-party application and they went ahead and did it!
Putting my 'out-sourcing provider' hat (and not my normal BI designer one) I would say that our responsibility to our customers extends to providing a well-managed and functional environment for their applications and not to providing an ad-hoc 'hack-your-data' service that almost certainly circumvents application audit. Fixing bugs and short-comings in applications is down to the application vendor. Of, course applying vendor supplied and authorised fixes could well be a DBA task. And even then all change control documentation needs to be completed
Putting my 'out-sourcing provider' hat (and not my normal BI designer one) I would say that our responsibility to our customers extends to providing a well-managed and functional environment for their applications and not to providing an ad-hoc 'hack-your-data' service that almost certainly circumvents application audit. Fixing bugs and short-comings in applications is down to the application vendor. Of, course applying vendor supplied and authorised fixes could well be a DBA task. And even then all change control documentation needs to be completed
" ... and not to providing an ad-hoc 'hack-your-data' service that almost certainly circumvents application audit"
That largely depends upon where the auditing is taking place, right? :)
That largely depends upon where the auditing is taking place, right? :)
# posted by : 12:43 AM, January 06, 2006
: 12:43 AM, January 06, 2006
Excuse the expression, but "It Depends."
Use the appropriate tool for the job. If you must have Sox, deal with it. If you have some old weird app, do what you must. Whatever happened to the axiom that a DBA should know his app?
Just before I read this blog-and-tom-y show (ouch, sorry), I updated a bunch of data with sql. Why? Because for that app with one-time mass updates, it makes the most sense to use sql when possible, and it's proprietary language when one would otherwise use PL/SQL, and sometimes there are just plain weird circumstances. But I'm elbow-deep in the guts of this implementation (and it is yucky), I wouldn't be so cavalier with a newbie. And of course, this is all RAD, so the requirements are jello. Bitter-tasting jello with unidentifiable rotten things that keep oozing out. But I make it work anyways.
xumjdsri
Use the appropriate tool for the job. If you must have Sox, deal with it. If you have some old weird app, do what you must. Whatever happened to the axiom that a DBA should know his app?
Just before I read this blog-and-tom-y show (ouch, sorry), I updated a bunch of data with sql. Why? Because for that app with one-time mass updates, it makes the most sense to use sql when possible, and it's proprietary language when one would otherwise use PL/SQL, and sometimes there are just plain weird circumstances. But I'm elbow-deep in the guts of this implementation (and it is yucky), I wouldn't be so cavalier with a newbie. And of course, this is all RAD, so the requirements are jello. Bitter-tasting jello with unidentifiable rotten things that keep oozing out. But I make it work anyways.
xumjdsri
The point about sox-compliance is very important nowadays. In our case, I simply cannot change any production data whatsoever, under any circumstances, without a proper request for it being signed and approved in the change control mechanism we've adopted. And for very good reasons:
1- Even though I know quite a bit about how, why and where data is kept in our application, I'm far from knowing it all having joined 3 years after the initial design. I'm really not qualified to make changes without proper control although of course I'd know how and why in most cases.
2- If the proverbial hit the fan, I'd love to have a proper-process ream of signed paperwork to back me and my changes: you never know what originated the need for the change in the first place, what if it was an illegal action to start with?
While I agree 100% that a dba should know a thing or two about their application environment, that is far from being a blanket approval to make changes to data: no way!
Now, test and development databases? That is a completely different kettle. Or isn't it?
Post a Comment
1- Even though I know quite a bit about how, why and where data is kept in our application, I'm far from knowing it all having joined 3 years after the initial design. I'm really not qualified to make changes without proper control although of course I'd know how and why in most cases.
2- If the proverbial hit the fan, I'd love to have a proper-process ream of signed paperwork to back me and my changes: you never know what originated the need for the change in the first place, what if it was an illegal action to start with?
While I agree 100% that a dba should know a thing or two about their application environment, that is far from being a blanket approval to make changes to data: no way!
Now, test and development databases? That is a completely different kettle. Or isn't it?
