Kbase P18098: How to implement an AUDIT within the Progress DB and 4GL?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
7/30/2007 |
|
Status: Verified
GOAL:
How to implement an AUDIT within the Progress DB and 4GL?
GOAL:
Audit the activity of Users.
GOAL:
How to Audit users activity
FACT(s) (Environment):
Progress 9.1x
OpenEdge 10.0x
FIX:
The following is an example of a table used for auditing the activity of users within an application. The example below contains both a .df file for the audit table and the 4GL code needed to populate it.
The following .df file contains the schema definitions for the 'LOG' table
ADD TABLE "LOG"
DESCRIPTION "Table LOG. Keep all the operations of a DB."
DUMP-NAME "log"
ADD FIELD "TableName" OF "LOG" AS character
FORMAT "X(15)"
INITIAL ""
POSITION 2
SQL-WIDTH 30
ORDER 10
ADD FIELD "Tablekey" OF "LOG" AS character
FORMAT "X(16)"
INITIAL ""
POSITION 3
SQL-WIDTH 32
ORDER 20
ADD FIELD "ldate" OF "LOG" AS date
FORMAT "99/99/9999"
INITIAL ?
POSITION 5
SQL-WIDTH 4
ORDER 40
ADD FIELD "origin" OF "LOG" AS character
FORMAT "X(3)"
INITIAL ""
POSITION 6
SQL-WIDTH 6
ORDER 50
ADD FIELD "_Userid" OF "LOG" AS character
FORMAT "x(12)"
INITIAL "?"
LABEL "Userid"
POSITION 7
SQL-WIDTH 24
LENGTH 0
ORDER 60
ADD FIELD "operation" OF "LOG" AS character
DESCRIPTION "C - Creation, U - Update, D - Delete"
FORMAT "X"
INITIAL ""
POSITION 8
SQL-WIDTH 2
ORDER 35
ADD FIELD "ltime" OF "LOG" AS character
FORMAT "X(8)"
INITIAL ""
POSITION 9
SQL-WIDTH 16
ORDER 45
ADD FIELD "field_raw" OF "LOG" AS raw
FORMAT "X(8)"
INITIAL ""
POSITION 10
SQL-WIDTH 16
ORDER 70
ADD INDEX "i_date_time" ON "LOG"
PRIMARY
INDEX-FIELD "ldate" ASCENDING
INDEX-FIELD "ltime" ASCENDING
ADD INDEX "i_log" ON "LOG"
INDEX-FIELD "TableName" ASCENDING
INDEX-FIELD "Tablekey" ASCENDING
INDEX-FIELD "ldate" ASCENDING
INDEX-FIELD "ltime" ASCENDING
Trigger code for all the tables which you want to audit
/* The following lines should be put in the DB trigger for CREATE, UPDATE or DELETE */
DO WHILE PROGRAM-NAME(w_n) <> ?:
ASSIGN w_n = w_n + 1.
END.
ASSIGN w_n = w_n - 1./* This will be the trigger for Create */
CREATE LOG.
/*
In the following code replace 'Table_Name' with the actual table name being Audited.
*/
ASSIGN LOG.TableName = "Table_Name"
LOG.TableKey = STRING(Table_Name, Index Name)
LOG.operation = /*Will depend in the trigger you are C - Creation,
U - Update, D - Delete*/
LOG.ldate = TODAY
LOG.ltime = STRING(TIME, "HH:MM:SS")
LOG.origin = PROGRAM-NAME(w_n)
LOG._userid = USERID.
RAW-TRANSFER Table_Name TO Log.field_raw.
END.
If you need to recover any record:
RAW-TRANSFER Log.Field_raw TO Table_Name.