sybase trigger to find the deleted querry

Question

hi ,

In my sybase server, some rows of a table (TBL_RESOURCE) are deleting from unknown source at random intervals.I tried a lot, but i cannot able to locate from which source/file/process this data is deleting.Is there any mechanism to locate this problem !! i need to find out who is deleting this rows.. how we can find out who is deleted it and from which file ... can we use triger to find the source of deletion ..?

Answer 1

yes, you can use triggers. see the sybase doc to see how to create delete triggers. In the trigger code, you can choose to log information (insert) like the current user, user id etc into a a table for auditing.

Answer 2

Something like this would do the trick.

create trigger deltrig 
on TBL_RESOURCE 
for delete 
as 
   BEGIN   
        insert  TBL_LOG (modifiedBy, modifiedDate)    
        select  user_name(), getdate() from deleted  
      END   

(you have to create the logging table TBL_LOG obviously)

Answer 3

Ok, so you do not have stored procs or transactions (which would allow the normal security: grant permissions to sprocs only; no direct updates to tables from users). therefore you have direct grants to users. Which means they can insert/update/delete from any client-side program, including Excel. Therefore it is quite possible that there is no code segment in the source code of the app, that deletes from the table. Having rows deleted at random moments is the nature of an online database; protecting it from unauthorised deletes is the requirement of the DBA.

I presume you have given permissions to specific people, not the whole world, and you are not sure exactly who is doing the nasty. The easiest is to simply ask the group.

The next easiest is to turn on Auditing for that table, or for the group (or role) of users permitted. But if you have not set up auditing, than can pose an obstacle.

Third, the trigger.

There are other methods, but they have a substantial overhead (22%), require substantial implementation labour, and you will have to wade through massive amounts of data.

If your environment is as insecure and unstable as it sounds, and the table is not supposed to be deleted from, simply revoke permissions on that (one) table, and wait until some one comes to you crying that their permissions have changed.

"This is assuming you don't have every single user logging in as DBA or some other [privileged] account."

Which of course is a very silly thing to do, asking for, pleading for disaster. As silly as granting delete on all tables to all users. I see where you are coming from.