February 7, 2002 at 4:43 pm
Hi can anyone help me?, someone delete all the data of the production databse, how can I see who did it?, how can I trace the things that were made today?.
Thanks for your help.
Ana
Ana
February 7, 2002 at 4:51 pm
There are tools to role back transactions in a databse as long as the transaction log has not been truncated and the data was deleted by a logged transaction (delete tblName is logged but truncate table tblName is not). I believe LogExplorer can do this, but I usually just keep good backups that I can go back thru as well as have audit triggers that write to another table so I can find the person who does this.
February 7, 2002 at 8:17 pm
LogExplorer will do that.
Triggers with audit tables work, but can produce a lot of overhead, but sometimes, though, they are the only way to go. Robert Marda has an article about using triggers for auditing:
http://www.sqlservercentral.com/columnists/rmarda/auditingtriggers.asp
As for your current situation, except for possibly Log Explorer, your options are limited. If you're auditing logins, and you know the approximate time, you can generate a list of culprits by looking at your logs. However, if there is a lot of user activity on the database, this may not help you any.
One way to prevent this in the future is give a user only the rights they need and nothing more. A good way to do this is to control all access through the use of stored procedures. Stored procedures define explicitly how users can access and manipulate the data. Give users access to the appropriate stored procedures, but not to the tables/views. If the stored procedure and the table/view have the same owner, SQL Server will assume the owner wants the user to have permissions required to complete the operations in the stored procedure, but only within the context of the stored procedure. Of course, this will mean that dynamic SQL queries are out as they are evaluated outside the context of the stored procedure, even if called from within one.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 4:41 am
Also keep in mind that stored procedures may require you giving at minimum read rights to the base tables. But this gives you far better control over what a user can do to your data. What I usually do is create a role for many of these admin task to help me keep hold. For instance stored procedures which allow access to delete data for specific needs may be given to a role of delprocs with 5 members and a role of readtbls has delprocs in it. Now since these are the only procedures that can perform deletes of any kind I can now narrow my list of culprits down.
February 8, 2002 at 7:19 am
quote:
Also keep in mind that stored procedures may require you giving at minimum read rights to the base tables.
No, that's one of the advantages of stored procedures so long as the ownership chain isn't broken. No rights have to be given to the tables. When dynamic SQL enters the picture, however, that's outside the scope of a stored procedure and that would be the only reason permissions would be needed on base tables. However, avoid dynamic SQL and have the same owner for tables, views, and stored procedures, and permissions only have to be given to the stored procedures.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 10:27 am
I agree with Brian and like his philosophy. If you want to do this in the past, you will need Log Explorer. For the future, a number of good suggestions given above.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply