General discussion


SQL Direct Update Controls

By BertaJennings ·
I work for a medium sized finance company where we have about 10 users (DBA and Database Analysts) who are required to process direct database updates through SQL Query Analyzer in the production environment several times a day. We are currently running a logging application (Lumigent Entegra) on our production servers to store all transactional data. I am wondering if anyone has used similar technology to develop an audit process for their users who have full access to production databases? Specifically, I would like to be able to tie each driect insert, delete or update to a request. Just to make my job more fun, requests can come in the form of a Remedy ARS ticket OR a Siebel Service Request object. THANKS!!!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Now that is scary

by Tony Hopkinson In reply to SQL Direct Update Control ...

The mere concept turns my hair greyer. I'd be doing everything in my power to remove this requirement.
Triggers might give you a more user friendly log.
What sort of privileges have thse guys got, everytime I think about it, I think of more strenuous objections to this practice.
Can they use the no log options ?
Alter the database ?

This would have me doing a creditable impression of Ben in the film Sexy Beast "No No No No No NO!"

But when all's said and done all you will have is documentary proof that your entire system is wide open to human error at best. Go to be dealt with properly.

Collapse -


by BertaJennings In reply to Now that is scary

I agree that there should be a better way to manipulate the data!!!

There are very few users with full rights to the databases. Two database analysts who have 20 years between them at the company and the 5 or so DBA's. The direct updates are last resorts. The DBA's, typically, do not alter data one-off without the analysts being involved and the analysts cannot use the no log option. We recently bought software that stores the log data and includes a UI and access via Crystal reports.

We are at the point where everyone is aware of the pontential for pain that can come from misuse of these rights and have studied the potential solutions. But for now, the powers that be are okay with the current situation as long as we can come up with the above described solution for tracking changes to the data. I am sure that other organizations have a similar audit statagy, but I am having issues with the logistics of it all!

Collapse -

There has to be a split.

by Tony Hopkinson In reply to Thanks

All DBAs have the requisite authority on the database to adjust the data. A good DBA just won't, not without agreement, fully audited procedures, a recovery plan and any other CYA they can get hold of. Giving non dba's a wide ranging ability to change the content of your production database is only necessary because a safer way of implementing the business need has not yet been put in place.
As a dba/devloper with a team of shift techs I had this level of authority to deal with. The authority was required, could not be done away with. Logging was via triggers to a table with a whole range of reporting options. As it was triggers the same audit level was also available to in place system based functions. In theory I or any member of my team could have circumvented the logging and buried any error we made, we were simply professional enough not to.
Personally for the non dba types, I'd start looking at what they are doing and start chipping away at their needs removing the requirement for full change access.

Start now otherwise it will never happen. Your only problem will be running into some complete **** who get's their bang out of the ability to change absolutely anything, have them thrown out of the window. They are the ones most likely to mess up your data and then make the situation worse by trying to hide the fact that they caused it.

Collapse -

complete ani

by BertaJennings In reply to There has to be a split.

The **** factor is exactly what we are trying to measure. Right now, the trust level that the DBA's and the analysts hold is a HUGE asset, but no one sticks around forever!!!

Thanks for you suggestions. I would love a solution that would stop all direct updates altogether, alas, one is not in the immediate future. It is hard to pry project money away from managers for something lacking bells and whistles!!

Collapse -

Your best bet on that front

by Tony Hopkinson In reply to complete ani

is when someone no matter how well intentioned, makes a mistake and costs a lot of money recovering the database. There's your justification in one go. The logging/auditting facility may tell you what, who and when, it might not help you put it back together again though.

Update products, set product_status = 'Sold' where customer = 'Fred & Co' after all, is only easy to unwind, if no further chnages have been made to that data or all those further chnages have been logged as well. Then you've got any functions that would have been carried out because of the sold status. A restore might be an option , but then you've got any other work since the cockup to re do. A lot of this is complexity dependant, but you and your bosses are going to get malformed digits from crossing them unless your system is extremely simple.
Have a look at some recovery scenarios based on what they do and what transposing some digits in say an order number would do to you. The big 'laugh' is someone who forgets to put in a where clause on an update or delete.

The likelihood of an erroneous command to your production database is 100% by the way. If they don't do it often they'll forget a step, if they do it a lot, they'll miss a step.

Collapse -

Standards anyone?

by mjkessler In reply to Your best bet on that fro ...

Does anyone know of any industry standards governing this type of activity?

Related Discussions

Related Forums