July 24, 2008 at 8:44 am
I am designing a backup script for a third party program and am adding some auditing capabilities to a table, let's say backup_log. This table has a timestamp, database being backed up, location and filename, etc.
I also have a procedure called purge_backup_log which deletes any entries older than a certain amount of time (do I really need to go back to a whole year's worth of backups?)
I would like to maintain the integrity of this log as much as possible. I want to create an INSTEAD OF Trigger that prevents any procedure/user from deleting from the backup log with the sole exception of the purge_backup_log procedure.
Within the INSTEAD OF trigger, is there a way to check the id/name of the procedure that called the delete on my backup log? The (pseudo)-code would go something like this:
CREATE TRIGGER dbo.trig_backup_log
ON msdb.dbo.backup_log
INSTEAD OF DELETE
AS
BEGIN
Step1: Check ID OR user calling the delete action
Step2: If ID OR user is authorized (or the purge_backup_log called this delete action)
then
delete from backup_log where timestamp in (select timestamp from deleted)
else
send email alert to Database Admin (or log attempt into audit log)
END
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 24, 2008 at 9:05 am
I don't think that there is a way to tell what SQL was executed to cause a trigger to fire. The best way to maintain that table is to not give anyone delete rights to it. As always the sysadmin will, but the sysadmin can disable the trigger so you have to trust them at some point.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2008 at 9:10 am
Hmmm...thanks. I guess that would be the best way in general. While security can sometimes be complicated, don't make it more complicated than it has to. So let the server work for me before I decide to use triggers.
Thanks.
Gaby Abed
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply