SQL Server Performance

Use SQL Server Triggers to Track Unauthorized Data

Discussion in 'Forum Announcements' started by gaurav_bindlish, Jul 4, 2003.

  1. gaurav_bindlish New Member

    For all the pople that need to track data changes in their databases.<br /><br /<a target="_blank" href=http://www.devx.com/dbzone/Article/7939>http://www.devx.com/dbzone/Article/7939</a><br /><br />Cheers [<img src='/community/emoticons/emotion-1.gif' alt=':)' />],<br /><br /><br />Gaurav
  2. bambola New Member

    While triggers can sometimes be a good solution, I would try to stay away from them when possible, for both security and performance reasons. There are other ways to prevent unauthorized changes on a database. One of them is making sure all changes on database can only be done via stored procedures. Stored procedures will be given permissions to the different users/roles as needed. I would not give a direct INSERT UPDATE or DELETE permissions on tables. In rare cases when I have no choice but using sp_executesql, I'd give a SELECT permission and probably use a view for this task.

    Bambola.
  3. Chappy New Member

    I agree with bambola; but its only in an ideal world where we can always design applications from the ground up to be secure and restrict access via stored procs.

    Quite often though, this isnt possible, due to legacy code which accesses tables directly (and isnt cost effective to change to store procs), or even third party tools over which you have no control. So with this in mind, the authors scenario could be useful as an audit to diagnose problems, although in this scenario I would probably resort to profiler until specific problems had been found and eliminated.

  4. gaurav_bindlish New Member

    Apart from security, I consider using triggers for auditing as design requirement. The case that I conside appropriate is like financial transations. We had a database where all the data had to be audited. The reason is, sometimes there were requirements from the clients to have an audit on a particular security (share / bond). Now in this case they wanted a list of all the transctions that have happened on the master table. The only way to track the changes in the amount over the day was to maintain an audit on the same so that I can have the data ready at anu point of time. The SP's can be made intelligent in this case but still I think triggers are always the most reliable option when it comes to auditing the data.

    Gaurav
  5. bradmcgehee New Member

    Another option is to use the new audit tool from Lumigent, called Integra. It uses the transaction log, not triggers, for auditing, greating reducing overhead.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

Share This Page