0

All, I have a new application that uses an administration database (SQL Server 2008 R2) to enforce data structure constraints. It is important that the table structure of this database is maintained at all costs, and the biggest danger of the structure not being upheld is via a user going into the database and manually changing it.

In this situation, am I best constructing a checksum on the tables I wish to 'protect' and checking against this each time I do something within the application? Or, am I best doing all the 'protecting' server-side? That is, creating a TRIGGER say

CREATE TRIGGER EnsureIntegrity ON ProtectedTable
FOR INSERT, UPDATE, DELETE AS
BEGIN   
    IF (EXISTS (Some_Condidtion)
        RAISERROR(/*Some Error Message*/)
            ROLLBACK TRANSACTION
END

Any advice of the best way to achieve what I want is most appreciated.

MoonKnight
  • 103
  • 3

1 Answers1

1

The best way is to limit access to responsible parties. Anyone who's able to login to the SQL server and change things in the DB, should be on the hook for fixing it. Between that, and good backups, perhaps with replication, you should be set for most bad occurrences.

mfinni
  • 36,144
  • 4
  • 53
  • 86
  • Thanks for your response. My take on this is the same as yours; if someone is going to go in and edit the database they should be responsible - however, if this is not rectified when data is passed back to our company, and the datais found to have been corrupt for the above reason, then we will have a major problem on our hands as the data for over 100 sites floods in. It is a question of how to prevent bad behaviour. In this case I think `TRIGGER`s or a simple check sum could be the best way? – MoonKnight Apr 10 '12 at 14:13
  • @Killercam so you are not asking how to protect the schema, you are asking how to protect the application data in case of a schema change? – Joshua Drake Apr 10 '12 at 14:24
  • Whatever I do in terms of protection of the database, if the administrator decides to fiddle with the administration database (and he is good enough) he will. I just want something in place for the foolish user, who thinks it is a good idea to UPDATE, INSERT, DELETE or generically make changes to the structure of the admin DB. I am proposing a `TRIGGER` on those tables I wish to protect which SQL Server will administer. Alternatively, I can do this application-side with a checksum. Before each major call to the server/Admin DB run the checksum - if there's a missmatch, throw a fatal error? – MoonKnight Apr 10 '12 at 14:35
  • Perhaps you should, in your question, describe the complete process in question and what you're accomplishing with it. The bigger picture will help us give the best answer. – mfinni Apr 10 '12 at 15:30
  • Including *why* you'd have people changing the structure, when that's so critical to you. – mfinni Apr 10 '12 at 15:31