Kbase P18048: How to find out who deleted a record from a table ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/03/2011 |
|
Status: Verified
GOAL:
How to find out who deleted a record from a table ?
GOAL:
How to find the DBKEY associated with the RECID of a database record ?
GOAL:
How to find the user associated with a record delete from the after-image notes ?
FACT(s) (Environment):
Progress 9.1x
OpenEdge 10.x
UNIX
Windows
All Supported Operating Systems
FIX:
The following conditions must exist:
- After Imaging must have been enabled at the time of the record deletion and the AI files must still exist.
- Which table(s) the record(s) were deleted from.
- Which Storage Area the table(s) are contained within.
- The recid(s) of the deleted record(s) or the blocknumber.
If all of the above information is known, the following steps can be performed to determine who deleted the suspect record(s).
1) Review the database configuration file "dbname.st" to confirm:
a. The correct records per block (RPB) for the storage area the table belongs to.
b. The Storage Area Number corresponding to the Storage Area Name
$ prostrct list <dbname> dbname.st
2a) Calculate the DBKEY from the RECID using the RPB
If the recid is known, divide this by the records per block and take the 'whole number and multiply by rpb again.
EXAMPLE:
RECID = 1366
RPB = 32
1366 / 32 = 42.6875
42 x 32 = 1344 = The first DBKEY which lives in the block that was modified.
2b) Calculate the DBKEY from the BLOCKNUMBER using the RPB
If the BlockNumber is known, multiply this by the records per block
EXAMPLE:
BLOCKNUMBER = 42
RPB = 32
DBKEY = 42 x 32 = 1344
3) Parse the after-image files that were created between the time the record was inplace to the time it no longer exists
3a) First extract the ai file transaction notes
$ rfutil <dbname> -C aimage scan verbose -a <dbname>.a<n> > AIscan<n>.txt"
3b) Parse the resulting AIscan.txt file for the DBKEY and corresponding Storage Area number.
NOTE: the format of this file has changed over releases, the following are provided only as examples
EXAMPLE: (Progress 9)
...
Trid: 329 Fri Jan 24 10:46:16 2009. (2598)
User Id: fredthebad. (2599) <------------------- This displays the userID.
...
...
code = RL_RMDEL (1637) <-------------------- The "RL_RMDEL" shows that a record delete was performed.
transaction index = 329 (1638)
area = 9 dbkey = 1344 update counter = 11 (9016) <---- This line shows the storage area number and dbkey
...
...
EXAMPLE: (OpenEdge 10)
...
Trid: 3863 Tue Feb 02 15:42:15 2010. (2598)
Trid: 3863 User Id: fredthebad (12531) <------- This displays the userID running the transaction responsible for the delete operation.
...
...
Trid: 3863 code = RL_RMDEL version = 2 (12528) <------- The "RL_RMDEL" shows that a delete was performed.
Trid: 3863 area = 9 dbkey = 1344 update counter = 83 (12529) <---- This line shows the storage area number and dbkey of the database BLOCK where the recid is placed
Some of the relevant codes from the scan are:
CX* are indexes,
RM* are data records, so these can be used to determine the table.
The Trid can be used to track this transaction's activity across ai files (until it's reused after a "RL_TEND" of course ...)
code = RL_TEND /* log+phy transaction end*/
code = RL_TBGN /* log+phy transaction begin*/
code = RL_RMDEL /* delete a record (fragment) */
code = RL_RMCR /* create a record (fragment) */
code = RL_RMCHG /* change a record (fragment */
code = RL_IXDEL /* logical index entry deletion */
code = RL_CXREM /* remove index entry --compressed index entry note*/
code = RL_CXREM /* remove index entry --compressed index entry note*/
code = RL_CXINS /* insert index entry --compressed index entry note*/
code = RL_BKREPL /* generic string replace */
code = RL_BKFRM /* master block changes for frrem */
code = RL_BKFRB /* database block changes for. frrem*/
code = RL_BKFAM /* master block changes for fradd */
code = RL_BKFAB /* database block changes for fradd */.