Post reply

Ideas for stopping customers meddling directly in the tables?

  • November 11, 2013 at 6:06 am

    #300127

    We have some issues with customers meddling directly in the tables for our application.

    Has anyone any ideas for monitoring or preventing this?

    I'm not talking about for malicious fraud reasons, more messing with indexes adding triggers etc...

    The issue is by the time we've worked out they've done this our support people have already wasted lots of time.

    If these people have sa access to the database there's not much that can be done.

    I thought of turning the auditing of SCHEMA_OBJECT_CHANGE_GROUP on so that any table definition changes get audited.

    Does anyone see anything wrong with this?

    Any ideas much appreciated.

  • November 11, 2013 at 7:54 am

    #1665485

    Auditing is a good place to start from.

    Another way could be revoking privileges, but very difficult when the database is at the client's site.

    -- Gianluca Sartori

  • November 11, 2013 at 9:34 am

    #1665517

    at my last job we had a similar situation, after one core issue boiled down to the client creating poorly written triggers, which broke the application's logic. against the user license and all that, but sometimes they do crap like that to satisfy their own reporting requirements, i know.

    I ended up creating an executable for the client to run for support purposes, which created an application "health" report, and they had to run that report for any db or application related issues/tickets.

    that report compared the database schema to a snapshot saved in xml, and reported any table/column/proc/trigger differences compared to that xml.

    it saved us loads and loads of time, and also let us narrow down issues that were not schema related, but more data-or-application code related instead.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 11, 2013 at 9:44 am

    #1665521

    James_UK_Dev (11/11/2013)

    We have some issues with customers meddling directly in the tables for our application.

    Has anyone any ideas for monitoring or preventing this?

    I'm not talking about for malicious fraud reasons, more messing with indexes adding triggers etc...

    The issue is by the time we've worked out they've done this our support people have already wasted lots of time.

    If these people have sa access to the database there's not much that can be done.

    I thought of turning the auditing of SCHEMA_OBJECT_CHANGE_GROUP on so that any table definition changes get audited.

    Does anyone see anything wrong with this?

    Any ideas much appreciated.

    Your contract with the customer should stipulate that any problems caused by customer changes will be billed for customer support.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 12, 2013 at 2:45 am

    #1665660

    Thanks.

    The idea of a utility to check the database has come up here, but has always been put off.

    I think we'll have to look at that again.

    There probably is something in the contract, but no ones ever enforced it as we always end up doing it for good will.

    Maybe if you charge too high rates customers go somewhere cheaper to get the work done and you end up clearing up the mess?

  • November 12, 2013 at 3:17 am

    #1665666

    James_UK_Dev (11/12/2013)

    There probably is something in the contract, but no ones ever enforced it as we always end up doing it for good will.

    That's probably something you need to address with your management.

    Maybe if you charge too high rates customers go somewhere cheaper to get the work done and you end up clearing up the mess?

    You should have it clearly stated in the contract (and enforced), that if they go somewhere cheaper to get the work done, then the cleaning up of any mess is either not your problem and has voided any support agreements they had (Microsoft does that) or will be billed at a nice high rate.

    One easy way to tell something has changed (without knowing what) is to have your app compute a CHECKSUM_AGG over the catalog views upon startup, log date and time of any changes detected and put that into a log file that your support people ask for before helping out.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply