Who has deleted my record???

[saravanakumar]

Hi Everybody,
we have an application developed using Progress 4GL. There is a DB which contains almost 10 to 15 tables. Consider one guy from our team has deleted a single record from a table say names as "x".

Is there a way to identify who has deleted the record?

Thanks in advance.

 

[StuartT]

Not if he was a developer who wrote some code to do the delete.
If it was through a user screen, then it depends if you are auditing that particular table or not.

 

[saravanakumar]

You are right. Its not through User Interface, if so as you mentioned we have audit records to identify. But it was done through code. I am curious to know if we have some other option to find who deleted that record.

 

[4GLNewbie]

You mean that asking who did this does not produce results? Very unusual.

I dont know of any method to discover this in sql server, oracle, or either mysql ( my knowledge is not so great, maybe i am wrong ): if you dont have a trigger or a procedure or something that writes ( i.e. ) some information about the session on a specific table when a specific event occurs, i think you'll never know.

If this is a progress db ( i m not sure ), i dont know if it is possible.

 

[LarryD]

You should be able to easily accomplish this using a database trigger for the 'delete' event. If you allow the trigger to be overwritten (as in a mass delete of some sort), then in the .p that executes the mass delete you need to add the DISABLE TRIGGERS statement.

Go into the database schema editor, modify table, then click on triggers. From there, you want to enter a file name (usually with the extent .w) for the trigger code, selecting whether overridable or not, etc.

The code will start with this:

TRIGGER PROCEDURE FOR Delete OF mytable.

All you need to do at that point is to add the audit logging you want.

e.g.

TRIGGER PROCEDURE FOR Delete OF mytable.

/* logging code goes here
USERID("DICTDB") = user who is doing delete
table record is already in the buffer, no find is required


*/

 

[StuartT]

You should be able to easily accomplish this using a database trigger for the 'delete' event. If you allow the trigger to be overwritten (as in a mass delete of some sort), then in the .p that executes the mass delete you need to add the DISABLE TRIGGERS statement.

Go into the database schema editor, modify table, then click on triggers. From there, you want to enter a file name (usually with the extent .w) for the trigger code, selecting whether overridable or not, etc.

The code will start with this:

TRIGGER PROCEDURE FOR Delete OF mytable.

All you need to do at that point is to add the audit logging you want.

e.g.

TRIGGER PROCEDURE FOR Delete OF mytable.

/* logging code goes here
USERID("DICTDB") = user who is doing delete
table record is already in the buffer, no find is required


*/

That's fine and dandy for finding out who deletes future records, but is no help at all for a delete that has already happened if there is no trigger already there or if the programmer disabled the trigger.

 

[LarryD]

I thought that may have been obvious, but then again perhaps I misunderstood the question.

Your point is valid for already deleted/updated/whatever without the trigger in place or disabling it. And we won't even get into the editing of the audit logs... ;-)

 

[saravanakumar]

Thanks for all your responses. I would have been happy if there was an automatic audit (say by means of hidden table). Why not a hidden AUDIT table? Let's see.

 

[Casper]

There is an automatic audit. Auditing was introduced in 10.1A. If you enable this then you can monitor everything you like.
Look in the documentation how to enable auditing on an OpenEdge database: http://communities.progress.com/pcom/docs/DOC-16272

Casper.

 

[TomBascom]

As Casper says if you have OE auditing enabled for the table in question then the answer is "yes".

Likewise if you had the foresight to code your own auditing routines.

Otherwise there is no way to retroactively determine who deleted a record.

 

[saravanakumar]

Thanks Tom and Casper. Unfortunately the version of our application is 10.0B, hoping that it will be upgraded to 10.1C at the earliest (...this october).

 

[TomBascom]

Don't get your hopes up too high. Enabling auditing should not be done lightly. You really need to carefully think through what really needs to be audited and how you are going to manage the subsequent flood of data.

There are a number of good presentations from previous Exchange conferences on PSDN as well as white papers and, of course, the documentation. I strongly suggest that you read them before you decide to pursue this auditing.

 

[saravanakumar]

Sure Tom. Will do so. Thanks a lot.